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);
}
}
}
Chandradev's Blog 