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.

Why would you expect that two tables are filled? Your command only contains a single `select` statement that returns a single table. — Nikola Anusev, Jul 05 '12 at 13:53
Why not use a `SqlDataAdapter` and its `Fill(...)` method instead of the `DbCommand`? — bluevector, Jul 05 '12 at 13:54
@Nikola Anusev - I know it, so I'm just asking any kind of suggestion — Andriy Zakharko, Jul 05 '12 at 13:55
@jonnyGold - preformanse. DataReader is fast, DataAdapter is slower. This is critical in my case — Andriy Zakharko, Jul 05 '12 at 13:56
@AndriyZakharko: The DataAdapter also uses a DataReader under the hood. http://stackoverflow.com/a/334667/284240 The only advantage of a reader is that you can stream the records one in a time instead of loading all into memory. — Tim Schmelter, Jul 05 '12 at 14:01

score 35 · Answer 1 · answered Dec 19 '12 at 19:17

35

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

answered Dec 19 '12 at 19:17

Mohsen Safari

6,669
5
42
58

Thank you for reply, but according my task - I need to use DataReader instead DataAdapter : that was described in my question – Andriy Zakharko Dec 20 '12 at 07:35
4

adding with TableMappings.Add is not neccassarry – Iman Oct 22 '14 at 10:06
4

@Iman - it might not be necessary if you're sending SQL to the server as per the example, but it is if you're calling a stored-procedure and returning multiple SELECTs. This answer was very useful to me – freefaller Jan 19 '22 at 10:56

score 24 · Accepted Answer · answered Jul 05 '12 at 13:54

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

You can just do `result.Load(reader)`. The `Load` method will handle multiple result-sets. — AMissico, Nov 12 '13 at 01:45
@AMissico, true but which table to fill with which resultset can't be explicitly stated with DataSet.Load, it will break if you define tables in another order. Also you have to state other parameters. — smoothdeveloper, Dec 19 '15 at 19:41

score 8 · Answer 3 · answered Dec 19 '14 at 15:34

8

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"]);
        }

answered Dec 19 '14 at 15:34

CularBytes

9,924
8
76
101

how can handle this situation, if sql(store proc) returning multiple tables? – Pankaj Parkar Sep 29 '16 at 13:32
1

Instead of doing `adapt.Fill(someDataSet.Tables["Table1"])` you would do `adapt.Fill(someDataSet)`. Because ofcourse your stored procedure retruns the tables, but only if it really returns TABLES and not a set of COLUMNS from multiple tables. – CularBytes Sep 29 '16 at 14:11
Thanks for response, will try this thing :-) – Pankaj Parkar Sep 29 '16 at 14:24

score 3 · Answer 4 · answered Jul 05 '12 at 13:56

3

Here is very good answer of your question

see the example mentioned on above MSDN page :-

answered Jul 05 '12 at 13:56

Pranav

8,563
4
26
42

Mario Vázquez · Answer 5 · 2022-10-13T11:05:03.800

3

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.

public static DataSet DataSetFromReader(IDataReader reader)
{
    DataSet ds = new DataSet();
    while (!reader.IsClosed)
    {
        DataTable t = new DataTable();
        t.Load(reader);
        ds.Tables.Add(t);
    }
    return ds;
}

edited Oct 13 '22 at 11:05

answered Sep 13 '18 at 10:27

Mario Vázquez

717
10
9

1

This one worked for me, very useful when I'm unsure how many separate tables are being returned in a stored proc (as I just found out when hitting a legacy db with encrypted stored procs) – George Newton Apr 08 '19 at 12:47

score 1 · Answer 6 · edited Sep 08 '15 at 08:15

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

score 1 · Answer 7 · answered Apr 02 '18 at 13:41

         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 <= 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 <= 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 ! ");
        }

score 1 · Answer 8 · answered Aug 08 '18 at 10:07

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

score 0 · Answer 9 · answered Dec 28 '20 at 09:55

0

DataSet ds = new DataSet();
using (var reader = cmd.ExecuteReader())
{
    while (!reader.IsClosed)
         {
              ds.Tables.Add().Load(reader);
         }
}
return ds;

answered Dec 28 '20 at 09:55

Nazim

1

2

Please don't post only code as answer, but also provide an explanation what your code does and how it solves the problem of the question. Answers with an explanation are usually more helpful and of better quality, and are more likely to attract upvotes. – Pouria Hemi Dec 28 '20 at 10:19

How to fill Dataset with multiple tables?

9 Answers9

Linked