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

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.

This question is related to c# sql ado.net dataset datareader

The answer is


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

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

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

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

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

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

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

Here is very good answer of your question

see the example mentioned on above MSDN page :-


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

Examples related to c#

How can I convert this one line of ActionScript to C#? Microsoft Advertising SDK doesn't deliverer ads How to use a global array in C#? How to correctly write async method? C# - insert values from file into two arrays Uploading into folder in FTP? Are these methods thread safe? dotnet ef not found in .NET Core 3 HTTP Error 500.30 - ANCM In-Process Start Failure Best way to "push" into C# array

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to ado.net

Error: No Entity Framework provider found for the ADO.NET provider with invariant name 'System.Data.SqlClient' How to fill a datatable with List<T> Populate data table from data reader Increasing the Command Timeout for SQL command how to resolve DTS_E_OLEDBERROR. in ssis Convert DataSet to List How to connect access database in c# MySQL Data Source not appearing in Visual Studio How do I get values from a SQL database into textboxes using C#? Connection to SQL Server Works Sometimes

Examples related to dataset

How to convert a Scikit-learn dataset to a Pandas dataset? Convert floats to ints in Pandas? Convert DataSet to List C#, Looping through dataset and show each record from a dataset column How to add header to a dataset in R? How I can filter a Datatable? Stored procedure return into DataSet in C# .Net Looping through a DataTable adding a datatable in a dataset How to fill Dataset with multiple tables?

Examples related to datareader

How to fill Dataset with multiple tables? Reading a date using DataReader How can I easily convert DataReader to List<T>?