Compared to default paging, custom paging can improve the performance of paging through data by several orders of magnitude, through custom paging large amounts of data can reterive more efficiently than implementing default paging, however, especially when adding sorting to the mix. In this tutorial, we'll extend the example from the preceding one to include support for sorting and custom paging.
Implementation of Custom paging required following three parts.
- Pager Control
- Stored Procedure
- Web page
Pager Control we need user control for add navigation buttons like move next, pervious, first and last button and later on to join with grid or any other control like repeater etc where we need paging functionality. You should perform following step to create user control. 1. Add user control from Open Visual Studio 2008 and choose File > New Item >User Control and rename the name with “Navigator.ascx” 2. Add four button for navigation move next, pervious, last and first record. 3. I use image button and add images through resource files
Code
<%@ Control Language="C#" AutoEventWireup="false" Codebehind="Navigator.ascx.cs"
Inherits="DMS.Controls.Navigator" %><table border="0" width="100%">
<asp:HiddenField ID="hdnCurrentPage" runat="server" />
<asp:HiddenField ID="hdnTotalPages" runat="server" />
<asp:HiddenField ID="hdnSelectedValue" runat="server" />
<tr>
<td>
<table style="width: 100%">
<tr>
<td align="center">
<asp:ImageButton ID="btnFirst" runat="server" CausesValidation="False"
AlternateText="First" meta:resourcekey="btnFirstResource1" />
<asp:ImageButton ID="btnPrevious" runat="server" CausesValidation="False"
AlternateText="Previous" meta:resourcekey="btnPreviousResource2" />
<asp:Label ID="Label3" runat="server" Text="Total Records: "
meta:resourcekey="Label3Resource1" ></asp:Label>
<asp:Label ID="lbltotalItem" runat="server"
meta:resourcekey="lbltotalItemResource1" ></asp:Label>
<asp:Label ID="Label4" runat="server" Text=", Page "
meta:resourcekey="Label4Resource1" ></asp:Label>
<asp:Label ID="lblcurrentpage" runat="server" Text="/"
meta:resourcekey="lblcurrentpageResource1" ></asp:Label>
<asp:Label ID="Label6" runat="server" Text=" of "
meta:resourcekey="Label6Resource1" ></asp:Label>
<asp:Label ID="lbltotalpages" runat="server"
meta:resourcekey="lbltotalpagesResource1" ></asp:Label>
<asp:ImageButton ID="btnNext" runat="server" CausesValidation="False"
AlternateText="Next" meta:resourcekey="btnNextResource2" />
<asp:ImageButton ID="btnLast" runat="server" CausesValidation="False"
AlternateText="Last" meta:resourcekey="btnLastResource2" />
</td>
</tr>
</table>
</td>
</tr></table>
Code Behind
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Globalization;
namespace Controls
{
public partial class Navigator : System.Web.UI.UserControl
{
#region Fields
private int _TotalRecord;
private int _PageSize;
#endregion
#region Events
// Delegates event for button click public event DataGridPageChangedEventHandler PageIndexChanged;
#endregion
#region Properties
public int PageSize
{
get { return _PageSize; }
set { _PageSize = value; }
}
public int TotalRecord
{
get { return _TotalRecord; }
set
{
_TotalRecord = value;
lbltotalItem.Text = Convert.ToString(_TotalRecord);
}
}
#endregion
#region Data
public void Initialize(int totalRecords, int pageSize)
{
try
{
TotalRecord = totalRecords;
_PageSize = pageSize;
if ((_TotalRecord % _PageSize) == 0)
TotalPages = _TotalRecord / _PageSize;
else
TotalPages = (_TotalRecord / _PageSize) + 1;
CurrentPage = 1;
EnableLinks();
}
catch (Exception ex)
{
throw;
}
}
private void EnableLinks()
{
try
{
if (CurrentPage == 1)
{
btnFirst.ImageUrl = HttpContext.GetGlobalResourceObject("Resource", "FirstImageD").ToString();
btnFirst.Enabled = false;
btnPrevious.ImageUrl = HttpContext.GetGlobalResourceObject("Resource", "PreviousImageD").ToString();
btnPrevious.Enabled = false;
}
else
{
btnFirst.ImageUrl = HttpContext.GetGlobalResourceObject("Resource", "FirstImageE").ToString();
btnFirst.Enabled = true;
btnPrevious.ImageUrl = HttpContext.GetGlobalResourceObject("Resource", "PreviousImageE").ToString();
btnPrevious.Enabled = true;
}
if (CurrentPage == TotalPages)
{
btnNext.ImageUrl = HttpContext.GetGlobalResourceObject("Resource", "NextImageD").ToString();
btnNext.Enabled = false;
btnLast.ImageUrl = HttpContext.GetGlobalResourceObject("Resource", "LastImageD").ToString();
btnLast.Enabled = false;
}
else
{
btnNext.ImageUrl = HttpContext.GetGlobalResourceObject("Resource", "NextImageE").ToString();
btnNext.Enabled = true;
btnLast.ImageUrl = HttpContext.GetGlobalResourceObject("Resource", "LastImageE").ToString();
btnLast.Enabled = true;
}
}
catch (Exception ex)
{
throw;
}
}
#endregion
protected void OnPageIndexChanged(object sender)
{
try
{
if (PageIndexChanged != null)
PageIndexChanged(this, new DataGridPageChangedEventArgs(sender, CurrentPage));
EnableLinks();
}
catch (Exception ex)
{
throw;
}
}
#region Event Handlers
void btnFirst_Click(object sender, ImageClickEventArgs e)
{
try
{
if (CurrentPage > 1)
CurrentPage = 1;
OnPageIndexChanged(sender);
//EnableLinks(); }
catch (Exception ex)
{
throw;
}
}
void btnPrevious_Click(object sender, ImageClickEventArgs e)
{
try
{
if (CurrentPage > 1)
CurrentPage--;
OnPageIndexChanged(sender);
//EnableLinks(); }
catch (Exception ex)
{
throw;
}
}
void btnNext_Click(object sender, ImageClickEventArgs e)
{
try
{
if (CurrentPage < TotalPages)
CurrentPage++;
OnPageIndexChanged(sender);
}
catch (Exception ex)
{
throw;
}
}
void btnLast_Click(object sender, ImageClickEventArgs e)
{
try
{
if (CurrentPage < TotalPages)
CurrentPage = TotalPages;
OnPageIndexChanged(sender);
}
catch (Exception ex)
{
throw;
}
}
#endregion
#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
InitializeComponent();
base.OnInit(e);
//CreateControls(); }
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary> private void InitializeComponent()
{
//this.Load += new System.EventHandler(this.Page_Load);
this.btnFirst.Click += new ImageClickEventHandler(btnFirst_Click);
this.btnPrevious.Click += new ImageClickEventHandler(btnPrevious_Click);
this.btnNext.Click += new ImageClickEventHandler(btnNext_Click);
this.btnLast.Click += new ImageClickEventHandler(btnLast_Click);
}
#endregion
#region Hidden Fields
public int CurrentPage
{
get { return int.Parse((!string.IsNullOrEmpty(hdnCurrentPage.Value) ? hdnCurrentPage.Value : "1")); }
set
{
hdnCurrentPage.Value = Convert.ToString(value);
lblcurrentpage.Text = Convert.ToString(hdnCurrentPage.Value,CultureInfo.CurrentCulture);
}
}
public int TotalPages
{
get { return int.Parse(hdnTotalPages.Value); }
set
{
hdnTotalPages.Value = Convert.ToString(value);
lbltotalpages.Text = hdnTotalPages.Value;
}
}
public string SelectedValue
{
get { return this.hdnSelectedValue.Value; }
set { this.hdnSelectedValue.Value = value; }
}
#endregion
}
}
Stored Procedure
Create the Table name customer add some major fields like customer name ,Customer ID , Phone No etc and insert dummy data then we will create the stored procedure for custom paging and sorting
/****** Object: StoredProcedure [dbo].[usp_CustomerSearch] Script Date: 11/24/2011 12:34:19 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[usp_CustomerSearch]
@Customer_ID int =Null,
@Customer_Name varchar(MAX)=Null,
@Phone_No varchar(MAX)=Null,
@Address varchar(MAX)=Null,
@SortDir VARCHAR(30) = 'ASC',
@SortOrder VARCHAR(max) ='Customer_Name' ,
@PageNumber INT = 1,
@PageSize INT = 25ASBEGIN
SET NOCOUNT ON;
DECLARE @FirstRow INT
DECLARE @LastRow INT
SET XACT_ABORT ON;
DECLARE @SQL NVARCHAR(4000)
SELECT @FirstRow = (@PageNumber - 1) * @PageSize + 1,
@LastRow = @PageSize + (@PageNumber - 1) * @PageSize;
IF @SortORder IS NULL OR @SortOrder = ''
SET @SortOrder = 'DESC';
WITH TempCustomer AS
(
SELECT * , ROW_NUMBER() OVER (ORDER BY
CASE @SortDir
WHEN 'DESC' THEN NULL
ELSE
CASE @SortOrder
WHEN 'Customer_ID' THEN CONVERT(VARCHAR(50),Customer_ID)
WHEN 'Customer_Name' THEN Customer_Name
WHEN 'Phone_No' THEN Phone_No
WHEN 'Address' THEN Address
ELSE Customer_Name
END
END ASC) AS RowNumberAsc,
ROW_NUMBER() OVER (ORDER BY
CASE @SortDir
WHEN 'ASC' THEN NULL
ELSE
CASE @SortOrder
WHEN 'Customer_ID' THEN CONVERT(VARCHAR(50),Customer_ID)
WHEN 'Customer_Name' THEN Customer_Name
WHEN 'Phone_No' THEN Phone_No
WHEN 'Address' THEN Address
ELSE Customer_Name
END
END DESC) AS RowNumberDesc
FROM (
SELECT [Customer_ID], [Customer_Name], [Phone_No], [Address] FROM [dbo].[Customer] (NOLOCK)
WHERE
1 = 1
AND (LOWER(Customer_Name) LIKE '%'+ @Customer_Name +'%' or @Customer_Name is null)
AND (LOWER(Phone_No) LIKE '%'+ @Phone_No +'%' or @Phone_No is null)
AND (LOWER(Address) LIKE '%'+ @Address +'%' or @Address is null)
AND (LOWER(Customer_ID)= @Customer_ID or @Customer_ID is null)
) AS TempRow
)
SELECT *,(SELECT COUNT(*) FROM TempCustomer) AS TotalRecords
FROM TempCustomer
WHERE CASE @SortDir WHEN 'DESC' THEN RowNumberDesc ELSE RowNumberAsc END
BETWEEN @FirstRow AND @LastRow
Order By CASE @SortDir
WHEN 'DESC'THEN RowNumberDesc
ELSE RowNumberAsc
END;
ENDGO
In Above stored procedure some fields extra taking as input like SortDir,SortOrder,PageNumber and PageSize.
Fields Details
SortDir : Use for order by direction means “ASC” or “DESC”
SortOrder: Field name on which you want to sort like “Customer_Name” or “Phone_No” etc
PageNumber: Current page number e.g ( when user click next then current page number 2 and so on)
PageSize : Grid page size how many records wants to show in a grid
Aspx Page Code to populate and binding of grid.
Add Grid in a aspx page also add navigator control for created before for paging as shown example given below
HTML Page
<%@ Page Language="C#" AutoEventWireup="false" CodeBehind="Default.aspx.cs" Inherits="WebApplication1._Default"
EnableEventValidation="true" %><%@ Register Src="Navigator.ascx" TagName="Navigator" TagPrefix="uc1" %><!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></title></head><body>
<form id="form1" runat="server">
<div>
</div>
<asp:GridView ID="GridView1" runat="server" AllowSorting="true">
<Columns>
<asp:BoundField DataField="Customer_ID" HeaderText="Customer ID" SortExpression="Customer_ID">
<HeaderStyle Width="10%" />
</asp:BoundField>
<asp:BoundField DataField="Customer_Name" HeaderText="Contact Person" SortExpression="Customer_Name">
<HeaderStyle Width="20%" />
</asp:BoundField>
<asp:BoundField DataField="Address" HeaderText="Address" SortExpression="Address">
<HeaderStyle Width="20%" />
</asp:BoundField>
<asp:BoundField DataField="Phone_No" HeaderText="Phone No" SortExpression="Phone_No" >
<HeaderStyle Width="10%" />
</asp:BoundField>
</Columns>
</asp:GridView>
<uc1:Navigator ID="Navigator1" runat="server" />
</form></body></html>
Code Behind
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.Globalization;
namespace WebApplication1
{
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
}
}
protected override void OnInit(EventArgs e)
{
base.OnInit(e);
Initializer();
}
private void Initializer()
{
Page.Load += new EventHandler(Page_Load);
GridView1.Sorting += new GridViewSortEventHandler(GridView1_Sorting);
Navigator1.PageIndexChanged += new DataGridPageChangedEventHandler(Navigator1_PageIndexChanged);
}
void Navigator1_PageIndexChanged(object source, DataGridPageChangedEventArgs e)
{
DoSearchAndSetData(Convert.ToString(ViewState["sortOrder"]), e.NewPageIndex);
}
void GridView1_Sorting(object sender, GridViewSortEventArgs e)
{
if (ViewState["sortdir"] == null)
{
ViewState["sortdir"] = "desc";
}
else if (Convert.ToString(ViewState["sortdir"]).ToLower() == "desc")
{
ViewState["sortdir"] = "asc";
}
else
{
ViewState["sortdir"] = "desc";
}
string[] sort = e.SortExpression.Split('.');
ViewState["sortOrder"] = e.SortExpression;
DoSearchAndSetData(e.SortExpression, 1);
}
void DoSearchAndSetData(string sortExperssion, int pageNumber)
{
int _pageSize = 25;
try
{
ModelCustomer objModel = new ModelCustomer();
objModel.SortOrder = sortExperssion;
objModel.SortDir = Convert.ToString(ViewState["sortdir"]);
objModel.PageNo = pageNumber;
objModel.PageSize = _pageSize;
List<ModelCustomer> colModel = new List<ModelCustomer>();
colModel = DalCustomer.Search(objModel);
if (colModel.Count > 0 && colModel != null)
{
Navigator1.Visible = true;
if (pageNumber == 1)
this.Navigator1.Initialize(Convert.ToInt32(colModel[0].TotalRecords, CultureInfo.CurrentCulture), _pageSize);
}
else
{
Navigator1.Visible = false;
}
GridView1.DataSource = colModel;
GridView1.DataBind();
}
catch (Exception ex)
{
throw;
}
}
}
}
