How to return a DataTable from WCF service

In this article we will see how to return data from a SQL Server table as a DataTable object using WCF service and consume it in a Console application

Introduction:

In this article first we will create a simple WCF service that will return data of the Employees table as a DataTable object. Then we will consume this WCF service in a Console application

Step 1:

Create a new WCF Service Application named EmployeeSercice

Step 2:

In IService1.cs, add ServiceContract and OperationContract:

[ServiceContract]
public interface IService1
{
     [OperationContract]
     Employee GetEmployee();
}

Then add DataContract and DataMember:

[DataContract]
public class Employee
{
     [DataMember]
     public DataTable EmployeeTable
     {
          get;
          set;
     }
}

In this article I am leaving binding to default, wsHttpBinding

Step 3:

Add following code in Service1.svc.cs file inside Service1 class:

public class Service1 : IService1
{
     string ConString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
     SqlConnection con;
     SqlCommand cmd;
     SqlDataAdapter sda;
     DataTable dt;
     Employee emp = new Employee();

     public Employee GetEmployee()
     {
          using (con = new SqlConnection(ConString))
          {
               cmd = new SqlCommand("SELECT EmployeeID, FirstName, LastName FROM Employees", con);
               sda = new SqlDataAdapter(cmd);
               dt = new DataTable("Paging");
               sda.Fill(dt);
               emp.EmployeeTable = dt;
               return emp;
          }
     }
}

We have created our WCF service that will return Employees data as a DataTable. Now its time to consume this service in a Console application.

Step 4:

Add a new Console Application named EmployeeServiceClient by right click on the Solution Explorer and selecting Add -> New Project

Step 5:

Add Service Reference to the WCF service in the Console Application using Add Service Reference dialog box.

Right Click on Console Application and select Add Service Reference

Step 6:

Write following code in the Main function of the Console Application:

static void Main(string[] args)
{
     ServiceReference1.Service1Client MyClient = new ServiceReference1.Service1Client();
     ServiceReference1.Employee emp = new ServiceReference1.Employee();
     emp = MyClient.GetEmployee();
     DataTable dt = new DataTable();
     dt = emp.EmployeeTable;
     Console.WriteLine(" EmpID".PadRight(10)
     +"FirstName".PadRight(10)
     +"LastName".PadRight(10));
     Console.WriteLine("---------------------------------------");
     for (int i = 1; i < dt.Rows.Count; i++)
     {
          Console.WriteLine(dt.Rows[i][0].ToString().PadRight(10)+
          dt.Rows[i][1].ToString().PadRight(10) +
          dt.Rows[i][2].ToString().PadRight(10));
     }
     Console.WriteLine(dt.Rows.Count.ToString());
     Console.ReadKey();
}

Step 7:

In App.Config, Change maxReceivedMessageSize attribute value to maximum inside Binding element to process large tables

maxReceivedMessageSize=”2147483647″

Output:

One thought on “How to return a DataTable from WCF service

Leave a Reply

Your email address will not be published. Required fields are marked *