How to fill Dataset with multiple tables?

Filling a DataSet with multiple tables can be done by sending multiple requests to the database, or in a faster way: Multiple SELECT statements can be sent to the database server in a single request. The problem here is that the tables generated from the queries have automatic names Table and Table1. However, the generated table names can be mapped to names that should be used in the DataSet.

SqlDataAdapter adapter = new SqlDataAdapter(      "SELECT * FROM Customers; SELECT * FROM Orders", connection);adapter.TableMappings.Add("Table", "Customer");adapter.TableMappings.Add("Table1", "Order");adapter.Fill(ds);

If you are issuing a single command with several select statements, you might use NextResult method to move to next resultset within the datareader: http://msdn.microsoft.com/en-us/library/system.data.idatareader.nextresult.aspx

I show how it could look bellow:

public DataSet SelectOne(int id){    DataSet result = new DataSet();    using (DbCommand command = Connection.CreateCommand())    {        command.CommandText = @"select * from table1select * from table2        ";        var param = ParametersBuilder.CreateByKey(command, "ID", id, null);        command.Parameters.Add(param);        Connection.Open();        using (DbDataReader reader = command.ExecuteReader())        {            result.MainTable.Load(reader);            reader.NextResult();            result.SecondTable.Load(reader);            // ...        }        Connection.Close();    }    return result;}

It is an old topic, but for some people it might be useful:

        DataSet someDataSet = new DataSet();        SqlDataAdapter adapt = new SqlDataAdapter();        using(SqlConnection connection = new SqlConnection(ConnString))        {            connection.Open();            SqlCommand comm1 = new SqlCommand("SELECT * FROM whateverTable", connection);            SqlCommand comm2g = new SqlCommand("SELECT * FROM whateverTable WHERE condition = @0", connection);            commProcessing.Parameters.AddWithValue("@0", "value");            someDataSet.Tables.Add("Table1");            someDataSet.Tables.Add("Table2");            adapt.SelectCommand = comm1;            adapt.Fill(someDataSet.Tables["Table1"]);            adapt.SelectCommand = comm2;            adapt.Fill(someDataSet.Tables["Table2"]);        }