Loading Gridview Records from Selecting Page Number's Wise

Gridview Page Loading

In this article i'm going to explain about how to load gridview records based on selecting page numbers wise, for this articles i'm using SQL Server Sample Database i.e NorthWind and Pubs Database and you can download this database from below section.

Download Database

download northwind Database


Create Stored Procedure

i'm using proc_getCustomerLoading StoredProcedure and this storedprocedure have two parameters i.e @pageIndex and @pageSize. Just imagine is that @pageIndex is nothing but a PageNumber, @pageSize it indicate how many records you want to display in Page

Create procedure [dbo].[proc_getCustomerLoading](
@pageIndex int, 
@pageSize int)
    INTO   #LoadCustomer
    FROM   Customers;
    DECLARE @recordCount AS INT;
    SELECT @recordCount = count(*) FROM #LoadCustomer;
    SELECT * FROM #LoadCustomer
    WHERE  RowNo BETWEEN (@pageIndex - 1) * @pageSize + 1 AND (((@pageIndex - 1) * @pageSize + 1) + @pageSize) - 1;
    DROP TABLE #LoadCustomer;


HTML Markup : Add Page :

In Design page we need to add one Dropdownlist and one Gridview Control. Dropdownlist is bind with numbers and its indicate or work as pageNumbers
For Example :- If i change the values of Dropdownlist immediately Dropdownlist SelectedIndexChanged Event Fire and its creates connection to database and pass values { i.e @pageIndex=1, @pageSize=10 (Default Values) } through Stored Procedure proc_getCustomerLoading.

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

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <form id="form1" runat="server">
    <div style="width:100%; padding-left:50px;">
                    Page Number is : 
                    <asp:DropDownList ID="ddl_PageIndex" runat="server" style="width:150px; padding:3px;" AutoPostBack="true" OnSelectedIndexChanged="ddl_PageIndex_SelectedIndexChanged">
            <asp:ListItem Text="1"></asp:ListItem>
            <asp:ListItem Text="2"></asp:ListItem>
            <asp:ListItem Text="3"></asp:ListItem>
            <asp:ListItem Text="4"></asp:ListItem>
            <asp:ListItem Text="5"></asp:ListItem>
            <asp:ListItem Text="6"></asp:ListItem>
            <asp:ListItem Text="7"></asp:ListItem>
            <asp:ListItem Text="8"></asp:ListItem>
            <asp:ListItem Text="9"></asp:ListItem>
            <asp:ListItem Text="10"></asp:ListItem>
            <asp:ListItem Text="11"></asp:ListItem>
            <asp:ListItem Text="12"></asp:ListItem>
            <asp:ListItem Text="13"></asp:ListItem>
            <asp:ListItem Text="14"></asp:ListItem>
            <asp:ListItem Text="15"></asp:ListItem>
            <asp:ListItem Text="16"></asp:ListItem>
            <asp:ListItem Text="17"></asp:ListItem>
            <asp:ListItem Text="18"></asp:ListItem>
            <asp:ListItem Text="19"></asp:ListItem>
            <asp:ListItem Text="20"></asp:ListItem>

    <div style="height:300px; overflow:auto; width:100%;">
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" BackColor="White" BorderColor="#3366CC" BorderStyle="None" BorderWidth="1px" CellPadding="4">
                <asp:TemplateField HeaderText="Row Number">
                        <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bindundefined"RowNo") %>'></asp:TextBox>
                        <asp:Label ID="Label1" runat="server" Text='<%# Bindundefined"RowNo") %>'></asp:Label>
                <asp:TemplateField HeaderText="CustomerID">
                        <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bindundefined"CustomerID") %>'></asp:TextBox>
                        <asp:Label ID="Label2" runat="server" Text='<%# Bindundefined"CustomerID") %>'></asp:Label>
                <asp:TemplateField HeaderText="Company Name">
                        <asp:TextBox ID="TextBox3" runat="server" Text='<%# Bindundefined"CompanyName") %>'></asp:TextBox>
                        <asp:Label ID="Label3" runat="server" Text='<%# Bindundefined"CompanyName") %>'></asp:Label>
                <asp:TemplateField HeaderText="Contact Name">
                        <asp:TextBox ID="TextBox4" runat="server" Text='<%# Bindundefined"ContactName") %>'></asp:TextBox>
                        <asp:Label ID="Label4" runat="server" Text='<%# Bindundefined"ContactName") %>'></asp:Label>
                <asp:TemplateField HeaderText="City">
                        <asp:TextBox ID="TextBox5" runat="server" Text='<%# Bindundefined"City") %>'></asp:TextBox>
                        <asp:Label ID="Label5" runat="server" Text='<%# Bindundefined"City") %>'></asp:Label>
                <asp:TemplateField HeaderText="Country">
                        <asp:TextBox ID="TextBox6" runat="server" Text='<%# Bindundefined"Country") %>'></asp:TextBox>
                        <asp:Label ID="Label6" runat="server" Text='<%# Bindundefined"Country") %>'></asp:Label>
            <FooterStyle BackColor="#99CCCC" ForeColor="#003399" />
            <HeaderStyle BackColor="#003399" Font-Bold="True" ForeColor="#CCCCFF" />
            <PagerStyle BackColor="#99CCCC" ForeColor="#003399" HorizontalAlign="Left" />
            <RowStyle BackColor="White" ForeColor="#003399" />
            <SelectedRowStyle BackColor="#009999" Font-Bold="True" ForeColor="#CCFF99" />
            <SortedAscendingCellStyle BackColor="#EDF6F6" />
            <SortedAscendingHeaderStyle BackColor="#0D4AC4" />
            <SortedDescendingCellStyle BackColor="#D6DFDF" />
            <SortedDescendingHeaderStyle BackColor="#002876" />
        <br />

C# Coding

C# Coding : Namespace

Adding Namespaces

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Web.Services;

C# Coding : Page Load

In Page_Load Event simply i'm calling GetCustomerLoadingData(1, 10); method by default i'm passing @pageIndex = 1 and @pageSize = 10 and return type of this method is Dataset and directly i'm binding to GridView control. Initially gridview control display 10 records

namespace LoadingData_Application
    public partial class GridviewLoadingData : System.Web.UI.Page
       static SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["dbCon"].ConnectionString);        

        protected void Page_Load(object sender, EventArgs e)
            if (!IsPostBack)
                GridView1.DataSource = GetCustomerLoadingData(1, 10);

C# Coding : GetCustomerLoadingData

this GetCustomerLoadingData(int gridpageIndex, int totalpageSize) method is used to get no of records based on a @pageIndex and this method i'm calling many times...

        private static DataSet GetCustomerLoadingData(int gridpageIndex, int totalpageSize)
            SqlCommand cmd = new SqlCommand("proc_getCustomerLoading", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@pageIndex", gridpageIndex);
            cmd.Parameters.AddWithValue("@pageSize", totalpageSize);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            return ds;

C# Coding : DropDownList SelectedIndexChanged Event

Whenever i change dropdownlist values this below event execute and its call GetCustomerLoadingData(int gridpageIndex, int totalpageSize) and passing parameter i.e @pageIndex value is now gettting from dropdownlist and @pageSize by default this value is 10 and result binding to gridview control

        protected void ddl_PageIndex_SelectedIndexChanged(object sender, EventArgs e)
            int no = Convert.ToInt16(ddl_PageIndex.SelectedItem.Text);
            DataSet ds = GetCustomerLoadingData(no, 10);
            if (ds.Tables[0].Rows.Count > 0)
                GridView1.DataSource = ds;
                GridView1.EmptyDataText = " No Record Found....";


Post a Comment