Paging through lots of data efficiently (and in an Ajax way) with ASP.NET 2.0

Features of the Sample

This sample is a self-contained ASP.NET application that demonstrates a few things:

- How to implement a data-result search page built with ASP.NET 2.0 and Atlas that can present hundreds of thousands of row results using the ASP.NET 2.0 GridView control. The results are formatted using a “paging” based UI model – where 15 results per page are displayed, and the user can skip from page to page to see their data. For kicks I also added support for editing and deleting each individual row.

- How to optimize the number of rows returned by the SQL Server to the middle-tier web-server. This sample demonstrates how a large query result (think 1000s, 10s of thousands or 100s of thousands of rows) can be paged efficiently so that only the 15 rows currently on display in a page are ever retrieved by the web-server from the SQL database (this is done using the ROW_NUMBER() function that David describes above, as well as the support for optimized paging provided by the GridView and ObjectDataSource controls). This avoids your web-server and SQL server grinding to a halt when you execute large queries, and makes for much more scalable performance.

- How to easily implement Ajax UI support for paging/editing/deleting on top of hundreds of thousands of rows of data (so no full page refreshes – instead it only updates the portion of the page that changes). This took me only 60 seconds to-do, and uses the same control support I talked about in this earlier blog post.

- How to easily implement Ajax UI support for adding “auto-suggest” behavior to controls like text-boxes. The December release of the Atlas Project provides a super-easy server control called the control that you can point at a TextBox, and that will then call a web-service to provide a list of suggestions when a browser user starts typing in the text-box. This sample demonstrates how to use this to auto-suggest items based on the contents in the database.

- How to implement a business class façade/wrapper around a data access layer. This is in turn used by the new ASP.NET 2.0 ObjectDataSource control for databinding scenarios. For this simple sample, I could have just used the data access layer built by using File->Add New Item->DataSet from the ObjectDataSource control directly (like I did with my earlier To-do List sample), and basically eliminate the need to write any code in the app. But I wanted to use this sample to help demonstrate how to build a richer business library layer abstraction that was separate from my data access layer. The business layer implementation in this sample is pretty trivial (and doesn’t really add much value), but it demonstrates a skeleton of how/where you could easily add business logic rules that were cleanly separated from your data layer.