Introduction
In this article, we'll see the implementation of custom paging on GridView
control. The Gridview offers two paging models 1) Default paging and 2) Custom
paging.
It's easy to configure default paging by only just enabling allowpaing property
to true but less efficient in performance. The simple paging model fetches all
records for each pageindexchanging event and displays the appropirate subset of
all returned records.
For example, you have 5000 records in your database's table. You want to display
100 records per page. Now, when page first loaded it will fetch 5000, but the GridView will display the first set of 100 records. Next, when user navigates to
the second page again 5000 records will be fetched and display the second set
of 100 records.
In custom paging, we have to do some more work, rather just enabling allowpaging
property to true. We have to develop custom logic to fetch selected records for
the specific page index instead fetching all records and display subset of them.
There are so many websites that explains the advantages and performance
comparison over default paging. There are so many websites too, that provide
example of custom paging but mostly I found those examples developed using
datasource controls.
Setting up database and tables in SQL Server 2005
- Create new database EmployeeDB SQL Sever 2005.
- Create new table Employee in EmployeeDB.
- Create table fields same as below.
| Id | Int |
| FName | nvarchar(50) |
| MName | nvarchar(50) |
| LName | nvarchar(50) |
| BirthDate | datetime |
| MaritalStatus | char(1) |
| Gender | char(1) |
| Designation | nvarchar(50) |
| Mobile | nvarchar(15) |
- Insert 20 to 30 records.
- Create new stored procedure that reterives the subset of records according to
current page index.
CREATE
PROCEDURE dbo.USP_GET_EMPLOYEES
(
@P_CurrentPageIndexINT,
@P_PageSizeINT,
@P_TotalRecordsINT OUTPUT
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @StartRowIndex INT
DECLARE @MaxRowIndex INT
SET @MaxRowIndex = @P_PageSize * @P_CurrentPageIndex
SET @StartRowIndex = (@MaxRowIndex - @P_PageSize)
+ 1
SELECT * FROM
(
SELECT ROW_NUMBER() OVER(ORDER BYId)
AS RowNum,
Id, FName, MName, LName,
BirthDate, MaritalStatus,
Gender, Designation,
Mobile FROM Employee.
) AS DerivedTable
WHERE RowNum BETWEEN@StartRowIndex AND @MaxRowIndex
--Return total no of records available as an
output parameter
SELECT @P_TotalRecords =COUNT(*) FROM EMPLOYEE
END
Web.config
1. Set appropriate conncetion string in web.config file.
Eg.
<connectionStrings> <add name="EmpDB"
connectionString="Data
Source=.;
Initial
Catalog=EmployeeDB;
Integrated
Security=True"
providerName="System.Data.SqlClient"/>
</connectionStrings>
The aspx page
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %> <!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 runat="server">
<title>Employee
details (Custom paging)</title>
</head>
<body>
<form id="form1" runat="server">
<table width="100%" style="font-family:
Verdana; font-size:
12px">
<tr align="center">
<td>
<asp:GridView ID="grdEmployee" runat="server" AutoGenerateColumns="False" BackColor="White"
BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px" CellPadding="4" Font-Names="verdana"
Font-Size="12px" ForeColor="Black" GridLines="Both" Width="80%">
<FooterStyle BackColor="#CCCC99" ForeColor="Black" />
<PagerStyle BackColor="White" ForeColor="Black" HorizontalAlign="Right" />
<SelectedRowStyle BackColor="#CC3333" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#333333" Font-Bold="True" ForeColor="White" />
<Columns>
<asp:BoundField DataField="RowNum" HeaderText="Serial No" />
<asp:TemplateField HeaderText="Full name">
<ItemTemplate>
<asp:Label ID="lblFname" runat="Server" Text='<%#
Eval("FName") %>'></asp:Label>
<asp:Label ID="lblMname" runat="server" Text='<%#
Eval("MName") %>'></asp:Label>
<asp:Label ID="lblLname" runat="server" Text='<%#
Eval("LName") %>'></asp:Label>
</ItemTemplate>
<ItemStyle HorizontalAlign="Left" Wrap="False" />
</asp:TemplateField>
<asp:BoundField DataField="BirthDate" HeaderText="Birthday" DataFormatString="{0:MM/dd/yyyy}"
HtmlEncode="False" />
<asp:TemplateField HeaderText="Marital">
<ItemStyle HorizontalAlign="Left" />
<ItemTemplate>
<asp:Label ID="lblMarital" runat="Server" Text='<%#
Eval("MaritalStatus") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Gender">
<ItemStyle HorizontalAlign="Left" />
<ItemTemplate>
<asp:Label ID="lblGender" runat="Server" Text='<%#
Eval("Gender") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="Designation" HeaderText="Designation">
<ItemStyle HorizontalAlign="Left" />
</asp:BoundField>
<asp:BoundField DataField="Mobile" HeaderText="Contact">
<ItemStyle HorizontalAlign="Left" />
</asp:BoundField>
</Columns>
</asp:GridView>
</td>
</tr>
<tr>
<td align="center">
<asp:LinkButton ID="lnkFirstPage" runat="server" Text="First"></asp:LinkButton>
||
<asp:LinkButton ID="lnkPrevious" runat="server" Text="Previous"></asp:LinkButton>
||
<asp:LinkButton ID="lnkNext" runat="server" Text="Next"></asp:LinkButton>
||
<asp:LinkButton ID="lnkLastPage" runat="server" Text="Last"></asp:LinkButton>
</td>
</tr>
</table>
</form>
</body>
</html>
The code behind (aspx.cs)
using
System;
using
System.Configuration;
using
System.Data;
using
System.Data.SqlClient;
using
System.Web.UI;
using
System.Web.UI.WebControls; public partial class _Default : System.Web.UI.Page
{
#region
[INITIALIZATION]
SqlConnection con;
SqlCommand cmd;
SqlDataAdapter da;
DataTable dt;
string conStr =
ConfigurationManager.ConnectionStrings["EmpDB"].ConnectionString;
int totalRecords;
#endregion
#region
[PAGE EVENTS]
protected void
Page_Init(object sender,
EventArgs e)
{
EventHandler clickHandler =
new EventHandler(PagingButtonClick);
lnkFirstPage.Click += clickHandler;
lnkLastPage.Click += clickHandler;
lnkNext.Click += clickHandler;
lnkPrevious.Click += clickHandler;
}
protected void
Page_Load(object sender,
EventArgs e)
{
if (!Page.IsPostBack)
{
lnkFirstPage.Enabled = false;
// Disable First and last button
lnkPrevious.Enabled = false;
// on first time page load
ViewState["pageindex"]
= 1; // Manages current page index.
LoadGrid(Convert.ToInt16(ViewState["pageindex"]));
//
SetViewState();
}
}
#endregion
#region
[USER FUNCTIONS]
/// <summary>
/// Makes
connection to the database
/// and
bind the gridview
/// </summary>
/// <param name="currentPageIndex"></param>
private void
LoadGrid(int currentPageIndex)
{
con = new
SqlConnection(conStr);
cmd = new
SqlCommand("USP_GET_EMPLOYEES", con);
cmd.Parameters.Add("@P_CurrentPageIndex",
SqlDbType.Int).Value = currentPageIndex;
cmd.Parameters.Add("@P_PageSize",
SqlDbType.Int).Value = grdEmployee.PageSize;
cmd.Parameters.Add("@P_TotalRecords",
SqlDbType.Int).Direction =
ParameterDirection.Output;
cmd.CommandType = CommandType.StoredProcedure;
try
{
if (con !=
null && con.State == ConnectionState.Closed)
{
con.Open();
da = new SqlDataAdapter();
dt = new DataTable();
da.SelectCommand = cmd;
da.Fill(dt);
}
}
finally
{
if (con !=
null && con.State != ConnectionState.Closed)
{
con.Close();
}
}
grdEmployee.DataSource = dt;
grdEmployee.DataBind();
}
/// <summary>
/// set the
viewstate for
/// 1.
Total records
/// 2.
Total page
/// </summary>
private void
SetViewState()
{
int _totalRecords = (int)
cmd.Parameters["@P_TotalRecords"].Value;
int _pageSize = grdEmployee.PageSize;
int _totalPages = 0;
// Make sure totalrecords are morethan gridview's
paging size
if (_totalRecords > _pageSize)
{
// Get the total no of pages need to be
created
if ((_totalRecords % _pageSize)
== 0)
{
_totalPages = _totalRecords / _pageSize;
}
else
{
_totalPages = (_totalRecords / _pageSize) + 1;
}
}
ViewState["totalrecord"] = _totalRecords;
ViewState["totalpage"] = _totalPages;
}
/// <summary>
/// Single
custom button click event
/// for
paging buttons
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected
void PagingButtonClick(object sender,
EventArgs e)
{
if (sender is LinkButton)
{
LinkButton clickedButton = (LinkButton)sender;
#region
switch case for button click
switch (clickedButton.Text)
{
case
"Next":
lnkFirstPage.Enabled = true;
lnkPrevious.Enabled = true;
ViewState["pageindex"] =
Convert.ToInt32(ViewState["pageindex"])
+ 1;
if (Convert.ToInt32(ViewState["totalpage"])
== Convert.ToInt32(ViewState["pageindex"]))
{
lnkNext.Enabled = false;
lnkLastPage.Enabled = false;
}
break;
case
"Previous":
lnkNext.Enabled = true;
lnkLastPage.Enabled = true;
ViewState["pageindex"] =
Convert.ToInt32(ViewState["pageindex"])
- 1;
if (Convert.ToInt32(ViewState["pageindex"])
== 1)
{
lnkNext.Enabled = true;
lnkLastPage.Enabled = true;
lnkPrevious.Enabled = false;
lnkFirstPage.Enabled = false;
}
break;
case
"First":
if (Convert.ToInt32(ViewState["pageindex"]
= 1) == 1)
{
lnkNext.Enabled = true;
lnkLastPage.Enabled = true;
lnkPrevious.Enabled = false;
lnkFirstPage.Enabled = false;
}
break;
case "Last":
lnkFirstPage.Enabled = true;
lnkPrevious.Enabled = true;
ViewState["pageindex"] =
Convert.ToInt32(ViewState["totalpage"]);
if (Convert.ToInt32(ViewState["totalpage"])
== Convert.ToInt32(ViewState["pageindex"]))
{
lnkNext.Enabled = false;
lnkLastPage.Enabled = false;
}
break;
}
#endregion
LoadGrid(Convert.ToInt32(ViewState["pageindex"]));
}
}
#endregion
}
Snapshot
![snap1.gif]()
Snapshot 1 the first page
![snap2.gif]()
Snapshot 2 Retrieved data for first page