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

Question

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 1

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"; 

Answer 2

Stored procedure :

    select 'tbA','tbB','tbC'      select * from tbA     select * from tbB     select * from tbC 

front-end:

       int i = 1;        foreach (string tablename in dsEmailData.Tables[0].Rows[0][0].ToString().Split(','))        {            dsEmailData.Tables[i++].TableName = tablename;        } 

Hope this helps