CODEWITHSUNDEEP
c#.netsql-serverado.netdataset
Bradley Smith
Bradley Smith

Reputation: 13601

Assign names to tables in an SQL Server result set

I am writing a stored procedure that executes several successive SELECT statements. When I execute this procedure via ADO.NET, my intention is to end up with a DataSet containing several DataTable objects. This behaves as expected.

I am currently relying on the order of the tables in the DataSet to match the order of the SELECT statements in the stored procedure, however there is really no significance in this order. The person who ultimately has to maintain the procedure shouldn't have to know the expected order of the results, nor should the person maintaining the application have to know the order of the statements in the procedure.

What I want to know is, is it possible to assign names to the result of each SELECT statement within the stored procedure itself, and then have these come through via ADO.NET (hopefully seamlessly) so that I can access each table by its name instead of its order?

e.g.

// populate DataSet with results from stored proc
DataSet ds = new DataSet();
dataAdapter.Fill(ds);

// now access one of the resulting DataTable via name
return ds.Tables["NamedResultFromTheProc"];

So, is there any way to achieve this? Or will I have to rely on the order of the SELECT statements and always access the desired table by its index?

Upvotes: 10

Views: 12262

Answers (6)

Mike Rollins
Mike Rollins

Reputation: 21

This is also not the best solution, but you could make the first column in your query be the table name:

    Select 'Customer', CustomerID, CustomerName, CustomerAddress
    From Customer
    Where CustomerID = @CustomerID;
    
    Select 'Orders', OrderID, OrderPrice, OrderDate
    From Order O
    Join Customer C on C.CustomerID = O.CustomerID
    Where C.CustomerID = @CustomerID;
    
    Select 'OrderItems', ItemID, ItemDescription, ItemPrice
    From OrderItems I
    Join Order O on O.OrderID = I.OrderID
    Join Customer C on C.CustomerID = O.CustomerID
    Where C.CustomerID = @CustomerID;

Upvotes: 2

Dave
Dave

Reputation: 3621

I've not tried this but could you not change the structure of the stored proc so that you have a query returning the name of the table before each data query?

i.e.

select 'TableName';
select * from Table where 1 = 1;

then build the Dataset manually by creating tables and adding them in?

Upvotes: 6

Nick Sandel
Nick Sandel

Reputation: 1

I've been thinking about this as well and the only solution I can think of is to create temporary tables within the procedure and populate the results into there (naming the tables as you go).

I've not tried this yet because it doesn't feel like the right way to do it with having to get the results twice (query into temp table, query the temp table).

It would be really useful if you could just rename your result set in SQL in the same way you can rename "Column AS [Custom Column]"...

Upvotes: 0

to StackOverflow
to StackOverflow

Reputation: 124686

The tables returned by your query will be given the names "Table", "Table1", "Table2" etc.

You can add TableMappings to your DataAdapter before filling your DataSet to map them to your table names:

myAdapter.TableMappings.Add("Table", "MyTable1");
myAdapter.TableMappings.Add("Table1", "MyTable2");
myAdapter.TableMappings.Add("Table2", "MyTable3");

Upvotes: 4

mmix
mmix

Reputation: 6278

It is not possible, but its SQL "fault", not the fault of DataAdapter/Set, because result set does not carry the name of the table queried (nor is that discernibly possible if you use inner join) nor does the table adapter have a query from which to pick the name. One method you can use is to first return a list of tables as Query#0 in the procedure, e.g.

select 'MyTable;MySecondTable;ThirdOrSo' as tables

followed by all other queries, then read index 0 table and this field, split/forloop to rename other tables in dataset. The maintainer would still need to know the mechanism but at least it gives him some freedom to reorganize..

Upvotes: 1

Tom
Tom

Reputation: 3374

Unfortunately, I do not believe this is possible! I have a similar setup which gets DataSets from Stored Procedures, and after looking I gave up and resorted to indexes.

Upvotes: 2

Related Questions