Click here to Skip to main content
15,867,488 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.6K   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.

Introduction

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:

Records.png

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.

Background

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:

SQL
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: 

DataPager.png

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.

C#
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):

DataPager_CS.png

The aspx.cs page contains the following code:

Records_cs.png

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.

C#
if (!IsPostBack)
        {
            BindGrid(1);
        }
        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:

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

	With RecordEntries as (
		SELECT ROW_NUMBER() OVER (ORDER BY A.APP_TYPE_ID ASC) as Row, _
		A.APP_TYPE_ID, 
		A.APP_TYPE_NAME,A.APP_STORE_ID,R.REVIEW_TITLE,R.AUTHOR_NAME,_
		R.REVIEW_DATE,
		R.REVIEW_RATING,R.REVIEW_TEXT FROM [dbo].[APP_TYPES] A
		INNER JOIN [CUSTOMER_APP_REVIEWS] R ON A.APP_TYPE_ID=R.APP_TYPE_ID
	)
	Select APP_TYPE_ID, APP_TYPE_NAME, APP_STORE_ID, REVIEW_TITLE, AUTHOR_NAME, 
	REVIEW_DATE, REVIEW_RATING, REVIEW_TEXT
	FROM RecordEntries
	WHERE Row between 
	@startRowIndex and @endRowIndex
	
	SELECT COUNT(*) FROM [dbo].[APP_TYPES] A
	INNER JOIN [CUSTOMER_APP_REVIEWS] R ON A.APP_TYPE_ID=R.APP_TYPE_ID
END 

Summary

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:

NoRecordFound.png

Points of Interest

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

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

History

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.

License

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


AWARDS:

  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

 
GeneralRe: My vote is 5 Pin
Sunasara Imdadhusen8-May-11 19:18
professionalSunasara Imdadhusen8-May-11 19:18 
GeneralRe: My vote is 5 Pin
Sunasara Imdadhusen22-Apr-14 2:43
professionalSunasara Imdadhusen22-Apr-14 2:43 
GeneralSuggestion Pin
thatraja6-May-11 7:28
professionalthatraja6-May-11 7:28 
GeneralRe: Suggestion Pin
Dewey6-May-11 11:19
Dewey6-May-11 11:19 
GeneralRe: Suggestion Pin
Sunasara Imdadhusen8-May-11 19:24
professionalSunasara Imdadhusen8-May-11 19:24 
GeneralRe: Suggestion Pin
thatraja23-Jun-11 20:42
professionalthatraja23-Jun-11 20:42 
GeneralRe: Suggestion Pin
Sunasara Imdadhusen23-Jun-11 21:03
professionalSunasara Imdadhusen23-Jun-11 21:03 
GeneralMy vote of 5 Pin
Burak Ozdiken6-May-11 5:17
Burak Ozdiken6-May-11 5:17 
nice
GeneralRe: My vote of 5 Pin
Sunasara Imdadhusen8-May-11 19:24
professionalSunasara Imdadhusen8-May-11 19:24 

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.