datasets with actual tablename

Question

i am trying to get data from my stored proc into my dataset. The problem is that in the dataset visualizer the actual table name ie customers or employees does not show up just Table1 , table2 etc. Is it possible to get the actual table names?

   using (SqlConnection sqlConnection = new SqlConnection("Data Source=myserver;Initial Catalog=Northwind;Integrated Security=True"))
        {
            sqlConnection.Open();

            SqlDataAdapter da = new SqlDataAdapter("EXECUTE  [Northwind].[dbo].[GetCustomers_Employees] ", sqlConnection);
            DataSet ds = new DataSet();
            da.Fill(ds);
        }

CREATE PROCEDURE GetCustomers_Employees
AS
BEGIN
SELECT top 10 * from customers
select top 10 * from Employees
END

Answer 1

You can add a name when you do the fill operatation, like this:

da.Fill(ds, "MyTable");

From that point forward, you can refer to the table as

ds.Tables["MyTable"];

instead of using the integer index (i.e.ds.Tables[0])

See here: http://msdn.microsoft.com/en-us/library/bh8kx08z(v=VS.100).aspx

EDIT:

In your case, you could use the TableName property, like this:

da.Fill(ds);
ds.Tables[0].TableName = "Customers";
ds.Tables[1].TableName = "Employees";

That is the quick and dirty approach, but not very general. Unfortunately, there is no way to get the names of the tables from the SP, which is probably what you want. One way to do that would be to modify your SP to return an output parameter:

CREATE PROCEDURE GetCustomers_Employees
   @tableNames varchar(20) OUTPUT
AS
BEGIN
    SET @tableNames = 'Customers,Employees'
    SELECT top 10 * from Customers
    SELECT top 10 * from Employees
END

But to make use of this, you also have to modify your SqlDataAdapter to handle a stored procedure with an output parameter:

using (SqlConnection = ...)
    {
       // sqlConnection.Open(); // Not really needed. Data Adapter will do this.

        SqlCommand cmd = new SqlCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "GetCustomers_Employees";
        cmd.Connection = sqlConnection;

        // Create the parameter object and add it to the command
        SqlParameter param = new SqlParameter("@tableNames", SqlDbType.VarChar);
        param.Direction = ParameterDirection.Output;
        cmd.Parameters.Add(param);

        // Get the Data
        SqlDataAdapter da = new SqlDataAdapter();
        da.SelectCommand = cmd;
        DataSet ds = new DataSet(); 
        da.Fill(ds);

        // Set the names of the tables in the dataset
        string strTableNames = cmd.Parameters["@tableNames"].Value.ToString();
        string[] tableNames = strTableNames.split(',');

        for (int i=0; i<tableNames.Length; i++)
        {
            ds.Tables[i].TableName = tableNames[i];
        }
    }

Note that the above will handle any number of tables returned, so you could easily encapsulate this in a function, which you might find useful:

DataSet function(string storedProcedureName, string connectionString)
{
    DataSet ds = new DataSet();
    ... // code above, without DataSet declaration
    return ds;
}