How to implement Custom paging Using ASP.NET Data Grid

How to implement Custom paging Using ASP.NET Data Grid

You have Inbuilt paging facility for some of the data binding control like data grid. But it can be very inefficient. Control like repeater you don’t have inbuilt data binding facility. When you are using inbuilt paging facility it loads all the rows so very resource consuming and inefficient. But when you are using custom paging facility you return only requested rows.

Here we will examine how can we implement custom paging for data Grid using stored procedure. Here is the implementation of stored procedure. Here you have 3 parameter for stored procedure.

@page is basically page number in data grid

@pageSize is Number of records you are going to show one page

@TotalNofRecords is output parameter. It return total Number of records in the Query

Here when you go through the stored procedure you have create a tempory table and you retrieve only required data rows from that tempory table. Using @StartRecordNo and @EndRecordNo

Create PROCEDURE [dbo].[GetEmployee]

(

@Page int,

@PageSize int,

@TotalNofRecords int output

)

AS

BEGIN

SET NOCOUNT ON

DECLARE @StartRecordNo int, @EndRecordNo int

IF (@Page=0)

BEGIN

SET @StartRecordNo = 1

SET @EndRecordNo = @PageSize

END

ELSE

BEGIN

SET @StartRecordNo = @PageSize * @Page + 1

SET @EndRecordNo = (@PageSize * (@Page+1) )

END

CREATE TABLE [#TmpEmployee]

(

[RowNo] int identity(1,1),

[EmployName] int not null,

[Address] varchar(255)

)

INSERT INTO [#TmpEmployee]([EmployName], [Address])

SELECT

FirstName, Address1

FROM Employee

ORDER BY FirstName

SELECT @TotalNofRecords = COUNT( FirstName) FROM [#TmpEmployee]

SET NOCOUNT OFF

SELECT [RowNo], [FirstName], [Address]

FROM [#TmpEmployee]

WHERE [RowNo]>= @StartRecordNo

AND [RowNo]<= @EndRecordNo

RETURN 0

END

When you bind the grid you can have method call BindGrid method

private void BindGrid(int pageNo)

{

try

{

int locationId ;

int recordsPerPage = 10 ;

int totalRecords = 0;

DataTable empList = EmpService.GetEmployee( pageNo, recordsPerPage ,ref totalRecords);

if (empList.Rows.Count > 0)

{

uxEmployeGrid.DataSource = jobsList;

uxEmployeGrid.VirtualItemCount = totalRecords;

uxEmployeGrid.DataBind();

}

}

catch(Exception ex)

{

throw ex;

}

}

When call the first time call the BindGrid Method with pageNo = 0

BindGrid(0);

When click the paging call the BindGrid method

protected void uxEmployeGrid_PageIndexChanged(object source, DataGridPageChangedEventArgs e)

{

uxEmployeGrid.CurrentPageIndex = e.NewPageIndex;

BindGrid(uxEmployeGrid.CurrentPageIndex);

}