Custom Paging -Store Procedure through next and Previous Button

In the Articles i explains the concept of paging to any data bound controls using store procedure because it is beneficial to use paging using store procedure to fetch large amount of data from database. Store Procedure only send the fixed size data according to page size. To control the paging i used two buttons NEXT and PREVIOUS.

This is very simple example because I take gridview to show the result from database but this can be used with other data bound controls.

the table structure used in this sample app is as follow:-
Table Name:- Emp
EmpId int
EmpName Varchar
EmpSalary varchar
EmpAge char

The store procedure used to fatch the records is as:-
Store Procedure:-sp_CustomPaging

CREATE PROCEDURE [dbo].[sp_CustomPaging]
@startRowIndex int,
@maximumRows int,
@totalRows int OUTPUT

DECLARE @first_id int, @startRow int

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

IF @startRowIndex = 0
SET @startRowIndex = 1

SET ROWCOUNT @startRowIndex

SELECT @first_id = EmpId FROM Emp ORDER BY EmpId

PRINT @first_id

SET ROWCOUNT @maximumRows

SELECT EmpId, EmpName,EmpSalary, EmpAge FROM Emp WHERE
EmpId >= @first_id


-- GEt the total rows

SELECT @totalRows = COUNT(EmpId) FROM Emp


The Default.aspx page contain gridview and two buttons having id btPrevious and btNext.
Code in the .cs file

protected int currentPageNumber = 1;
private const int PAGE_SIZE = 5;

The currentpageNumber is used to store current page no. and constant PAZE_SIZE is used to set page size of gridview.
Below is the code to bind the gridview with data source

private void bind()
customerCls obj = new customerCls();
SqlCommand cmd = new SqlCommand();
cmd.Connection = obj.getcon();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_CustomPaging";
cmd.Parameters.AddWithValue("@startRowIndex", currentPageNumber);
cmd.Parameters.AddWithValue("@maximumRows", PAGE_SIZE);
cmd.Parameters.Add("@totalRows", SqlDbType.Int, 4);
cmd.Parameters["@totalRows"].Direction = ParameterDirection.Output;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
GridView1.DataSource = ds;
// get the total rows
double totalRows = (int)cmd.Parameters["@totalRows"].Value;
lbldisplay.Text = CalculateTotalPages(totalRows).ToString();
lblDisplay1.Text = currentPageNumber.ToString();

if (totalRows <= PAGE_SIZE)
btPrevious.Enabled = false;
btNext.Enabled = false;

else if (currentPageNumber == 1)
btPrevious.Enabled = false;
if (Int32.Parse(lbldisplay.Text) > 0)
btNext.Enabled = true;

btNext.Enabled = false;

btPrevious.Enabled = true;

if (currentPageNumber == Int32.Parse(lbldisplay.Text))
btNext.Enabled = false;
else btNext.Enabled = true;

// Calculate Total pages (this is used to calculate total pages)
private int CalculateTotalPages(double totalRows)
int totalPages = (int)Math.Ceiling(totalRows / PAGE_SIZE);

return totalPages;

The bind() method is use to bind gridview. the page load code is as:-

protected void Page_Load(object sender, EventArgs e)
if (!IsPostBack)


The following line of code is used to set the click events of both button to fetch data from data base and fill the grid with latest copy of data.
Here is the code:-

protected void btPrevious_Click(object sender, EventArgs e)
currentPageNumber = Int32.Parse(lblDisplay1.Text) - 1;
protected void btNext_Click(object sender, EventArgs e)
currentPageNumber = Int32.Parse(lblDisplay1.Text) + 1;

All these code is well tested with other data bound controls as well.

This is very useful if you have hues amount of data and gridview paging slow down your performance.

Thanks and Regards,


Author: naresh06 Oct 2011 Member Level: Silver   Points : 1

Hello friend i cant understand the variables used in stored procedure clearly so can u explain still clearly so that its useful for me

  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: