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
 
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
 
wcf1.gif 
 
Step 2: In IService1.cs, add ServiceContract and OperationContract:
  1. [ServiceContract]    
  2. public interface IService1    
  3. {    
  4.     [OperationContract]    
  5.     Employee GetEmployee();    
  6. }  
Then add DataContract and DataMember:
  1. [DataContract]  
  2. public class Employee  
  3. {  
  4.     [DataMember]  
  5.     public DataTable EmployeeTable  
  6.     {  
  7.         get;  
  8.         set;  
  9.     }  
  10. }  
In this article, I am leaving binding to the default, wsHttpBinding.
 
Step 3: Add the following code in the Service1.svc.cs file inside Service1 class:
  1. public class Service1 : IService1  
  2. {  
  3.     string ConString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;  
  4.     SqlConnection con;  
  5.     SqlCommand cmd;  
  6.     SqlDataAdapter sda;  
  7.     DataTable dt;  
  8.     Employee emp = new Employee();   
  9.     public Employee GetEmployee()  
  10.     {               
  11.         using (con = new SqlConnection(ConString))  
  12.         {  
  13.             cmd = new SqlCommand("SELECT EmployeeID, FirstName, LastName FROM Employees", con);  
  14.             sda = new SqlDataAdapter(cmd);  
  15.             dt = new DataTable("Paging");  
  16.             sda.Fill(dt);  
  17.             emp.EmployeeTable = dt;  
  18.             return emp;  
  19.         }          
  20.     }  
  21. }  
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-clicking on the Solution Explorer and selecting Add -> New Project.
 
Step 5: Add a Service Reference to the WCF service in the Console Application using Add Service Reference dialog box.
 
Right-click on the Console Application and select Add Service Reference:
 
wcf2.gif 
 
Step 6: Write following code in the Main function of the Console Application:
  1. static void Main(string[] args)  
  2. {  
  3.     ServiceReference1.Service1Client MyClient =   
  4.         new ServiceReference1.Service1Client();  
  5.     ServiceReference1.Employee emp =   
  6.         new ServiceReference1.Employee();  
  7.     emp = MyClient.GetEmployee();  
  8.     DataTable dt = new DataTable();  
  9.     dt = emp.EmployeeTable;  
  10.     Console.WriteLine(" EmpID".PadRight(10)   
  11.         +"FirstName".PadRight(10)  
  12.         +"LastName".PadRight(10));  
  13.     Console.WriteLine("---------------------------------------");  
  14.     for (int i = 1; i < dt.Rows.Count; i++)  
  15.     {  
  16.         Console.WriteLine(dt.Rows[i][0].ToString().PadRight(10)+   
  17.             dt.Rows[i][1].ToString().PadRight(10) +   
  18.             dt.Rows[i][2].ToString().PadRight(10));  
  19.     }   
  20.     Console.WriteLine(dt.Rows.Count.ToString());  
  21.     Console.ReadKey();  
  22. }  
Step 7: In App.Config, change the maxReceivedMessageSize attribute value to maximum inside the Binding element to handle large tables.
 
maxReceivedMessageSize="2147483647"
 
Output
 
wcf3.gif