Paging through lots of data efficiently (and in an Ajax way) with ASP.NET 2.0
David Hayden blogged about a cool new ROW_NUMBER() function that SQL 2005 provides that got me excited and inspired to write a little code last night.
You can download the sample app I built here.
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 <atlas:updatepanel> 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 <atlas:autocompleteextender> 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.
How To Run and Use the Application
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 “PagedData.aspx” file and hit run.
This will bring up a page that looks like this:
This allows you to query for lists by categories in your database. The default database in the sample doesn’t have any list items, so first we’ll want to create some. To-do this click the “Bulk Add” link.
Create a category called “test” and add 100 items to it. This will create a bunch of sample data in the database that will help simulate paging. Then repeat this with a new category called “test2” and add 10,000 items to it. Then repeat this with a new category called “test3” and add 100,000 items to it. Note that this last category will probably take a few minutes to create (since the logic in my app is simple and just adds them one row at a time).
Then click on the “Return to Paged Data Page” link and start typing a search string in the textbox. Pause slightly after you type “tes” – and then notice how the auto-suggest box will list the three new categories you added:
These are being populated using the <atlas:autocompleteextender> server control (which is hitting a web-service on the server to lookup the available categories – and so will return the three new ones we just created).
Search using “test3” (the one with 100,000 items), and you’ll see this paged view of 100,000 items in the GridView:
Each page of the screen contains 15 items from the database. You can switch around to see the different pages. Click the “…” link to go to pages 11-20. Click “Last” to jump to the final page of data:
Note that as you choose the different pages, the only thing that gets updated is the grid of data – the overall page does not refresh. This is because the GridView control is wrapped using the <atlas:updatepanel> control which allows incremental
Page back a few screens, and then click “edit” on one of the rows. Note that you can now edit and update its items. You can also click “delete” on a row to remove one. Both operations occur in an
How is this Application Built
Here is what the solution directory looks like in Visual Web Developer:
It contains two pages – PagedData.aspx and AddData.aspx – that are each based on the Site.master master-page. It also contains one web-service – categories.asmx – which is used by the <atlas:autocompleteextender> control.
The database is implemented in PagingSample.mdf and contains 1 simple table called “Lists” (note: you can create new SQL Express databases by select File->Add New Item->Database File):
ListId is the primary key, and the Category column has been marked to be indexed (note: this is important since we’ll be creating 100,000+ rows). To set indexes, right click on a column in the table designer and choose “Indexs/Keys” and make sure that “Categories” is being indexed.
The database has one stored procedure called “GetPagedLists” that was created using the ROW_NUMBER() approach described in David’s blog:
Note: you can double-click on the sproc name in the solution explorer to open and edit it.
The data access layer was implemented using a DataSet component (choose File->Add New Item->DataSet to create one). It is defined declaratively within the “MyDataLayer.xsd” file and contains two table-adapters:
GetPagedListData goes against the SPOC above, but all other methods are normal SQL queries (note: the GetListCountByCategory method is defined as: SELECT COUNT(*) FROM Lists where category=@category and returns a scalar integer value).
I could have just used the DAL layer directly from the UI tier, but instead chose to wrap the DAL with a business layer façade that looks like this:
The two significant methods for our paging sample are “GetListCountByCategory” and “GetListsByCategory”. Here are the simple implementations of them in our business façade layer (note: right now they just thinly wrap the data access layer and expose the row data as custom “ListItem” types – so they don’t provide much value over calling the DAL directly, but do demonstrate how you could add your own custom logic around it):
Databinding a paged GridView control to this middle-tier object is easy (and automated if you are using the WYSIWYG designer in Visual Web Developer). Just set the “AllowPaging=true” property on the GridView and set the PageSize value you want (for this sample I am using 15 rows per page). Then point the GridView at an ObjectDataSource control, which is configured to use the ListManager business façade class. It has the “AllowPaging” attribute set to true, and has the “SelectMethod” and “SelectCount” properties pointing to the “GetLisByCategory” and “GetListCountByCategory” methods above.
Now, when you run the application, the GridView binds against the ObjectDataSource which will invoke the “GetListCountByCategory” method on our “ListManager” business façade class to calculate the total number of rows to page, and then invoke the “GetListByCategory” method passing the appropriate “category” parameter along with the “startRowIndex” and “maximumRows” parameter values that correspond to the page index that the GridView is currently on.
The beauty is that because we only retrieve and return the 15 rows of data we actually need from the database as part of this operation, the result is fast and scalable. As a browser user pages back and forth on the data within the GridView across the 100,000 rows of data, all UI paging logic and update/edit semantics are handled for us.
The last little implementation note to call out is the use of the <atlas:AutoCompleteExtender> control to auto-suggest items within the Category TextBox. This is a new control provided by the Atlas December drop, and is simple to implement. Basically, you just add the control to your page and point at the TextBox you want to complete, along with a web-service URL and method to call when the browser user types three letters in the TextBox -- these are then passed to the web-service, which can provide a list of suggestions back:
The implementation of the web-service method then looks like this within this sample:
Hope this helps and proves to be a useful sample. Thanks again to David for the blog post on the new ROW_NUMBER() function in SQL 2005 that makes this much easier to implement.
Hope this helps – and happy new year!
Scott
P.S. Note that with SQL 2000 you can use the same approach as above, although the SPROC ends up being slightly more complicated.