• Download SQLPagerForEverything - 322.15 KB
• Download SQL Database Script.txt - 7.04 KB

Introduction

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 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 Pagging. 

Just drag and drop DataPager and one 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

Background  

SQL Pagging means it is implement 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 following properties of DataPager control are: 

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

Delegate ultimately need is 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
"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 contain 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)
        {
            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 is pass 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 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 have 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.