Naming DataSet.table after executing SQLCommand (Select) request

Question

In MS SQL stored procedure My query:

SELECT * FROM ContentReportRequests a,UserPreferences d WHERE a.UserID = d.UserID and a.ID =@ID 

I want to give the result table some name. How can i do this?

I want to pull it into ADO.Net DataSet.tables ["NAME"]

Answer 1

I can imagine a few things you can keep in mind.

If you want to save this result set, for consumption in the next few queries you can search for SELECT INTO:

 SELECT * into NewTableName FROM ContentReportRequests a,UserPreferences d WHERE a.UserID = d.UserID and a.ID =@ID 

Where NewTableName is the new name, and a new (persistent) table will be created. If you want this table to disappear when you are finished, the prefix name is # to make it a temporary table.

Alternatively, you can just soak it into one larger request, in which case you want to make it a subquery:

 SELECT * FROM (SELECT * FROM ContentReportRequests a,UserPreferences d WHERE a.UserID = d.UserID and a.ID =@ID ) NewTableName WHERE NewTableName.ColumnValue = 'abc' 

or CTE:

 WITH NewTableName AS ( SELECT * FROM ContentReportRequests a,UserPreferences d WHERE a.UserID = d.UserID and a.ID =@ID ) SELECT * from NewTableName 

Finally, you can talk about pulling out the result set, for example. ADO.Net DataTable, and you want the name to be configured automatically. I am not sure if this is possible.

Answer 2

You can use a variable like table . More details here: Variable tables in T-SQL

Answer 3

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

but best for the last ... the way the actual name of the tables in the dataset 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 legacy stored procedures work 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 ...

Answer 4

 SELECT * AS MyTableName FROM ContentReportRequests a, UserPreferences d WHERE a.UserID = d.UserID and a.ID =@ID