Providing Sorting and Pagination Support in a Single DataGrid
| When building a DataGrid that supports multiple features, I find that it helps to start by implementing one feature completely before beginning to tackle the next one. This approach works well when implementing features that are not dependent on one another. However, in the previous section I forewarned that the sorting, paging, and data modification functionalities were not independent you cannot simply add one feature on top of the other without any changes and expect it to work. Given this, you might be quite perplexed as to why I am planning to tackle these features one at a time. The reason is because these features, while somewhat dependent, are more independent than not. Furthermore, it has been my personal experience that in real-world settings, you might be asked to implement a particular feature (say, pagination support), and it's not until after you complete the task that you are requested to add another, potentially dependent feature (such as sorting). Therefore, I think it will be worthwhile to approach this problem in this manner. Having said that, let's start by creating a DataGrid that will display the contents of the Comment table and provide pagination support. (Recall that we examined the Comments database table in Chapter 8, "Providing DataGrid Pagination," and Chapter 9, "Editing the DataGrid Web Control.") As we saw in Chapter 8, you can provide pagination support either through the DataGrid's default paging or through custom paging. With default paging, the complete data that is to be paged through must be loaded into an object that implements the ICollection interface, such as the DataSet. When the DataGrid is rendered, only a portion of this data is displayed. The portion being displayed is dependent upon what page of data the user is viewing and how many records per page are being displayed. The downside of default paging is that each time the user steps through a page of data, the entire contents of the data being paged through must be retrieved from the database. Custom paging, on the other hand, circumvents this extraneous data retrieval by allowing only those specific records to be displayed on the current DataGrid page to be retrieved from the database. As we saw in Chapter 8, retrieving the correct subset of rows from the database can be a bit tricky. One approach is to implement a stored procedure that builds a temporary table and completely copies the data being paged through into this temporary table. Then it can selectively pick out the rows that the DataGrid needs to display, and send only these rows to the ASP.NET Web page. If the data being displayed has an auto-incremented primary key value, the primary key method can be employed to provide pagination. Intuitively, you might opt to provide pagination support using the primary key method, as we did in the "Retrieving Only the Needed Records Using Primary Key Values" section in Chapter 8. This method is the most efficient in terms of retrieving only the records needed and not succumbing to unneeded copying, as with the stored procedure method. Unfortunately, the primary key method cannot be used when we want to include sorting support as well. If you were to do so, to also add sorting support for the DataGrid, you would need to redo the paging, either using the stored procedure method or the DataGrid's default paging. The custom paging primary key method will not work with sorting because the data must be displayed in order of its primary key values for the primary key approach to work. That is, if we want to provide pagination for data that has an integer, auto-increment, primary key field, the data must be displayed in order of either increasing or decreasing primary key values. Recall that when using the primary key method, we determined the subset of records to retrieve for the next or previous page by examining the primary key value of the first or last row in the DataGrid for the current page. If the data is not sorted by this primary key value, we cannot use this logic. Therefore, we cannot provide both sorting and pagination when using the primary key method for custom paging because the user can't sort on any DataGrid column other than the one that contains the primary key value. The stored procedure method for pagination can be used in tandem with sorting, but it is not very pretty code. Specifically, the stored procedure must accept an additional input parameter: the name of the field to sort by. When issuing the INSERT statement to populate the temporary table, a SQL dynamic statement must be created that sorts on the field passed in by the user. This dynamic SQL statement can be executed using the EXEC function. NOTE For more information on executing dynamic SQL statements in a stored procedure, consult the references in the "On the Web" section at the end of this chapter. Rather than bog ourselves down with the stored procedure method details, we'll use the DataGrid's default paging method. Although this isn't the optimal approach, it is the easiest and quickest. Adding Pagination SupportHaving decided to implement pagination support for our DataGrid using the default DataGrid paging method, we're ready to examine the code for a pageable DataGrid that displays the contents of the Comment table 10 records at a time. Listing 10.1 contains the necessary code and DataGrid declaration. The Next/Previous hyperlinks paging interface is used in Listing 10.1, although the list of hyperlinks paging interface could also have been used. In addition to the standard pagination, the user is shown a helpful message indicating what page of data is being viewed, and how many total pages the data is divided up into. Listing 10.1 A DataGrid with Pagination Support 1: <%@ import Namespace="System.Data" %> 2: <%@ import Namespace="System.Data.SqlClient" %> 3: <script runat="server" language="VB"> 4: Sub Page_Load(sender as Object, e as EventArgs) 5: If Not Page.IsPostBack then 6: BindData() 7: End If 8: End Sub 9: 10: 11: Sub BindData() 12: '1. Create a connection 13: Const strConnString as String = "server=localhost;uid=sa;pwd=; database=pubs" 14: Dim objConn as New SqlConnection(strConnString) 15: 16: '2. Create a command object for the query 17: Dim strSQL as String 18: strSQL = "SELECT CommentID, Name, Comment, DateAdded " & _ 19: "FROM Comments" 20: 21: Dim objCmd as New SqlCommand(strSQL, objConn) 22: 23: '3. Create a DataAdapter and Fill the DataSet 24: Dim objDA as New SqlDataAdapter() 25: objDA.SelectCommand = objCmd 26: 27: objConn.Open() 28: 29: Dim objDS as DataSet = New DataSet() 30: objDA.Fill(objDS, "titles") 31: 32: objConn.Close() 33: 34: 'Finally, specify the DataSource and call DataBind() 35: dgComments.DataSource = objDS 36: dgComments.DataBind() 37: 38: objConn.Close() 'Close the connection 39: 40: ShowPageInformation() 41: End Sub 42: 43: 44: Sub ShowPageInformation() 45: 'This sub displays paging information in the appropriate label 46: lblPagingInfo.Text = "Displaying Page " & _ 47: (dgComments.CurrentPageIndex+1).ToString() & " of " & _ 48: dgComments.PageCount 49: End Sub 50: 51: 52: Sub dgComments_Paging(sender As Object, e As DataGridPageChangedEventArgs) 53: dgComments.CurrentPageIndex = e.NewPageIndex 54: BindData() 55: End Sub 56: </script> 57: 58: <form runat="server"> 59: <asp:DataGrid runat="server" 60: Font-Name="Verdana" Font-Size="9pt" CellPadding="5" 61: AlternatingItemStyle-BackColor="#dddddd" 62: AutoGenerateColumns="False" Width="75%" 63: PageSize="10" AllowPaging="True" 64: OnPageIndexChanged="dgComments_Paging"> 65: 66: <HeaderStyle BackColor="Navy" ForeColor="White" Font-Size="13pt" 67: Font-Bold="True" HorizontalAlign="Center" /> 68: 69: <PagerStyle BackColor="Navy" ForeColor="White" Font-Size="8pt" 70: Font-Bold="True" HorizontalAlign="Right" 71: NextPageText="Next >" PrevPageText="< Prev" /> 72: 73: <Columns> 74: <asp:BoundColumn DataField="Name" HeaderText="Name" 75: SortExpression="Name" /> 76: <asp:BoundColumn DataField="Comment" HeaderText="Comment" 77: SortExpression="Comment" /> 78: <asp:BoundColumn DataField="DateAdded" HeaderText="Date Added" 79: SortExpression="DateAdded" 80: ItemStyle-HorizontalAlign="Center" DataFormatString="{0:d}" /> 81: </Columns> 82: </asp:DataGrid> 83: <asp:label runat="server" Font-Name="Verdana" 84: Font-Size="9pt" Font-Italic="True" Width="75%" HorizontalAlign="Right" /> 85: </form> Although Listing 10.1 contains 85 lines of code and DataGrid declaration content, the majority of it is code we've examined before. Recall that to provide pagination support, we must set the DataGrid's AllowSorting property to True, which is done on line 63. Next, we must provide an event handler for the DataGrid's PaegIndexChanged event. On line 64, we wire this event to the event handler dgComments_Paging. Paging using the DataGrid's default paging is fairly simple and straightforward. In the dgComments_Paging event handler (lines 52 55) we specify what page of data the user wants to view by setting the DataGrid's CurrentPageIndex property to the value of the NewPageIndex property of the event handler's DataGridPageChangedEventArgs parameter (line 53). Next, the BindData() subroutine is called (line 54). The BindData() subroutine (lines 11 41) fills a DataSet with the entire results of the SQL query that the user can page through; namely, all the records in the Comment table (see lines 18 and 19). This DataSet is then assigned to the DataGrid's DataSource property (line 35) and the DataGrid's DataBind() method is called (line 36). Note that in addition to performing these tasks, the BindData() subroutine also makes a call to the ShowPageInformation() subroutine (line 40). The ShowPageInformation() subroutine (lines 44 49) displays a message indicating what page of data the user is currently viewing, and the total number of pages. It accomplishes this by setting the Text property of the Label lblPagingInfo to a string that includes the CurrentPageIndex and PageCount properties of the DataGrid. The lblPageInfo Label is defined in the HTML section on lines 83 and 84. NOTE Note that in the ShowPageInformation() subroutine we display the current page of data the user is viewing by emitting the value of the DataGrid's CurrentPageIndex property plus 1 (see line 47). The reason we add 1 to the value of the CurrentPageIndex property is because the CurrentPageIndex property is indexed at zero, meaning when the user is viewing the first page of data, CurrentPageIndex equals zero. By adding 1, the user is shown a message such as "Displaying Page 1 of 6" instead of "Displaying Page 0 of 6." A screenshot of the code in Listing 10.1 can be seen in Figure 10.1. Note that with our inclusion of the lblPageInfo Label and ShowPageInformation() subroutine, the user is shown a message indicating what page of data she's viewing and how many total pages of data exist. Figure 10.1. The user can page through the contents of the Comment table. Adding Sorting Support to a Pageable DataGridNow that we have a DataGrid with paging support, let's see what we need to do to also include sorting support. In the code examples in Chapter 7, where we discussed the DataGrid's sorting features, we used a modified version of the BindData() subroutine we've been using throughout this book. Specifically, our BindData() subroutine accepted a string input parameter, sortExpression , which indicated the field by which to order the SQL results. With this version of the BindData(sortExpression ) subroutine, the code to provide sorting is trivial. All we have to do is set the DataGrid's AllowSorting property to True and provide an event handler for the DataGrid's SortCommand event. This event handler need only call the BindData(sortExpression ) subroutine, passing in the value of the SortExpression the user wants to sort by. (For a sorting code example, refer back to Chapter 7.) To add sorting to a DataGrid that already supports pagination, we must alter the BindData() subroutine used by the pageable DataGrid so that it accepts a sortExpression input parameter. This modified BindData(sortExpression ) subroutine would look identical to the original BindData() subroutine, save for one important difference: the SQL query would contain an ORDER BY clause based on the sortExpression parameter. For example, the new BindData(sortExpression ) subroutine might look like this: Sub BindData(sortExpr as String) ... Dim strSQL as String strSQL = "SELECT CommentID, Name, Comment, DateAdded " & _ "FROM Comments ORDER BY " & sortExpr ... End Sub In addition to this small change in the BindData() subroutine, the code from Listing 10.1 will need to have its Page_Load and dgComments_Paging event handlers updated, because two event handlers call the BindData() subroutine (see lines 6 and 54 in Listing 10.1). In the Page_Load event handler, we might opt to replace the call to BindData() with a call to BindData("DateAdded ASC"), which will have the data in the DataGrid initially ordered by the DateAdded field in chronologically increasing order. But what parameter value do we pass into the new BindData(sortExpression ) subroutine from the dgComments_Paging event handler? Imagine that the user has first visited the page, meaning we want to sort the results by CommentID in ascending order. When the user clicks the Next hyperlink to view the second page of data, we need to have the BindData(sortExpression ) subroutine grab the data in the correct order that is, we still must sort by DateAdded in ascending order. Similarly, if a user clicks the hyperlink in a particular DataGrid's column's header to sort by that particular column, each time the user pages through the data, the dgComments_Paging event handler is executed, which must call the BindData(sortExpression ) subroutine passing in the proper sort expression. To know the proper sort expression to pass into the BindData(sortExpression ) subroutine from the dgComments_Paging event handler, we must have some way of saving the current sort expression across postbacks. Sounds like a job for a ViewState variable! Each time the user opts to sort on a particular column, our code must set some ViewState variable to the SortExpression property of the sorted column. Then, in the dgComments_Paging event handler, the call to BindData(sortExpression ) must pass in the value of this ViewState variable. When the user is paging through the data, the ordering of the data can be "remembered" across postbacks in this manner. Listing 10.2 contains the source code for a DataGrid that supports both paging and sorting. Listing 10.2 This DataGrid Provides Both Pagination and Sorting Capabilities1: <%@ import Namespace="System.Data" %> 2: <%@ import Namespace="System.Data.SqlClient" %> 3: <script runat="server" language="VB"> 4: ' ... The ShowPageInformation() subroutine has been omitted for brevity ... 5: ' ... Refer back to Listing 10.1 for its details ... 6: 7: Sub Page_Load(sender as Object, e as EventArgs) 8: If Not Page.IsPostBack then 9: ViewState("SortExpr") = "DateAdded ASC" 10: BindData(ViewState("SortExpr")) 11: End If 12: End Sub 13: 14: 15: Sub BindData(sortExpr as String) 16: '1. Create a connection 17: Const strConnString as String = "server=localhost;uid=sa;pwd=; database=pubs" 18: Dim objConn as New SqlConnection(strConnString) 19: 20: '2. Create a command object for the query 21: Dim strSQL as String 22: strSQL = "SELECT CommentID, Name, Comment, DateAdded " & _ 23: "FROM Comments ORDER BY " & sortExpr 24: 25: Dim objCmd as New SqlCommand(strSQL, objConn) 26: 27: '3. Create a DataAdapter and Fill the DataSet 28: Dim objDA as New SqlDataAdapter() 29: objDA.SelectCommand = objCmd 30: 31: objConn.Open() 32: 33: Dim objDS as DataSet = New DataSet() 34: objDA.Fill(objDS, "titles") 35: 36: objConn.Close() 37: 38: 'Finally, specify the DataSource and call DataBind() 39: dgComments.DataSource = objDS 40: dgComments.DataBind() 41: 42: objConn.Close() 'Close the connection 43: 44: ShowPageInformation() 45: End Sub 46: 47: 48: Sub dgComments_Paging(sender As Object, e As DataGridPageChangedEventArgs) 49: dgComments.CurrentPageIndex = e.NewPageIndex 50: BindData(ViewState("SortExpr")) 51: End Sub 52: 53: 54: Sub dgComments_Sorting(sender as Object, e as DataGridSortCommandEventArgs) 55: 'Set the sortExpr ViewState variable accordingly 56: ViewState("SortExpr") = e.SortExpression 57: 58: 'Reset the data to show the FIRST page of data 59: dgComments.CurrentPageIndex = 0 60: 61: 'Bind the data! 62: BindData(ViewState("SortExpr")) 63: End Sub 64: 65: </script> 66: <form runat="server"> 67: <asp:DataGrid runat="server" 68: Font-Name="Verdana" Font-Size="9pt" CellPadding="5" 69: AlternatingItemStyle-BackColor="#dddddd" 70: AutoGenerateColumns="False" Width="75%" 71: PageSize="10" AllowPaging="True" 72: OnPageIndexChanged="dgComments_Paging" 73: AllowSorting="True" OnSortCommand="dgComments_Sorting"> 74: 75: <HeaderStyle BackColor="Navy" ForeColor="White" Font-Size="13pt" 76: Font-Bold="True" HorizontalAlign="Center" /> 77: 78: <PagerStyle BackColor="Navy" ForeColor="White" Font-Size="8pt" 79: Font-Bold="True" HorizontalAlign="Right" 80: NextPageText="Next >" PrevPageText="< Prev" /> 81: 82: <Columns> 83: <asp:BoundColumn DataField="Name" HeaderText="Name" 84: SortExpression="Name" /> 85: <asp:BoundColumn DataField="Comment" HeaderText="Comment" 86: SortExpression="Comment" /> 87: <asp:BoundColumn DataField="DateAdded" HeaderText="Date Added" 88: SortExpression="DateAdded" 89: ItemStyle-HorizontalAlign="Center" DataFormatString="{0:d}" /> 90: </Columns> 91: </asp:DataGrid> 92: <asp:label runat="server" Font-Name="Verdana" 93: Font-Size="9pt" Font-Italic="True" Width="75%" HorizontalAlign="Right" /> 94: </form> The code in Listing 10.2 builds upon the code presented in Listing 10.1 by adding sorting support to the DataGrid. Recall that to provide sorting support, the DataGrid's AllowSorting property must be set to True, and an event handler for the DataGrid's SortCommand event must be specified (line 73). Additionally, when explicitly listing the DataGrid columns in a Columns tag, as is the case here (lines 82 90), those columns that are sortable must have their SortExpression property provided (see lines 84, 86, and 88). As discussed in Chapter 7, when a particular DataGrid column's sorting hyperlink is clicked, the ASP.NET Web page is posted back and the DataGrid's SortCommand event is fired, causing the specified event handler to fire (dgComments_Sorting in Listing 10.2, lines 54 63). The event handler is passed a DataGridSortCommandEventArgs parameter that includes a SortExpression property. The value of this parameter's SortExpression property is assigned the SortExpression property value of the DataGrid column whose sorting hyperlink was clicked. To enable both paging and sorting support for a DataGrid, the BindData() subroutine from Listing 10.1 has to be updated to accept an input parameter. The updated version, BindData(sortExpression ), is included in Listing 10.2 and can be found spanning line 15 to line 45. This subroutine is essentially the same as it was in Listing 10.1, except for one very important part the SQL query, constructed on lines 22 and 23, orders the query results by the field specified by the sortExpression parameter. With this altered BindData(sortExpression ) subroutine, the event handlers that call the subroutine must be updated so that they pass in a parameter to sort the results by. The Page_Load event handler (lines 7 12) passes in the value DateAdded ASC, which initially sorts the comments by the date they were made in chronologically increasing order. Note that the ViewState variable SortExpr is assigned the value DateAdded ASC (line 9). Recall from our discussion prior to Listing 10.2 that we need to be able to persist the sort expression across postbacks to the page, which we accomplish by using a ViewState variable. The dgComments_Paging event handler, which fires every time the user navigates from one page to another, sets the DataGrid's CurrentPageIndex property on line 49, as it did in Listing 10.1, and then calls the BindData(sortExpression ) subroutine, passing in as the sortExpression the value of the ViewState variable SortExpr (line 50) . Finally, an event handler for the DataGrid's SortCommand event is needed. This event handler, dgComments_Sorting (lines 54 63), assigns the new SortExpression value to ViewState("SortExpr") (line 56), sets the DataGrid's CurrentPageIndex property to 0 (line 59), and rebinds the DataGrid by calling the BindData(sortExpression ) subroutine, passing in the value of the new SortExpression (line 62). You might be wondering why the dgComments_Sorting event handler sets the DataGrid's CurrentPageIndex property to 0. Imagine for a moment that we didn't reset the DataGrid's CurrentPageIndex property to 0 and the following events transpired: The user visits our page and is shown the data in chronologically increasing order. He pages through the first few pages of comments and is viewing page four of six when he decides he wants to see the results ordered by the Name column. When he clicks the Name column, he sees comments from Ned, Sally, and Scott (as opposed to comments from other posters whose names come sooner in the alphabet, such as Dave, Frank, and Jisun). Why is this? In our example, the user pages to the fourth page of data before opting to sort the results. However, if the DataGrid's CurrentPageIndex property is not reset to 0, when the user clicks on the Name column's sorting hyperlink, he will be shown the fourth page of data as sorted by the DataGrid's Name column. Such behavior will undoubtedly confuse users. When a user opts to sort data by a particular field, he expects to start viewing the sorted data starting from the beginning of the data, not somewhere in the middle. By resetting the CurrentPageIndex property to 0, the user is shown the first page of the sorted data. Figures 10.2 and 10.3 contain screenshots of Listing 10.2. Note that in both screenshots, the DataGrid column headers are hyperlinks, indicating that the columns are sortable. Figure 10.2 shows a screenshot of the fourth page of data being displayed when the DataGrid is sorted by the Date Added column in ascending order (the default sort order). Figure 10.3 shows a screenshot of the second page of data being displayed when the DataGrid is sorted by the Name column. Figure 10.2. The DataGrid is sorted by the Date Added column. Figure 10.3. The DataGrid is sorted by the Name column.
|
EAN: 2147483647
Pages: 111