How to do Custom paging in Gridview using SP ?

We can do custom paging using store Procedure in gridview like this

Step1: Write the Sp like this

USE [Test]
GO
/****** Object: StoredProcedure [dbo].[usp_GetEmpName] Script Date: 01/29/2012 18:18:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_GetEmpName]
@startRowIndex int,
@maximumRows int,
@totalRows int OUTPUT

AS

DECLARE @first_id int, @startRow int

SET @startRowIndex = (@startRowIndex – 1) * @maximumRows

IF @startRowIndex = 0
SET @startRowIndex = 1

SET ROWCOUNT @startRowIndex

SELECT @first_id = Id FROM tblEmp ORDER BY Id

PRINT @first_id

SET ROWCOUNT @maximumRows

SELECT ID, EmpName,EmpSal FROM tblEmp WHERE
ID >= @first_id
ORDER BY ID

SET ROWCOUNT 0
— GEt the total rows

SELECT @totalRows = COUNT(ID) FROM tblEmp

Step2: Design the default page like this

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Button ID="Button2" runat="server" Text="Click Here"
onclick="Button2_Click" />
<br />
<br />
<asp:GridView ID="Gridview1" runat="server">
</asp:GridView>
<asp:Button ID="Btn_Previous" CommandName="Previous"
runat="server" OnCommand="ChangePage"
Text="Previous" />
<asp:Button ID="Btn_Next" runat="server" CommandName="Next"
OnCommand="ChangePage" Text="Next" />
<br />
Pages <asp:Label ID="lblCurrentPage" runat="server" Text=""></asp:Label> Of
<asp:Label ID="lblTotalPages" runat="server" Text=""></asp:Label>

<br />
<br />

</div>
</form>
</body>
</html>

Step3: Write the C# code in DAL like this

public class EmpDAL
{
string connectionString = "Data Source=.\\sqlExpress;Initial Catalog=Test;Integrated Security=True";
public DataTable GetAllEmpName(int CurrPage,int PageSize,out int rowno)
{
using (SqlConnection con = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand("usp_GetEmpName", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@startRowIndex", CurrPage);
cmd.Parameters.AddWithValue("@maximumRows", PageSize);
cmd.Parameters.Add("@totalRows", SqlDbType.Int, 4);
cmd.Parameters["@totalRows"].Direction = ParameterDirection.Output;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
rowno = (int)cmd.Parameters["@totalRows"].Value;
return dt;
}
}
}

}

Step4: Write the C# code in Default page 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.Data;
using System.Data.SqlClient;

public partial class FinalEmpCustomPaging : System.Web.UI.Page
{
protected int currentPageNumber = 1;
private const int PAGE_SIZE = 10;
protected void Page_Load(object sender, EventArgs e)
{
NotVisible();

}

private void NotVisible()
{
Btn_Next.Visible = false;
Btn_Previous.Visible = false;
lblCurrentPage.Visible = false;
lblTotalPages.Visible = false;
}
private void Visible1()
{
Btn_Next.Visible = true;
Btn_Previous.Visible = true;
lblCurrentPage.Visible = true;
lblTotalPages.Visible = true;
}
private void BindData()
{
int rowno1;
EmpDAL objDAL = new EmpDAL();
Gridview1.DataSource = objDAL.GetAllEmpName(currentPageNumber, PAGE_SIZE,out rowno1);
Gridview1.DataBind();
double totalRows = rowno1;
lblTotalPages.Text = CalculateTotalPages(totalRows).ToString();
lblCurrentPage.Text = currentPageNumber.ToString();

if (currentPageNumber == 1)
{
Btn_Previous.Enabled = false;

if (Int32.Parse(lblTotalPages.Text) > 0)
{
Btn_Next.Enabled = true;
}
else
Btn_Next.Enabled = false;
}

else
{
Btn_Previous.Enabled = true;

if (currentPageNumber == Int32.Parse(lblTotalPages.Text))
Btn_Next.Enabled = false;
else Btn_Next.Enabled = true;
}
}
private int CalculateTotalPages(double totalRows)
{
int totalPages = (int)Math.Ceiling(totalRows / PAGE_SIZE);

return totalPages;
}
protected void ChangePage(object sender, CommandEventArgs e)
{
switch (e.CommandName)
{
case “Previous”:
currentPageNumber = Int32.Parse(lblCurrentPage.Text) – 1;
break;

case “Next”:
currentPageNumber = Int32.Parse(lblCurrentPage.Text) + 1;
break;
}

BindData();
Visible1();
}

protected void Button2_Click(object sender, EventArgs e)
{
Visible1();
BindData();
}
}

Developer Tool September 28, 2012 / 6:07 am

Really good article man. http://developertool.wordpress.com

Sumith November 21, 2013 / 11:39 am

Nice Article….