Custom paging in ASP.NET 2.0 and SQL Server 2005 using ObjectDataSource

Introduction

Paging is a common feature of business applications when reporting and searching specific business entity information. Using paging mechanisms, you can break all the data into smaller page of data and allow the user to step through the data one page at a time, thus improving performance as well as enabling the data be more manageable.

This article is intended to show how to apply the paging mechanisms in Web applications using ASP.NET 2.0 and SQL Server 2005 as well as using the ObjectDataSource components. The underlying business scenario is AdventureWorks Inc. which wants to publish its products using Portal site. The data associated to the business entity Product is persisted in the table Production.Product of the database AdventureWorks shipped with SQL Server 2005.

Developing the solution

One way to implement paging in ASP.NET application is by setting the Enable Paging checkbox from the GridView control's smart tag. This limits the number of items shown per page allowing the user navigate from one page to another in order to find other items. One drawback with this solution is that we need to re-request all the data from the underlying data source whenever we move from one page to another (even though only a subset of the items is actually rendered). This is not a good choice to navigate through large amounts of data. This style is commonly referred as default paging.

I want to explain another style of paging referred as custom paging. In this case, we request only the required items to be rendered in the Web page. This style of paging improves dramatically the performance of the Web application specifically when we're searching through large amounts of data.

This approach is implemented by writing SQL queries which returns the precise set of items to be displayed for a particular page. In SQL Server we can find a new keyword for ranking results, which enables us to write a query which can efficiently retrieve a subset of records. We're going to use this new feature to write the SQL queries that retrieves the items for the underlying pages.

When you're developing a solution which uses paging mechanisms, we need to consider two things: the page number being requested and the number of items displayed on this page. There are three parameters that dictate which items are retrieved and how the paging user interface should be rendered:

TotalItemsCount. The total number of items to be paged and displayed. It's not important to determine what records to be retrieve although it's very important for the generation of the user interface, because it dictates how many pages the UI will have.
MaxItemsCount. The maximum number of items to be displayed in a page.
StartItemIndex. The index of the first row in the page to be displayed. You can do math to get this index using the following formula StartItemIndex=(PageNumber-1)*MaxItemsCount+1. If MaxItemsCount is equal to 10, then the formula is (PageNumber-1)*10+1 and the first item in the page 3 is (3-1)*10+1=21.

The server-side code

The code to return the total number of records being paged through is implemented using a stored procedure as shown in Listing 1.

create procedure spGetTotalProductsCount

begin

select count(*)

from Production.Product;
end;

Listing 1

In order to retrieve the precise subset of products, we need to deal with a new feature of SQL Server 2005: the ROW_NUMBER. The ROW_NUMBER feature associates a ranking with each retrieved row from a table based on some ordering. This ranking can be used as a row index for each row. After we have assigned a row index to each row, thus we can retrieve just those records starting at the StartItemIndex and up to the MaxItemsCount number of records. Let's see how to establish a row index to products using the ROW_NUMBER feature (see Listing 2).

select [Name], ProductNumber, ListPrice, ROW_NUMBER() over(order by [Name]) as ProductIndex
from Production.Product;

Listing 2

One drawback with the ROW_NUMBER feature is that it cannot directly be used in WHERE clause, although a derived table can be used to return the ROW_NUMBER values, which can then be used in WHERE clause.

Let's write the code for a stored procedure which receives two input parameters @StartItemIndex and @MaxItemsCount and returns only the subset of products whose row index is greater than the specified @StartItemIndex and less than or equal to the @StartItemIndex+@MaxItemsCount (see Listing 3).

create procedure spGetPagedProducts

@StartItemIndex int,

@MaxItemsCount int

begin

select [Name], ProductNumber, ListPrice

from (select [Name], ProductNumber, ListPrice, ROW_NUMBER() over(order by [Name]) as ProductIndex

from Production.Product) as ProductsWithIndexRow

where ProductIndex > @StartItemIndex and ProductIndex <= @StartItemIndex+@MaxItemsCount;

end;

Listing 3

The Web-side code

Now it's time to write the Web-side code of the paging solution.

The first step is to create a Web application. Open Visual Studio.NET 2005 and select File | New | Web Site. Create a Web Site named PagingWebApp (see Figure 1).

Figure 1

Now let's add a strongly typed DataSet to the project and name it DSProduct.xsd (see Figure 2).

Figure 2

Then the TableAdapter Configuration Wizard is launched. In the first page, you need to select a connection to the AdventureWorks database. Click Next button, and enter the name for the connection string setting to be saved in the application configuration file. Click Next button, and in the Choose a Command Type page select the option "Use existing stored procedure" (see Figure 3).

Figure 3

Click the Next button and in the Bind Commands to Existing Stored Procedures page, map the spGetPagedProducts to the Select command (see Figure 4).

Figure 4

Then rename the method's name (see Figure 5).

Figure 5

Finally, click on the Finish button.

Now add a new query to get the total products. Right-click on the newly created DataTable and select Add Query from the context menu. Choose the option "Use existing stored procedure" on the "Choose a Command Type" page (see Figure 6).

Figure 6

Then select the spGetTotalProductsCount stored procedure (see Figure 7).

Figure 7

Then specify that the stored procedure will return a single value (see Figure 8).

Figure 8

Set GetTotalProductsCount as the name of this method and finally rename the DataTable as PageProduct (see Figure 9).

Figure 9

Configuring the ObjectDataSource to use custom paging

Now let's work on the presentation layer. Open the Default.aspx Web Form page in your project. Let's configure the ObjectDataSource as the proxy between the Web controls in the Presentation layer and the business objects in the Business Logic layer.

Drag and drop an ObjectDataSource component from the Data tab in the Toolbox window onto the Web Form page. Click on the Configure Data Source link in the ObjectDataSource and the Configure Data Source Wizard is launched.

Then choose a business object, in this case, the PagedProductsTableAdapter (see Figure 10).

Figure 10

In the next page, map the GetData method of the PagedProductsTableAdapter to the Select method of the ObjectDataSource (see Figure 11).

Figure 11

Finally the wizard prompts for the sources of the GetData method's StartItemIndex and MaxItemsCount input parameter's value. These parameter values will actually be set by the GridView automatically, thus leave the source set to None and click on the Finish button (see Figure 12).

Figure 12

Now let's drag and drop a GridView control from the Data tab in the Toolbox window onto the Web Form page. Click on the smart tag and select Choose Data Source option. Then configure the GridView to support paging by checking the Enable Paging checkbox in its smart tag (see Figure 13).

Figure 13

Let's configure the ObjectDataSource in order to use custom paging. Click on the ObjectDataSource and open to the Properties window.

Set the EnablePaging property to True. Set the SelectCountMethod property to the method that returns the total number of records being paged through, in this case, is GetTotalProductsCount.

Then Set the MaximumRowsParameterName to MaxItemsCount. And finally set the StartRowIndexParameterName to StartItemIndex.

Now we need to do a trick. Remove the <asp:Parameter> elements inside the ObjectDataSource's declarative markup. These elements are added by the Wizard you configure the Select method of the ObjectDataSource (see Figure 11). When you set the EnablePaging property to True, these parameters are passed automatically to the Select method. You leave these parameters in the declarative syntax, you will receive an exception.

Conclusion

In this article, I discussed the key techniques to implement the paging mechanisms in Web applications using the new features of SQL Server 2005 as well as ASP.NET with ObjectDataSource component.