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
