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

Brann_47341 0
Viewed 104896

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.

dataset sql-server sql .net
17 Answers

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

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.

Matt_Hamilton_6150

You could return an extra result set that names the tables like this:

SELECT 'firstTableName' AS tbl
UNION
SELECT 'secondTableName' AS tbl
UNION
...

If that was the first result set, you could use it to name the subsequent result sets as you go.

user_12209630

Try this

YourDataAdapter.Fill(ds, "Table");
user_66979780

should use select into "yourtablename" from originaltable, this is just an example, find the correct syntax of select into statement.

This is the correct way to do it for your requirement. Thankyou.

user_79524240

I know this is old post but I was in similar case when my stored procedure was returning multiple dataset where I am not sure how many dataset will return based on condition satisfied.

So I implemented ordering as below to get my return dataset in specific order so I can manage on C# code each dataset based on provided condition on code side too.

if (dsop != null && dsop.ReturnDataSet != null && dsop.ReturnDataSet.Tables.Count > 0)
{
            foreach (DataTable dt in dsop.ReturnDataSet.Tables)
            {
                if (dt.Rows.Count > 0 && dt.Columns.Count > 1)
                {
                    foreach (DataRow dr in dt.Rows)
                    {            //Alarm table[0]
                        if (dt.TableName == "Table")
                        {
                            OperatorHistory operatorReportEntity = new peratorHistory
                            {                                   
                            };
                            reportdata.Add(operatorReportEntity);
                        }
                        //ARC table[2], DW table[3], GT table[4]
                        else if (dt.TableName == "Table2" || dt.TableName == "Table3" || dt.TableName == "Table4")
                        {
                            OperatorHistory operatorReportEntity = new peratorHistory
                            {                                   
                            };
                            reportdata.Add(operatorReportEntity);
                        }
                   }    
               }           
             }  
  }

--table[0]
if(LEN(@ADEventIdsForAlarms ) > 1)
select * from (
---my query logic here
) as TBL_ADEventIdsForAlarms
else
select 'TBL_ADEventIdsForAlarms'

--table[1]
--/* Just Message  */ UNION ALL
if(LEN(@ADEventIdsForOPR ) > 1)
---my query logic here
) as TBL_ADEventIdsForOPR
else
select 'TBL_ADEventIdsForOPR'

--table[2]
if(LEN(@ADEventIdsForARC) > 1)
select * from (
---my query logic here
) as TBL_ADEventIdsForARC
else
select 'TBL_ADEventIdsForARC'
user_13661700
Related