CODEWITHSUNDEEP
c#sqlado.netdatasetdatareader
Andriy Zakharko
Andriy Zakharko

Reputation: 1673

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.

Upvotes: 23

Views: 137161

Answers (9)

Mario Vázquez
Mario Vázquez

Reputation: 777

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

Upvotes: 3

Nazim
Nazim

Reputation: 1

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

Upvotes: 0

Kashif Faraz
Kashif Faraz

Reputation: 331

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

Upvotes: 1

Mitesh Gadhiya
Mitesh Gadhiya

Reputation: 362

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

Upvotes: 1

user5311866
user5311866

Reputation: 11

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

Upvotes: 1

CularBytes
CularBytes

Reputation: 10321

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

Upvotes: 8

Mohsen Safari
Mohsen Safari

Reputation: 6795

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

Upvotes: 37

Pranav
Pranav

Reputation: 8871

Here is very good answer of your question

see the example mentioned on above MSDN page :-

Upvotes: 3

smoothdeveloper
smoothdeveloper

Reputation: 2022

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

Upvotes: 24

Related Questions