How to retrieving two or more result sets using SqlDataReader object’s NextResult() method in ASP.Net c#?

In this article we will discuss, How to retrieving two or more result sets using SqlDataReader object’s NextResult() method in ASP.Net c#.  To retrieve the second result-set from SqlDataReader object using the NextResult() . In this example sqlCommand object will return two result-sets one is employee and ProductDetail tables.

We will be using ProductDetail and Employee table.

Step 1: Create a table using the following script with data:

CREATE TABLE [dbo].[Employee](
      [EmployeeID] [int] IDENTITY(1,1) NOT NULL,
      [Name] [nvarchar](50) NULL,
      [Gender] [nvarchar](50) NULL,
      [City] [nvarchar](50) NULL,
 CONSTRAINT [PK_tbl_Employee] PRIMARY KEY CLUSTERED 
(
      [EmployeeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Employee] ON
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Gender], [City]) VALUES (1, N'Thivan', N'male', N'tirunelveli')
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Gender], [City]) VALUES (2, N'Rasik', N'male', N'Tuticorin')
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Gender], [City]) VALUES (3, N'Usman', N'male', N'tirunelveli')
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Gender], [City]) VALUES (4, N'karishma', N'female', N'mumbai')
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Gender], [City]) VALUES (5, N'chaitrali', N'female ', N'mumbai')
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Gender], [City]) VALUES (6, N'mansoor', N'male', N'gujarat')
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Gender], [City]) VALUES (7, N'mydeen', N'male', N'chennai')
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Gender], [City]) VALUES (8, N'zubair', N'male', N'melapalayam')
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Gender], [City]) VALUES (9, N'matkar', N'male', N'mumbai')
INSERT [dbo].[Employee] ([EmployeeID], [Name], [Gender], [City]) VALUES (10, N'Rahim', N'male', N'mumbai')
SET IDENTITY_INSERT [dbo].[Employee] OFF
  
CREATE TABLE ProductDetail 
(
 ProductId int identity primary key,
 ProductName nvarchar(50),
 UnitPrice int
)

INSERT INTO ProductDetail VALUES('Lenova',523)
INSERT INTO ProductDetail VALUES('Nokia 520',550)
INSERT INTO ProductDetail VALUES('Micromax',560)
INSERT INTO ProductDetail VALUES('Samsung Galaxy S5',926)
INSERT INTO ProductDetail VALUES('Sony',450)

Step 2: Copy and paste the following code.

Default.aspx:

    <table style="border: 1px solid #e2e2e2; font-family: Arial">
        <tr>
            <td style="padding:10px 5px 5px 40px">
                <asp:GridView ID="GridView1" runat="server" ></asp:GridView>
            </td>
        </tr>
          <tr>
            <td style="padding:10px 5px 5px 40px">
                <asp:GridView ID="GridView2" runat="server" ></asp:GridView>
            </td>
        </tr>
    </table>

Default.aspx.cs:

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
 
public partial class _Default : Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            string ConnString = ConfigurationManager.ConnectionStrings["ShoppingZone"].ConnectionString;
            using (SqlConnection connection = new SqlConnection(ConnString))
            {
                connection.Open();
                SqlCommand cmd = new SqlCommand("Select * from ProductDetail; select * from Employee", connection);
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    GridView1.DataSource = reader;
                    GridView1.DataBind();
                    while (reader.NextResult())
                    {
                        GridView2.DataSource = reader;
                        GridView2.DataBind();
                    }
                }
            }
        }
    }
}

Output: