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.

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