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?
For anyone having similar problems, I have solved my issue. It was several-fold.
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.
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.