Jan 11, 2011

Custom Paging in GridView in ASP.Net

In this article, you will learn what is Custom Paging and how it is implemented in a Grid-view.

First of all, I would like to share the benefit of using Custom Paging over Normal Paging.

The purpose for using Custom Paging over Normal Paging is performance. The key point in Custom Paging is that it only retrieves "n" no. of records from the database that are to be displayed on the page.

Suppose, there are 100K records in the database and you want to display 10 records on a page. In Normal Paging on each page change, all 100K records are retrieved from the database or is maintained in the viewstate, but in Custom Paging we implement it in such a way that it retrieves only 10 records from database on each page change.

Let's have an example code to show how custom paging is implemented

Suppose we have a Employee Table and we are going to bind employee details to Grid-view.
The code has below listed functionalities for navigation of pages
  • First, Previous, Next & Last Buttons to navigate between pages
  • Link Buttons to navigate between pages
First create a Table and populate some data in it.
CREATE TABLE dbo.tblEmployee(
    EmpID    char(8),
    EmpName varchar(50),
    DOB        datetime,
    DOJ        datetime,
    Gender    char(1)
)
INSERT INTO tblEmployee
SELECT    'EMP' + RIGHT('00000' + cast(number as varchar),4)
        , 'EMP' + RIGHT('00000' + cast(number as varchar),4)       
        , CONVERT(VARCHAR, DATEADD(D, (-10000 + ABS(CHECKSUM(NEWID()))%1000), GETDATE()),101)
        , CONVERT(VARCHAR, DATEADD(D, (-1000 + ABS(CHECKSUM(NEWID()))%1000), GETDATE()),101)
        , CASE WHEN ABS(CHECKSUM(NEWID()))%100 > 50 THEN 'M' ELSE 'F' END       
FROM    master..spt_values
WHERE   type='P'
AND     number between 1 and 35
Now let's create a stored procedure that would retrieve only 10 records on records based on the selected page. We have used Row_Number (window function) to generate a sequential no. to the result and based on the which we would fetch the records.

Stored Procedure returns employee details based on current page
CREATE PROCEDURE [dbo].[uspGetEmployees] (
    @iPageNo INT
    , @iPageRecords INT
)
AS
BEGIN   
    DECLARE @ROWCNT INT, @PAGECNT INT
    SELECT EmpID, EmpName, DOB, DOJ, Gender
    FROM (
        SELECT  ROW_NUMBER() OVER(ORDER BY EmpID)  AS RowNum
                , EmpID, EmpName
                , CONVERT(VARCHAR, DOB, 101) AS DOB
                , CONVERT(VARCHAR, DOJ, 101) AS DOJ
                , CASE Gender WHEN 'M' THEN 'Male' ELSE 'Female' END as Gender
        FROM    tblEmployee
    ) TAB WHERE RowNum BETWEEN @iPageRecords * (@iPageNo - 1 ) + 1 AND @iPageNo * @iPageRecords
 
    SELECT @ROWCNT = COUNT(1) FROM tblEmployee
    SELECT @ROWCNT/@iPageRecords + CASE WHEN @ROWCNT%@iPageRecords = 0 THEN 0 ELSE 1 END
END
Now let's add a Page to the ASP.Net application

CustomPaging.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="CustomPaging.aspx.cs" Inherits="CustomPaging" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Custom Paging</title>
    <style type="text/css">
        tr{
            background-color: ButtonFace;
        }
        td{
            font-family:Verdana;
            font-size:12px;
        }
    </style>
</head>

<body>
    <form id="form1" runat="server">
    <asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>
    <asp:UpdatePanel ID="UpdatePanel1" runat="server">
        <ContentTemplate>
            <table style="width: 100%; border-color: Gray;">
                <tr align="center">
                    <td colspan="2">
                        Employees Details
                    </td>
                </tr>
                <tr>
                    <td colspan="2">
                        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="4"
                            ForeColor="#333333" GridLines="Horizontal" Width="100%">
                            <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
                            <Columns>
                                <asp:BoundField HeaderText="Employee ID" DataField="EmpID" />
                                <asp:BoundField HeaderText="Employee Name" DataField="EmpName" />
                                <asp:BoundField HeaderText="Date of Birth" DataField="DOB" />
                                <asp:BoundField HeaderText="Date of Joining" DataField="DOJ" />
                                <asp:BoundField HeaderText="Gender" DataField="Gender" />
                            </Columns>
                            <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>
                    </td>
                </tr>
                <tr>
                    <td>
                        <asp:Button ID="btnFirst" runat="server" Text="<<" OnClick="btnFirst_Click"/>
                        <asp:Button ID="btnPrev" runat="server" Text="<" OnClick="btnPrev_Click" />
                        Page
                        <asp:TextBox ID="txtPageNo" runat="server" Text="1" Width="45px"></asp:TextBox>
                        of
                        <asp:Label ID="lblPages" runat="server" Text="1"></asp:Label>
                        <asp:Button ID="btnNext" runat="server" Text=">" OnClick="btnNext_Click" />
                        <asp:Button ID="btnLast" runat="server" Text=">>" OnClick="btnLast_Click"/>
                        <asp:RequiredFieldValidator ID="rfvPageNo" runat="server" ControlToValidate="txtPageNo"
                            ErrorMessage="*" ValidationGroup="grpGo">
                        </asp:RequiredFieldValidator>
                        <asp:RangeValidator ID="rvPageNo" runat="server" ControlToValidate="txtPageNo" Type="Integer"
                            MinimumValue="1" MaximumValue="1" ValidationGroup="grpGo"></asp:RangeValidator>                       
                    </td>
                    <td align="right">
                        <asp:Label ID="lblStatus" runat="server"> Displaying 1 of 1</asp:Label>
                    </td>
                </tr>
                <tr>
                    <td colspan="2">
                        <asp:Panel ID="pnl" runat="server"></asp:Panel>
                    </td>
                </tr>
            </table>
        </ContentTemplate>
    </asp:UpdatePanel>
    </form>
</body>
</html>
CustomPaging.aspx.cs
using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI.WebControls;

public partial class CustomPaging : System.Web.UI.Page
{
    enum PageNav { First, Previous, Next, Last, None}
    private int iPageRecords;

    #region Events
    protected void Page_Load(object sender, EventArgs e)
    {
        iPageRecords = 10;
        if (!IsPostBack)
        {
            BindGrid(1, iPageRecords);
        }
        generateLinkBtns();
    }

    protected void lnkButton_Click(object sender, EventArgs e)
    {
        ViewState["currentPage"] = ((LinkButton)sender).Text;
        BindGrid(Convert.ToInt32(ViewState["currentPage"]), iPageRecords);
        txtPageNo.Text = ViewState["currentPage"].ToString();
        PageChange(Convert.ToInt32(ViewState["currentPage"]), PageNav.None);
    } 

    protected void btnFirst_Click(object sender, EventArgs e)
    {
        PageChange(Convert.ToInt32(ViewState["currentPage"]), PageNav.First);
    }

    protected void btnPrev_Click(object sender, EventArgs e)
    {
        PageChange(Convert.ToInt32(ViewState["currentPage"]), PageNav.Previous);
    }

    protected void btnNext_Click(object sender, EventArgs e)
    {
        PageChange(Convert.ToInt32(ViewState["currentPage"]), PageNav.Next);
    }

    protected void btnLast_Click(object sender, EventArgs e)
    {
        PageChange(Convert.ToInt32(ViewState["currentPage"]), PageNav.Last);
    }
    #endregion

    #region Methods
    string GetConnectionString()
    {
        //Mofify connection string       
        return "Data Source=SERVERNAME;Initial Catalog=DBNAME;User Id=UserName;Password=Pwd; Connect Timeout=0";       
    }

    private void generateLinkBtns()
    {
        LinkButton lnkButton = null;
        Label lbl = null;
        for (int i = 1; i <= Convert.ToInt32(ViewState["PageCount"]); i++)
        {
            lnkButton = new LinkButton();
            lnkButton.Click += new EventHandler(lnkButton_Click);
            lnkButton.ID = "lnk" + i.ToString();
            lnkButton.Text = i.ToString();
            pnl.Controls.Add(lnkButton);
            lbl = new Label();
            lbl.Text = " ";
            pnl.Controls.Add(lbl);
        }
    }

    private void BindGrid(int iPageNo, int iPageRecords)
    {
        DataSet ds = new DataSet();
        ds = getEmployees(iPageNo, iPageRecords);
        GridView1.DataSource = ds.Tables[0];
        GridView1.DataBind();
        if (!IsPostBack)
        {           
            int iPageCount;
            iPageCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);           
            rvPageNo.MaximumValue = iPageCount.ToString();
            ViewState["PageCount"] = iPageCount;           
            btnFirst.Enabled = false;
            btnPrev.Enabled = false;
            ViewState["currentPage"] = "1";
        }          

        txtPageNo.Text = iPageNo.ToString();
        lblPages.Text = ViewState["PageCount"].ToString();
        lblStatus.Text = "Displaying Page : " + iPageNo.ToString() + " of " + ViewState["PageCount"].ToString();
    }

    public DataSet getEmployees(int iPageNo, int iRecords)
    {
        SqlParameter[] param;
        param = new SqlParameter[2];
        param[0] = new SqlParameter("@iPageNo", iPageNo);
        param[1] = new SqlParameter("@iPageRecords", iRecords);
        SqlConnection conn = new SqlConnection(GetConnectionString());
        conn.Open();
        SqlCommand cmd = new SqlCommand("uspGetEmployees", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddRange(param);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        conn.Close();
        conn.Dispose();
        return ds;
    }

    private void PageChange(int currentPage, PageNav pg)
    {
        int pageCount;
        pageCount = Convert.ToInt32(ViewState["PageCount"]);
        btnFirst.Enabled = true;
        btnPrev.Enabled = true;
        btnNext.Enabled = true;
        btnLast.Enabled = true;       
        switch (pg)
        {
            case PageNav.First:
                currentPage = 1;
                btnFirst.Enabled = false;
                btnPrev.Enabled = false;
                break;
            case PageNav.Previous:
                if (currentPage == 2)
                {
                    btnFirst.Enabled = false;
                    btnPrev.Enabled = false;
                }
                currentPage--;
                break;
            case PageNav.Next:
                if (currentPage == pageCount - 1)
                {
                    btnNext.Enabled = false;
                    btnLast.Enabled = false;
                }
                currentPage++;
                break;
            case PageNav.Last:
                btnNext.Enabled = false;
                btnLast.Enabled = false;
                currentPage = Convert.ToInt32(ViewState["PageCount"]);
                break;
            case PageNav.None:
                if (currentPage == 1)
                {
                    btnFirst.Enabled = false;
                    btnPrev.Enabled = false;
                }
                else if (currentPage == pageCount)
                {
                    btnNext.Enabled = false;
                    btnLast.Enabled = false;
                }
                break;
        }
        BindGrid(currentPage, iPageRecords);
        ViewState["currentPage"] = currentPage;
    }
    #endregion
}
OUTPUT
Custom Paging Output

    Choose :
  • OR
  • To comment
3 comments:
Write Comments
  1. I need coding for custom paging. It should be displayed as first previous 1 2 3 4 5 6 7 8 9 10 ....Next last.and should be increased as 10 ,10 records

    ReplyDelete
  2. Currently it display the links for all the pages in one go
    Ex: 1 2 3 4 5 6 ..............N
    you need to tweak the code and implement the logic as per your requirement to display only 10 links at a time.

    ReplyDelete
  3. Its a very nice article. Just wanted to share some more to make the above article complete, that is showing the running total along with "Grand Total" in all gridview pages.

    http://www.encodedna.com/2013/06/sum-of-gridview-column-in-footer.htm

    ReplyDelete