Populating a dataset using table names from a stored procedure

Question

I have a stored procedure that returns multiple tables.

It fills my dataset correctly, but it calls my tables [ Table , Table1 , Table2 , ...].

Is there something I can add to the database level (to my stored procedure) that will correctly name the tables?

Answer 1

Your SP does not actually return multiple tables, but returns columns and rows from your tables, so there is no "table name" and therefore why they are called table1, table2, etc. If this is important, you can return an additional column for each selection, and in this column enter it with the name you want, 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 the 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), which cannot be called tables in the result set. But this will lead you there without a HUGE byte repeating the name of the table in each row.

The remaining overhead passes a NULL value for each row. Perhaps passing the BIT value will be even less ...

And the 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 null rows.

but best for the last ... the way the actual name of the tables in the data set is automatically, every time FROM SQL STORED PROCEDURE (using 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 that, you can access your tables with a name that you control in the stored procedure ... as it should have been from the first day!

EDIT : custom implementation: Name the first column in the template "TN: Client". Your obsolete stored procedures work just fine, only affecting the stored procedures you want 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 ...