Define table names for results of multiple SQL selects in a single query

Question

For example if I run the following query:

select * from table1
select * from table2

And run it with a DB adapter (C#) I get a dataset with two tables. How can I define the names for the result tables in SQL?

I can only do this inside the SQL. I don't have access to the c# code.

Yancey

Answer 1

@Timothy Khouri: It can be done! EDIT: but not at the SQL level!

You can use TableMappings on the DataAdapter.

If the SelectCommand of a DataAdapter returns multiple result sets, the DataAdapter uses table mappings to fill corresponding DataTables in a DataSet. By default, the first result set will be filled to a DataTable named "Table", and the second result set will be filled to a DataTable named "Table1" etc.

SqlDataAdapter sqlDa = new SqlDataAdapter();
SqlCommand selectCmd = new SqlCommand();
selectCmd.CommandText = "spReturnMultpileResultSets";
selectCmd.CommandType = CommandType.StoredProcedure;
selectCmd.Connection = this.sqlConnection1;
sqlDa.SelectCommand = selectCmd;

// Add table mappings to the SqlDataAdapter
sqlDa.TableMappings.Add("Table", "Customers");
sqlDa.TableMappings.Add("Table1", "Orders");

// DataSet1 is a strongly typed DataSet
DataSet1 ds = new DataSet1();

this.sqlConnection1.Open();

sqlDa.Fill(ds);

this.sqlConnection1.Close();

Refs:

http://blogs.msdn.com/vsdata/archive/2007/03/08/tableadapter-multiple-result-sets.aspx http://www.eggheadcafe.com/software/aspnet/32696845/strongly-typed-datasets.aspx

Cyndycynera

Excellent answer, I should have clarified my response :) You can always rename them after (or use the mappings as you said), but you're basically just going off of the ordinal. We had an issue where a sproc called another sproc, and a dev changed the second one to do a select inbetween! (crash) – Eel

@mitch It's kind of weird that we get multiple result set without explicilty enabling MARS - it isn't enable by default and still this does work. I know cuz i'm doingit at work ( fill datasets) and we don't have mars enabled. yet - how does it work without mars enable ? can u shed light ? – Spanos