CODEWITHSUNDEEP
.netsqlsql-serverdataset
Brann
Brann

Reputation: 32396

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

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.

Upvotes: 41

Views: 112226

Answers (17)

Softtech
Softtech

Reputation: 112

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'

Upvotes: 0

GenTech
GenTech

Reputation: 738

This question is an old one but, it's at the top of google search, hehe. Maybe it will help somebody ...

And the answer is yes, it's possible to specify the name of the tables in the code so that you can access DataTable in DataSet by its name.

First, I must explain how TableMapping works. If we don't specify TableMappings with SqlDataAdapter (SqlDataAdapter that will fill our DataSet) then by default first table will be named "Table", second will be named "Table1", third will be named "Table2" etc.

So, when we want to name DataTable in DataSet, we use it like this:

//...

System.Data.DataSet myDataSet = new System.Data.DataSet();

using (System.Data.SqlClient.SqlDataAdapter dbAdapter = new System.Data.SqlClient.SqlDataAdapter(dbCommand))
{
    dbAdapter.TableMappings.Add("Table", "MyFirstTitleForTableOne");
    dbAdapter.TableMappings.Add("Table1", "MyFirstTitleForTableTwo");
    dbAdapter.TableMappings.Add("Table2", "MyFirstTitleForTableThree");
    dbAdapter.TableMappings.Add("Table3", "MyFirstTitleForTableFour");
        //...

    dbAdapter.Fill(myDataSet);
}

//...

And, now we can access DataTable by our title:

System.Data.DataTable firstTable = myDataSet.Tables["MyFirstTitleForTableOne"];
System.Data.DataTable secondTable = myDataSet.Tables["MyFirstTitleForTableTwo"];

Sorry, but I didn't write code that will check for null (myDataSet) or set it in try/catch block because I think that is not irrelevant to this question.

Upvotes: 0

Levi M
Levi M

Reputation: 65

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.

Upvotes: 0

sushil.agarwal
sushil.agarwal

Reputation: 161

Try this. In MS SQL 2008 R2, I always use this.

mySqlDataAdapter.Fill(ds,"NameTableA");

Upvotes: 0

R4n0n
R4n0n

Reputation: 1

Try this

YourDataAdapter.Fill(ds, "Table");

Upvotes: 0

Anil Pal
Anil Pal

Reputation: 101

You can try this:

add below line in your procedure

Select 'TableName1','TableName2';

Using C#:

for (var i=0;i<ds.Tables[0].Columns.Count;i++)
 {
    ds.Tables[i + 1].TableName = ds.Tables[0].Columns[i].ColumnName;

  }

Upvotes: 0

Ramzan Mohideen
Ramzan Mohideen

Reputation: 11

In order to give names to all table in a dataset, we need to write one more select query at last of the SP or T-Sql.

    lsqry = New System.Text.StringBuilder
lsqry.AppendLine("select * from Bill_main")
lsqry.AppendLine("select * from serviceonly")
lsqry.AppendLine("select * from PendingService")
lsqry.AppendLine("select * from T_servicebillhd")
lsqry.AppendLine("select 'Bill_Main','serviceonly','PendingService','T_servicebillhd'")

Using con As New SqlConnection(lsConnection)
    Try
        con.Open()
        Using cmd As SqlCommand = con.CreateCommand
            With cmd
                .CommandText = lsqry.ToString
                .CommandType = CommandType.Text
            End With
            Using da As New SqlDataAdapter(cmd)
                da.Fill(DS)
            End Using
        End Using

        For li As Integer = 0 To DS.Tables.Count - 2
            DS.Tables(li).TableName = DS.Tables(DS.Tables.Count - 1).Rows(0).Item(li)
        Next
    Catch ex As Exception

    End Try
End Using

In the above example, i am using t-sql statement instead of SP. In which i wrote 4 select query and last one is table name of those tables. And fetch last table which contains names of tables and assign it to table using TableName property.

Upvotes: 1

vinayak hegde
vinayak hegde

Reputation: 2252

May be this could be the work around 

 Create procedure psfoo ()
 AS
 select * ,'tbA' as TableName from tbA
 select * ,'tbB' as TableName from tbB

Then in C# code 

     foreach (DataTable dt in ds.Tables)
        {
            if (dt.Rows[0]["TableName"].ToString().Contains("tbA"))
            {

            }
            else if (dt.Rows[0]["TableName"].ToString().Contains("tbB"))
            {

            }
        }

Upvotes: -1

Ashish
Ashish

Reputation: 9

This works for me but needs some additional 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

Upvotes: 0

Rory Hunter
Rory Hunter

Reputation: 3470

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.

Upvotes: 0

Keith Beard
Keith Beard

Reputation: 1611

I know this is an old question but I was thinking of doing the same things today so i wasn't just looping through an index of the result dataset. Because if the stored procedure changes in order of select the .net code would bomb out.

I came up with the solution of adding a column to the result set with the table name and using a case statement to see if the column exists in the table. But it just seemed like i was moving code around. Just made the SQL static. Plus what if you have a column named the same in a different table. Lame...

myDS.Tables(i).Columns.Contains("TableName")

Upvotes: 0

Joe
Joe

Reputation: 1

I know this is a very old post, but to answer... Yes, it's possible. Simply add the "TableName" in the dataadapter Fill call..

MyDataAdapter.Fill(ds, "TableName");

I'm a C# guy, but you get the point.

Upvotes: 0

Thangamani Palanisamy
Thangamani Palanisamy

Reputation: 5300

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

Upvotes: 11

toddmo
toddmo

Reputation: 22466

I have one stored proc that accesses all the data in my enterprise and returns multiple result sets. It makes more sense to me to do a select with the name before each result set. Like so:

SELECT 'Customers' AS TableName
SELECT ID, Name FROM dbo.Customer
SELECT 'Orders' AS TableName
SELECT ID, Created FROM dbo.[Order]

Then when I iterate over MyDataSet.Tables, I only have the one loop and straight-forward lookup logic. There isn't a master metadata table to keep up with. This even works when no tables are returned. If table data is not returned, the matching meta data is not returned, which makes sense to me.

For Each DataTable As DataTable In MyDataSet.Tables
  MyDataSet.Tables(MyDataSet.Tables.IndexOf(DataTable) + 1).TableName = DataTable.Rows(0)("TableName")
Next

If I want to add more columns to the meta-data tables I can.

Upvotes: 2

DeveloperX
DeveloperX

Reputation: 4683

good idea ,its better your procedure returns three resultset instead of adding table name on 

firs trow of each table
select 'tbA' FirstTableName,'tbB' SecondTableName
select * from tbA
select * from tbB

everytime you executing procedure the tables[0] will have a row that keeps follwing select tablenames

Another idea can be passing table name as output paramter of procedure

Create procedure psfoo (@tb1 varchar(50) output,@tb2 varchar(50) output)
AS
set @tb1='tbA'
set @tb2 'tbB'
select * from tbA
select * from tbB

if this methods limits you and selects are variable you can create procedure like this

Create procedure psfoo ()
    AS
    select * from tbA
    select * from tbB
return 'tbA,tbB'

and by splitting procedure retrun value into string[] you can get the name of tables ordinally

[email protected](new char[]{','});
string tablenam1=returnvalue[0];
string tablenam2=returnvalue[1];

Upvotes: 2

David Wengier
David Wengier

Reputation: 10179

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

Upvotes: 35

Matt Hamilton
Matt Hamilton

Reputation: 204259

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.

Upvotes: 7

Related Questions