Saturday, 10 September 2016

Accessing multiple result set using sql data reader next result set in ADO.NET

23:18    No comments

Let's see with an example:
We are creating 3 table and a procedure
select * from employeeMaster


select * from employeeDesignationDetails



 select * from employeeLocationDetails


Creating Procedure for accessing data from the above tables as 3 result set
create proc A_GetmultipleResultSet

as
select * from employeeMaster
select * from employeeDesignationDetails
select * from employeeLocationDetails


In the aspx file put the following code

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ReadingNextResultSetfromProc.aspx.cs" Inherits="ADONET.ReadingNextResultSetfromProc" %>


<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <div>
                <asp:Label ID="lblEmployeeDetails" runat="server" Text="Employee Details"></asp:Label>
                <asp:GridView ID="gdvEmlpoyee" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None">
                    <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
                    <EditRowStyle BackColor="#999999" />
                    <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                    <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                    <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
                    <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
                    <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
                    <SortedAscendingCellStyle BackColor="#E9E7E2" />
                    <SortedAscendingHeaderStyle BackColor="#506C8C" />
                    <SortedDescendingCellStyle BackColor="#FFFDF8" />
                    <SortedDescendingHeaderStyle BackColor="#6F8DAE" />
                </asp:GridView>

            </div>
            <div>
                <asp:Label ID="lblLocationDetails" runat="server" Text="Location Details"></asp:Label>
                <asp:GridView ID="gdvLocationDetails" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None">
                    <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
                    <EditRowStyle BackColor="#999999" />
                    <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                    <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                    <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
                    <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
                    <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
                    <SortedAscendingCellStyle BackColor="#E9E7E2" />
                    <SortedAscendingHeaderStyle BackColor="#506C8C" />
                    <SortedDescendingCellStyle BackColor="#FFFDF8" />
                    <SortedDescendingHeaderStyle BackColor="#6F8DAE" />
                </asp:GridView>

            </div>
            <div>
                <asp:Label ID="lblDesignationDetails" runat="server" Text="Designation Details"></asp:Label>
                <asp:GridView ID="gdvDesignationDetails" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None">
                    <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
                    <EditRowStyle BackColor="#999999" />
                    <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                    <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                    <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
                    <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
                    <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
                    <SortedAscendingCellStyle BackColor="#E9E7E2" />
                    <SortedAscendingHeaderStyle BackColor="#506C8C" />
                    <SortedDescendingCellStyle BackColor="#FFFDF8" />
                    <SortedDescendingHeaderStyle BackColor="#6F8DAE" />
                </asp:GridView>

            </div>
        </div>
    </form>
</body>
</html>

In code behind add the following code
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace ADONET
{
    public partial class ReadingNextResultSetfromProc : System.Web.UI.Page
    {
        public int ResultSet = 0;
        protected void Page_Load(object sender, EventArgs e)
        {
            String CS = ConfigurationManager.ConnectionStrings["EmplyoeeDB"].ConnectionString;
            using (SqlConnection sqlcon = new SqlConnection(CS))
            {
                SqlCommand sqlcmd = new SqlCommand("A_GetmultipleResultSet", sqlcon);
                sqlcmd.CommandType = System.Data.CommandType.StoredProcedure;
                sqlcon.Open();
                SqlDataReader sqlreader = sqlcmd.ExecuteReader();
                gdvEmlpoyee.DataSource = sqlreader;
                gdvEmlpoyee.DataBind();
                while (sqlreader.NextResult())
                {
                    if (ResultSet == 0)
                    {
                        gdvLocationDetails.DataSource = sqlreader;
                        gdvLocationDetails.DataBind();
                        ResultSet++;
                    }
                    else
                    {
                        gdvDesignationDetails.DataSource = sqlreader;
                        gdvDesignationDetails.DataBind();
                    }
                }
                sqlcon.Close();
            }
        }
    }
}


Lets Execute our application we will get the following output


0 comments:

Post a Comment