5

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.

LiorE
  • 111
  • 4
  • 9

1 Answers1

9

@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

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
  • 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) – Timothy Khouri Nov 01 '08 at 17:43
  • @mitch It's kind of weird that we get multiple result set without explicilty enabling [MARS](http://msdn.microsoft.com/en-us/library/h32h3abf(v=vs.110).aspx) - 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 ? – Royi Namir May 26 '14 at 18:07