CODEWITHSUNDEEP
c#sqlsql-server
user1409590
user1409590

Reputation: 13

Change returned table name from stored procedure at the SQL side

I have written a single stored procedure that returns 2 tables: 

select * 
from workers

select *
from orders

I call this stored procedure from my C# application and get a DataSet with two tables, and everything is working fine.

My question is how can I change the tables name at the SQL Server side so that in the C# side I will be able to access it via a name (instead of Tables[0]):

myDataSet.Tables["workers"]...

I tried to look for the answer in Google but couldn't find it. Maybe the search keywords was not sufficient.

Upvotes: 1

Views: 1751

Answers (2)

marc_s
marc_s

Reputation: 755371

You cannot really do anything from the server-side to influence those table names - those names only exist on the client-side, in your ADO.NET code.

What you can do is on the client-side - add table mappings - something like:

SqlDataAdapter dap = new SqlDataAdapter(YourSqlCommandHere);
dap.TableMappings.Add("Table", "workers");
dap.TableMappings.Add("Table1", "orders");

This would "rename" the Table (first result set) to workers and Table1 (second result set) to orders before you actually fill the data. So after the call to

dap.Fill(myDataSet);

you would then have myDataSet.Tables["workers"] and myDataSet.Tables["orders"] available for you to use.

Upvotes: 4

Filip De Vos
Filip De Vos

Reputation: 11908

The TDS Protocol documentation (Which is the protocol used to return results from SQL Server) does not mention a "resultset name". So the only way you will ever be able to access the result sets in ADO.net is by the number as mentioned in your example.

Upvotes: 0

Related Questions