How to create custom paging in Gridview using CTE ?



There are so many approaches to do custom paging in Gridview for eaxmple using LINQ, using Temp table, using CTE etc.

Recently i used custom paging in gridview using CTE in store procedure. CTE is commmon table Expression. This feature come in sql server 2005 onward.

We generally use custom paging to increase the performance of application. if we will not use custom paging then every time all data will fetch from database. So performance of application will be very very slow.

We can do custom paging using CTE in very simple ways

Note: Here i have used datalist for displaying paging in footer of Gridview.

Step1: Write the store procedure using CTE like this


--Exec getEmpDetails 1,100,0

      @StartIndex int,
      @PageSize int,
      @TotalCount int OutPut
--Select @TotalCount=count(1) from tblEmp where EmpName like '%';
Select @TotalCount=count(1) from tblEmp;

   select top(@startIndex+@PageSize-1) ROW_NUMBER() OVER(ORDER BY EmpId) 
   from tblEmp 
select * from EmpCTE where RowNumber between @startIndex and (@startIndex+@PageSize-1)


Step 2: Create the Entity of table like in Business Entity layer

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace CustomPaging
    public class EmpBE
        public int StartIndex { get; set; }
        public int PageSize { get; set; }
        public int TotalCount { get; set; }
        public int EmpId { get; set; }
        public string EmpName { get; set; }
        public string EmpSal { get; set; }

Step3: Write the Method for fetching data from database in Data acess layer like this

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Collections.ObjectModel;

namespace CustomPaging
    public class EmpDAL
        SqlConnection con=new SqlConnection("Data Source=.\\sqlexpress;Initial Catalog=EmpDB;Integrated Security=True");

        public Collection<EmpBE> GetEmpsDetails(EmpBE objEmp,out int totalcount) 
            Collection<EmpBE> EmpList = new Collection<EmpBE>();
            using (SqlCommand cmd = new SqlCommand("getEmpDetails", con))
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@StartIndex", objEmp.StartIndex);
                cmd.Parameters.AddWithValue("@PageSize", objEmp.PageSize);
                SqlParameter parTotalCount = new SqlParameter("@TotalCount", SqlDbType.Int);
                parTotalCount.Direction = ParameterDirection.Output;

                using (SqlDataReader dr = cmd.ExecuteReader())
                    while (dr.Read())
                        EmpBE objEmp1 = new EmpBE();
                        objEmp1.EmpId = Convert.ToInt32(dr["EmpId"]);
                        objEmp1.EmpName = Convert.ToString(dr["EmpName"]);
                        objEmp1.EmpSal = Convert.ToString(dr["EmpSal"]);
                    totalcount = Convert.ToInt32(parTotalCount.Value);
            return EmpList;

Step 4: Design the .aspx page like this

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm2.aspx.cs" Inherits="CustomPaging.WebForm2" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "">
<html xmlns="">
<head runat="server">
    <style type="text/css">
            width: 100%;
            width: 121px;
            width: 98px;
    <form id="form1" runat="server">
        <asp:GridView ID="GridView1" runat="server" CellPadding="4" Width="356px" ForeColor="#333333"
            <AlternatingRowStyle BackColor="White" />
                <asp:BoundField DataField="EmpId" HeaderText="EmpId" />
                <asp:BoundField DataField="EmpName" HeaderText="EmpName" />
                <asp:BoundField DataField="EmpSal" HeaderText="EmpSal" />
            <EditRowStyle BackColor="#7C6F57" />
            <FooterStyle BackColor="#1C5E55" ForeColor="White" Font-Bold="True" />
            <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
            <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />
            <RowStyle BackColor="#E3EAEB" />
            <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
            <SortedAscendingCellStyle BackColor="#F8FAFA" />
            <SortedAscendingHeaderStyle BackColor="#246B61" />
            <SortedDescendingCellStyle BackColor="#D4DFE1" />
            <SortedDescendingHeaderStyle BackColor="#15524A" />
        <asp:DataList CellPadding="2" RepeatDirection="Horizontal" runat="server" ID="dlPager"
            OnItemCommand="dlPager_ItemCommand" BackColor="LightGoldenrodYellow" Width="356px"
            BorderColor="Tan" BorderWidth="1px" ForeColor="Black">
            <AlternatingItemStyle BackColor="PaleGoldenrod" />
            <FooterStyle BackColor="Tan" />
            <HeaderStyle BackColor="Tan" Font-Bold="True" />
                <asp:LinkButton Enabled='<%#Eval("Enabled") %>' runat="server" ID="lnkPageNo" Text='<%#Eval("Text") %>'
                    CommandArgument='<%#Eval("Value") %>' CommandName="PageNo"></asp:LinkButton>
            <SelectedItemStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite" />
        <br />

Step 5: write the code in Code behind file like this

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Collections.ObjectModel;

namespace CustomPaging
    public partial class WebForm2 : System.Web.UI.Page
        int pageSize = 3;
        int totalRowCount = 0;
        protected void Page_Load(object sender, EventArgs e)
            if (!IsPostBack)

        /// <summary>
        /// This Method is used to generate the paging.
        /// </summary>
        /// <param name="totalRowCount">It has passed as integer.</param>
        /// <param name="pageSize">It has passed as integer.</param>
        /// <param name="currentPage">It has passed as integer.</param>

        public void generatePager(int totalRowCount, int pageSize, int currentPage)
            int totalLinkInPage = 3;
            int totalPageCount = (int)Math.Ceiling((decimal)totalRowCount / pageSize);

            int startPageLink = Math.Max(currentPage - (int)Math.Floor((decimal)totalLinkInPage / 2), 1);
            int lastPageLink = Math.Min(startPageLink + totalLinkInPage - 1, totalPageCount);

            if ((startPageLink + totalLinkInPage - 1) > totalPageCount)
                lastPageLink = Math.Min(currentPage + (int)Math.Floor((decimal)totalLinkInPage / 2), totalPageCount);
                startPageLink = Math.Max(lastPageLink - totalLinkInPage + 1, 1);

            List<ListItem> pageLinkContainer = new List<ListItem>();

            if (startPageLink != 1)
                pageLinkContainer.Add(new ListItem("First", "1", currentPage != 1));
            for (int i = startPageLink; i <= lastPageLink; i++)
                pageLinkContainer.Add(new ListItem(i.ToString(), i.ToString(), currentPage != i));

            if (lastPageLink != totalPageCount)
                pageLinkContainer.Add(new ListItem("Last", totalPageCount.ToString(), currentPage != totalPageCount));

            dlPager.DataSource = pageLinkContainer;
        protected void dlPager_ItemCommand(object source, DataListCommandEventArgs e)
            if (e.CommandName == "PageNo")

        private void BindGridview(int currentPage) 
            int startRowNumber = ((currentPage - 1) * pageSize) + 1;
            EmpDAL objDAL = new EmpDAL();
            EmpBE objBE = new EmpBE();
            objBE.StartIndex = startRowNumber;
            objBE.PageSize = 3;
            objBE.TotalCount = 0;
            Collection<EmpBE> objEmpList = new Collection<EmpBE>();
            objEmpList = objDAL.GetEmpsDetails(objBE,out totalRowCount);
            GridView1.DataSource = objEmpList;
            generatePager(totalRowCount, pageSize, currentPage);


Leave a Reply

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

You are commenting using your 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

%d bloggers like this: