How can you name the Dataset's Tables you return in a stored proc?

Brann picture Brann · Feb 26, 2009 · Viewed 94.6k times · Source

I've got the following stored procedure

Create procedure psfoo ()
AS
select * from tbA
select * from tbB

I'm then accessing the data this way :

     Sql Command mySqlCommand = new SqlCommand("psfoo" , DbConnection)
     DataSet ds = new DataSet();
     mySqlCommand.CommandType = CommandType.StoredProcedure;
     SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
     mySqlDataAdapter.SelectCommand = mySqlCommand;
     mySqlDataAdapter.Fill(ds);

Now, when I want to access my tables, I have to do this :

     DataTable datatableA = ds.Tables[0];
     DataTable datatableB = ds.Tables[1];

the dataset Tables property also got an accessor by string (instead of int).

Is it possible so specify the name of the tables in the SQL code, so that I can instead write this :

     DataTable datatableA = ds.Tables["NametbA"];
     DataTable datatableB = ds.Tables["NametbB"];

I'm using SQL server 2008, if that makes a difference.

Answer

David Wengier picture David Wengier · Feb 26, 2009

As far as I know, from the stored proc, you can't do that. You can, however, set the names once you have retrieved the DataSet, and then use them from then on.

ds.Tables[0].TableName = "NametbA";