It would be nice if your table could sort its data when you click a column within the table. It would be even better if the data could toggle between sorting in ascending order and sorting in descending order when you click the column header multiple times. You can accomplish this goal, but you'll need to write some code to make it happen.
No sorting can occur in the DataGrid control unless you handle two issues:
The control's AllowSorting property must be set to True.
Each column for which you want to allow sorting must have its SortExpression property set to reflect the expression you'd like to use for sorting when that column is selected.
You handled both of these issues when you created the DataGrid control earlier in the chapter. Now, when a user clicks one of the links in the DataGrid control's column headers, the control raises its SortCommand event, and you can include code to sort the underlying data and rebind the grid.
The SortCommand event procedure passes to you, as a property of its second parameter, the sort expression you selected. It doesn't, however, indicate the direction you want to sort it leaves handling sort direction up to your own code. To accomplish sorting, you will need some way to track the column that's currently sorted as well as the direction it's sorted in. (You need to keep track of both pieces of information so that if a user clicks a column other than the currently sorted column, you know to use an ascending sort. If the user clicks the same column that's currently sorted, you need to toggle the sort order.) To keep track of the sort column and sort order, you can use two Session variables, initialized in the Page_Load procedure.
To add sorting support to your page, you'll need to take these actions:
Modify the Page_Load procedure to initialize the Session variables Sort and SortDirection.
Add a procedure, HandleSort, that can rebuild the page's DataSet, taking into account the selected sorting order.
Modify the CategoryLoad procedure so that it sorts the data correctly.
Modify the GridLoad procedure to take the sorting into account, as it loads the data into the grid.
Add the grdCatSales_SortCommand event handler to run the appropriate code when the user clicks a column header.
Follow these steps to add sorting functionality to your page:
In the Solution Explorer window, select CategorySales.aspx, right-click, and select View Code from the context menu.
Modify the Page_Load procedure so that it looks like this:
Private Sub Page_Load( _ ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles MyBase.Load If Not Page.IsPostBack Then Session("Sort") = String.Empty Session("SortDirection") = String.Empty CategoryLoad() GridLoad() End If End Sub
Add the HandleSort procedure, shown in Listing 16.1, to the class. This procedure compares the passed-in sort expression to the current sort expression, and it sets the sort direction accordingly (toggles between ASC and DESC if the sort expression hasn't changed or sets it to ASC if the sort expression has changed). The procedure also sets the Sort and SortDirection session variables.
Listing 16.1 Handle Ascending and Descending Data Sorting
Public Sub HandleSort(ByVal SortExpression As String) Dim strDirection As String If SortExpression = String.Empty Then SortExpression = "ProductName" strDirection = "ASC" End If ' Is the current SortExpression the same ' as the last time? If so, alter the ' direction. If SortExpression = CStr(Session("Sort")) Then ' Was it sorted ascending or descending ' last time? Session("SortDirection") will ' tell you. If CStr(Session("SortDirection")) = "ASC" Then strDirection = "DESC" Else strDirection = "ASC" End If Else strDirection = "ASC" End If Session("Sort") = SortExpression Session("SortDirection") = strDirection End Sub
Modify the CategoryLoad procedure, adding support for sorting. You need to add an optional parameter and a call to the HandleSort procedure:
Private Sub CategoryLoad( _ Optional ByVal SortExpression As String = "ProductName") Dim ds As DataSet Dim strSQL As String Dim strConn As String ' Set up SQL and Connection strings. strSQL = "SELECT * FROM [Sales By Category]" strConn = Session("ConnectString").ToString ' Create DataSet and ' store DataSet into Session variable Session("DS") = DataHandler.GetDataSet(strSQL, strConn) ' Set Sort Expressions into Session Vars HandleSort(SortExpression) End Sub
Modify the GridLoad procedure, adding code to sort the DataView object to which you're binding the DataGrid control. If, for example, you selected the ProductName field for the second time, the Sort property value would be ProductName DESC:
Private Sub GridLoad() Dim dv As DataView ' Get default view from stored DataSet dv = CType(Session("DS"), _ DataSet).Tables(0).DefaultView ' Set Sort property of DataView dv.Sort = Session("Sort").ToString & " " & _ Session("SortDirection").ToString ' Fill in DataSource and bind to data. grdCatSales.DataSource = dv grdCatSales.DataBind() End Sub
Finally, select grdCatSales from the drop-down list at the top left of the code editor window. Select SortCommand from the list of events in the right drop-down list. Modify the event procedure so that it looks like this:
Private Sub grdCatSales_SortCommand( _ ByVal source As System.Object, _ ByVal e As System.Web.UI.WebControls. _ DataGridSortCommandEventArgs) _ Handles grdCatSales.SortCommand HandleSort(e.SortExpression) GridLoad() End Sub
This step hooks up the event that occurs when a user clicks a column. This event procedure first takes the SortExpression property of the parameter sent to the procedure, which contains the SortExpression property of the column that was clicked. The HandleSort procedure sets up the Session variables Sort and SortDirection, and the GridLoad procedure uses those variables to sort the data correctly as it loads the grid. Because clicking the link reposts the page, as the page renders the DataGrid control's contents, the new sorting takes effect and you see the data sorted correctly.
Modify the ItemCommand event procedure to use the DataView in a sorted order. This ensures you are selecting the correct category ID. Listing 16.2 shows the complete procedure.
Listing 16.2 The ItemCommand Event Handler Runs Whenever You Click Any Clickable Item in the DataGrid Control
Private Sub grdCatSales_ItemCommand( _ ByVal source As Object, _ ByVal e As System.Web.UI.WebControls. _ DataGridCommandEventArgs) _ Handles grdCatSales.ItemCommand Dim dr As DataRowView Dim dv As DataView Select Case e.CommandName Case "Select" ' Convert stored DataSet into DataView dv = CType(Session("DS"), _ DataSet).Tables(0).DefaultView dv.Sort = Session("Sort").ToString & _ " " & Session("SortDirection").ToString ' Get the Row from the DataView dr = dv.Item(e.Item.DataSetIndex) ' Display the Category Name column lblCategory.Text = "Category: " & _ dr("CategoryName").ToString Case Else ' No others, right now. End Select End Sub
Just as a DataTable object provides a collection of DataRow objects, the DataView object provides a collection of DataRowView objects. The grdCatSales_ItemCommand procedure uses a DataRowView object, which you've not needed previously. This object corresponds to the standard DataRow object but works as part of a DataView rather than a DataTable. You work with a DataRowView object in just the same way you work with a DataRow object, as far as this procedure is concerned.
| || |
As a final test, try browsing the page once again. This time, click a column header and verify that it sorts correctly. Click again, and the column sorts in the opposite order.