C# 在 WCF/.NET 中返回数据表

Returning DataTables in WCF/.NET

提问by goric

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.

我有一个 WCF 服务，我想从中返回一个 DataTable。我知道这通常是一个备受争议的话题，就返回 DataTables 是否是一个好习惯而言。让我们暂时搁置一下。

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:

当我从头开始创建 DataTable 时，如下所示，没有任何问题。表被创建、填充并返回给客户端，一切正常：

[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."

但是，一旦我出去点击数据库以创建表，如下所示，我得到一个 CommunicationException “底层连接已关闭：连接意外关闭。”

[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.

该表正在服务器端正确填充。它比我循环并返回的测试表小得多，查询又小又快——这里没有超时或大数据传输的问题。正在使用完全相同的函数和 DataContracts/ServiceContracts/BehaviorContracts。

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

为什么填充表格的方式会影响表格的成功返回？

采纳答案by goric

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 TableNameproperty of the DataTable.

  var table = new DataTable();
  table.TableName = "someName";
  

• 正如 Darren 建议和 Paul 所支持的，配置中的 Max..Size 属性需要扩大。SvcTraceViewer 实用程序有助于确定这一点，但它仍然不总是提供最有用的错误消息。
• 似乎当在客户端更新服务引用时，配置有时不会正确更新（例如，更改服务器上的配置值并不总是在客户端正确更新。我不得不进入并更改 Max..在我的调试过程中在客户端和服务器端多次调整属性大小）

• 要使 DataTable 可序列化，需要为其指定名称。默认构造函数没有为表命名，因此：

  return new DataTable();
  

  不会被序列化，而：

  return new DataTable("someName");
  

  将命名表作为参数传递的任何内容。

  请注意，可以随时通过将字符串分配给TableNameDataTable的属性来为表命名。

  var table = new DataTable();
  table.TableName = "someName";
  

Hopefully that will help someone.

希望这会帮助某人。

回答by Darren Kopp

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

你想要的属性是 OperationContract（在接口上）/操作行为（在方法上）：

[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).

默认情况下，wsHttpBinding 的接收大小配额为 65 KB，因此如果序列化数据表的 XML 超过该配额，则会引发错误（我 95% 确定数据表大于 65 KB，其中包含数据） ）。

You can change the settings for the reader quotas and such in the web.config/ app.configor 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.

您可以在web.config/ 中更改读取器配额等app.config设置，也可以在代码中在绑定实例上进行设置。但是，是的，如果默认情况下您没有更改它，那可能就是您的问题。

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

WSHttpBindingBase 成员- 查看 ReaderQuotas 属性以及 MaxReceivedMessageSize 属性。

回答by Chris Gillum

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:

诊断这些类型的 WCF 错误（实际上并没有告诉您太多信息的错误）的最佳方法是启用跟踪。在您的 web.config 文件中，添加以下内容：

  <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.

然后，您可以在 .NET Framework SDK（或 Visual Studio）中提供的 SvcTraceViewer.exe 实用程序中打开生成的文件。在我的机器上，它可以在 %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.

只需查找错误消息（红色粗体），它就会具体告诉您问题是什么。

回答by Paul Mrozowski

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.

我认为 Darren 很可能是正确的 - 为 WCF 提供的默认值小得可笑，如果你碰到它们，最终会出现难以追踪的错误。一旦您尝试执行简单测试用例之外的任何操作，它们似乎就会出现。我浪费了比承认调试问题更多的时间，这些问题结果证明与客户端和服务器上的各种配置（大小）设置有关。我想我最终修改了几乎所有这些，例如。MaxBufferPoolSize、MaxBufferSize、MaxConnections、MaxReceivedMessageSize 等。

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.

话虽如此，还提到的 SvcTraceViewer 实用程序很棒。我确实遇到过一些它没有我希望的那么有用的情况，但总的来说，它是一个很好的分析通信流和错误的工具。

回答by Sam

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

您可能超出了配额 - 数据表大于连接允许的最大数据包大小。

You probably need to set MaxReceivedMessageSizeand MaxBufferSizeto higher values on your connection.

您可能需要在连接上将MaxReceivedMessageSize和MaxBufferSize设置为更高的值。

回答by aditya pathak

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.tablenameproperty should not be blank.

确保您从 webservice 传递/返回的每个表都必须有一个表名，这意味着该table.tablename属性不应为空。

回答by Jani5e

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

我将 Datable 添加到数据集并像这样返回表......

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 :)

希望能帮助到你 ：）

回答by Mukesh Methaniya

There are 3 reason for failed return type as datatablein WCF services

datatableWCF 服务中返回类型失败的原因有 3 个

• 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 datatablemember variable like

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

• 您必须指定数据表名称，例如：

  MyTable=new DataTable("tableName");
  

• 在 WCF 服务的客户端添加引用时，选择可重用的 dll system.data

• 在datatable成员变量上指定属性，如

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