Click here to Skip to main content
11,648,785 members (79,566 online)
Click here to Skip to main content

SQL Pager Control for GridView, DataList, Repeater, DataGridView

, 6 May 2011 CPOL 44.3K 3.2K 88
Rate this:
Please Sign up or sign in to vote.
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 DataPageris a custom control that attaches to a data control (such as a GridView, ListView, Datalist, Repeater) allows paging on that control.

The DataPageritself 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 DataPagerand 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/ObjectDataSourcecontrols. 

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

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 WebFormhas a BindGrid()method to populate the data, and then populates the Page controls appropriately. The WebFormcreates 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):

DataPager_CS.png

The aspx.cs page contains the following code:

Records_cs.png

Program Flow

On the initial WebFormload, the relevant control flow starts at the WebForm's Page_Loadand 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 Delegateproperty upon every postback because the Delegateis not serialized and saved to the ViewStateby default. After the WebForm Page_Load, the User Control's Page_Loadis called. This sets the default business values (stored in the User Control's properties) and then calls the UpdatePageIndex()method.

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 Delegatereference from the property and invokes the method, triggering the data to be updated on the parent page. When the user clicks the RecordIndexprevious 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
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 Delegateand 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:

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)

Share

About the Author

Sunasara Imdadhusen
Software Developer (Senior) 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...

You may also be interested in...

Comments and Discussions

 
SuggestionUrgent Suggestions Pin
Member 985215126-Feb-13 20:11
memberMember 985215126-Feb-13 20:11 
GeneralRe: Urgent Suggestions Pin
Sunasara Imdadhusen19-May-13 19:56
professionalSunasara Imdadhusen19-May-13 19:56 
Generalmy vote of 4 Pin
Uday P.Singh11-Jan-12 19:59
memberUday P.Singh11-Jan-12 19:59 
GeneralRe: my vote of 4 Pin
Sunasara Imdadhusen22-Apr-14 2:43
professionalSunasara Imdadhusen22-Apr-14 2:43 
GeneralMy vote of 5 Pin
Shiv Shankar Maiti14-Dec-11 0:50
memberShiv Shankar Maiti14-Dec-11 0:50 
GeneralRe: My vote of 5 Pin
Sunasara Imdadhusen14-Dec-11 1:46
memberSunasara Imdadhusen14-Dec-11 1:46 
GeneralMy vote of 5 Pin
saxenaabhi623-Jun-11 18:19
membersaxenaabhi623-Jun-11 18:19 
GeneralRe: My vote of 5 Pin
Sunasara Imdadhusen23-Jun-11 21:04
memberSunasara Imdadhusen23-Jun-11 21:04 
GeneralMy vote of 5 Pin
Ali Al Omairi(Abu AlHassan)22-Jun-11 22:18
memberAli Al Omairi(Abu AlHassan)22-Jun-11 22:18 
GeneralRe: My vote of 5 Pin
Sunasara Imdadhusen23-Jun-11 21:03
memberSunasara Imdadhusen23-Jun-11 21:03 
GeneralMy vote of 5 Pin
NIRMAL UPADHYAY21-Jun-11 18:35
memberNIRMAL UPADHYAY21-Jun-11 18:35 
GeneralRe: My vote of 5 Pin
Sunasara Imdadhusen23-Jun-11 21:01
memberSunasara Imdadhusen23-Jun-11 21:01 
GeneralMy vote of 5 Pin
jayantbramhankar17-Jun-11 20:22
memberjayantbramhankar17-Jun-11 20:22 
GeneralRe: My vote of 5 Pin
Sunasara Imdadhusen18-Jun-11 2:16
memberSunasara Imdadhusen18-Jun-11 2:16 
General5 *Excellect* Pin
Anuj Tripathi14-Jun-11 1:31
memberAnuj Tripathi14-Jun-11 1:31 
GeneralRe: 5 *Excellect* Pin
Sunasara Imdadhusen14-Jun-11 3:52
memberSunasara Imdadhusen14-Jun-11 3:52 
GeneralRe: 5 *Excellect* Pin
Anuj Tripathi14-Jun-11 7:38
memberAnuj Tripathi14-Jun-11 7:38 
GeneralRe: 5 *Excellect* Pin
Sunasara Imdadhusen15-Jun-11 21:58
memberSunasara Imdadhusen15-Jun-11 21:58 
GeneralMy vote of 5 Pin
Monjurul Habib9-May-11 21:18
memberMonjurul Habib9-May-11 21:18 
GeneralRe: My vote of 5 Pin
Sunasara Imdadhusen11-May-11 18:37
memberSunasara Imdadhusen11-May-11 18:37 
GeneralMy vote of 4 Pin
ambarishtv7-May-11 23:01
memberambarishtv7-May-11 23:01 
GeneralRe: My vote of 4 Pin
Sunasara Imdadhusen8-May-11 19:15
memberSunasara Imdadhusen8-May-11 19:15 
GeneralThe Paging Method Pin
Dewey6-May-11 11:22
memberDewey6-May-11 11:22 
GeneralRe: The Paging Method Pin
Sunasara Imdadhusen8-May-11 19:17
memberSunasara Imdadhusen8-May-11 19:17 
GeneralMy vote is 5 Pin
Shahriar Iqbal Chowdhury6-May-11 11:16
memberShahriar Iqbal Chowdhury6-May-11 11:16 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.150804.4 | Last Updated 6 May 2011
Article Copyright 2011 by Sunasara Imdadhusen
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid