CODEWITHSUNDEEP
c#sqldatareader
FLICKER
FLICKER

Reputation: 6683

DataTable.Load Skips to next result set in multi-result DataReader

I have a multi result query and am trying to bind each result to separate DataGridView.

using (SqlConnection con = new SqlConnection(connectionString))
{
    con.Open();
    SqlCommand cmd = new SqlCommand("SELECT 1 select 2 select 3 select 4 select 5 select 6"
                                    , con);
    SqlDataReader reader = cmd.ExecuteReader();
    int x = 50;
    int y = 100;
    do
    {
        DataGridView dgv1 = new DataGridView();
        DataTable dt = new DataTable();
        dt.Load(reader);
        dgv1.DataSource = dt;
        dgv1.Left = x;
        dgv1.Top = y;
        dgv1.Height = 60;
        y = y + 70;
        this.Controls.Add(dgv1);
    } while (reader.NextResult());
    reader.Close();
}

if I do not load data into DataTable and do not bind it to grid, I will get 6 result set, but in code above, I get on;y the 1st, 3rd and 5th result set, Looks like populating skips a result set in each loop.

Question are:

  1. Why this happens.
  2. What is the simplest solution to achieve this goal.

Upvotes: 5

Views: 2123

Answers (2)

ggo
ggo

Reputation: 471

The answer using read.IsClosed() is not wrong, but beware:

It seems there's a problem when the number of fields is not the same in all the SELECTs (the same data type too):

eg 1: 

    select 2,2 select 1

eg 2: 

    select 2,2 select 'one', 'one'

(tested with .NET 4.5.2)

Upvotes: 0

user7577311
user7577311

Reputation:

DataTable.Load, advances to next result set so you don't need to use NextResult().

Just do the loop until reader is open.

using (SqlConnection con = new SqlConnection(connectionString))
{
    con.Open();
    SqlCommand cmd = new SqlCommand("SELECT 1 select 2 select 3 select 4 select 5 select 6"
                                    , con);
    SqlDataReader reader = cmd.ExecuteReader();
    int x = 50;
    int y = 100;
    do
    {
        DataGridView dgv1 = new DataGridView();
        DataTable dt = new DataTable();
        dt.Load(reader);
        dgv1.DataSource = dt;
        dgv1.Left = x;
        dgv1.Top = y;
        dgv1.Height = 60;
        y = y + 70;
        this.Controls.Add(dgv1);
    } while (!reader.IsClosed); // here is the change
    reader.Close();
}

Upvotes: 10

Related Questions