Efficient Data Paging with the ASP.NET 2.0 DataList Control and ObjectDataSource
page 1 of 5
Published: 07 Jan 2006
Unedited - Community Contributed
Abstract
In this article, Scott examines how to efficiently page through data using DataList and ObjectDataSource controls included with ASP.NET 2.0.
by Scott Guthrie
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 50680/ 116

Introduction

Republished With Permission - Original Article

Last weekend I posted about how to implement super efficient data-paging using the new ROW_NUMBER() within SQL 2005 (which is supported with the free SQL Express edition and up) and the new ASP.NET 2.0 GridView/ObjectDataSource controls.

The BIG win with this approach is that it enables a web-application to efficiently retrieve only the specific rows it needs from the database, and avoid having to pull back dozens, hundreds, or even thousands of results to the web-server – only to then end up trimming them down to the 10-15 you end up displaying on a web-page (which is what most data-paging solutions do today).  This can lead to significant performance wins within your application (both in terms of latency and throughput), and reduce the load on your database.

A few people have asked me to also provide a similar sample that demonstrates how to also implement efficient data-paging using an ASP.NET DataList or Repeater control instead (since neither of these have built-in paging UI semantics – but they do have the big benefit of allowing developers to have tight control over the html generated). 

You can download a sample app that demonstrates how to-do this here.

For the scenario in the sample I basically “borrowed” the UI used on several popular ecommerce web-sites (although I tried to be a good CSS citizen, and implemented it using CSS rules instead of a table-heavy format):

Figure 1

It provides a list of product-categories on the left-hand side of the product listing page (the value in parenthesis indicates the number of products in each category), and then a 2 column layout for the products on the right (the default page size is 10 per page – although you can change this with a 1 line code-change if you want a different size).  You can page back and forward throughout the listing, and are provided UI that helps indicate where you are in the paging series (the Previous/More links also auto-hide if you are on the first or last page of the results).

What is cool about the sample is that all of the data on the right-hand side of the screen (everything but the product category listing on the left) is retrieved with a single database call on each page view (so no multiple database trips).  This single database call also only returns the product rows of data we end up displaying on the given data page (so with the screen-shot above it returns 10 rows of data instead of all 97 rows in that product category).  This makes it super fast and efficient.

The entire sample to implement this app is only about ~20 lines of total code (this includes all the code in both the UI and data code layers).


View Entire Article

User Comments

Title: That great but,   
Name: Quoc Thinh
Date: 8/17/2007 11:42:53 PM
Comment:
There are two thing, first SQL 2000 don't have ROW_NUMBER, and the second if the results has too many pages, and the pager should be, for example 10 page per view then when click Next, it show next 10 pager. So is there any solution for it, and even if you could this should use AJAX.
Thank very much
Title: That great but,   
Name: Quoc Thinh
Date: 7/1/2007 11:06:28 AM
Comment:
There are two thing, first SQL 2000 don't have ROW_NUMBER, and the second if the results has too many pages, and the pager should be, for example 10 page per view then when click Next, it show next 10 pager. So is there any solution for it, and even if you could this should use AJAX.
Thank very much
Title: Use the New keyword to create the instance.   
Name: Gary
Date: 5/5/2007 7:58:24 PM
Comment:
Great article Scott, but line 15 in Products.aspx.cs throws the error: "Use the New keyword to create an object instance."

I'm trying to work it out but I'm pretty new to C#.

Product Spotlight
Product Spotlight 





Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2014 ASPAlliance.com  |  Page Processed at 9/2/2014 10:07:33 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search