ADO.NET 3.5 Cookbook, 2nd Edition

10.8. 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 solution uses a single stored procedure, which is shown in Example 10-12.

Page_Contact

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.

@FirstContactID

An input parameter that contains the ContactID of the first record of the client's current page of Contact data.

@LastContactID

An input parameter that contains the ContactID 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 10-12. Stored procedure: Page_Contact

USE AdventureWorks
GO

CREATE PROCEDURE Page_Contact @PageCommand nvarchar(10), @FirstContactID int = null, @LastContactID int = null, @PageCount int output, @CurrentPage int output AS SET NOCOUNT ON SELECT @PageCount = CEILING(COUNT(*)/10) ...