Returning DataTables in WCF/.NET

Question

I have a WCF service from which I want to return a DataTable. I know that this is often a highly-debated topic, as far as whether or not returning DataTables is a good practice. Let's put that aside for a moment.

When I create a DataTable from scratch, as below, there are no problems whatsoever. The table is created, populated, and returned to the client, and all is well:

[DataContract]
public DataTable GetTbl()
{
    DataTable tbl = new DataTable("testTbl");
    for(int i=0;i<100;i++)
    {
        tbl.Columns.Add(i);
        tbl.Rows.Add(new string[]{"testValue"});
    }
    return tbl;
}

However, as soon as I go out and hit the database to create the table, as below, I get a CommunicationException "The underlying connection was closed: The connection was closed unexpectedly."

[DataContract]
public DataTable GetTbl()
{
    DataTable tbl = new DataTable("testTbl");
    //Populate table with SQL query

    return tbl;
}

The table is being populated correctly on the server side. It is significantly smaller than the test table that I looped through and returned, and the query is small and fast - there is no issue here with timeouts or large data transfer. The same exact functions and DataContracts/ServiceContracts/BehaviorContracts are being used.

Why would the way that the table is being populated have any bearing on the table returning successfully?

score 85 · Accepted Answer · edited Jun 23 '15 at 15:28

85

For anyone having similar problems, I have solved my issue. It was several-fold.

As Darren suggested and Paul backed up, the Max..Size properties in the configuration needed to be enlarged. The SvcTraceViewer utility helped in determining this, but it still does not always give the most helpful error messages.
It also appears that when the Service Reference is updated on the client side, the configuration will sometimes not update properly (e.g. Changing config values on the server will not always properly update on the client. I had to go in and change the Max..Size properties multiple times on both the client and server sides in the course of my debugging)
For a DataTable to be serializable, it needs to be given a name. The default constructor does not give the table a name, so:
```
return new DataTable();
```
will not be serializable, while:
```
return new DataTable("someName");
```
will name the table whatever is passed as the parameter.

Note that a table can be given a name at any time by assigning a string to the TableName property of the DataTable.
```
var table = new DataTable();
table.TableName = "someName";
```

Hopefully that will help someone.

edited Jun 23 '15 at 15:28

saluce

13,035
3
50
67

answered Sep 03 '08 at 19:14

goric

11,491
7
53
69

1

Very strange with the name thingy. I had same problem. Setting a name on the DataTable solved it... Weird. – MartinHN Aug 01 '09 at 17:52
8

+1 for table name. I looked at the HTTP return in Fiddler, and VS uses that table name to generate a custom datatype. If the name is null, I guess it tries to use a blank name for that custom datatype and just errors out. – akatakritos Dec 21 '11 at 18:37
1

Great question and great answer. I remember having this problem with something else before, I can't remember, I think it was just classic web services. I don't want to open a can of worms, but I don't feel guilty using DataTables across the pipe because if I am only using it for display information, I don't think it matters at all. When you start passing DataTables and DataRows as parameters, that is when it becomes a major @#$#(*@%_ up. Objects are always safer, but if your data changes often because of a skitzo decision maker - then I don't see the harm just for display purposes. – dyslexicanaboko Sep 01 '12 at 00:05
Holy crap on a cringle... TableName... I've been circling WCF issues for hours (days?) and I think this is the root cause. He's a vote for an almost 10 year old answer. – WernerCD May 30 '17 at 01:10

score 15 · Answer 2 · answered Aug 23 '08 at 18:56

The best way to diagnose these kinds of WCF errors (the ones that really don't tell you much) is to enable tracing. In your web.config file, add the following:

  <system.diagnostics>
    <sources>
      <source name="System.ServiceModel" 
              switchValue="Information" 
              propagateActivity="true">
        <listeners>
          <add name="ServiceModelTraceListener" 
               type="System.Diagnostics.XmlWriterTraceListener, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" 
               initializeData="wcf-traces.svclog"/>
        </listeners>
      </source>
    </sources>
  </system.diagnostics>

You can then open the resulting file in the SvcTraceViewer.exe utility which comes in the .NET Framework SDK (or with Visual Studio). On my machine, it can be found at %PROGRAMFILES%\Microsoft SDKs\Windows\v6.0A\Bin\SvcTraceViewer.exe.

Just look for an error message (in bold red) and that will tell you specifically what your problem is.

You can find the log file `wcf-traces.svclog` in the `Debug` folder of your project. — Daniel Bonetti, Dec 20 '17 at 19:04

score 6 · Answer 3 · edited Jul 25 '15 at 21:52

6

Other than setting maximum values for all binding attributes.

Make sure each table you are passing/returning from webservice must have a table name, meaning the table.tablename property should not be blank.

edited Jul 25 '15 at 21:52

Peter Mortensen

30,738
21
105
131

answered Dec 21 '10 at 13:33

aditya pathak

61
1
1

score 5 · Answer 4 · answered Oct 24 '11 at 08:29

5

I added the Datable to a data set and returned the table like so...

DataTable result = new DataTable("result");

//linq to populate the table

Dataset ds = new DataSet();
ds.Tables.Add(result);
return ds.Tables[0];

Hope it helps :)

answered Oct 24 '11 at 08:29

Jani5e

163
2
8

In my case DataTable created as result of Database Query was working fine but it was always having problem with DataTable created in my code. It got solved when I added this table to newly created dataset. It is quite bizzare. I believe it is some bug in Microsoft's serialisation. – Softec Mar 14 '17 at 15:12

score 4 · Answer 5 · edited Jul 25 '15 at 21:51

The attribute you want is OperationContract (on interface) / Operation Behavior (on method):

[ServiceContract]
public interface ITableProvider
{
    [OperationContract]
    DataTable GetTbl();
}


[OperationBehavior]
public DataTable GetTbl(){
    DataTable tbl = new DataTable("testTbl");
    //Populate table with SQL query

    return tbl;
}

Also, in the... I think service configuration... you want to specify that errors can be sent. You might be hitting an error that is something like the message size is to big, etc. You can fix that by fudging with the reader quotas and such.

By default wsHttpBinding has a receive size quota of like 65 KB, so if the serialized data table's XML is more than that, it would throw an error (and I'm 95% sure the data table is more than 65 KB with data in it).

You can change the settings for the reader quotas and such in the web.config / app.config or you can set it on a binding instance in code. But yeah, that's probably what your problem is, if you haven't changed it by default.

WSHttpBindingBase Members - Look at ReaderQuotas property as well as the MaxReceivedMessageSize property.

score 2 · Answer 6 · answered Oct 24 '08 at 13:33

2

You probably blew your quota - the datatable is larger than the allowed maximum packet size for your connection.

You probably need to set MaxReceivedMessageSize and MaxBufferSize to higher values on your connection.

answered Oct 24 '08 at 13:33

Sam

28,421
49
167
247

Mukesh Methaniya · Answer 7 · 2016-12-19T04:49:58.767

1

There are 3 reason for failed return type as datatable in WCF services

You have to specify data table name like:
```
MyTable=new DataTable("tableName");
```
When you are adding reference on client side of WCF service select reusable dll system.data

Specify attribute on datatable member variable like

[DataMember]
public DataTable MyTable{ get; set; }

edited Dec 19 '16 at 04:49

answered Dec 02 '16 at 05:14

Mukesh Methaniya

752
1
5
13

score 0 · Answer 8 · answered Sep 02 '08 at 14:18

I think Darren is most likely correct - the default values provided for WCF are laughably small and if you bump into them you end up with errors that can be difficult to track down. They seem to appear as soon as you attempt to do anything beyond a simple test case. I wasted more time than I'd like to admit debugging problems that turned out to be related to the various configuration (size) settings on both the client and server. I think I ended up modifying almost all of them, ex. MaxBufferPoolSize, MaxBufferSize, MaxConnections, MaxReceivedMessageSize, etc.

Having said that, the SvcTraceViewer utility also mentioned is great. I did run into a few cases where it wasn't as helpful as I would have liked, but overall it's a nice tool for analyzing the communications flow and errors.

Returning DataTables in WCF/.NET

8 Answers8

Linked