ADO.NET Cookbook

9.4. Improving Paging Performance

Problem

Given an application that allows the user to page through a large result set in a data grid, you need to improve the performance of the paging.

Solution

Build a custom paging solution that overcomes the performance limitations of the overloaded Fill( ) method of the DataAdapter.

The sample uses a single stored procedure, which is shown in Example 9-5:

SP0904_PageOrders

Used to return 10 records from the Orders table of the Northwind database that correspond the first, last, next, or previous page, or a specific page. The procedure has the following arguments:

@PageCommand

An input parameter that accepts one of the following values: FIRST, LAST, PREVIOUS, NEXT, or GOTO. This specifies the page of results to return to the client.

@FirstOrderId

An input parameter that contains the OrderID of the first record of the client’s current page of Orders data.

@LastOrderId

An input parameter that contains the OrderID of the last record of the client’s current page of Orders data.

@PageCount

An output parameter that returns the number of pages, each of which contains 10 records, in the result set.

@CurrentPage

An output parameter that returns the page number of the result set returned.

Example 9-5. Stored procedure: SP0904_PageOrders

ALTER PROCEDURE SP0904_PageOrders @PageCommand nvarchar(10), @FirstOrderId int = null, @LastOrderId int = null, @PageCount int output, @CurrentPage int output AS SET NOCOUNT ON select @PageCount = CEILING(COUNT(*)/10) from Orders -- first ...