Filtering Custom Paged ResultsBy Scott Mitchell
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
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!
|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
@DepartmentIDinput parameter and then use that parameter in the
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
@startRowIndexof 0. If we are displaying through 10 records per page, then when requesting the second page of data the
@startRowIndexparameter value will be 10.
@maximumRows- the number of records displayed per page.
GetEmployeesSubsetSortedbuilds up the
SELECTstatment in a string and then uses the
sp_executesqlstored procedure to dynamically execute the SQL string.
To update this stored procedure to include filtering support we need to add an additional input parameter -
in this case - and include it in the
WHERE clause. Rather than modify the
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:
In addition to accepting a
@DepartmentID parameter and adding a corresponding
note that the stored procedure starts by checking to see if
NULL and, if so,
it delegates the call to
GetEmployeesSubsetSorted, returning all employees regardless of their
@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
GetEmployeesRowCountthat used the following query:
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
Similar to the
GetEmployeesSubsetByDepartmentIDSorted stored procedure,
is designed to accept a
NULL value for its
@DepartmentID input parameter. If
NULL then the count logic is delegated to the
GetEmployeesRowCount stored procedure.
|Don't Forget the INDEX|
When a particular column appears in a |
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
App_Codefolder 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
GetEmployeesByDepartmentRowCount that calls the
GetEmployeesByDepartmentRowCount stored procedure
and is configured to return scalar data; and a second time to create a method named
that calls the
GetEmployeesSubsetByDepartmentIDSorted stored procedure and returns tabular data.
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
Departmentsdatabase table from a SqlDataSource. The additional "-- All Departments --" option is added by setting the DropDownList's
AppendDataBoundItemsproperty to True and then manually adding an
<asp:ListItem>as the following declarative markup shows:
Note that the "-- All Departments --"
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
SelectCountMethod. But this time they both expect a
DepartmentIDinput parameter. Therefore we need to specify this parameter through the ObjectDataSource's
<SelectParameters>collection like so:
That's all there is to it! When the page is visited the ObjectDataSource will call the Data Access Layer's
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
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
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):
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.