Issue While Returning DataTable Through WCF Service

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:

 

 

Solution ( 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:

1. Create Database Table
2. Create WCF Service
3. 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

1. Database name:  Registration
2. 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:

1. IService.cs
2. Service.svc
3. 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:

1. OperationContract
2. 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.