SqlDataReader object NextResult() method - Part 9

Suggested Videos
Part 6 - Sql injection prevention
Part 7 - Calling a stored procedure with output parameters
Part 8 - SqlDataReader object in ado.net

In this video we will learn about retrieving two or more result sets using the SqlDataReader object's NextResult() method. The follwoing SqlCommand object returns two result-sets, one from - tblProductInventory and the other from tblProductCategories.
SqlCommand command = new SqlCommand("select * from tblProductInventory; select * from tblProductCategories", connection);

When you run this code only the result set from tblProductInventory is displayed. The result-set from tblProductCategories is not shown.
string ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
    connection.Open();
    SqlCommand command = new SqlCommand("select * from tblProductInventory; select * from tblProductCategories", connection);
    using (SqlDataReader reader = command.ExecuteReader())
    {
        ProductsGridView.DataSource = reader;
        ProductsGridView.DataBind();

        CategoriesGridView.DataSource = reader;
        CategoriesGridView.DataBind();
    }
}



To retrieve the second result-set from SqlDataReader object, use the NextResult() as shown in the code snippet below. The NextResult() method returns true and advances to the next result-set. 
string ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
    connection.Open();
    SqlCommand command = new SqlCommand("select * from tblProductInventory; select * from tblProductCategories", connection);
    using (SqlDataReader reader = command.ExecuteReader())
    {
        ProductsGridView.DataSource = reader;
        ProductsGridView.DataBind();

        while (reader.NextResult())
        {
            CategoriesGridView.DataSource = reader;
            CategoriesGridView.DataBind();
        }
    }
}

The SqlDataReader object's Read() method is used to loop thru the rows in a given result set, where as the NextResult() method is used to loop thru multiple  result sets.



Sql script to create and populate the required tables we used in this demo.
Create table tblProductInventory 
(
ProductId int identity primary key,
ProductName nvarchar(50),
UnitPrice int
)

Insert into tblProductInventory values('iPhone',350)
Insert into tblProductInventory values('Apple Laptops',1250)
Insert into tblProductInventory values('Books',110)
Insert into tblProductInventory values('Acer Laptops',1150)
Insert into tblProductInventory values('iPads',450)

Create table tblProductCategories
(
CategoryId int identity primary key,
CategoryName nvarchar(50),
)

Insert into tblProductCategories values ('Electronics')
Insert into tblProductCategories values ('Sports')
Insert into tblProductCategories values ('Kitchen')