Filtering Custom Paged Results
By Scott Mitchell
Introduction
The ASP.NET 2.0 data web controls - the GridView, DetailsView, and FormView - all provide built-in paging support that can be enabled at the tick of a checkbox. Unfortunately, this built-in paging support is very inefficient when paging through large amounts of data since it naively grabs all of the records from the data being paged through even though only a subset of the records are displayed. Consequently, when paging through several hundred or thousands of records, it behooves you to implement custom paging. With custom paging, the data Web control hands over its paging responsibilities to us, the page developer. We are tasked with efficiently grabbing the precise subset of records to display and providing this information to the data Web control.
Previous articles here on 4Guys have looked at implementing custom paging in ASP.NET 2.0. In
Custom Paging in ASP.NET 2.0 with SQL Server 2005 we
looked at using a GridView, a Typed
DataSets, an ObjectDataSource control, and
SQL Server 2005's new ROW_NUMBER()
keyword
to efficiently page through 50,000 records 10 at a time. Another article, Sorting
Custom Paged Results, looked at a technique for efficiently sorting the custom paged results.
In addition to paging and sorting, another common data request is filtering. When using the inefficient default paging, filtering is easy to implement but includes the same inherent inefficiencies: the data Web control retrieves all of the filtered records even though only a subset of them are displayed on each page. It is possible to implement filtering with custom paging and sorting, however. In this article we will look at extending the custom paging and sorting example - which currently pages and sorts through the 50,000 employees in a fictional company - to include the ability to filter the displayed employees by their department. Read on to learn more!
Before tackling this article make sure you have read and worked through Custom Paging in ASP.NET 2.0 with SQL Server 2005 and Sorting Custom Paged Results...
Try It Out! |
---|
The stored procedures, Typed DataSet, and ASP.NET pages discussed in this article are all available in an easy to use demo that is available at the end of this article. |
Efficiently Retrieving the Precise Subset of Records Filtered by Department
To implement custom paging we have to efficiently retrieve the precise set of records to display. In Custom Paging in ASP.NET 2.0 with SQL Server 2005 and Sorting Custom Paged Results we created a stored procedure that used SQL Server 2005's
ROW_NUMBER()
keyword to efficiently grab a particular subset of records.
To add the ability to filter by department we simply need to update the stored procedure to accept a @DepartmentID
input parameter and then use that parameter in the WHERE
clause.
The stored procedure used in Sorting Custom Paged Results was named GetEmployeesSubsetSorted
and accepted
three input parameters:
@sortExpression
- the expression by which to sort the data, such as: "EmployeeID DESC" or "Salary ASC".@startRowIndex
- the index of the first row requested by the data Web control. For example, when the data Web control wants to retrieve the first page of data it will pass in a@startRowIndex
of 0. If we are displaying through 10 records per page, then when requesting the second page of data the@startRowIndex
parameter value will be 10.@maximumRows
- the number of records displayed per page.
GetEmployeesSubsetSorted
builds up the SELECT
statment in a string and then uses
the sp_executesql
stored procedure to dynamically execute the SQL string.
To update this stored procedure to include filtering support we need to add an additional input parameter - @DepartmentID
,
in this case - and include it in the WHERE
clause. Rather than modify the GetEmployeesSubsetSorted
I decided to create a new stored procedure named GetEmployeesSubsetByDepartmentIDSorted
that meets these
requirements. I've bolded the parts that differ from the GetEmployeesSubsetSorted
stored procedure:
ALTER PROCEDURE dbo.GetEmployeesSubsetByDepartmentIDSorted
|
In addition to accepting a @DepartmentID
parameter and adding a corresponding WHERE
clause,
note that the stored procedure starts by checking to see if @DepartmentID
is NULL
and, if so,
it delegates the call to GetEmployeesSubsetSorted
, returning all employees regardless of their
department. If @DepartmentID
is not NULL
then the stored procedure builds up the dynamic
SQL string with the @DepartmentID
parameter in the WHERE
clause, thereby returning only those
employees in the specified department. The NULL
check allows for a user interface where the visitor can opt to
view all employees or narrow down the results to a specific department.
Determining the Number of Filtered Records
As discussed in Custom Paging in ASP.NET 2.0 with SQL Server 2005, when implementing custom paging we must provide a method that, when invoked, returns the precise number of records being paged through. This information is used by the data Web control in rendering the paging interface. This was accomplished in the previous articles through a stored procedure named
GetEmployeesRowCount
that used the following query:
SELECT COUNT(*)
|
We need to create a new stored procedure that accepts a @DepartmentID
as input and returns the number of employees
that work for that department. I created sich a stored procedure and named it GetEmployeesByDepartmentRowCount
.
Similar to the GetEmployeesSubsetByDepartmentIDSorted
stored procedure, GetEmployeesByDepartmentRowCount
is designed to accept a NULL
value for its @DepartmentID
input parameter. If @DepartmentID
is NULL
then the count logic is delegated to the GetEmployeesRowCount
stored procedure.
ALTER PROCEDURE dbo.GetEmployeesByDepartmentRowCount
|
Don't Forget the INDEX |
---|
When a particular column appears in a WHERE clause it is usually a good idea to add an INDEX for performance
reasons, especially when working with large amounts of data where the INDEX is fairly selective and access to the table is
read-dominated. This is the case for this demo and, therefore, I have added an INDEX on Employees.DepartmentID .
|
Updating the Data Access Layer
With these two new stored procedures created, we need to update the Data Access Layer to include methods that invoke these stored procedures. The demo uses Typed DataSets for its Data Access Layer. Namely, there is a Typed DataSet named
Employees
in the App_Code
folder that has a single DataTable/TableAdapter. We need to add two new methods to the TableAdapter that, when invoked,
will call the appropriate stored procedure.
To add a method to the TableAdapter, right-click on the TableAdapter in the DataSet Designer and choose Add Query from
the context menu. Choose to "Use exsiting stored procedure", select the stored procedure to invoke, indicate the type of
data it returns, and then provide a name for the TableAdapter's method. Perform these steps twice: once to create a method
named GetEmployeesByDepartmentRowCount
that calls the GetEmployeesByDepartmentRowCount
stored procedure
and is configured to return scalar data; and a second time to create a method named GetEmployeesSubsetByDepartmentIDSorted
that calls the GetEmployeesSubsetByDepartmentIDSorted
stored procedure and returns tabular data.
For a detailed discussion on creating and working with Typed DataSets, see Creating a Data Access Layer (VB) (C#).
Creating the Filtering User Interface
The demo available at the end of this article uses a DropDownList to list all of the departments as well as add an "-- All Departments --" option. The data is retrieved from the
Departments
database table from a SqlDataSource. The additional "-- All Departments --"
option is added by setting the DropDownList's AppendDataBoundItems
property to True and then manually adding
an <asp:ListItem>
as the following declarative markup shows:
<strong>Filter by Department:</strong>
|
Note that the "-- All Departments --" <asp:ListItem>
's Value
property is manually set to an
empty string (Value=""
). This has the side-effect of using a database NULL
value when the "-- All Departments --"
option is selected. Note that if you omit the Value=""
and instead have
<asp:ListItem>-- All Departments --</asp:ListItem>
, the Value
of the ListItem will be
the same as its Text
("-- All Departments --").
Configuring the ObjectDataSource
Lastly we need to configure the GridView's ObjectDataSource so that it uses custom paging rather than default paging. In Custom Paging in ASP.NET 2.0 with SQL Server 2005 and Sorting Custom Paged Results we didn't have any parameters to pass into the specified
SelectMethod
or SelectCountMethod
. But this time they
both expect a DepartmentID
input parameter. Therefore we need to specify this parameter through the
ObjectDataSource's <SelectParameters>
collection like so:
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
|
That's all there is to it! When the page is visited the ObjectDataSource will call the Data Access Layer's
GetEmployeesSubsetByDepartmentIDSorted
and GetEmployeesByDepartmentRowCount
methods to retrieve
the set of records to display and the total number of records being paged through. The ObjectDataSource passes the
GetEmployeesSubsetByDepartmentIDSorted
method the currently selected department from the Departments
DropDownList, the GridView's sort expression, the current page index, and the maximum rows to retrieve. This information is
used to efficiently grab and return the appropriate, precise subset of data.
When the visitor moves to another page, sorts the grid, or selects a different department, a postback occurs, the custom
paging methods are requeried, and the new data is displayed.
All Employees are Displayed

Only Employees in Marketing are Displayed

There is one final enhancement worth making. When the user selects a different department from the DropDownList the grid remains
on the same page as before and the sort expression is left unchanged. This can have undesired effects if the user is viewing,
say, page 20 of the employees in the IT department, but then switches to the Marketing department where there are only 15 pages of
data. In such a scenario the grid will not be displayed because no records will be returned by the GetEmployeesSubsetByDepartmentIDSorted
stored procedure.
We can fix this by creating an event handler for the DropDownList's SelectedIndexChanged
event. In that event
handler we can simply set the GridView's PageIndex
property back to the first page and then resort the grid
by the default sorting value (EmployeeID
in ascending order):
Protected Sub Departments_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles Departments.SelectedIndexChanged
|
Conclusion
By default, ASP.NET's data Web controls implement paging in a rather naive way: they retrieve all of the records being paged through even though only a subset will be displayed. This approach is untenable when paging through large amounts of data and must be replaced by custom paging. Custom paging involves a bit more effort from the page developer, but as we've seen in this article and previous ones, it is still possible to sort and filter custom paged data. Download the demo at the end of this article to see the difference in performance times when paging through 50,000 records using default vs. custom paging, as well as the techniques used for sorting and filtering custom paged data.
Happy Programming!
Attachments
Further Readings