Set dbo.Table name as DataTable Name

Question

I have a Stored Procedure which returns 14 tables. Now, in my application the dataset which is containing all the dataTables with DataTable name as table, table1, table2, table3 ... so on. What I want here is , my dataset should have all the datatables having same name as database table names. Is it possible ?

Thanks in advances.

I guess the same question is [here](http://stackoverflow.com/questions/2801799/how-to-return-table-name-from-stored-procedure-in-dataset). The short answer, you can't, sorry. — alexb, Jan 16 '13 at 12:46
@alexb: The [longer answer](http://stackoverflow.com/a/14358983/284240), you can ;) — Tim Schmelter, Jan 16 '13 at 13:06
@Tim Schmelter I stay on my opinion :). The way I understand his issue, he needs an automatic table name assignement not a manual onebut if I understood this wrong, mea culpa — alexb, Jan 16 '13 at 13:17
@NayeemKhan, can you please update your question by specifying if you need table names directly from DB or it doesn't matter as long as you will be able to name the System.Data.DataTable(s) — alexb, Jan 16 '13 at 13:24
@alexb: i'm afraid you're right. I haven't found a way to get the table names of a SP that returns multiple result sets automatically. Tried with `con.getSchema`, `dataReader.GetSchemaTable` and so on(the BaseTable is just `null`). So i think you simply should not return multiple result sets from the Stored-Procedure. It might also increase readability and reusability of when you split them into multiple procs. — Tim Schmelter, Jan 16 '13 at 13:52
@Tim Schmelter I agree, as far as I know sql connections have their own pool so I don't think getting multiple result sets in a single sproc have significant performance implications — alexb, Jan 16 '13 at 14:04

Tim Schmelter · Answer 1 · 2013-01-16T13:11:00.043

You can use TableMappings for your DataAdapter to get the correct table names:

using (var con = new SqlConnection(connectionString))
{
   string sql = @"SELECT * FROM locGSP; 
                  SELECT * FROM locCountry; 
                  SELECT * FROM locMarketUnit";
   using(var da = new SqlDataAdapter(sql, con))
   {
       // demonstrate the issue here:
       DataSet dsWrong = new DataSet();
       da.Fill(dsWrong); // now all tables are in this format: Table,Table1,Table2

       // following will map the correct names to the tables
       DataSet dsCorrect = new DataSet();
       da.TableMappings.Add("Table", "locGSP");
       da.TableMappings.Add("Table1", "locCountry");
       da.TableMappings.Add("Table2", "locMarketUnit");
       da.Fill(dsCorrect); // now we have the correct table-names: locGSP,locCountry,locMarketUnit
   }
}

Here's a different way using a DataReader and DataSet.Load to fill the DataSet:

using (var con = new SqlConnection(connectionString))
{
   string sql = @"SELECT * FROM locGSP; 
                  SELECT * FROM locCountry; 
                  SELECT * FROM locMarketUnit";
   using (var cmd = new SqlCommand(sql, con))
   {
       con.Open();
       using (var rdr = cmd.ExecuteReader())
       {
           // after the next line the DataSet will have the correct table-names
           ds.Load(rdr, LoadOption.OverwriteChanges, "locGSP", "locCountry", "locMarketUnit");
       }
   }
}

Background:

Populating a DataSet from a DataAdapter

Multiple Result Sets: If the DataAdapter encounters multiple result sets, it creates multiple tables in the DataSet. The tables are given an incremental default name of TableN, starting with "Table" for Table0. If a table name is passed as an argument to the Fill method, the tables are given an incremental default name of TableNameN, starting with "TableName" for TableName0.

score 0 · Answer 2 · answered Jan 16 '13 at 13:04

Here you have a gist to help you with this mappings.

Usage:

SomeDataSet dt = DataUtil.Fill<SomeDataSet>(
    procedureName: "someSP", 
    connectionString: "yourConnectionString", 
    values: new {
        someValue = 15,
        otherValue = "test",
    },
    mappings: new { 
        Table1: "YourDataSetTable1",
        Table2: "YourDataSetTable2",
    }
);

(last code is only for framework 4.0)

score -1 · Answer 3 · answered Jan 16 '13 at 12:29

-1

Your procedure returns 14 result sets, not tables. Unless you specifically name them, they are unnamed result sets. For example, what is the table name of the following query?

select c.Name,sum(o.orders) as NumOrder,sum(o.price) as TotalPrice
from 
customer c 
join orders o on o.cust_id=c.cust_id
group by c.name

Valid SQL query, but SQL has no idea what to call this "virtual table" or result set.

answered Jan 16 '13 at 12:29

Sparky

14,967
2
31
45

If he is pulling the results into a `DataSet`, which is a basically collection of `DataTable`s, he _can_ name the `DataTable`s themselves using the `DataTable` naming facility... – MoonKnight Jan 16 '13 at 12:34
True, he can name them himself, but SQL doesn't know what to name them – Sparky Jan 16 '13 at 16:46

Set dbo.Table name as DataTable Name

3 Answers3

Linked