Click here to Skip to main content
15,847,549 members
Articles / Web Development / ASP.NET

SQL Pager Control for GridView, DataList, Repeater, DataGridView

Rate me:
Please Sign up or sign in to vote.
4.65/5 (32 votes)
6 May 2011CPOL5 min read 81.2K   3.6K   87   34
The DataPager is a custom control that attaches to a data control (such as a GridView, ListView, Datalist, Reapeater) allows paging on that control.


The DataPager is a custom control that attaches to a data control (such as a GridView, ListView, Datalist, Repeater) allows paging on that control.

The DataPager itself displays the "First", "Next", "Previous" and "Last" buttons (or Numerical pages or a Custom combination). You click on the buttons the pager provides and your data control will automatically page through the data using SQL Paging. 

Just drag and drop DataPager and one or two lines of code in your .aspx.cs page:


Problem Event Handling with User Control

User Controls have a lot of benefits – they let you abstract a group of commonly used controls to a single, reusable control. Sometimes, there will be a business need to pass data between the User Control and its containing ASP.NET Web page. For example, you might have an address control, and upon loading the page it sets the address control's street, city, and state properties. However, while it's simple enough for an Aspx page to trigger a User Control's methods, it is not as simple for the User Control to conversely trigger its containing page's methods.


SQL Paging means it implements super efficient data-paging using the new ROW_NUMBER() within SQL 2005 (which is supported with the free SQL Express edition and up) and the new ASP.NET 2.0 GridView/ObjectDataSource controls. 

Sample SQL Server paging query:

With RecordEntries as (
		SELECT ROW_NUMBER() OVER (ORDER BY [OrderByField) as Row, 
		FROM [TableName]
Select * FROM RecordEntries
WHERE Row between @startRowIndex and @endRowIndex

Using the Code

When you put the control in .aspx page, then you are able to set the following properties of DataPager control: 


Now add the following line of code in .aspx.cs file:

Delegate ultimately needs for the User Control to be able to call a method, i.e., we'd like to pass it a method reference and let it call that method on its own terms. This is exactly what a Delegate lets us do. According to MSDN, a Delegate "is a data structure that refers to a static method or to a class instance and an instance method of that class."

In other words, you can assign a method-reference to a Delegate and pass that similar to how you'd pass other types. We will solve the problem with an example solution for the record navigator problem we mentioned in the introduction. In this example, there is a WebForm that contains a User Control. The User Control contains two properties, one for a Delegate and one for the business data – in this case an index as an integer. The WebForm has a BindGrid() method to populate the data, and then populates the Page controls appropriately. The WebForm creates a Delegate that refers to the BindGrid() method, and passes that Delegate to the User Control's Delegate-type property. Whenever the User Control's previous or next buttons are clicked, it then calls the Delegate that it was given, passing in the data values selected from the User Control (in this case just index). Finally the Delegate in turn triggers the parent page's BindGrid() method.

public delegate void delPopulateData(int myInt);

Technical Implementation

Now that we have a high-level understanding of what we want to do, let's code it. First, we want to create the User Control. Create a User Control named DataPager.ascx and add the following two properties to it (Code is very self explanatory):


The aspx.cs page contains the following code:


Program Flow

On the initial WebForm load, the relevant control flow starts at the WebForm's Page_Load and sets the User Control's properties. It only needs to set the business data (like the Page Index) the first time because that data is serialized and persists in the page's ViewState. It sets the Delegate property upon every postback because the Delegate is not serialized and saved to the ViewState by default. After the WebForm Page_Load, the User Control's Page_Load is called. This sets the default business values (stored in the User Control's properties) and then calls the UpdatePageIndex() method.

if (!IsPostBack)
        delPopulateData delPopulate = new delPopulateData(this.BindGrid);
        pagerApps.UpdatePageIndex = delPopulate;

This method updates the User Control's GUI to reflect the values, and then gets the Delegate reference from the property and invokes the method, triggering the data to be updated on the parent page. When the user clicks the RecordIndex previous or next buttons, they update the internal business data appropriately, and then call the UpdatePageIndex() method, which updates the parent page as just described previously. The control flow is nearly identical for postbacks – except that the default business data is not reset.

SQL Server Paging Logic

The following Stored Procedure will return only those records that are passed using parameter:

--EXEC [GetAppsDetails] 1,10
ALTER PROCEDURE [dbo].[GetAppsDetails] 
	@PageIndex INT,
	@RecordsPerPage INT
	Declare @startRowIndex INT;
	Declare @endRowIndex INT;
	set @endRowIndex = (@PageIndex * @RecordsPerPage);
	set @startRowIndex = (@endRowIndex - @RecordsPerPage) + 1;

	With RecordEntries as (
	FROM RecordEntries
	WHERE Row between 
	@startRowIndex and @endRowIndex


User Controls offer many benefits to Web applications. Part of taking advantage of these benefits is passing data both ways between a WebForm and a User Control. While passing data to the User Control is trivial, passing it back from the User Control to the page is not. However, we can still solve this by having the page instantiate a Delegate and pass that to the User Control, giving the User Control the ability to trigger a parent page's method on demand.

If no data exists in source, then DataPager looks like:


Points of Interest

I will try to develop a control that has not required any single line of code like:

public delegate void delPopulateData(int myInt);
 delPopulateData delPopulate = new delPopulateData(this.BindGrid);
        pagerApps.UpdatePageIndex = delPopulate;


If you find some issues or bugs with it, just leave a comment or drop me an email. If you make any notes on this, let me know that too so I don't have to redo any of your hard work. Please provide a "Vote" if this would be helpful.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By
Technical Lead Infostretch Ahmedabad-Gujarat
India India
Aspiring for a challenging carrier wherein I can learn, grow, expand and share my existing knowledge in meaningful and coherent way.

sunaSaRa Imdadhusen


  1. 2nd Best Mobile Article of January 2015
  2. 3rd Best Web Dev Article of May 2014
  3. 2nd Best Asp.Net article of MAY 2011
  4. 1st Best Asp.Net article of SEP 2010

Read More Articles...

Comments and Discussions

SuggestionUrgent Suggestions Pin
Member 985215126-Feb-13 21:11
Member 985215126-Feb-13 21:11 
GeneralRe: Urgent Suggestions Pin
Sunasara Imdadhusen19-May-13 20:56
professionalSunasara Imdadhusen19-May-13 20:56 
Generalmy vote of 4 Pin
Uday P.Singh11-Jan-12 20:59
Uday P.Singh11-Jan-12 20:59 
GeneralRe: my vote of 4 Pin
Sunasara Imdadhusen22-Apr-14 3:43
professionalSunasara Imdadhusen22-Apr-14 3:43 
GeneralMy vote of 5 Pin
Shiv Shankar Maiti14-Dec-11 1:50
Shiv Shankar Maiti14-Dec-11 1:50 
GeneralRe: My vote of 5 Pin
Sunasara Imdadhusen14-Dec-11 2:46
professionalSunasara Imdadhusen14-Dec-11 2:46 
GeneralMy vote of 5 Pin
saxenaabhi623-Jun-11 19:19
saxenaabhi623-Jun-11 19:19 
GeneralRe: My vote of 5 Pin
Sunasara Imdadhusen23-Jun-11 22:04
professionalSunasara Imdadhusen23-Jun-11 22:04 
GeneralMy vote of 5 Pin
Ali Al Omairi(Abu AlHassan)22-Jun-11 23:18
professionalAli Al Omairi(Abu AlHassan)22-Jun-11 23:18 
GeneralRe: My vote of 5 Pin
Sunasara Imdadhusen23-Jun-11 22:03
professionalSunasara Imdadhusen23-Jun-11 22:03 
GeneralMy vote of 5 Pin
NIRMAL UPADHYAY21-Jun-11 19:35
NIRMAL UPADHYAY21-Jun-11 19:35 
GeneralRe: My vote of 5 Pin
Sunasara Imdadhusen23-Jun-11 22:01
professionalSunasara Imdadhusen23-Jun-11 22:01 
GeneralMy vote of 5 Pin
jayantbramhankar17-Jun-11 21:22
jayantbramhankar17-Jun-11 21:22 
GeneralRe: My vote of 5 Pin
Sunasara Imdadhusen18-Jun-11 3:16
professionalSunasara Imdadhusen18-Jun-11 3:16 
General5 *Excellect* Pin
Anuj Tripathi14-Jun-11 2:31
Anuj Tripathi14-Jun-11 2:31 
GeneralRe: 5 *Excellect* Pin
Sunasara Imdadhusen14-Jun-11 4:52
professionalSunasara Imdadhusen14-Jun-11 4:52 
GeneralRe: 5 *Excellect* Pin
Anuj Tripathi14-Jun-11 8:38
Anuj Tripathi14-Jun-11 8:38 
GeneralRe: 5 *Excellect* Pin
Sunasara Imdadhusen15-Jun-11 22:58
professionalSunasara Imdadhusen15-Jun-11 22:58 
GeneralMy vote of 5 Pin
Monjurul Habib9-May-11 22:18
professionalMonjurul Habib9-May-11 22:18 
GeneralRe: My vote of 5 Pin
Sunasara Imdadhusen11-May-11 19:37
professionalSunasara Imdadhusen11-May-11 19:37 
GeneralMy vote of 4 Pin
ambarishtv8-May-11 0:01
ambarishtv8-May-11 0:01 
good presentation
GeneralRe: My vote of 4 Pin
Sunasara Imdadhusen8-May-11 20:15
professionalSunasara Imdadhusen8-May-11 20:15 
GeneralThe Paging Method Pin
Dewey6-May-11 12:22
Dewey6-May-11 12:22 
GeneralRe: The Paging Method Pin
Sunasara Imdadhusen8-May-11 20:17
professionalSunasara Imdadhusen8-May-11 20:17 
GeneralMy vote is 5 Pin
Shahriar Iqbal Chowdhury/Galib6-May-11 12:16
professionalShahriar Iqbal Chowdhury/Galib6-May-11 12:16 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.