Wednesday, June 12, 2013

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


<%@ 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" />&nbsp;&nbsp; <asp:ImageButton ID="btnPrevious" runat="server" CausesValidation="False" AlternateText="Previous" meta:resourcekey="btnPreviousResource2" />&nbsp;&nbsp;&nbsp;&nbsp; <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>&nbsp;&nbsp;&nbsp;&nbsp; <asp:ImageButton ID="btnNext" runat="server" CausesValidation="False" AlternateText="Next" meta:resourcekey="btnNextResource2" />&nbsp;&nbsp; <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; } } } }

5 comments:

  1. 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

    ReplyDelete
  2. 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

    ReplyDelete
  3. 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

    ReplyDelete
  4. 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

    ReplyDelete
  5. 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


    ReplyDelete

PDF Arabic watermark using MVC and iTextSharp

PDF full page Arabic watermark using MVC and iTextSharp Download :  Source Code Most of the time we have requirement to  gen...