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:
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:
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)
{
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:
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 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.
