How to fill Dataset with multiple tables?

Question

I'm trying to fill DataSet which contains 2 tables with one to many relationship. I'm using DataReader to achieve this :

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

But I've got only one table filled up. How do I achieve my goal - fill both tables?

I would like to use DataReader instead DataAdapter, if it possible.

Answer 1

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); 

Answer 2

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 table1 select * 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; }