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...

- continued -

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.

Recall that the 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 ( @DepartmentID int, @sortExpression nvarchar(50), @startRowIndex int, @maximumRows int ) AS IF @DepartmentID IS NULL -- If @DepartmentID is null, then we want to get all employees EXEC dbo.GetEmployeesSubsetSorted @sortExpression, @startRowIndex, @maximumRows ELSE BEGIN -- Otherwise we want to get just those employees in the specified department IF LEN(@sortExpression) = 0 SET @sortExpression = 'EmployeeID' -- Since @startRowIndex is zero-based in the data Web control, but one-based w/ROW_NUMBER(), increment SET @startRowIndex = @startRowIndex + 1 -- Issue query DECLARE @sql nvarchar(4000) SET @sql = 'SELECT EmployeeID, LastName, FirstName, DepartmentID, Salary, HireDate, DepartmentName FROM (SELECT EmployeeID, LastName, FirstName, e.DepartmentID, Salary, HireDate, d.Name as DepartmentName, ROW_NUMBER() OVER(ORDER BY ' + @sortExpression + ') as RowNum FROM Employees e INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID WHERE e.DepartmentID = ' + CONVERT(nvarchar(10), @DepartmentID) + ' ) as EmpInfo WHERE RowNum BETWEEN ' + CONVERT(nvarchar(10), @startRowIndex) + ' AND (' + CONVERT(nvarchar(10), @startRowIndex) + ' + ' + CONVERT(nvarchar(10), @maximumRows) + ') - 1' -- Execute the SQL query EXEC sp_executesql @sql END

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(*) FROM Employees

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 ( @DepartmentID int ) AS IF @DepartmentID IS NULL -- Return the TOTAL number of employees EXEC GetEmployeesRowCount ELSE -- Return the number of employees in a specific department SELECT COUNT(*) FROM Employees WHERE DepartmentID = @DepartmentID

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> <asp:DropDownList ID="Departments" runat="server" AppendDataBoundItems="True" AutoPostBack="True" DataSourceID="DepartmentsDataSource" DataTextField="Name" DataValueField="DepartmentID"> <asp:ListItem Value="">-- All Departments --</asp:ListItem> </asp:DropDownList> <asp:SqlDataSource ID="DepartmentsDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:PagingDataConnectionString %>" SelectCommand="SELECT [DepartmentID], [Name] FROM [Departments] ORDER BY [Name]"> </asp:SqlDataSource>

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" SelectMethod="GetEmployeesSubsetByDepartmentIDSorted" TypeName="EmployeesTableAdapters.EmployeesTableAdapter" EnablePaging="True" SelectCountMethod="GetEmployeesByDepartmentRowCount" SortParameterName="sortExpression"> <SelectParameters> <asp:ControlParameter ControlID="Departments" Name="DepartmentID" PropertyName="SelectedValue" Type="Int32" /> </SelectParameters> </asp:ObjectDataSource>

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 'Reset the Grid's page index to 0 and SortExpression to "EmployeeID" GridView1.PageIndex = 0 GridView1.Sort("EmployeeID", SortDirection.Ascending) End Sub

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!

By Scott Mitchell

Attachments

Download the code used in this article