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

c#, sqldatareader

Solution

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

Problem

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: - Why this happens. - What is the simplest solution to achieve this goal.

Original source