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: