Paging through ResultSets in an ASP.NET application
Part 2: Implementing a custom-paging solution

By Sam Kapoor
Borland Software Corporation

In the previous article we talked about using the default-paging mechanism of the DataGrid component. We also discussed about the drawback of using this approach in certain real-world situations. In this article we shall discuss the second approach to paging (using a customized-paging solution) and will show how to implement this using C#Builder.

Custom-Paging: The DataGrid component includes another property named AllowCustomPaging that when set to True disables the built-in-paging mechanism of the DataGrid. Instead it expects the DataSource of the DataGrid to contain only the records that need to be displayed on a particular page. The big advantage of using this approach is that only the number of rows that need to be displayed on a given page (for example 10) need to be stored in the web-servers memory as opposed to the entire resultset. This in-turn can result in a much more scalable solution (especially for situations in which each session needs to have its own copy of the data. For even better scalability though one should have a middle-tier that can be scaled out).

The difficulty in implementing a custom-paging solution lies in the fact that different databases employ different techniques for returning a limited resultset.

Implementing a custom-paging solution: In this article we shall re-write the previous example (which used the Employee table from the Employee.gdb Interbase Database) to use custom-paging. Let us start by creating a new ASP.NET Web application.

Lets then select a DataGrid from the Web Controls list and drop it on our Web-form design surface. Set the DataGrids AutoFormat property to Professional1. Then set the AllowCustomPaging and the AllowPaging property to True and let the PageSize stay at 10. By setting the AllowPaging property to True we indicate to the DataGrid that a pager is to used and by setting the AllowCustomPaging property to True we indicate that the DataGrid should not implement any algorithm to page over the bound DataSource.

VirtualItemCount: When using the default-paging mechanism the DataGrid infers the total number of pages it needs to display by looking at the record count of the DataSource. This does not work in the Custom-paging scenario because the DataSource does not have all the records loaded as it only has the subset of records that are to be displayed on the current page. Hence we must provide a way for the DataGrid component to know how many pages to display (if using a numbered pager) or when to enable or disable the Next/Prev link (if using the Next/Prev pager). We do that by setting the VirtualItemCount property of the DataGrid to the total number of rows in the Employee table.

To get to work lets drop a BdpConnection and a BdpCommand component on our Design surface. We can do this by double-clicking on the 2 components. Set the BdpConnections ConnectionString property to the Interbase connection and the BdpCommands CommandText property to the appropriate SQL to get the record count from the Employee table. The BdpCommand has a CommandTextEditor that can be invoked to do this.

The BdpCommands Connection property is automatically set to the BdpConnection.

Lets now define a method that we can use to set the DataGrids VirtualItemCount property.

The ExecuteScalar method returns the value in the 0,0 postion (or the first column in the row in the resultset). When the page is loaded for the first time we must set the DataGrid components VirtualItemCount property to the return value from our setVirtualCount method. The only 2 requirements for Custom-paging to work are setting the VirtualItemCount property of the DataGrid and setting its AllowCustomPaging property.

Devising a paging strategy: Since we are going to implement the logic for allowing our resultset to be paged we must figure out a way to be able to scroll back and forth in our query. Since our DataGrid is only going to show 10 rows on a page the trick we will employ is to save the first and last Employee_Number (our key in the Employee Table) that is displayed on the grid and save it to the pages ViewState. The ViewState is the page call context and is implemented as a hidden field (encoded as a Base64 string) that gets written to the page when sent back to the browser. Our logic is going to be such that if the next button is clicked we will execute a query that returns the first 10 rows that have an Employee Number greater than what is stored in the ViewStates last employee number. Conversely when the user clicks the previous link on the pager we shall execute a query that returns the last 10 rows that are less than the first employee number stored in the ViewState. Once we get this logic implemented we shall be able to have customized paging. Each request to the page will execute a query that will only return 10 rows at the maximum. We must point out here that this strategy will only work with queries that have a unique key (which for most tables is not a problem).

Interbase specific SQL: One of the challenges in employing a custom-paging strategy is the dependence on database specific SQL to accomplish the task. With Interbase we can use the rows clause of the Select statement that will restrict our resultset to 10 rows. MS SQL Server has a TOP clause and Oracle has a RowNum clause. The included zip file contains the source of the GET_EMP stored procedure.

The stored procedure takes 3 parameters. NumRows indicates the number of rows to return, EmpNo is the employee number to be passed in (our key), and Prev is a boolean flag which is True if the Previous link is clicked else False. 

If the Next link is clicked we must execute a SQL such as :

Here the 10 is the number of rows to retrieve, 40 is the last Employee number displayed on the page and False implies that we are wanting to move forward.

If the Prev link is clicked our SQL query will be:

Here the 10 is the number of rows, 30 is the first employee number displayed on the page and True implies that we are wanting to scroll back. The Order By clause is required to get the resultset in the correct order.

Initialization steps: When the page is requested for the first time we need to perform some initialization of the ViewState of the page.

Here we initialize the entries in the ViewState to 0 so that the first page is retrieved with a LastEmpNo of 0 (which would return the first 10 rows).

Trapping the PageIndexChanged Event of the DataGrid: In order to determine in which direction we need to execute the query (forwards or backwards) we need to determine the pagers link that was clicked by the user. When the user clicks the pagers link (next/prev) the DataGrid fires the ItemCommand event. The CommandArgument member is set to either Next or Prev.

After the ItemCommand event fires the DataGrid fires its PageIndexChanged event in which we can execute the query and re-bind the DataGrid.

Notice here after binding the DataSource to the DataGrid we set the ViewState to hold the First Employee Number displayed in the DataGrid and the Last Employee Number to use for the next query (to move forwards or backwards).

We use the same BdpCommand component to issue our query. However, we use an instance of a BdpDataReader to bind as a DataSource to the DataGrid instead of a DataSet as it is much faster.

The complete code-behind C# class can be found here

Conclusions: As you can see with a little bit of work we can devise an effective customized paging strategy. The trick is coming up with the correct SQL (database specific usually) to get the correct subset of records. This technique will also work with tables that get updated/deleted. For web-applications that serve a large number of concurrent users who need access to different subsets of data a customized paging solution is far better than the default-paging solution. To achieve greater scalability one can encapsulate the logic for the customized paging in a middle-tier.