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

Is there any reason you can't name them manually after filling the DataSet?

mySqlDataAdapter.Fill(ds);
ds.Tables[0].TableName = "NametbA";
ds.Tables[1].TableName = "NametbB";

I don't know of any way to name the DataTables that are returned as part of multiple result sets from a stored procedure, but if you know what the stored proc is returning then manually naming them should work fine.

Edit

Knowing that you have control over the stored procedure, one alternative might be to add a column to the result sets which represents the table name. Then you might be able to do something like:

foreach (DataTable table in ds.Tables)
{
    table.TableName = table.Rows[0]["TableName"].ToString();
}

However, this relies on the result sets coming back from the stored procedures actually containing rows. If they don't contain rows then you'd have to wrap it in an "if" statement and not every table would get a name.

Answer 3

This works for but needs some addition work to get the expected table names :

Dim tableCount As Integer = 3 Dim tables(tableCount) As DataTable

    tables(0) = (New DataTable("Employee"))
    tables(1) = (New DataTable("Manager"))
    tables(2) = (New DataTable("Department"))
    dsUControlData.Tables.Add(tables(0))
    dsUControlData.Tables.Add(tables(1))
    dsUControlData.Tables.Add(tables(2))

    'Fill required tables
    da.Fill(0, 0, tables)

    Return dsUControlData