O'Reilly logo

ADO.NET Cookbook by Bill Hamilton

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required