Custom Paging -Store Procedure through next and Previous Button

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

AS
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
ORDER BY EmpId

SET ROWCOUNT 0

-- GEt the total rows

SELECT @totalRows = COUNT(EmpId) FROM Emp

GO


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();
da.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
// 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;
}
else
{

btNext.Enabled = false;
}


}
else
{
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)
{
bind();
}

}


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;
bind();
}
protected void btNext_Click(object sender, EventArgs e)
{
currentPageNumber = Int32.Parse(lblDisplay1.Text) + 1;
bind();
}

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,
Balwant,