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

User · Accepted Answer

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

User · Answer

Method Load of DataTable executes NextResult on the DataReader  so you shouldn t call NextResult explicitly when using Load  otherwise odd tables in the sequence would be omitted   Here is a generic solution to load multiple tables using a DataReader      your command initialization code here        DataSet ds   new DataSet    DataTable t  using  DbDataReader reader   command ExecuteReader        while   reader IsClosed          t   new DataTable        t Load rs       ds Tables Add t

User · Answer

string connetionString   null          SqlConnection connection           SqlCommand command           SqlDataAdapter adapter   new SqlDataAdapter            DataSet ds   new DataSet            int i   0          string firstSql   null          string secondSql   null           connetionString    Data Source ServerName Initial Catalog DatabaseName User ID UserName Password Password           firstSql    Your First SQL Statement Here           secondSql    Your Second SQL Statement Here           connection   new SqlConnection connetionString            try                       connection Open                 command   new SqlCommand firstSql  connection               adapter SelectCommand   command              adapter Fill ds   First Table                 adapter SelectCommand CommandText   secondSql              adapter Fill ds   Second Table                 adapter Dispose                command Dispose                connection Close                   retrieve first table data              for  i   0  i  lt   ds Tables 0  Rows Count - 1  i                                  MessageBox Show ds Tables 0  Rows i  ItemArray 0      --     ds Tables 0  Rows i  ItemArray 1                                retrieve second table data              for  i   0  i  lt   ds Tables 1  Rows Count - 1  i                                  MessageBox Show ds Tables 1  Rows i  ItemArray 0      --     ds Tables 1  Rows i  ItemArray 1                                     catch  Exception ex                        MessageBox Show  Can not open connection

User · Answer

protected void Page Load object sender  EventArgs e        SqlConnection con   new SqlConnection  data source   uid sa pwd 123 database shop          SqlCommand cmd   new SqlCommand  select   from tblemployees   con         SqlCommand cmd1   new SqlCommand  select   from tblproducts   con         SqlDataAdapter da   new SqlDataAdapter           DataSet ds   new DataSet          ds Tables Add  emp          ds Tables Add  products          da SelectCommand   cmd        da Fill ds Tables  emp           da SelectCommand   cmd1         da Fill ds Tables  products         SqlDataAdapter da   new SqlDataAdapter  select   from tblemployees   con       DataSet ds   new DataSet        da Fill ds   em        da   new SqlDataAdapter  select   from tblproducts   con       da Fill ds   prod         GridView1 DataSource   ds Tables  em        GridView1 DataBind        GridView2 DataSource   ds Tables  prod        GridView2 DataBind

User · Answer

DataSet ds   new DataSet    using  var reader   cmd ExecuteReader          while   reader IsClosed                           ds Tables Add   Load reader                return ds

User · Answer

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

User · Answer

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

User · Answer

public DataSet GetDataSet                 try                       DataSet dsReturn   new DataSet                using  SqlConnection myConnection   new SqlConnection Core con                                 string query    select   from table1   select  from table2                   SqlCommand cmd   new SqlCommand query  myConnection                   myConnection Open                    SqlDataReader reader   cmd ExecuteReader                    dsReturn Load reader  LoadOption PreserveChanges  new string      tableOne    tableTwo                      return dsReturn                                  catch  Exception                        throw

User · Answer

Here is very good answer of your question  see the example mentioned on above MSDN page  -

[c#] How to fill Dataset with multiple tables?

Examples related to c#

Examples related to sql

Examples related to ado.net

Examples related to dataset

Examples related to datareader