使用存储过程中的表名填充数据集

I have a stored procedure that returns multiple tables.

我有一个返回多个表的存储过程。

It populates my dataset correctly but it names my tables [Table,Table1,Table2,...].

它正确填充我的数据集，但它命名我的表[Table，Table1，Table2，...]。

Is there something I can add in the database layer (to my stored procedure) that will name the tables properly?

我可以在数据库层（到我的存储过程）中添加哪些内容来正确命名表吗？

2 个解决方案

#1

Your SP is not actually returning multiple tables, its returning a selection of columns and rows from your tables, therefore there is no 'table name', and hence why they are named table1, table2 etc. If its important, you could return an extra column for each selection, and in that column fill it with the desired name and then use it from there.

你的SP实际上并没有返回多个表，它从表中返回一系列列和行，因此没有'表名'，因此它们被命名为table1，table2等。如果它很重要，你可以返回一个额外的每个选择的列，并在该列中填入所需的名称，然后从那里使用它。

i.e.

即

   select *,'MyTableName1' As [TableName] name from mytablename1
   select *,'MyTableName2' As [TableName] name from mytablename2

#2

in stored procedure:

在存储过程中：

select  CH.PrimaryKey, CH.Name,
        NULL    "CustomerHeader"
from CustomerHeader "CH";
--
select  CD.PrimaryKey, CD.ShipTo,
        NULL    "CustomerDetail"
from CustomerDetail "CD";
--
select  *, NULL "Orders"
from    OrderTable;

in Vb.Net code:

在Vb.Net代码中：

Dim ds As DataSet = Nothing
ds = SqlExecute();
Dim dtCustHeader As DataTable = Nothing
Dim dtCustDetail As DataTable = Nothing
Dim dtOrders As DataTable = Nothing
For Each dt As DataTable In ds.tables
    Select Case True
        Case dt.Columns.Contains("CustomerHeader")
            dtCustHeader = dt
        Case dt.Columns.Contains("CustomerDetail")
            dtCustDetail = dt
        Case dt.Columns.Contains("Orders")
            dtOrders = dt
    End Select
Next

Kinda SILLY (OR STUPID) that you cannot name tables in a result set. But this gets you there without a HUGE byte count repeating the table name within each row.

有点SILLY（或STUPID），您不能在结果集中命名表。但这会让你在那里没有重复每行中的表名的巨大字节数。

There is still overhead passing the NULL value back for each row. Perhaps passing a BIT value would be smaller yet...

仍有开销为每行传回NULL值。也许传递BIT值会更小......

And an alternative is to always use column(0): in SQL:

另一种方法是始终使用column（0）：在SQL中：

select NULL "CustomerDetail", CustName,Addr1,Addr2... from CustomerDetail;

in vb.net:

在vb.net中：

    Dim ds As DataSet = Nothing
    ds = SqlExecute();
    Dim dtCustHeader As DataTable = Nothing
    Dim dtCustDetail As DataTable = Nothing
    Dim dtOrders As DataTable = Nothing
    For Each dt As DataTable In ds.Tables
        Dim tblName As String = dt.Columns(0).ColumnName
        Select Case tblName.ToUpper
            Case "CUSTOMERDETAIL" : dtCustHeader = dt
            Case "CUSTOMERDETAIL" : dtCustDetail = dt
            Case "ORDERS" : dtOrders = dt
        End Select
    Next

These methods get your table-names even if the query returns zero rows.

即使查询返回零行，这些方法也会获取表名。

but the best for last... a way to actually name the tables in the dataset automatically, every time FROM SQL STORED PROCEDURE (with help from your code):

但最好是最后一种...每次FROM SQL STORED PROCEDURE（在您的代码的帮助下）自动命名数据集中的表的方法：

Dim ds As DataSet = Nothing
ds = SqlExecute();
For Each dt As DataTable In ds.Tables
    dt.TableName = dt.Columns(0).ColumnName
Next

After this, you may access your tables with the name YOU control within the stored procedure... as it should have been from day-one!

在此之后，您可以在存储过程中使用您控制的名称访问您的表...因为它应该是从第一天起！

EDIT: selective implementation: Name the first column in the pattern "TN:Customer". Your legacy stored procedures work normally, only impacting the stored procedures you wish to modify.

编辑：选择性实现：命名模式“TN：Customer”中的第一列。您的旧存储过程正常工作，仅影响您要修改的存储过程。

            For Each dt As DataTable In mo_LastDataset.Tables
                Dim tblName() As String = dt.Columns(0).ColumnName.Split(":")
                If tblName.Length >= 2 AndAlso tblName(0).ToUpper = "TN" Then
                    dt.TableName = tblName(1)
                End If
            Next

... david ...

... 大卫 ...

#1