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

Published Saturday, January 7, 2006 7:12 PM by ScottGu

Comments

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

Sunday, January 8, 2006 5:13 AM by Marshall Brooke
Scott,

I Hope you keep these little tidbits coming, I'm finding them really usefull. I like the fact that you add in spurious ASP.NET v2.0 features along the way, and the more I read, the more it sinks in. I'm gradually "getting" the v2.0 mind set.

many thanks

ps Do you work weekdays as well?

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

Sunday, January 8, 2006 12:00 PM by Logic
Since you work for MS and considering that there ARE VB developers who use MS tools, I think you should consider posting both C# and VB versions of your tutorials. Your tutorials are very helpful ONLY to C# coders!
I will keep complaining till you do.
Btw: How about doing one on Gridview with Master details that is templated and USES A STORED PROC in EDIT mode? Stored Proc that uses Three tables for param input?

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

Sunday, January 8, 2006 2:23 PM by scottgu
Hi Marshall,

Cool -- glad they are proving useful! I will definitely keep them coming.

Thanks,

Scott

P.S. Yep -- I work all the weekdays too (I've just been on vacation the last two weeks -- which is why I've been posting a lot). :-)

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

Sunday, January 8, 2006 11:57 PM by scottgu
Hi Logic,

I am working on building a VB version of this sample that I hope to have done soon. I'm also going to do my next data blog post in VB as the default language -- so hopefully you'll see some good coverage of it in my blog going forward.

Hope this helps,

Scott

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

Monday, January 9, 2006 4:41 AM by Erwyn van der Meer
I have added input validation for the query string parameters that are specified declaratively in this sample. This prevents an exception from occurring when the parameters are "hacked" in the query string. Check out my blog entry. It only takes a couple of lines of extra code.

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

Monday, January 9, 2006 7:52 AM by scottgu
Hi Erwyn,

Cool stuff -- thanks for showing how to extend the sample and do that!

I didn't add parameter value validation to this particular sample since often the pattern ends up being to send the user to a clean error page as opposed to trying to fix it (since someone is trying to manually fake the parameters, sites like Amazon typically don't make the error message super friendly).

To create an error message you could do one of a few things:

1) Add a <customErrors> section to your web.config and point at an error page to display (this is typicaly the behavior of most site in cases like this).

Or:

2) You can check to see whether an error occured in retrieving the data from the datasource within the Selected event in the sample by checking whether e.Exeception is null, and if not do appropriate error message output based on it (for example: you could output an error specific message within the page itself). If you handle the exception yourself, you can set the e.ExceptionHandled property to true to avoid ASP.NET sending back the configured custom error handling page.

Hope this helps,

Scott

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

Monday, January 9, 2006 3:25 PM by Thomas G Sluder
Scott,

Great article. Just one question, I generally print out the .NET articles that I think would be a good reference (I know....I could just keep them in my favorites). Have you thought of publishing copies of your articles to MSDN or some other site that allows easy printing? The formatting when I try to print from weblogs.asp.net just doesn't look right.

Thanks,
Tom

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

Monday, January 9, 2006 8:03 PM by Steve
How do you deploy the mdf file to a ISP host? Is there any special security?

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

Monday, January 9, 2006 11:26 PM by scottgu
Hi Steve,

Typically when you deploy a .mdf file (a SQL Express database), you'll copy the file to the host, and then run an upsize tool to add it to the hoster's shared SQL Server box. We are coming out with a tool soon that hosters will be able to deploy to automate this process.

Hope this helps,

Scott

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

Monday, January 9, 2006 11:46 PM by scottgu
Hi Thomas,

I hope we might publish a few of these posts in the future on MSDN -- although right now I'm kind of making them up as I go along <g>. Apoligies for the formatting problems until then. :-(

Thanks,

Scott

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

Wednesday, January 11, 2006 12:47 AM by scottgu
Hi Roger,

There are two options you could consider:

1) You could put the .xsd file in a class library project and use that to provide a namespace.

or:

2) You can rename the .xsd file to have a namespace directly -- for example "MyNamespace.MyDalLayer.xsd". This will then cause the generated data layer classes to be prefixed under the "MyNamespace" namespace.

Hope this helps!

- Scott

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

Saturday, January 14, 2006 11:42 AM by Paolo
Hello, and (belated) happy New Year.

I am using Sql 2005, and have (don't really know why) removed SqlExpress. )I am using VS Professional 2005) I get the error, " The user instance login flag is not supported on this version of SQL Server "
Could you please suggest how to overcome this error? (Or provide a file to dupicate the database in Sql Server 2005?)
Thanks,

Paolo

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

Monday, January 16, 2006 3:32 AM by scottgu
Hi Paolo,

You should be able to edit the <connectionString> section within my web.config file to point at a regular SQL Server database for the data. I unfortunately don't have a .sql file handy that I can point at to create the database from scratch -- but you should be able to use the schema I outlined above to create it.

Hope this helps,

Scott

P.S. It is possible to have both SQL Express and SQL 2005 run side-by-side, so you might consider installing SQL Express again on the machine for sample purposes.

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

Sunday, January 22, 2006 9:50 AM by HA
What would you do if GetCategories would need to take some parameter?

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

Sunday, January 22, 2006 3:43 PM by Manolo
Dear Scott,

Sorry fot the late comment (i just can´t keep up wit blog reading).

Regarding the the t-sql code: Isn´t that select count just too much of a hit for large tables?

In many clients you are not allowed to do select counts. The only caveat is not knowing how many records you have and having a nice paging 1 of 20..).

But wouldn´t just a back next work fine? and this way not incurr in the select count.

Cheers,
Manuel

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

Monday, January 23, 2006 10:39 PM by scottgu
Hi Manolo,

If you don't want to have the total product count, then you could definitely just omit the select count and not return it from the SPROC (you could also then remove the code from the page to do the products 40-50 message).

Ultimately whether you want to-do it or not primarily depends on the user experience you want to have on the site -- and ultimately also what limitations (or not) you might have at the database layer.

Hope this helps,

Scott

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

Monday, January 23, 2006 10:45 PM by scottgu
Hi HA,

You could ceretainly pass parameters to the GetCategories method if you need to. There are two ways to-do this with the ObjectDataSource control:

1) Through a declarative parameter that you specify within the <asp:objectdatasource> tag. You can use this to bind to querystring params, form params, or other controls.

2) By setting code programmatically on the ObjectDataSource control -- either by manipulating its Select parameters collection, or by handling its Selecting event (which fires right before the GetCategories method is called).

Alternatively, if you don't want to use the ObjectDataSource control at all, you can just programmatically call the method and bind the results to the List.

Hope this helps,

Scott

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

Saturday, January 28, 2006 1:06 AM by vbdotnet2003coder
Hi scott,
I just need your view point on Datalist paging. Why can't we use PagedDatasource class and let the fremwork take control of all paging mechanism.Won't it be simple?

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

Monday, January 30, 2006 2:10 AM by scottgu
Hi Vbdotnet2003coder,

You could use the PagedDataSource for cases where you want to retrieve all the data and then do the paging at the middle-tier level. The downside is that if you have lots of data (for example: 100,000s of rows), then this is going to be very inefficient.

The above example does the paging logic in the database -- which means you only retrieve the 10 or so rows that you need. This will scale and perform much faster.

Hope this helps,

Scott

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

Tuesday, January 31, 2006 11:00 AM by vbdotnet2003coder
Hi Scott,
Thanx a lot for your explaination.I agree with your point on PagedDataSource and retrieveing huge records is really a problem if one uses PagedDatasource-which I'm facing right now ."Efficient Data Paging with the ASP.NET 2.0 DataList Control and ObjectDataSource" is really a great article and nice idea too.Thanks for sharing your thought with us.

Sincerely,
VbDotNet2003Coder.

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

Sunday, February 5, 2006 3:46 PM by zonker
Scott, this is a great post and I've been adapting the concept to work with Oracle data and the ObjectDataSource control. The problem that I'm running into is using this sort of paging in addition to sorting. I can't do the sorting on the returned data since certain records won't always belong on the current page depending on the sort expression/direction. Any suggestions for handling this as well?

BTW - just recently discovered your blog and I've been going through your archives...lotsa good stuff. Thanks very much for all the work you've done here. It's definitely been helpful to me and it's greatly appreciated!

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

Thursday, June 8, 2006 3:52 PM by Chintu
Are we trying to say that using PagedDatasource is recommended for small product sets?

I want to use an efficient method of implementing the above but I dont want to kill the system even though I know I will hardly have 5 pages of products.

Kindly advice.

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

Saturday, June 10, 2006 1:13 AM by ScottGu
Hi Chintu,

PagedDataSource is probably fine for cases where the total number of rows is less than 100.  For larger result-sets I'd recommend using the database paging technique above.

Hope this helps,

Scott

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

Wednesday, August 9, 2006 10:59 PM by robert
I have custom paging working perfectly in a Gridview, which is bound programmatically, except for the fact that the paging controls don't appear. I'm using only stored procedures wired up to a tableadapter wrapped by a business layer code class. The sprocs return the rowcount as an output parameter. The gridview select parameters (filters) are populated programmatically. The grid populates the first page perfectly with all variances of filters. The record count is queried and populated correctly. The gridview has AllowPaging="True" PageSize="20" and the objectdatasource has EnablePaging="True" MaximumRowsParameterName="maxRows" SelectCountMethod="GetRowCount". After 2 days of trying, I cannot make the paging controls appear.

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

Friday, August 11, 2006 2:46 AM by ScottGu

Hi Robert,

Unfortunately the way the GridView gets its total page count makes setting this programmatically harder than it should be.  Two suggestions I'd recommend looking at:

1) Add the paging using the ObjectDatasource control using the approach here: http://weblogs.asp.net/scottgu/archive/2006/01/01/434314.aspx.  This will handle it for you.

2) Consider adding your own custom paging controls to the page similar to how I did it with the DataList above.  You could use this to control the pageindex and programmatically populate the GridView yourself.

Hope this helps,

Scott

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

Thursday, September 14, 2006 9:10 AM by Bo Laumann
Hi Scott, You have saved my day (and project) - this is just what I needed. Thanks! Best Regards Bo Laumann

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

Wednesday, October 11, 2006 8:29 AM by Paul
Scott- Can custom paging be achieved (and use the standard gridview pager) if a Dataset is not used? I can configure custom paging if I use a Dataset in my ObjectDataSource, but if I use a custom class and a custom classCollection, my gridview header will appear, but no data and no pager control. Binding to the classCollection w/o paging enabled works fine. Dim sc As New SiteCollection() While dr.Read() Dim s As New Site s.Site_ID = dr("Site_ID") ... sc.Add(s) End While cn.Close()

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

Wednesday, October 11, 2006 11:59 PM by ScottGu

Hi Paul,

Yep - custom paging can be used without requiring a DataSet.  You still want your data code to only return the few records you need though - so I still recommend using the smart SQL paging support I describe above.

What you want to avoid is grabbing thousands of records and manually paging through them to just get the 10 or so you want.

Hope this helps,

Scott

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

Wednesday, October 18, 2006 5:32 AM by bharath reddy
Hi scott, I am workin on a datatable which has to display photo albums in two columns..I use VS 2005 and SQL 2005.I am unable to put paging for this datatable..when I downloaded ur application and tried to run I get the following error.Help ME!!!!and secondly I was unable to use ROw number function in SQL 2005..can u give me an example of how to use it..pls Error 1 The type or namespace name 'MyDalLayerTableAdapters;' could not be found (are you missing a using directive or an assembly reference?) F:\bharathprojects\datalistpaging\DataListPaging\Site.master.cs 11 7 F:\bharathprojects\datalistpaging\

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

Thursday, November 16, 2006 1:13 PM by Michaux Kelley

Hey Scott, this is an excellent tutorial/pattern thanks! Is there a way to get rid of the span tags that the flow layout produces, or replace them with floated divs?

My reasoning is to try and achieve XHTML compliant code where block-level elements aren't nested inside of inline elements like the span element.

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

Friday, November 17, 2006 1:47 AM by ScottGu

Hi Michaux,

The CSS Control Adapters for ASP.NET should help you remove the <span> tags: http://weblogs.asp.net/scottgu/archive/2006/10/28/CSS-Control-Adapters-Update-_2800_Beta3_2900_.aspx

Hope this helps,

Scott

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

Tuesday, November 21, 2006 5:29 AM by Tom

How could this be changed to work from a folder with images within it?

Nice tutorial by the way :)

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

Tuesday, November 21, 2006 11:12 AM by ScottGu

Hi Tom,

Yep - you could definitely modify this to work with a folder with images in.

Hope this helps,

Scott

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

Wednesday, November 22, 2006 10:00 AM by motaz

this is a great post ..

thanks a lot

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

Monday, December 4, 2006 3:12 AM by cs

very helpful tutorial. Does this paging support SELECT commands with sort according to ascending order?

cs

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

Monday, December 4, 2006 9:54 AM by ScottGu

Hi CS,

Yes -- you can use ascending or descending order for this and it should work just fine.

Thanks,

Scott

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

Tuesday, December 26, 2006 2:49 AM by john

how i can use this technique with access database

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

Wednesday, December 27, 2006 4:28 AM by A.R. Ashraf

HI, This is really a great article, i have used the stored procs for custom paging in tables. however i didn't know that how to use the same methodology with the views. can you please guide me how can we achieve custom paging in views which have no unique column via stored procs.

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

Friday, January 5, 2007 5:06 PM by XYZ

how i can use this technique with access database

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

Monday, January 8, 2007 7:57 PM by Jim

Scott,

Maybe I am missing something but won't this code @startRowIndex =(@PageIndex * @NumRows) +1 always make it start at a row higher than zero on the first pull?

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

Wednesday, January 10, 2007 2:22 AM by ScottGu

Hi Jim,

It has been awhile since I wrote this code - so don't remember the exact semantics 100%.  But I believe TSQL is 1 index based and not 0 index based - so row 1 is the first row and not the second one.

Thanks,

Scott

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

Friday, January 26, 2007 10:23 PM by Robert

Scott,

Great Article.  I have a Sql 2005 Stored Procedure that look exactly like your example.  I am using Enterprise Library 2.0.  I set the parameter value to out.  When I call the stored proc using EntLibrary SqlDatabase.ExecuteReader(command)

to return the @CategoryProductCount through  

the value returned is always "4"  No matter what.  I can execute the SP in a query window and it returns correct.  I've been beating my head trying to find out why is "4" always returned no matter what?  I do SqlDatabase.GetParameterValue(cmd, "@CategoryProductCount") to get the value.  Did I miss something?  

Again Great work and any hints you could provide me would be appreciated!!

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

Wednesday, January 31, 2007 7:11 AM by sengottuvelu

Scott,

 Nice one.Its very useful to me.

 can u give me idea of how to add first and last page link in the datagird.

one more thing , suppose if the page size is 10 means ,thn we can get oly ten records from database ,if u go second page thn it ll get next 10 records. at the first time itself dont get all records at a time.whr we ca nchange and get the data

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

Friday, February 2, 2007 11:44 AM by Scott R

Scott, this is great. Thanks.

Is there a way to get the paging more Search engine friend like that the one above in regards to gridview?

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

Wednesday, February 7, 2007 3:37 AM by San

Hi,

Sorry,it was a silly mistake in SQL from my side.Figured it out.

Thank you

San

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

Saturday, February 17, 2007 8:51 PM by ScottGu

Hi Andreas,

The DNS server that serves my site went down today, but should be back up in a few hours.  Once it is the link will work again.

Thanks,

Scott

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

Wednesday, February 28, 2007 9:47 AM by Brad

Scott,

I love this paging techinique.  But I am having trouble getting data to the UI.

I have built my SPROC, and it works great with the paging in it.  I have the XSD reading that SPROC, and it is getting the data.  When I call the TableAdapter from the UI, passing it proper data, I always get 0 rows back.  I run the same paramters through using the XSD 'Preview Data' functionality, and it works fine.  I modified a PROC that workd (no paging), and added the extra parameters needed to make the paging work, and that is the only difference between the two TableAdapters (old and new).  The UI works fine with the old TableAdapter.  What else can I look at?

Thanks,

Brad

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

Saturday, March 3, 2007 9:39 PM by ScottGu

Hi Brad,

That is pretty odd - can you try re-creating the TableAdapter?

Also - is the TableAdapter in the same project as the UI?  Could it be that it the web app is pointing to a different version of the database?

Thanks,

Scott

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

Sunday, March 4, 2007 7:34 PM by Michael Washington

If you don't unzip te datalistpaging.zip with the folders (so you get the app_code and app_data folders) you will get the namespace name 'MyDalLayerTableAdapters;' could not be found error

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

Thursday, March 15, 2007 12:59 PM by Harrinbw

Hey, Scott this seems like a great article but I'm having the exact same problem as Brad (posted Wednesday, February 28, 2007 9:47). All items on their own work great. The stored procedures return the proper data. The grid binds data when not using ObjectDataSource Paging and setting default values for the 2 select parameters. But as soon as I Set ODS paging to true and delete the 2 select parameters("maximumRow" and "startRowIndex") the Gridview Headers show but there are no rows after that. Any ideas of why this just doesn't work? My next step is to quit using ODS paging and just implement the Custom Paging in the GridView.