email article printer friendly get the code more resources

Design a Custom Paging Solution
Turbocharge paging through large datasets in your Web apps' list screens with ASP.NET's DataGrid control.
by Brett Olges and Doug Safford

October 2002 Issue

For this solution: .NET Framework, SQL Server 7.0 or SQL Server 2000

Web applications are a great way to expose informa tion you hold in databases to both internal and external data consumers. Though complex business, access, and security rules might lie between the Web browser and the database server, at their core most Web applications are front ends providing users with simple and intuitive ways to query and update a database. One criterion users judge any database application by is its ability to deliver information quickly. Many Web apps provide one or several searchable list screens to help users locate records efficiently. A simple example is an online library system that enables users to search for books by author, title, or subject.

Figure 1 Default Paging Returns All Rows

The new DataGrid control in ASP.NET makes creating list screens easier then ever for Web developers. In addition to its built-in presentation logic and methods, the control provides out-of-the box logic for breaking a list into pages. Paging provides the user with a more manageable list to look through and can sometimes provide better perceptible performance. DataGrid's built-in paging logic is easy to implement, but its convenience comes at the price of poorer performance when your app returns a large data set. In this article, we'll show you how to design a blazing-fast custom paging solution with the DataGrid that can handle extremely large result sets.

The technique we'll describe is faster and more efficient then the DataGrid's default paging because it doesn't require the database to send the entire result set to the Web server with every page request (see Figure 1 and the sidebar, "Make the DataGrid Control's Built-In Paging Efficient"). Instead, it sends only the rows you need to build each specific page. For example, if a user asks for page 3 of a 100-page result set, you'll send down rows 75 to 100 instead of rows 1 to 2500 (see Figure 2).

Figure 2 Custom Paging Returns Selected Rows

The first step in designing a custom paging solution is to build stored procedures on the database that accept parameters representing the first and last row number of the set of rows you need to return. SQL Server 7.0 and above include a "top" extension to Structured Query Language (SQL), allowing you to request only the first given number of rows be returned. Unfortunately, no extension in SQL Server lets you specify that a middle section of rows—for instance, rows 75 through 100—be returned. Oracle includes a rownum() extension in its procedural-language extension to SQL (PL/SQL) that can help you return a middle section of rows by using a statement such as "select * from Authors where Au_LName = 'Anderson' and rownum() >= 75 and rownum <= 100". However, because Oracle assigns the "rownum" value to a row before the sort, a PL/SQL query such as "select * from Authors where rownum <= 25 order by Au_LName" probably won't yield the results you expect. The technique we'll describe was developed for SQL Server, but the concept might be useful for Oracle developers too, especially if the list screen you're designing must return results in a specific order.