Paging, Sorting, and Filtering Data with the ObjectDataSource Control

Paging, Sorting, and Filtering Data with the `ObjectDataSource` Control

The ObjectDataSource control provides you with two options for paging and sorting database data. You can take advantage of either user interface or data source paging and sorting. The first option is easy to configure and the second option has much better performance. In this section, you learn how to take advantage of both options.

You also learn how to take advantage of the ObjectDataSource control's support for filtering. When you combine filtering with caching, you can improve the performance of your data-driven web pages dramatically.

User Interface Paging

Imagine that you want to use a GridView control to display the results of a database query in multiple pages. The easiest way to do this is to take advantage of user interface paging.

For example, the page in Listing 15.16 uses a GridView and ObjectDataSource control to display the records from the Movies database table in multiple pages (see Figure 15.4).

Figure 15.4. Displaying multiple pages with user interface paging.

Listing 15.16. `ShowUIPaging.aspx`

<%@ Page Language="VB" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"   "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head  runat="server">     <style type="text/css">         .movies td,.movies th         {             padding:5px;         }     </style>     <title>Show User Interface Paging</title> </head> <body>     <form  runat="server">     <div>     <asp:GridView                  DataSource         AllowPaging="true"         PageSize="3"         Css         Runat="server" />     <asp:ObjectDataSource                  TypeName="MovieUIPaging"         SelectMethod="GetMoviesDataSet"         Runat="server" />     </div>     </form> </body> </html>

The GridView control in Listing 15.16 includes an AllowPaging property that is set to the value true. Setting this property enables user interface paging.

The ObjectDataSource control in Listing 15.16 represents the MovieUIPaging component in Listing 15.17. This component includes a GetMoviesDataSet() method that returns an ADO.NET DataSet object.

To take advantage of user interface paging, you must bind the GridView control to the right type of data source. The right type of data source includes a collection, a DataSet, a DataTable, and a DataView. The right type of data source does not include, for example, a DataReader.

Listing 15.17. `MovieUIPaging.vb`

Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Web.Configuration Public Class MovieUIPaging     Private ReadOnly _conString As String     Public Function GetMoviesDataSet() As DataSet         ' Create DataAdapter         Dim commandText As String = "SELECT Id,Title,Director FROM Movies"         Dim dad As SqlDataAdapter = New SqlDataAdapter(commandText, _conString)         ' Return DataSet         Dim dstMovies As DataSet = New DataSet()         Using dad             dad.Fill(dstMovies)         End Using         Return dstMovies     End Function     Public Sub New()         _conString = WebConfigurationManager.ConnectionStrings("Movies").ConnectionString     End Sub  End Class

User interface paging is convenient because you can enable it by setting a single property. However, there is a significant drawback to this type of paging. When user interface paging is enabled, all the movie records must be loaded into server memory. If the Movies database table contains 3 billion records, and you are displaying 3 records a page, then all 3 billion records must be loaded to display the 3 records. This places an incredible burden on both the web server and database server. In the next section, you learn how to use data source paging, which enables you to work efficiently with large sets of records.

Data Source Paging

Data source paging enables you to write custom logic for retrieving pages of database records. You can perform the paging in a component or you can perform the paging in a stored procedure.

If you want the best performance then you should write your paging logic in a stored procedure. That's the approach taken in this section.

The page in Listing 15.18 contains an ObjectDataSource control with data source paging enabled.

Listing 15.18. `ShowDSPaging.aspx`

<%@ Page Language="VB" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"    "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head  runat="server">     <style type="text/css">         .movies td,.movies th         {             padding:5px;         }     </style>     <title>Show Data Source Paging</title> </head> <body>     <form  runat="server">     <div>     <asp:GridView                  DataSource         AllowPaging="true"         PageSize="3"         Css         Runat="server" />     <asp:ObjectDataSource                  TypeName="MoviesDSPaging"         SelectMethod="GetMovies"         SelectCountMethod="GetMovieCount"         EnablePaging="True"         Runat="server" />     </div>     </form> </body> </html>

Notice that the ObjectDataSource control includes an EnablePaging property that has the value TRue. The ObjectDataSource also includes a SelectCountMethod property that represents the name of a method that retrieves a record count from the data source.

Notice, furthermore, that the GridView control includes both an AllowPaging and PageSize property. Even when using data source paging, you need to enable the AllowPaging property for the GridView so that the GridView can render its paging user interface.

When an ObjectDataSource control has its EnablePaging property set to the value true, the ObjectDataSource passes additional parameters when calling the method represented by its SelectMethod property. The two additional parameters are named StartRowIndex and MaximumRows.

The ObjectDataSource in Listing 15.18 represents a component named MoviesDSPaging. The control calls the component's GetMovies() and GetMovieCount() methods. The MoviesDSPaging component is contained in Listing 15.19.

Listing 15.19. `MoviesDSPaging.vb`

[View full width]

Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Web.Configuration Public Class MoviesDSPaging     Private Shared ReadOnly _conString As String     Public Shared Function GetMovies(ByVal startRowIndex As Integer, ByVal maximumRows As  Integer) As SqlDataReader         ' Initialize connection         Dim con As SqlConnection = New SqlConnection(_conString)         ' Initialize command         Dim cmd As SqlCommand = New SqlCommand()         cmd.Connection = con         cmd.CommandText = "GetPagedMovies"         cmd.CommandType = CommandType.StoredProcedure         ' Add ADO.NET parameters         cmd.Parameters.AddWithValue("@StartRowIndex", startRowIndex)         cmd.Parameters.AddWithValue("@MaximumRows", maximumRows)         ' Execute command         con.Open()         Return cmd.ExecuteReader(CommandBehavior.CloseConnection)     End Function     Public Shared Function GetMovieCount() As Integer         Dim context As HttpContext = HttpContext.Current         If context.Cache("MovieCount") Is Nothing Then             context.Cache("MovieCount") = GetMovieCountFromDB()         End If         Return CType(context.Cache("MovieCount"), Integer)     End Function     Private Shared Function GetMovieCountFromDB() As Integer         Dim result As Integer = 0         ' Initialize connection         Dim con As SqlConnection = New SqlConnection(_conString)         ' Initialize command         Dim cmd As SqlCommand = New SqlCommand()         cmd.Connection = con         cmd.CommandText = "SELECT Count(*) FROM Movies"         ' Execute command         Using con             con.Open()             result = CType(cmd.ExecuteScalar(), Integer)         End Using         Return result     End Function     Shared Sub New()         _conString = WebConfigurationManager.ConnectionStrings("Movies").ConnectionString     End Sub  End Class

To improve performance, the GetMovieCount() method attempts to retrieve the total count of movie records from the server cache. If the record count cannot be retrieved from the cache, the count is retrieved from the database.

The GetMovies() method calls a stored procedure named GetPagedMovies to retrieve a particular page of movies. The StartRowIndex and MaximumRows parameters are passed to the stored procedure. The GetPagedMovies stored procedure is contained in Listing 15.20.

Listing 15.20. `GetPagedMovies.sql`

CREATE PROCEDURE dbo.GetPagedMovies (     @StartRowIndex INT,     @MaximumRows INT ) AS -- Create a temp table to store the select results CREATE TABLE #PageIndex (     IndexId INT IDENTITY (1, 1) NOT NULL,     RecordId INT ) -- INSERT into the temp table INSERT INTO #PageIndex (RecordId) SELECT Id FROM Movies -- Get a page of movies SELECT     Id,     Title,     Director,     DateReleased FROM     Movies     INNER JOIN #PageIndex WITH (nolock)     ON Movies.Id = #PageIndex.RecordId WHERE     #PageIndex.IndexID > @startRowIndex     AND #PageIndex.IndexID < (@startRowIndex + @maximumRows + 1) ORDER BY     #PageIndex.IndexID

The GetPagedMovies stored procedure returns a particular page of database records. The stored procedure creates a temporary table named #PageIndex that contains two columns: an identity column and a column that contains the primary key values from the Movies database table. The temporary table fills in any holes in the primary key column that might result from deleting records.

Next, the stored procedure retrieves a certain range of records from the #PageIndex table and joins the results with the Movies database table. The end result is that only a single page of database records is returned.

When you open the page in Listing 15.18, the GridView displays its paging interface, which you can use to navigate between different pages of records (see Figure 15.5).

Figure 15.5. Displaying multiple pages with data source paging.

Note

The paging mechanism described in this section is based on the mechanism used by the Microsoft ASP.NET forums at http://www.asp.net/forums and the XBOX forums at http://www.xbox.com. Both of these websites handle an incredible number of message posts every day. The forums software was written with ASP.NET and it is available from TelligentSystems (www.telligentsystems.com) as part of their Community Server product.

If temporary tables make you anxious, you have an alternative when working with Microsoft SQL Server 2005. You can take advantage of the new ROW_NUMBER() function to select a range of rows. The ROW_NUMBER() function automatically calculates the sequential number of a row within a resultset.

The modified stored procedure in Listing 15.21 does the same thing as the stored procedure in Listing 15.20. However, the modified stored procedure avoids any temporary tables.

Listing 15.21. `GetPagedMovies2005.sql`

CREATE PROCEDURE dbo.GetPagedMovies2005 (         @StartRowIndex INT,         @MaximumRows INT ) AS WITH OrderedMovies AS ( SELECT         Id,         ROW_NUMBER() OVER (ORDER BY Id) AS RowNumber FROM Movies ) SELECT         OrderedMovies.RowNumber,         Movies.Id,         Movies.Title,         Movies.Director FROM         OrderedMovies         JOIN Movies         ON OrderedMovies.Id = Movies.Id WHERE         RowNumber BETWEEN (@StartRowIndex + 1) AND (@startRowIndex + @maximumRows + 1)

User Interface Sorting

If you need to sort the records displayed by the GridView control, then the easiest type of sorting to enable is user interface sorting. When you take advantage of user interface sorting, the records are sorted in the server's memory.

For example, the page in Listing 15.22 contains a GridView that has its AllowSorting property set to the value true. The GridView is bound to an ObjectDataSource that represents the Employees database table (see Figure 15.6).

Figure 15.6. Sorting records with user interface sorting.

Listing 15.22. `ShowUISorting.aspx`

<%@ Page Language="VB" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"    "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head  runat="server">     <title>Show User Interface Sorting</title> </head> <body>     <form  runat="server">     <div>     <asp:GridView                  DataSource         AllowSorting="True"         Runat="server" />     <asp:ObjectDataSource                  TypeName="EmployeesUISorting"         SelectMethod="GetEmployees"         Runat="server" />     </div>     </form> </body> </html>

The ObjectDataSource control in Listing 15.22 is bound to the component in Listing 15.23. Notice that the GetEmployees() method returns an ADO.NET DataSet object. When taking advantage of user interface sorting, the ObjectDataSource control must represent the right type of data source. The right type of data source includes a DataSet, a DataTable, a DataView, and a collection.

Listing 15.23. `EmployeesUISorting.vb`

Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Web.Configuration Public Class EmployeesUISorting     Private Shared ReadOnly _conString As String     Public Shared Function GetEmployees() As DataSet         ' Initialize ADO.NET objects         Dim selectText As String = "SELECT Id,FirstName,LastName,Phone FROM Employees"         Dim dad As New SqlDataAdapter(selectText, _conString)         Dim dstEmployees As New DataSet()         ' Fill the DataSet         Using dad             dad.Fill(dstEmployees)         End Using         Return dstEmployees     End Function     Shared Sub New()         _conString = WebConfigurationManager.ConnectionStrings("Employees").ConnectionString     End Sub  End Class

User interface sorting is convenient. You can enable this type of sorting by setting a single property of the GridView control. Unfortunately, just as with user interface paging, some serious performance drawbacks result from user interface sorting. All the records from the underlying database must be loaded and sorted in memory. This is a particular problem when you want to enable both sorting and paging at the same time. In the next section, you learn how to implement data source sorting, which avoids this performance issue.

Data Source Sorting

Imagine that you are working with a database table that contains 3 billion records and you want to enable users to both sort the records contained in this table and page through the records contained in this table. In that case, you'll want to implement both data source sorting and paging.

The page in Listing 15.24 contains a GridView and ObjectDataSource control. The GridView has both its AllowSorting and AllowPaging properties enabled (see Figure 15.7).

Figure 15.7. Paging and sorting database records.

Listing 15.24. `ShowDSSorting.aspx`

<%@ Page Language="VB" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"  "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head  runat="server">     <style type="text/css">     .employees td,.employees th     {         font:16px Georgia,Serif;         padding:5px;     }     a     {         color:blue;     }     </style>     <title>Show Data Source Sorting</title> </head> <body>     <form  runat="server">     <div>     <asp:GridView                  DataSource         AllowSorting="true"         AllowPaging="true"         PageSize="3"         Css         Runat="server" />     <asp:ObjectDataSource                  TypeName="EmployeesDSSorting"         SelectMethod="GetEmployees"         SelectCountMethod="GetEmployeeCount"         EnablePaging="true"         SortParameterName="sortExpression"         Runat="server" />     </div>     </form> </body> </html>

The ObjectDataSource control in Listing 15.24 represents the EmployeesDSSorting component in Listing 15.25. Notice that the ObjectDataSource control includes a SortParameterName property. When this property is present, the ObjectDataSource control uses data source sorting instead of user interface sorting.

Listing 15.25. `EmployeesDSSorting.vb`

[View full width]

Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Web.Configuration Public Class EmployeesDSSorting     Private Shared ReadOnly _conString As String     Public Shared Function GetEmployees(ByVal sortExpression As String, ByVal  startRowIndex As Integer, ByVal maximumRows As Integer) As SqlDataReader         ' Initialize connection         Dim con As New SqlConnection(_conString)         ' Initialize command         Dim cmd As New SqlCommand()         cmd.Connection = con         cmd.CommandText = "GetSortedEmployees"         cmd.CommandType = CommandType.StoredProcedure         ' Create parameters         cmd.Parameters.AddWithValue("@SortExpression", sortExpression)         cmd.Parameters.AddWithValue("@StartRowIndex", startRowIndex)         cmd.Parameters.AddWithValue("@MaximumRows", maximumRows)         ' Execute command         con.Open()         Return cmd.ExecuteReader(CommandBehavior.CloseConnection)     End Function     Public Shared Function GetEmployeeCount() As Integer         Dim context As HttpContext = HttpContext.Current         If context.Cache("EmployeeCount") Is Nothing Then             context.Cache("EmployeeCount") = GetEmployeeCountFromDB()         End If         Return CType(context.Cache("EmployeeCount"), Integer)     End Function     Private Shared Function GetEmployeeCountFromDB() As Integer         Dim result As Integer = 0         ' Initialize connection         Dim con As SqlConnection = New SqlConnection(_conString)         ' Initialize command         Dim cmd As SqlCommand = New SqlCommand()         cmd.Connection = con         cmd.CommandText = "SELECT Count(*) FROM Employees"         ' Execute command         Using con             con.Open()             result = CType(cmd.ExecuteScalar(), Integer)         End Using         Return result     End Function     Shared Sub New()         _conString = WebConfigurationManager.ConnectionStrings("Employees").ConnectionString     End Sub End Class

The GetEmployees() method in the component in Listing 15.25 calls a stored procedure to sort and page records. The stored procedure, named GetSortedEmployees, returns a sorted page of records from the Employees database table. This stored procedure is contained in Listing 15.26.

Listing 15.26. `GetSortedEmployees.sql`

CREATE PROCEDURE GetSortedEmployees (     @SortExpression NVarChar(100),     @StartRowIndex INT,     @MaximumRows INT ) AS -- Create a temp table to store the select results CREATE TABLE #PageIndex (     IndexId INT IDENTITY (1, 1) NOT NULL,     RecordId INT ) -- INSERT into the temp table INSERT INTO #PageIndex (RecordId) SELECT Id FROM Employees ORDER BY CASE WHEN @SortExpression='Id' THEN Id END ASC, CASE WHEN @SortExpression='Id DESC' THEN Id END DESC, CASE WHEN @SortExpression='FirstName' THEN FirstName END ASC, CASE WHEN @SortExpression='FirstName DESC' THEN FirstName END DESC, CASE WHEN @SortExpression='LastName' THEN LastName END ASC, CASE WHEN @SortExpression='LastName DESC' THEN LastName END DESC, CASE WHEN @SortExpression='Phone' THEN Phone END ASC, CASE WHEN @SortExpression='Phone DESC' THEN Phone END DESC -- Get a page of records SELECT     Id,     FirstName,     LastName,     Phone FROM     Employees     INNER JOIN #PageIndex WITH (nolock)     ON Employees.Id = #PageIndex.RecordId WHERE     #PageIndex.IndexID > @StartRowIndex     AND #PageIndex.IndexID < (@StartRowIndex + @MaximumRows + 1) ORDER BY     #PageIndex.IndexID

Notice that the stored procedure in Listing 15.26 uses SQL CASE functions to sort the records before they are added to the temporary table. Unfortunately, you can't use a parameter with an ORDER BY clause, so the sort columns must be hard-coded in the CASE functions. Next, a page of records is selected from the temporary table.

Filtering Data

You can supply the ObjectDataSource control with a filter expression. The filter expression is applied to the data returned by the control's select method. A filter is particularly useful when used in combination with caching. You can load all the data into the cache and then apply different filters to the cached data.

Note

You learn how to cache data with the ObjectDataSource control in Chapter 23, "Caching Application Pages and Data."

For example, the page in Listing 15.27 contains a DropDownList and GridView control. The DropDownList displays a list of movie categories, and the GridView displays matching movies (see Figure 15.8).

Figure 15.8. Filtering movies with the `ObjectDataSource` control.

Listing 15.27. `ShowFilteredMovies.aspx`

<%@ Page Language="VB" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"    "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head  runat="server">     <title>Show Filtered Movies</title> </head> <body>     <form  runat="server">     <div>     <asp:DropDownList                  DataSource         DataTextField="Name"         DataValueField="Id"         Runat="server" />     <asp:Button                  Text="Select"         Runat="server" />     <hr />     <asp:GridView                  DataSource         AutoGenerateColumns="false"         Runat="server">         <Columns>         <asp:BoundField             DataField="Title"             HeaderText="Movie Title" />         <asp:BoundField             DataField="Director"             HeaderText="Movie Director" />         </Columns>     </asp:GridView>     <asp:ObjectDataSource                  TypeName="FilterMovies"         SelectMethod="GetMovieCategories"         EnableCaching="true"         CacheDuration="Infinite"         Runat="server" />     <asp:ObjectDataSource                  TypeName="FilterMovies"         SelectMethod="GetMovies"         EnableCaching="true"         CacheDuration="Infinite"         FilterExpression="CategoryID={0}"         Runat="server">         <FilterParameters>         <asp:ControlParameter             Name="Category"             Control />         </FilterParameters>     </asp:ObjectDataSource>     </div>     </form> </body> </html>

Both ObjectDataSource controls in Listing 15.27 have caching enabled. Furthermore, the second ObjectDataSource control includes a FilterExpression property that filters the cached data, using the selected movie category from the DropDownList control.

Both ObjectDataSource controls represent the component in Listing 15.28.

Listing 15.28. `FilterMovies.vb`

Imports System Imports System.Web Imports System.Data Imports System.Data.SqlClient Imports System.Web.Configuration Public Class FilterMovies     Private ReadOnly _conString As String     Public Function GetMovies() As DataSet         ' Initialize DataAdapter         Dim commandText As String = "SELECT Title,Director,CategoryId FROM Movies"         Dim dad As SqlDataAdapter = New SqlDataAdapter(commandText, _conString)         ' Return DataSet         Dim dstMovies As New DataSet()         Using dad             dad.Fill(dstMovies)         End Using         Return dstMovies     End Function     Public Function GetMovieCategories() As DataSet         ' Initialize DataAdapter         Dim commandText As String = "SELECT Id,Name FROM MovieCategories"         Dim dad As New SqlDataAdapter(commandText, _conString)         ' Return DataSet         Dim dstCategories As New DataSet()         Using dad             dad.Fill(dstCategories)         End Using         Return dstCategories     End Function     Public Sub New()         _conString = WebConfigurationManager.ConnectionStrings("Movies").ConnectionString     End Sub  End Class

The ObjectDataSource enables you to filter data only when the data is represented by a DataSet, DataTable, or DataView object. This means that if you use filtering, the data must be returned as one of these objects.

Note

Behind the scenes, the ObjectDataSource control uses the DataView.RowFilter property to filter database rows. You can find detailed documentation on proper filter syntax by looking up the DataColumn.Expression property in the .NET Framework SDK 2.0 Documentation.

Paging, Sorting, and Filtering Data with the ObjectDataSource Control