Issue While Returning DataTable Through WCF Service

Rohatash Kumar

Issue While Returning DataTable Through WCF Service

Problem

In the previous article, I faced a common problem when returning a DataTable through a WCF Service. I'm receiving that error message when using one of my webmethods on my WCF webservice.

"The underlying connection was closed: The connection was closed unexpectedly."

This error only occurs when returning a DataTable. If I return some primitive type or even a Dictionary<string, string>, it returns no problem.

Iservice1.cs File

Now we create a function in the OperationContract section of the Iservice1.cs file:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Web;
using System.Text;
using System.Data ;
namespace WCFServiceWith_ReturnTable
{
[ServiceContract]
public interface IService1
{
[OperationContract]
DataTable SelectUserDetails();
}
// Use a data contract as illustrated in the sample below to add composite types to service operations.
[DataContract]
public class UserDetails
{
[DataMember]
public DataTable UserRecord
{
get;
set;
}
}
}

Service.svc.cs File

In this file we define the function SelectUserDetails(UserDetails userInfo).

And replace the code with the following:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Web;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace WCFServiceWith_ReturnTable
{
// NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "Service1" in code, svc and config file together.
public class Service1 : IService1
{
public DataTable SelectUserDetails()
{
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=registration;User ID=sa;Password=wintellect");
con.Open();
SqlCommand cmd = new SqlCommand("Select * from RegistrationTable", con);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
sda.Fill(dt);
cmd.ExecuteNonQuery();
con.Close();
return dt;
}
}
}

Add a Service Reference to the WCF service in the Web Application using the Add Service Reference dialog box:

Write following code in the web Application:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using ServiceReference1;
using System.IO;
using System.Data;
using System.Xml;
public partial class _Default : System.Web.UI.Page
{
ServiceReference1.Service1Client objServiceClientobjService = new ServiceReference1.Service1Client();
protected void Page_Load(object sender, EventArgs e)
{
DataTable dt = new DataTable();
dt=objServiceClientobjService.SelectUserDetails();
GridView1.DataSource = dt;
GridView1.DataBind();
}
}

Now run the application. The following error will occur:

Returning DataTable in WCF

This is a serialization issue with WCF. Your Datacontract contains a type which the DataContractSerializer is not able to serialize. Here, we will introduce how to return a DataTable using a WCF service from C# code. To return a DataTable using a WCF service, we must do the following 3 things:

Create Database Table
Create WCF Service
Create a Web Application

In the first step we will create a table in SQL Server; after that we create a simple function to return the table from the database using a WCF service. In a web application, add a reference of the service and data to be returned which will be sent to the web services function. Let's take a look at a practical example. The example application is developed in Visual Studio 2010 and SQL Server 2008.

Step 1: Creating Database Table

Database name: Registration
Database table name: RegistrationTable

RegistrationTable Table

CREATE TABLE [dbo].[RegistrationTable]
(
[UserName] [varchar](100) NOT NULL,
[Password] [varchar](20) NOT NULL,
[Country] [varchar](100) NOT NULL,
[Email] [varchar](200) NOT NULL
)

Step 2: Creating WCF Service

Now you have to create a WCF Service:

Go to Visual Studio 2010
New -> Select a project

Now click on the project and select WCF Service Application and provide a name for the service:

Now click on the Ok Button. Then you will get 3 files in the Solution Explorer:

IService.cs
Service.svc
Service.svc.cs

The following image shows the following files:

For inserting data into the database you need to write the following code in the IService1.cs file which contains the two sections:

OperationContract
DataContract

The OperationContract section is used to add service operations and the DataContract is used to add types to service operations.

Iservice1.cs File

Now we create a function in the OperationContract section of the Iservice1.cs file:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Web;
using System.Text;
using System.Data ;
namespace WCFServiceWith_ReturnTable
{
[ServiceContract]
public interface IService1
{
[OperationContract]
UserDetails SelectUserDetails();
}
[DataContract]
public class UserDetails
{
[DataMember]
public DataTable UserRecord
{
get;
set;
}
}
}

Service.svc.cs File

In this file we define the definition of the function SelectUserDetails(UserDetails userInfo).

And replace the code with the following:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Web;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace WCFServiceWith_ReturnTable
{
// NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "Service1" in code, svc and config file together.
public class Service1 : IService1
{
public UserDetails SelectUserDetails()
{
UserDetails ObjUserDetail = new UserDetails();
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=registration;User ID=sa;Password=wintellect");
con.Open();
SqlCommand cmd = new SqlCommand("Select * from RegistrationTable", con);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable("TableName");
sda.Fill(dt);
ObjUserDetail.UserRecord = dt;
cmd.ExecuteNonQuery();
con.Close();
return ObjUserDetail;
}
}
}

Step 3: Create web Application (Accessing the Service)

Now, you have to create a web site.

Go to Visual Studio 2010
New -> Select a website application
Click OK

Now add a new page to the website:

Go to the Solution Explorer
Right-click on the project name
Select add new item
Add new web page and give it a name
Click OK

Add the service reference in web application

Now add the service reference.

When we click on the add the service reference the following window will be opened:

Now paste the above URL in the address and click on the go button.

Click on the ok Button. Now the reference has been added in the Solution Explorer.

Double-click the on the form and add the following code on the page load event:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using ServiceReference2;
using System.IO;
using System.Data;
public partial class _Default : System.Web.UI.Page
{
ServiceReference2.Service1Client objServiceClientobjService = new ServiceReference2.Service1Client();
ServiceReference2.UserDetails emp =
new ServiceReference2.UserDetails();
protected void Page_Load(object sender, EventArgs e)
{
DataTable dt = new DataTable();
emp=objServiceClientobjService.SelectUserDetails();
dt = emp.UserRecord;
}
}

Now use a breakpoint to show the database value in the table.

Now press F5 to start debugging.

Now click on the DataTable Visualizer to see the table.

It works fine.

WCF ServiceCreate WCF ServiceWCF service with SQL ServerDatatable in WCFError when returning Datatable in WCFIssue While Returning DataTable

1 Comment

Join the conversation! Your thoughts help the community grow.

sanjib manna8y ago
Still facing the same exception.

Issue While Returning DataTable Through WCF Service

Rohatash Kumar

7y74.5k1100Article

Issue While Returning DataTable Through WCF Service

Problem

In the previous article, I faced a common problem when returning a DataTable through a WCF Service. I'm receiving that error message when using one of my webmethods on my WCF webservice.

"The underlying connection was closed: The connection was closed unexpectedly."

This error only occurs when returning a DataTable. If I return some primitive type or even a Dictionary<string, string>, it returns no problem.

Iservice1.cs File

Now we create a function in the OperationContract section of the Iservice1.cs file:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Web;
using System.Text;
using System.Data ;
namespace WCFServiceWith_ReturnTable
{
[ServiceContract]
public interface IService1
{
[OperationContract]
DataTable SelectUserDetails();
}
// Use a data contract as illustrated in the sample below to add composite types to service operations.
[DataContract]
public class UserDetails
{
[DataMember]
public DataTable UserRecord
{
get;
set;
}
}
}

Service.svc.cs File

In this file we define the function SelectUserDetails(UserDetails userInfo).

And replace the code with the following:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Web;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace WCFServiceWith_ReturnTable
{
// NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "Service1" in code, svc and config file together.
public class Service1 : IService1
{
public DataTable SelectUserDetails()
{
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=registration;User ID=sa;Password=wintellect");
con.Open();
SqlCommand cmd = new SqlCommand("Select * from RegistrationTable", con);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
sda.Fill(dt);
cmd.ExecuteNonQuery();
con.Close();
return dt;
}
}
}

Add a Service Reference to the WCF service in the Web Application using the Add Service Reference dialog box:

Write following code in the web Application:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using ServiceReference1;
using System.IO;
using System.Data;
using System.Xml;
public partial class _Default : System.Web.UI.Page
{
ServiceReference1.Service1Client objServiceClientobjService = new ServiceReference1.Service1Client();
protected void Page_Load(object sender, EventArgs e)
{
DataTable dt = new DataTable();
dt=objServiceClientobjService.SelectUserDetails();
GridView1.DataSource = dt;
GridView1.DataBind();
}
}

Now run the application. The following error will occur:

Returning DataTable in WCF

Create Database Table
Create WCF Service
Create a Web Application

Step 1: Creating Database Table

Database name: Registration
Database table name: RegistrationTable

RegistrationTable Table

CREATE TABLE [dbo].[RegistrationTable]
(
[UserName] [varchar](100) NOT NULL,
[Password] [varchar](20) NOT NULL,
[Country] [varchar](100) NOT NULL,
[Email] [varchar](200) NOT NULL
)

Step 2: Creating WCF Service

Now you have to create a WCF Service:

Go to Visual Studio 2010
New -> Select a project

Now click on the project and select WCF Service Application and provide a name for the service:

Now click on the Ok Button. Then you will get 3 files in the Solution Explorer:

IService.cs
Service.svc
Service.svc.cs

The following image shows the following files:

For inserting data into the database you need to write the following code in the IService1.cs file which contains the two sections:

OperationContract
DataContract

The OperationContract section is used to add service operations and the DataContract is used to add types to service operations.

Iservice1.cs File

Now we create a function in the OperationContract section of the Iservice1.cs file:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Web;
using System.Text;
using System.Data ;
namespace WCFServiceWith_ReturnTable
{
[ServiceContract]
public interface IService1
{
[OperationContract]
UserDetails SelectUserDetails();
}
[DataContract]
public class UserDetails
{
[DataMember]
public DataTable UserRecord
{
get;
set;
}
}
}

Service.svc.cs File

In this file we define the definition of the function SelectUserDetails(UserDetails userInfo).

And replace the code with the following:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Web;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace WCFServiceWith_ReturnTable
{
// NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "Service1" in code, svc and config file together.
public class Service1 : IService1
{
public UserDetails SelectUserDetails()
{
UserDetails ObjUserDetail = new UserDetails();
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=registration;User ID=sa;Password=wintellect");
con.Open();
SqlCommand cmd = new SqlCommand("Select * from RegistrationTable", con);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable("TableName");
sda.Fill(dt);
ObjUserDetail.UserRecord = dt;
cmd.ExecuteNonQuery();
con.Close();
return ObjUserDetail;
}
}
}

Step 3: Create web Application (Accessing the Service)

Now, you have to create a web site.

Go to Visual Studio 2010
New -> Select a website application
Click OK

Now add a new page to the website:

Go to the Solution Explorer
Right-click on the project name
Select add new item
Add new web page and give it a name
Click OK

Add the service reference in web application

Now add the service reference.

When we click on the add the service reference the following window will be opened:

Now paste the above URL in the address and click on the go button.

Click on the ok Button. Now the reference has been added in the Solution Explorer.

Double-click the on the form and add the following code on the page load event:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using ServiceReference2;
using System.IO;
using System.Data;
public partial class _Default : System.Web.UI.Page
{
ServiceReference2.Service1Client objServiceClientobjService = new ServiceReference2.Service1Client();
ServiceReference2.UserDetails emp =
new ServiceReference2.UserDetails();
protected void Page_Load(object sender, EventArgs e)
{
DataTable dt = new DataTable();
emp=objServiceClientobjService.SelectUserDetails();
dt = emp.UserRecord;
}
}

Now use a breakpoint to show the database value in the table.