为返回多个表的存储过程创建强类型数据集 [英] Create Strongly Typed Dataset for a stored procedure that return more than one table

问题描述

我有一个执行3个选择的存储过程。如何创建可访问所有3个表并读取数据的强类型数据集。默认情况下，Visual Studio会生成仅具有第一个表的数据集。

I have a stored procedure that performs 3 select. How do I create a strongly typed dataset that can access all the 3 tables and read the data. Visual studio by default generates a dataset with the 1st table only

我已经尝试过使用Visual Studio的Typed Dataset拖放存储过程。

I have already tried using visual studio Typed Dataset, to drag and drop the Stored Procedure.

存储过程如下：

Create Procedure GetData
As
Begin
Select ColA, ColB, ColC from TableA
Select ColD, ColE, ColF from TableB
Select ColG, ColH, ColI from TableC
End

推荐答案

如果您不愿意这样做，我认为您不会使用纯粹的强类型设计师生成的解决方案获得成功； tableadapters旨在在db数据的本地数据表表示形式（您的强类型数据表）和返回行的数据库查询之间进行中介。 表指的是表。在与数据表（而不是数据库表）相关的tableadapter中。

If you're desperate to do this I don't think you'll succeed with a pure strongly-typed designer generated solution; tableadapters are intended to mediate between a local datatable representation of the db data (your strongly typed datatable) and a database query that returns rows. The "table" in tableadapter related to th datatable, not the database table.

单个tableadapter不能用作3个本地数据表和提供3个数据库查询输出的远程过程之间的中介。 。基本上它无法执行此操作，因为对于您的sql，没有任何客户端代码可用于标识...

A single tableadapter is not intended to function as a mediator between 3 local datatables and a remote procedure that delivers the output of 3 database queries. Primarily it cannot do this because there is nothing the client side code can use to identify, for your sql of...

Select ColA, ColB, ColC from TableA
Select ColD, ColE, ColF from TableB
Select ColG, ColH, ColI from TableC

...来自TableA的 select *的结果应该放在数据集中的TableADataTable等中。从tableA出来的数据在

...that the results from select * from TableA are supposed to go in TableADataTable in your dataset etc. The fact that the data came out of tableA is lost in the transmission over the wire because it is completely irrelevant and might not even be true.

当一个表适配器正确地与单个选择一起使用时，它隐式知道应该将结果放入数据表中。 TableADataTable有一个对应的TableATableAdapter，TableATableAdapter从db中的某个位置选择数据并将其存储在TableADataTable中-数据集中没有其他TableATableAdapter设计用来操纵的表，因此不需要 关于数据块在哪里的提示运行查询后的s。 TableATableAdapter甚至可以加载查询，该查询根本不会从数据库TableA返回任何数据；只要查询运行就会产生一组具有正确编号和类型的列，该数据就会进入TableADataTable中，因为TableATableAdapter是硬编码的。它没有其他数据表，也没有其他数据表。

When a tableadapter is correctly used with a single select, it knows implicitly the datatable the results should be put in. TableADataTable has a corresponding TableATableAdapter, and TableATableAdapter selects data from somewhere in the db and stashes it in the TableADataTable - there is no other table in the dataset that TableATableAdapter is designed to manipulate so it doesn't need any hints about where the block of data goes after it runs the query. TableATableAdapter can even be loaded with a query that doesn't return any data from the database TableA at all; so long as the query it runs produces a set of columns of th right number and type, that data will go in TableADataTable because that's what TableATableAdapter is hardcoded to do. It serves no other datatable, and has no interest in any other datatable.

由于您的存储过程无法向表适配器指示应将哪些结果集存储在哪个表中，

Because your stored procedure has no way of indicating to the tableadapter which of the result sets should be stored in which table, the solution you're envisaging cannot work.

简单的规则是：一只狗，一只完成。 -一个数据库查询结果集，一个表适配器，一个强类型数据表。

The simple rule is: "one dog, one done" - "one db query result set, one tableadapter, one strongly typed datatable"

因此，我强烈建议您按预期使用这些东西：

As such, i firmly recommend you use these things as they were intended:

• 为TableA，TableB和TableC创建3个表适配器和相应的数据表


• 在代码中分别填充：

var ds = new StronglyTypedDataSet();
var ata as new TableATableAdapter();
ata.Fill(ds);
var bta as new TableBTableAdapter();
bta.Fill(ds);
var cta as new TableCTableAdapter();
cta.Fill(ds);

---

我们希望避免对单个页面进行多次数据库调用并没有真正的意义-听起来像是解决了您想象中的问题，而不是真的解决。尝试一次执行而不是3次执行这些事情，几乎没有性能优势。您可能会不同意，但是要对其进行测试-不要只凭直觉就行。连接被池化，语句被缓存并准备好，如果您真的认为这将极大地帮助您，则可以同时执行3条语句。

---

"We would like to avoid multiple db calls for a single page" doesn't really make sense - it sounds like a solution to a problem you've imagined rather than on that will really happen. There's so little performance advantage to be gained by trying to execute these things in one hit rather than 3. You might disagree, but test it - don't just go off a hunch. Connections are pooled, statements are cached and prepared, 3 statements could be executed concurrently if you really think it will help vastly.

如果您要处理9兆字节的数据，那么一天结束时从数据库中提取数据，这样做的差别是每3 mb提取3 mb与1提取9提取的差异将很小。您并不需要等待30秒才能打开连接，每秒读取3 mb，等待另外30秒来关闭广告，而无需重新做一次（总时间为183秒），并且所有瓶颈都归因于连接管理。即使您 did 有一个超级等待时间的连接，要花30秒来传输SELECT，又要花30秒才能开始读取数据，您也可以同时启动3个请求，并且根据定义，这将花费相同的时间发送3个SELECT，因为它将调用1个过程调用（均耗时61秒）

At the end of the day if you have 9 megabytes of data to pull out of a database, the difference in doing it as 3 pulls of 3 mb each versus 1 pull of 9 is going to be miniscule; you aren't waiting 30 seconds for a connection to open, reading 3 mb in a second, waiting another 30s to close it ad having to do it all over again (total time 183 seconds) and having all the bottleneck attributable to the connection management. Even if you did have a super latency connection that will take 30s to transmit the SELECT and another 30s to start reading data, you can launch your 3 requests simultaneously and by definition it will take the same time to send the 3 SELECTs as it will to invoke 1 procedure call (both take 61 seconds)

如果您不能同意尝试全部执行的原因，一个是虚假的，那么您可能希望继续尝试通过选择的方法来执行此操作，在这种情况下，我认为您将不得不选择：

If you cannot agree that the reason for trying to do it all in one is spurious then you may wish to keep trying to do it via your chosen method, in which case I think you're going to have to choose:

，然后将数据移入类型集以供使用

and then move the data into the typed set to work with it

SqlConnection con = new SqlConnection("YourConnection String");
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand("storedprocedure", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@p1", whatever); //if you have parameters.
SqlDataAdapter da= new SqlDataAdapter(cmd);
da.Fill(ds);
con.Close();

现在您有一个包含3个表的数据集，要弄清楚哪个表是您的问题。假设ds.Tables [0]用于TableA：

Now you have a dataset with 3 tables in it, it's your problem to figure out which table is which. Let's assume ds.Tables[0] is for TableA:

foreach(var ro in ds.Tables[0].Rows)
  typedDs.TableA.AddTableARow(ro.ItemArray);

为b和c表重复此操作

您的示例似乎表明所有3个表的列数均相同。如果这些列也属于同一类型，则可以合并查询，并将其加载到单个表适配器中，然后将其填充到单个强类型数据表中。然后，您可能需要做更多的工作才能将它们分成单独的数据表。也许修改查询以返回列，以便您可以跟踪数据的来源：

Your example seems to indicate all 3 of your tables have the same number of columns. If the columns are the same type too, then you can union the queries, and load them into a single table adapter and fill them into a single strongly typed datatable. You might then have some more work to do splitting them out into separate datatables. Perhaps modify th query to return a column so you can track where the data came from:

Select 'tableA' as wherefrom, ColA, ColB, ColC from TableA
UNION ALL
Select 'tableB' as wherefrom, ColD, ColE, ColF from TableB
UNION ALL
Select 'tableC' as wherefrom, ColG, ColH, ColI from TableC

这是一团糟，麻烦，骇客

It's a mess, a hassle, a hack

为什么这么难？好吧..引用另一句老话：如果很难，那说明您做错了。 TableAdapter设计为X方式，您正在尝试以Y方式使用它们。退后一步，看看这样做的原因-这才是真正的问题所在

Why is this so hard? Well.. to quote another old saying: if it's hard, you're doing it wrong. TableAdapters were designed X way and you're trying to use them Y way. Take a step back and examine the reasons behind why you're doing it - that's where the real problem lies

这篇关于为返回多个表的存储过程创建强类型数据集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！