Efficient Data Paging with the ASP.NET 2.0 DataList Control and ObjectDataSource

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

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

How To Run and Use the Sample

To setup the sample, follow the below steps:

1) Have VS 2005 or Visual Web Developer and SQL Express installed.  Visual Web Developer and SQL Express are both free downloads and provide everything you need to build, debug and run the application.  You can download them from here.

2) Download this .zip file containing the sample.  Expand it into any directory you want.

3) Using Visual Web Developer or VS 2005, choose File->Open Web-site and point to the root directory of the sample sub-directory (the one with the files in it).

4) Select the “Products.aspx” file and hit run.

This will bring up a page like the screenshot above that lists products and allows you to page back and forth throughout a product category listing (note that the next/previous buttons will auto-hide if you are at the beginning or end of the listing).  Click on the left-hand side of the page to switch category listings.

To add a new product category, run the “AddData.aspx” page:

When you add a new product category, you can specify the number of products to add to it.  You can add dozens, hundreds, thousands or tens of thousands of products (the AddData.aspx page names the products sequentially for you automatically so you can simulate different sized loads).

Because the data paging implementation in the sample uses an intelligent paging algorithm that only pulls the needed page rows to the web-server (by default only 10 rows per web request), your server won’t start to run into performance issues even if you have a category or result with tens of thousands of product results.

How this Sample is Built

Here is what the solution directory looks like in Visual Web Developer:

It contains three pages – “Products.aspx”, “ProductDetails.aspx”, and “AddData.aspx” – that are each based on the “Site.master” master-page. 

The database is implemented in Products.mdf and contains 2 simple tables – “Products” and “Categories” (note: you can create new SQL Express databases by select File->Add New Item->Database File).  Here is what they look like in the database designer built-into the free Visual Web Developer IDE:

The Categories table contains a list of product categories, and the Products table contains a list of products contained within them. The ProductID column in the Products table is the primary key (and is automatically indexed), and an index has also been created on the CategoryId column (to create an index in the designer, just right click on a column and select “Indexes/Keys” to bring up the Index manager dialog).  These indexes are going to be important if we put 100,000s of products of entries into our Products table.

The database has one stored procedure called “GetProductsByCategoryId” that was created using the ROW_NUMBER() approach described in David’s blog to enable us to retrieve only the specific product data rows we want from the Products database (this means that we retrieve just the 10 or so products we need for the current page instead of the 1000 products that might be in the product category:

Notice that I’m also returning the total number of products in a category as well as the friendly name of the category as output parameters of the SPROC.  Originally I was fetching these with a separate database call, but to demonstrate how to implement a nice performance optimization I’m returning them along with our 10 rows of data as output parameters here.  The benefit of doing this optimization (versus a separate call to the database – which would actually be logically cleaner) is that it means you can retrieve all of the data you need with a single database query (which is also in turn only returning 10 rows of data).  For a high-volume page like this where performance matters, it is a performance optimization to seriously consider.

The data access layer was then implemented using a DataSet designer (choose File->Add New Item->DataSet to create one), which allows us to avoid having to write any manual ADO.NET data access code – and instead just use strongly-typed data classes that are generated and maintained in the project automatically by defining methods/relationships using the DataSet designer. 

The generated DataSet definitions for this sample are stored within the “MyDataLayer.xsd” file in the App_Code directory and contain two table-adapters (note: if you want to be advanced you can open and edit this XML file defining the DAL structure by right-clicking on it, choosing “Open With”, and selecting the XML source editor):

The GetProductsByCategoryId() method goes against the SPROC we defined earlier, but all other methods are normal SQL queries defined within the DAL layer.  Once defined using the DataSet designer, it is possible to write procedural code like the code below to invoke and use a method defined with our new DAL components:

        int categoryId = 0;

        int pageIndex = 0;

        int pageSize = 10;

        int numTotalProducts = 0;

        string categoryName = String.Empty;

        ProductsTableAdapter products = new ProductsTableAdapter();

        MyDalLayer.ProductsDataTable products = products.GetProductsByCategoryId( categoryId,

                                                                                  pageIndex,

                                                                                  pageSize,

                                                                                  ref categoryName,

                                                                                  ref numTotalProducts);

        foreach (MyDalLayer.ProductsRow product in products) {

            int productId = product.ProductId;

            string productDescription = product.Description;

            string productImage = product.ProductImage;

        }

The DAL components generated are strongly typed with both type and data relationship validation built-in.  You can add custom validation and/or code to the generated DAL components very cleanly via either code inheritance (subclass the DAL and override/add your own logic), or by adding a partial type to the project which will be compiled with the DAL (allowing you to avoid having to worry about the designer stomping on your code).  Here is an article that covers the DataSet designer in more detail. 

In my previous blog sample on efficient data paging using the GridView control, I showed how to optionally build a custom business façade that then wraps our generated DAL layer (which was built using the DataSet designer like above), and in turn provides another layer of isolation and separation.  For this sample, though, I’m just going to use the DAL directly. 

In terms of building the UI for the sample, the “Site.Master” page defines the outer “chrome” of all pages within the site, as well as the product listing on the left:

Within the Site.Master master page, I’m using an <asp:repeater> control to dynamically build the list of products and associated links (note the use of the new ASP.NET 2.0 Eval() data-binding syntax that provides a much terser way to evaluate data-binding expressions against the container parent):

    <div id="productmenu">

        <h5>Products</h5>

        <ul>

            <asp:repeater ID="ProductNav" DataSourceID="CategoryDataSource" runat="server">

                <ItemTemplate>

                    <li>

                        <a href="Products.aspx?categoryid=<%#Eval("CategoryId") %>"><%#Eval("Name")%></a>

                        (<%#Eval("ProductCount") %>)

                    </li>

                </ItemTemplate>

            </asp:repeater>

        </ul>

    </div>

    <asp:ObjectDataSource ID="CategoryDataSource"

                          TypeName="MyDalLayerTableAdapters.CategoriesTableAdapter"

                          SelectMethod="GetCategories"

                          runat="server" />

I’m using declarative data-binding with the new ASP.NET 2.0 ObjectDataSource control to bind the <asp:repeater> against the “MyDalLayerTableAdapters.CategoriesTableAdapter” class and its GetCategories() data method – which is one of the data classes defined and built for us as part of our DataSet designer based DAL.

The products.aspx page is then based on the Site.Master master page, and contains a <asp:DataList> control as well as some standard hyperlink html elements that we’ll use to programmatically control paging navigation:

And in source-view:

A few quick things to point out above: 1) the ItemTemplate within the DataList is using <divs> and CSS for styling and positioning, 2) Eval() supports an optional data-formatting syntax that I’m using to format the price of each product as currency, 3) I’ve disabled view-state for the page (since we don’t need it), and 4) the “tag-navigator” (bottom of the screen-shot) and tag-highlighting features in the free Visual Web Developer html source editor are pretty convenient to identify where your cursor is within the document (they dynamically update as you move the cursor around the html source).

As you can see above, I’m using declarative data-binding with the ObjectDataSource control for this DataList control as well (alternatively I could have just written procedural code to invoke the ProductAdapter, set the datasource, and call databind on the control).  There are a couple of benefits to doing this the ObjectDataSource way – one is that it handles when to grab the data in the page lifecycle automatically.  Another is that the WYSIWYG page designer will offer to automatically generate default data templates for you within the DataList above when you wire one up to the ObjectDataSource (it will reflect on the data from the returned method and generate a default template based on it that you can then easily edit).  The last is that you can declaratively bind parameter values from other controls, querystring/form values, and the new ASP.NET Profile object – all without having to write any code yourself.  You can see this in action with this last point – where I’m declaratively specifying that the CategoryId and PageIndex values should be pulled from the QueryString (and a value of “0” should be used if it isn’t present). 

The above markup is actually all we need in our page to bind to our DAL, retrieve 10 rows of data, and generate pretty output containing the results.  If you ran a page with just this, you’d be able to page back and forth through the product listing data by manually adding a “CategoryId” and “PageIndex” value to the querystring of the page (for example: Products.aspx?CategoryId=0&PageIndex=2). 

Rather than force people to manually do this, though, obviously what we want to-do instead is provide some built-in navigation UI to enable this.  To accomplish this, I added a <div> section at the bottom of the page with some hyperlinks that we’ll use to page back and forth, as well as a label that I can use to output where the user currently is (specifically text that says something like: “1-10 of 56 Products”).

    <div id="Navigation" class="navigation" runat="server">

        <div id="leftnav">

            &nbsp;

            <a id="PreviousPageNav" runat="server"> &lt;&lt; Previous Products</a>

        </div>

        <div id="rightnav">

            &nbsp;

            <a id="NextPageNav" runat="server">More Products &gt;&gt;</a>

        </div>

        <div id="numnav">

            <asp:Label ID="PagerLocation" runat="server" />

        </div>

    </div>

Note the use of standard hyperlinks above.  They have a runat=”server” attribute on them so that I can program against them on the server.  I chose to implement the paging semantics within this sample using standard HTTP GET requests for everything – instead of using post-backs.  Doing the navigation via post-backs would have been easier, but I wanted to enable users to easily bookmark pages (which will automatically persist the querystring values for me), as well as to enable cross-linking from things like search engines.

To dynamically update the hyperlink values (as well as other elements of the page), I added an event-handler to the ObjectDataSource so that I’ll be called after it has fetched the data from our DAL (specifically: I’m using the “selected” event – I would have used the “selecting” event if I wanted to inject code immediately before the DAL was called):

    protected void ProductDataSource_Selected(object sender, ObjectDataSourceStatusEventArgs e) {

        // Retrieve output parameter values returned from the "GetProductsByCategoryId"

        // method invoked by the ObjectDataSource control on the ProductsTableAdapter class

        int productCount = (int) e.OutputParameters["CategoryProductCount"];

        string categoryName = (string)e.OutputParameters["CategoryName"];

        // Retrieve pageIndex and categoryId from querystring, pageSize pulled from ObjectDataSource

        int pageIndex = Convert.ToInt32(Request.QueryString["pageIndex"]);

        int categoryId = Convert.ToInt32(Request.QueryString["categoryid"]);

        int pageSize = Int32.Parse(ProductDataSource.SelectParameters["NumRows"].DefaultValue);

        // Update various page elements with data values

        UpdateTitles(categoryName);

        UpdatePagerLocation(pageIndex, pageSize, productCount);

        UpdateNextPrevLinks(categoryId, pageIndex, pageSize, productCount);

    }

Notice above that I am using the event argument (specifically its OutputParameters collection) to retrieve the output parameter results from the DAL method (e.ReturnValue provides access to the return value of the method). 

I’m retrieving other HTTP GET parameters from the Request.QueryString collection.  I’m using Convert.ToInt32() to convert them to integers instead of Int32.Parse() because Convert.ToInt32() will return a 0 value instead of throwing if the querystring isn’t specified (and so it saves me having to-do a null check).

At the end of the event, I then call three helper methods that I’m using the update the page contents with the various data results.  The last two are used to customize the html navigation <div> above.  Specifically, the “UpdatePagerLocation” method emits the location text (“1-10 of 44 Products”):

    void UpdatePagerLocation(int pageIndex, int pageSize, int productCount) {

        int currentStartRow = (pageIndex * pageSize) + 1;

        int currentEndRow = (pageIndex * pageSize) + pageSize;

        if (currentEndRow > productCount)

            currentEndRow = productCount;

        PagerLocation.Text = currentStartRow + "-" + currentEndRow + " of " + productCount + " products";

    }

And then the “UpdateNextPrevLinks” just updates and auto shows/hides the <a> elements depending on whether we are at the beginning or end of the product listing (note: we are using the CategoryProductCount that was returned as an output parameter to calculate the total number of products in the category):

    void UpdateNextPrevLinks(int categoryId, int pageIndex, int pageSize, int productCount) {

        string navigationFormat = "products.aspx?categoryId={0}&pageIndex={1}";

        PreviousPageNav.HRef = String.Format(navigationFormat, categoryId, pageIndex - 1);

        PreviousPageNav.Visible = (pageIndex > 0) ? true : false;

        NextPageNav.HRef = String.Format(navigationFormat, categoryId, pageIndex + 1);

        NextPageNav.Visible = (pageIndex + 1) * pageSize < productCount ? true : false;

    }

Last but not least, I have a simple method that updates the Page’s title element (using the new ASP.NET 2.0 Page.Title property), as well as a <h1> header at the top of the page:

    void UpdateTitles(string title) {

        ProductHeader.Text = title;

        Page.Title = "Products: " + title;

    }

And that is all the code there is to the sample….

One thing to play with on the ObjectDataSource is to adjust the “NumRows” parameter value. 

For example, if you changed this to “4” instead of “10” (which is what the sample ships with), you’d get 4 rows of products per page:

No additional code changes are required to enable this – just change the value in one place and you are good to go (no DAL, code-behind or other changes needed). 

You can also experiment with the number of columns rendered by the DataList – try changing the “RepeatColumns” property on it to 1 or 3 to see a different layout.

Summary

Because we only retrieve and return the rows needed for the current page of rendering from the database, and because we only use a single database

call to retrieve all of the data for the product-listing page, the execution of the page should be very, very fast and scalable (even when you have thousands

of results).  As you can see above, the code to implement this is pretty small and clean. 

Because we are using standard navigational HTTP GET requests everywhere in the sample, no client-script is needed or emitted on the page (do a view-source

in the browser and you’ll notice there isn’t a single line of javascript anywhere on the page).  All markup in the sample is also XHTML compliant and cross-browser.

I used FireFox for all the screen-shots above – but obviously it also works with IE.

Hope this helps,

Scott