Here is what the solution directory looks like in Visual Web
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>
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
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
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
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
Hope this helps – and happy new year!
P.S. Note that with SQL 2000 you can use the same approach
as above, although the SPROC ends up being slightly more complicated.