CODEWITHSUNDEEP
c#datatable
RashidInman
RashidInman

Reputation: 67

Iterate Multiple DataTables

I have a SQL Server stored procedure that I run two select statements. I can easily return one select statement and store it in a DataTable but how do I use two?

How can I set my variable countfromfirstselectstatement equal to the count returned from my first select statement and how can I set my variable countfromsecondselectstatement equal to the count returned from the second select.

private void ReturnTwoSelectStatements()
{
  DataSet dt112 = new DataSet();
  using (var con = new SqlConnection(connectionString))
  {
    using (var cmd = new SqlCommand("Return2SelectStatements", con))
    {
        using (var da = new SqlDataAdapter(cmd))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            da.Fill(dt112);
        }
    }
  }
  DataTable table1 = dt112.Tables[0];
  DataTable table2 = dt112.Tables[1];

  string countfromFirstSelectStatement = table1.Rows.Count().ToString();
  string countfromSecondSelectStatement = table2.Rows.Count().ToString();

  //I only want to iterate the data from the 1st select statement
  foreach (DataRow row in dt112.Rows)
  {

  }
}

Upvotes: 0

Views: 679

Answers (2)

cyi
cyi

Reputation: 96

Fill a Dataset with both select statements and access it:

....
DataSet dataSet = new DataSet();
da.Fill(dataSet);
....

DataTable table1 = dataSet.Tables[0];
DataTable table2 = dataSet.Tables[1];

string countfromFirstSelectStatement = table1.Rows.Count.ToString();
string countfromSecondSelectStatement = table2.Rows.Count.ToString();

Upvotes: 1

Luaan
Luaan

Reputation: 63732

You could also directly use the DbDataReader (cmd.ExecuteReader()), which gives you NextResult to advance to the next result set. DataTable.Load allows you to load rows from the data reader to the table.

DbDataAdapter is really a bit of an overkill for just reading data into a data table. It's designed to allow the whole CRUD-breadth of operation for controls that are abstracted away from the real source of data, which isn't really your case.

Sample:

using (var reader = cmd.ExecuteReader())
{
  dataTable1.Load(reader);

  if (!reader.NextResult()) throw SomethingWhenTheresNoSecondResultSet();

  dataTable2.Load(reader);
}

Upvotes: 2

Related Questions