Populate Dataset With Table Names From Stored Proc

Question

I have a stored procedure that returns multiple tables.

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

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

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

i.e.

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

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

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.

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

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

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

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

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.

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