Web Service Can't Return A DataTable In .NET

When a web service has a method that returns a DataTable, DataRow, DataView, or DataViewManager, problems may arise because these objects cannot be serialized. In particular, if you browse to the URL of the web service or try to add a reference to the web service in Visual Studio .NET, you may see either a blank browser window or any of the following errors:

• System.Data.DataRelation cannot be serialized because it does not have a default public constructor.
• Internal Server Error. Unable to request "http://localhost/Webservice/Service.asmx?WSDL". The server responded with error code "ProtocolError".
• System.NotSupportedException: Cannot serialize member System.ComponentModel.MarshalByValueComponent.Site of type System.ComponentModel.ISite because it is an interface.
• The document at the url http://localhost/vdir/service.asmx was not recognized as a known document type.

The workaround is simple. Whenever you need a web method to return any part of a DataSet, instead of returning just the part you need, you have to either return the entire DataSet, or create a new DataSet that contains a copy of the data you want.

The following C# code will cause an error because it returns a DataTable:

[WebMethod]
public System.Data.DataTable GetDataTable()
{
  System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection("Server=localhost;Initial Catalog=pubs;Integrated Security=SSPI;");
  System.Data.DataSet ds = new System.Data.DataSet();
  System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter();
  adapter.SelectCommand = new System.Data.SqlClient.SqlCommand("SELECT * FROM AUTHORS", conn);
  adapter.Fill(ds, "Authors");
  return ds.Tables["Authors"];
}

The solution is to rewrite the method to return a DataSet instead of a DataTable:

[WebMethod]
public System.Data.DataSet GetDataSet()
{
  System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection("Server=localhost;Initial Catalog=pubs;Integrated Security=SSPI;");
  System.Data.DataSet ds = new System.Data.DataSet();
  System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter();
  adapter.SelectCommand = new System.Data.SqlClient.SqlCommand("SELECT * FROM AUTHORS", conn);
  adapter.Fill(ds, "Authors");
  return ds;
}