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

Figure 7

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

Figure 8

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:

Figure 9

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:

Figure 10

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:

Figure 11

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

Figure 12

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. 

Figure 13

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:

Figure 14

The implementation of the web-service method then looks like this within this sample:

Figure 15

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.