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