Custom Paging in ASP.Net GridView using SQL Server Stored Procedure

In this article we will learn how to use Custom Paging in ASP.Net GridView using SQL Server Stored Procedure.

Stored Procedure for Paging

The following Stored Procedure makes use of ROW_NUMBER function to fetch records Page Wise from SQL Server Database Table.
The PageIndex and PageSize values are passed as parameter and the Stored Procedure returns the Total records of the Table using the RecordCount Output parameter.

HTML Markup

The following HTML Markup consists of a GridView, a DropDownList for selecting the Page Size and a Repeater which will be used for populating the pager.

The DropDownList is populated with some static values which will be later used for setting the Page Size of the GridView control.

Binding the GridView

• Inside the Page Load event, the GetCustomersPage function is called with PageIndex parameter value as 1.
• Inside the function, the Stored Procedure is executed and it fetches the records based on PageIndex and PageSize (fetched from the DropDownList) from Customers Table of the Northwind database using DataReader.
• Finally, the DataReader is used to populate the GridView. After execution of DataReader, the Total Record Count value is fetched using the RecordCount Output parameter.

The Total Record Count value is passed to the PopulatePager method along with the PageIndex.

Populating the Pager

The PopulatePager method accepts the count of the total records present in the Table and the current PageIndex.

Then some calculations are done to find the first and the last page of the pager and the using a loop a List of Pages are populated and finally are bound to the Repeater control for populating the Pager.

Binding the GridView on DropDownList Change

Following event handler is executed when the Page Size DropDownList is changed.

This method simply calls the GetCustomersPage method.

Binding the GridView on Pager LinkButton Click

Following event handler is executed when the Page Number LinkButton is clicked.

• Inside this event handler, the PageIndex is referenced using LinkButton CommandArgument property.
• Finally, GetCustomersPage method is called by passing the PageIndex value to it.
• Here we used custom stylesheet for gridview pager.

Output

When run your application you can see the output like below.

</> Find the Source Code in Github

  Summary

In this tutorial we discussed how to use Custom Paging in ASP.Net GridView using SQL Server Stored Procedure. If have any question related to this topic then give your feedback.