Generic Custom Paging and Sorting Using SQL Server and .Net C#


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


<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--><%@ 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>

.csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, “Courier New”, courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; }

Code Behind


<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->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(); }

///
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
///
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


<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->/****** 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


<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--><%@ 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


<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->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;

}
}



}
}

Advertisements
This entry was posted in asp.net C#, Custom Pagign, Custom Sorting, dynamic paging, dynamic sorting, pager control. Bookmark the permalink.

9 Responses to Generic Custom Paging and Sorting Using SQL Server and .Net C#

  1. Aashi siva says:

    I do trust all of the concepts you’ve presented on your post. They’re really convincing and will definitely work. Still, the posts are too brief for newbies. May you please extend them a little from subsequent time?Also, I’ve shared your website in my social networks.

    Corporate Training in Chennai

  2. Aasha says:

    Really nice information you had posted. Its very informative and definitely it will be useful for many people

    Digital Marketing For Small Business in Chennai

  3. deeksha says:

    I come to know many more solutions about this concept it is really good and thanks for providing this valuable information which is really good and awesome.

    Informatica Training in Chennai

  4. Akshaysri says:

    Great Article… I love to read your articles because your writing style is too good, its is very very helpful for all of us and I never get bored while reading your article because, they are becomes a more and more interesting from the starting lines until the end.
    Germany Education Consultants in Chennai

  5. Truely a very good article on how to handle the future technology. After reading your post,thanks for taking the time to discuss this, I feel happy about and I love learning more about this topic.keep sharing your information regularly for my future reference

    Best Dental Clinic In Velachery

  6. Great Article… I love to read your articles because your writing style is too good, its is very very helpful for all of us and I never get bored while reading your article because, they are becomes a more and more interesting from the starting lines until the end.

    Fresher Jobs in Mumbai
    Fresher Jobs in Pune
    Fresher Jobs in Noida
    Fresher Jobs in Hyderabad

  7. tessa tessa says:

    Very nice post here thanks for it I always like and search such topics and everything connected to them. Keep update more information..

    Digital Marketing Company in India|SEO Company in India

  8. sathya says:

    There are lots of information about latest technology and how to get trained in them, like this have spread around the web, but this is a unique one according to me. The strategy you have updated here will make me to get trained in future technologies. By the way you are running a great blog. Thanks for sharing this.

    Hadoop Training in Chennai

    Base SAS Training in Chennai

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s