In this article I will explain with an example, how to return DataTable from WCF Service (SVC) in ASP.Net using C# and VB.Net.
The WCF Service will return a DataTable which will be later used to populate a GridView control in ASP.Net using C# and VB.Net.
Database
I have made use of the following table Customers with the schema as follows.
![Return DataTable from WCF Service (SVC) in ASP.Net using C# and VB.Net]()
I have already inserted few records in the table.
![Return DataTable from WCF Service (SVC) in ASP.Net using C# and VB.Net]()
Note: You can download the database table SQL by clicking the download link below.
Adding Service to Project
The very first thing you need to do is add a WCF service to your project by clicking on Add New Items as shown below.
![Return DataTable from WCF Service (SVC) in ASP.Net using C# and VB.Net]()
Building the WCF Service
The next task is to add the OperationContract method to the WCF Service that will fetch the data records from the database and will act as a source of data for the GridView.
Namespaces
You will need to import the following namespaces.
C#
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.ServiceModel;
using System.Runtime.Serialization;
VB.Net
Imports System.Data
Imports System.Configuration
Imports System.Data.SqlClient
Imports System.ServiceModel
Imports System.Runtime.Serialization
IService Interface
The IService Interface class has a DataContract class named CustomerData which contains a DataTable Property CustomersTable which will be used to send the data from the WCF Service to the Web Application.
The IService Interface has a method GetCustomers which is decorated with OperationContract attribute.
C#
[ServiceContract]
public interface IService
{
[OperationContract]
CustomerData GetCustomers();
}
[DataContract]
public class CustomerData
{
public CustomerData()
{
this.CustomersTable = new DataTable("CustomersData");
}
[DataMember]
public DataTable CustomersTable { get; set; }
}
VB.Net
<ServiceContract()> _
Public Interface IService
<OperationContract()> _
Function GetCustomers() As CustomerData
End Interface
<DataContract()> _
Public Class CustomerData
Public Sub New()
Me.CustomersTable = New DataTable("CustomersData")
End Sub
<DataMember()> _
Public Property CustomersTable() As DataTable
Get
Return m_CustomersTable
End Get
Set(value As DataTable)
m_CustomersTable = value
End Set
End Property
Private m_CustomersTable As DataTable
End Class
Service Class
The IService Interface has been implemented in a class named Service which contains the definition of the GetCustomers method.
This method gets the records from the Customers table and populates the DataTable of the CustomerData class object which finally is returned to the client accessing the WCF service.
C#
public class Service : IService
{
public CustomerData GetCustomers()
{
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("SELECT CustomerId, Name, Country FROM Customers"))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
CustomerData customers = new CustomerData();
sda.Fill(customers.CustomersTable);
return customers;
}
}
}
}
}
}
VB.Net
Public Class Service
Implements IService
Public Function GetCustomers() As CustomerData Implements IService.GetCustomers
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As New SqlConnection(constr)
Using cmd As New SqlCommand("SELECT CustomerId, Name, Country FROM Customers")
Using sda As New SqlDataAdapter()
cmd.Connection = con
sda.SelectCommand = cmd
Using dt As New DataTable()
Dim customers As New CustomerData()
sda.Fill(customers.CustomersTable)
Return customers
End Using
End Using
End Using
End Using
End Function
End Class
Adding Reference of WCF Service
The next task is to add the Service Reference of the WCF service to the project so that it can be used to populate the GridView.
1. Right Click the Project in the Solution Explorer and click Add Service Reference from the context menu.
![Return DataTable from WCF Service (SVC) in ASP.Net using C# and VB.Net]()
2. Then you need to locate the WCF Service in the project using the Discover button and then press OK to add its Service Reference.
![Return DataTable from WCF Service (SVC) in ASP.Net using C# and VB.Net]()
Bind GridView using data from WCF Service
HTML Markup
The HTML Markup consists of an ASP.Net GridView.
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
<Columns>
<asp:BoundField DataField="CustomerId" HeaderText="Customer Id" ItemStyle-Width="150" />
<asp:BoundField DataField="Name" HeaderText="Name" ItemStyle-Width="150" />
<asp:BoundField DataField="Country" HeaderText="Country" ItemStyle-Width="100" />
</Columns>
</asp:GridView>
Code
Inside the Page Load event, the GridView is populated with data from the WCF Service by accessing the GetCustomers method using the ServiceClient object.
C#
protected void Page_Load(object sender, EventArgs e)
{
ServiceReference.ServiceClient client = new ServiceReference.ServiceClient();
GridView1.DataSource = client.GetCustomers().CustomersTable;
GridView1.DataBind();
}
VB.Net
Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
Dim client As New ServiceReference.ServiceClient()
GridView1.DataSource = client.GetCustomers().CustomersTable
GridView1.DataBind()
End Sub
![Return DataTable from WCF Service (SVC) in ASP.Net using C# and VB.Net]()
Demo
Downloads
