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