How to create custom paging in Gridview using CTE ?


CustomPaging

Hi

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



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Exec getEmpDetails 1,100,0

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

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

GO

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) 
        {
            con.Open();
            
            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;
                cmd.Parameters.Add(parTotalCount);

                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"]);
                        EmpList.Add(objEmp1);
                    }
                    con.Close();
                    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" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <style type="text/css">
        .style1
        {
            width: 100%;
        }
        .style3
        {
            width: 121px;
        }
        .style4
        {
            width: 98px;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" runat="server" CellPadding="4" Width="356px" ForeColor="#333333"
            AutoGenerateColumns="False">
            <AlternatingRowStyle BackColor="White" />
            <Columns>
                <asp:BoundField DataField="EmpId" HeaderText="EmpId" />
                <asp:BoundField DataField="EmpName" HeaderText="EmpName" />
                <asp:BoundField DataField="EmpSal" HeaderText="EmpSal" />
            </Columns>
            <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:GridView>
        <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" />
            <ItemTemplate>
                <asp:LinkButton Enabled='<%#Eval("Enabled") %>' runat="server" ID="lnkPageNo" Text='<%#Eval("Text") %>'
                    CommandArgument='<%#Eval("Value") %>' CommandName="PageNo"></asp:LinkButton>
            </ItemTemplate>
            <SelectedItemStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite" />
        </asp:DataList>
        <br />
    </div>
    </form>
</body>
</html>

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

       
        /// <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;
            dlPager.DataBind();
        }
        protected void dlPager_ItemCommand(object source, DataListCommandEventArgs e)
        {
            if (e.CommandName == "PageNo")
            {
                BindGridview(Convert.ToInt32(e.CommandArgument));
            }
        }

        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;
            GridView1.DataBind();
            generatePager(totalRowCount, pageSize, currentPage);
        }
    }
}

Advertisements

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

%d bloggers like this: