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.
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
<%@ 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
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
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

TAGS

Related Posts
Next
Cumulative sum in Sql Server
Previous
Pivot with Dynamic Columns in SQL Server
Sandeep Mittal

About Sandeep Mittal


I am a Software Engineer and passionate programmer. Love working in SQL Server. Like participating in online technical communities like asp.net, MSDN, technical discussions and helping my friends, colleagues and others in their technical problems

Subscribe to this Blog via Email :
    Choose :
    • OR
    • To comment
3 comments:
Write Comments
  1. AnonymousSeptember 27, 2012 at 4:16 PM

    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. Sandeep MittalOctober 2, 2012 at 10:38 AM

    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. UnknownJuly 2, 2013 at 12:28 PM

    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

© Copyright 2021 IT Developer Zone. Designed by Bloggertheme9 | Distributed By Gooyaabi Templates. Powered by Blogger.