While using data reader we know the limitations that it can load one result set at a time.

So sometime we have situations where we need to read multiple result sets in our application.

Their are many cases when we need to execute multiple SQL statements with the Command Object then in those case SqlDataReader will result multiple result set.

In order to receive multiple result set we use NextResult method of DataReader.

  sqlReader.NextResult();

Example:

ListViewItem item;
                lv_orders.Columns.Add("Order No", 50);
                lv_orders.Columns.Add("Name", 100);
                lv_orders.Columns.Add("Quantity", 50);
                lv_orders.View = View.Details;
                lv_orders.GridLines = true;
                lv_customer.Columns.Add("ID", 50);
                lv_customer.Columns.Add("Customer Name", 50);
                lv_customer.View = View.Details;
                lv_customer.GridLines = true;
                lv_employee.Columns.Add("ID", 50);
                lv_employee.Columns.Add("Name", 100);
                lv_employee.Columns.Add("Job", 50);
                lv_employee.Columns.Add("Manager", 70);
                lv_employee.Columns.Add("Joining Date", 100);
                lv_employee.Columns.Add("Salary", 100);
                lv_employee.Columns.Add("Commission", 100);
                lv_employee.Columns.Add("Department", 50);
                lv_employee.View = View.Details;
                lv_employee.GridLines = true;
                string connectionstring = "Data Source=servername;Initial Catalog=DatabaseName;User ID=UserName;Password=password";
                string Sql = "SELECT TOP 2 *  FROM ORDERS;SELECT TOP 2* FROM CUSTOMERS;SELECT * FROM EMP";
                using (SqlConnection conn = new SqlConnection(connectionstring))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(Sql, conn);
                    SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        //MessageBox.Show("First SQL - " + reader.GetValue(0) + " - " + reader.GetValue(1)+ " - " + reader.GetValue(2));
                        item = new ListViewItem( new string[] { reader.GetValue(0).ToString(),
                                  reader.GetValue(1).ToString(), reader.GetValue(2).ToString()});
                        lv_orders.Items.Add(item);
                    }
                    reader.NextResult();
                    while (reader.Read())
                    {
                        //MessageBox.Show("Second SQL - " + reader.GetValue(0) + " - " + reader.GetValue(1));
                        item = new ListViewItem(new string[] { reader.GetValue(0).ToString(), reader.GetValue(1).ToString()});
                        lv_customer.Items.Add(item);
                    }
                    reader.NextResult();
                    while (reader.Read())
                    {
                        item = new ListViewItem(new string[] { reader.GetValue(0).ToString(),
                                            reader.GetValue(1).ToString(),
                                            reader.GetValue(2).ToString(),
                                            reader.GetValue(3).ToString(),
                                            reader.GetValue(4).ToString(),
                                            reader.GetValue(5).ToString(),
                                            reader.GetValue(6).ToString(),
                                            reader.GetValue(7).ToString()
                                            });
                        lv_employee.Items.Add(item);
                    }
                    reader.Close();
                }
            }