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