Adding Advanced Sorting Features

So far, we have seen how to add very simple sorting support to a DataGrid control. Although the sorting capabilities we have examined thus far are nice, they lack more advanced features that users are often accustomed to. For example, in Listing 7.4, the user can sort the DataGrid's data alphabetically by the title or by the price in ascending order. But what if the user wants to view the data by price in descending order? Or what if she wants to view the price in ascending order? What if two or more books have the same price, and she wants to have those books sorted alphabetically?

With some additional programming, we can build a DataGrid that supports these advanced features. In the next section, we'll look at how to provide the ability for a particular DataGrid column to be sorted in either ascending or descending order. In the section "Providing Multi-Column Sorting," we'll examine how to sort the contents of the DataGrid on one column, breaking ties by a sorting a different column.

Before we begin addressing how to add these two advanced features, let's take a moment to quickly review over what is happening behind the scenes when a user opts to sort a DataGrid column by clicking on the column's sort header. Specifically, in Listing 7.4, when the sort header hyperlink of either the Title or Price column is clicked, the following events occur:

1. The ASP.NET Web page is posted back.

2. The DataGrid's SortCommand event is raised.

3. The SortDataGrid event handler, which is wired up to the DataGrid's SortCommand event, is executed.

4. The BindData(SortFieldName ) subroutine is called, passing in the SortExpression property of the column whose sort header hyperlink was clicked.

5. A SQL query is formed that orders the results by the DataSource field represented by the SortFieldName input parameter to the BindData(SortFieldName ) subroutine.

6. The SQL query is used to populate a SqlDataReader object.

7. The DataGrid's DataSource property is set to the SqlDataReader object from step 6, and the DataGrid's DataBind() method is called.

8. The DataGrid is displayed to the user sorted in ascending order by the DataGrid column whose sort header was clicked.

This chain of events always leads to the columns being sorted in ascending order, because in our BindData(SortFieldName ) subroutine, we built up our SQL query by simply appending the name of the field we want to order the results by to the end of the SQL query. That is, if the user clicks on the Title column, which has a SortExpression property value of "title", the SQL query becomes

 SELECT title, price, pubdate  FROM titles  ORDER BY title 

With SQL, the default sorting order is ascending. Of course, you can specify the sorting order using either the ASC or DESC keywords, for ascending and descending, respectively. If we want to sort the titles in reverse alphabetical order (descending order), we would need to adjust our SQL query so that it looks like this:

 SELECT title, price, pubdate  FROM titles  ORDER BY title DESC 

To sort the results in alphabetical order (ascending order), we could leave off any specification on the sort order:

 SELECT title, price, pubdate  FROM titles  ORDER BY title 

We could also specify that the sort be done in ascending order by adding the ASC keyword:

 SELECT title, price, pubdate  FROM titles  ORDER BY title ASC 

Now that we have a good understanding of the process that occurs when a DataGrid column's sort header hyperlink is clicked, we're ready to learn how to sort a DataGrid column in both ascending and descending order.

Supporting Ascending and Descending Sorting Orders

Before providing the capability to sort a DataGrid column in both ascending and descending order, it is important that we take a moment to decide on the user interface. For our first example, we'll simply use the standard sort header hyperlinks. That is, the first time a DataGrid column's sort header hyperlink is clicked, the results are displayed in ascending order. If a particular row's sort order is ascending and the sort header hyperlink is clicked again, the results are displayed in descending order. Similarly, if a particular row's sort order is descending and the sort header hyperlink is clicked again, the results are displayed in ascending order.

To accomplish this, we need some way to keep track of how a row should be sorted if its sort header hyperlink is clicked. This information needs to persist across postbacks. With classic ASP, we might opt to store such information in the Session object, and this is also an option with ASP.NET. However, because we only need to store this information on a page-level basis as opposed to a user session-level basis, it would be wiser to use the ViewState.

NOTE

For more information on the ViewState, check out Susan Warren's article "Taking a Bite Out of ASP.NET ViewState." You can find a reference to the article in the "On the Web" section at the end of this chapter.

For each sortable DataGrid column, we will add a string variable in the ViewState that indicates whether the column needs to be displayed in ascending or descending order when the column's sort header hyperlink is clicked. Listing 7.5 illustrates how we can enable DataGrid columns to be sorted both in ascending and descending order. Note that the BindData(SortFieldName ) subroutine from Listing 7.5 has been omitted for brevity; to review the BindData(SortFieldName ) subroutine code, refer back to Listing 7.3. Additionally, the DataGrid declaration in Listing 7.5 has been omitted as well it is the same as the DataGrid declaration in Listing 7.4 .

Listing 7.5 Each Column's Sort Direction Is Stored in a ViewState Variable

  1: <%@ import Namespace="System.Data" %>   2: <%@ import Namespace="System.Data.SqlClient" %>   3: <script runat="server" language="VB">   4:   '... The BindData method has been omitted for brevity ...   5:      '... Refer back to Listing 7.3 for its details ...   6:   7:     Sub Page_Load(sender as Object, e as EventArgs)   8:       If Not Page.IsPostBack then   9:         ViewState("titleSortDirection") = "ASC"  10:         ViewState("priceSortDirection") = "ASC"  11:  12:         BindData("title " & ViewState("titleSortDirection"))  13:  14:         SwapDirection("titleSortDirection")  15:       End If  16:     End Sub  17:  18:  19:     Sub SwapDirection(viewStateKey as String)  20:       If ViewState(viewStateKey) = "ASC" then  21:         ViewState(viewStateKey) = "DESC"  22:       Else  23:         ViewState(viewStateKey) = "ASC"  24:       End If  25:     End Sub  26:  27:   28:     Sub SortDataGrid(sender as Object, e as DataGridSortCommandEventArgs)  29:       BindData(e.SortExpression & " " & ViewState(e.SortExpression & "SortDirection"))  30:  31:       SwapDirection(e.SortExpression & "SortDirection")  32:     End Sub  33: </script>  34:  35:   <%-- The DataGrid declaration has been omitted for brevity  36:        Refer back to Listing 7.4 for its details...   --%> 

The key pieces of Listing 7.5 can be found in the Page_Load event handler (lines 9 through 14), the SwapDirection(ViewStateKey ) subroutine (lines 19 through 25), and the code in the SortDataGrid event handler (lines 29 and 31) .

CAUTION

When storing a variable in the ViewState, realize that the key used to retrieve and store the variable is case sensitive. That is, ViewState("titleSortDirection") and ViewState("TitleSortDirection") reference two different variables in the ViewState (because the latter example has its first character capitalized, and the former does not).

Let's first look at what happens when the page is visited for the first time. Recall that for each sortable column, we need to store a string variable in the ViewState that indicates how the column should be sorted when its sort header hyperlink is clicked. Because we only have two sortable columns in our DataGrid, the Title and Price columns, we create two such ViewState variables on lines 9 and 10 in our first visit to the page, setting the variables to "ASC", the direction we want to have the columns sorted by default. Next, a call to the BindData(SortFieldName ) subroutine is made on line 12. Instead of just passing in a DataSource field name, we pass in both the field name and the direction we want the results sorted. Hence, on line 12 the SortFieldName parameter is passed in as a value of "title ASC", which sorts the results by the title field in ascending order.

After making a call to the BindData(SortFieldName ) subroutine on line 12, a call to the SwapDirection(ViewStateKey ) is made on line 14. The SwapDirection(ViewStateKey ) subroutine accepts a ViewState key name (a string) as its input parameter. It then checks to see whether the value of the particular ViewState variable is "ASC" (line 20); if it is, it changes the value to "DESC". If the value of the ViewState variable is not "ASC", SwapDirection(ViewStateKey ) changes the value back to "ASC" (lines 20 24). This toggling of the ViewState variable has the effect of changing the column's sort order each time the column's sort header hyperlink is clicked.

The SortDataGrid event handler (lines 28 through 32) first makes a call to the BindData(SortFieldName) subroutine on line 29, passing in an appropriate string by concatenating the SortExpression property value with a blank space and the value of the particular column's ViewState variable. Finally, on line 31, a call to the SwapDirection(ViewStateKey ) subroutine is made, swapping the sort order for the column whose sort header hyperlink was just clicked.

To clear any confusion that may exist, let's run through the code's execution life cycle when a column's sort header hyperlink is clicked. First the ASP.NET Web page is posted back, and the DataGrid's SortCommand event is fired. This in turn executes the SortDataGrid event handle, which calls the BindData(SortFieldName) subroutine. Imagine that the Title column was the column that had its sort header hyperlink clicked. Then the value of e.SortExpression is "title", so the call to BindData(SortFieldName ) on line 29 is passed in the value of e.SortExpression, concatenated with a blank string, and then concatenated with the ViewState variable whose name is the value of e.SortExpression combined with the string "SortDirection". Because the value of e.SortExpression is "Title", we are grabbing the ViewState variable "titleSortDirection". The ViewState variable "titleSortDirection" will have a value of either "ASC" or "DESC", meaning that the string passed into the BindData(SortFieldName) subroutine will be either "title ASC" or "title DESC", depending on the current value of the ViewState variable "titleSortDirection". Assuming the input to the BindData(SortFieldName) subroutine was "title DESC", the following SQL query is used in repopulating the DataGrid:

 SELECT title, price, pubdate  FROM titles  ORDER BY title DESC 

NOTE

Note that when choosing the ViewState variable names in the Page_Load event handler (lines 9 and 10), I was careful to choose the names so that they followed the pattern FieldName SortDirection. Using this technique, I can then query the value of the proper ViewState variable by using the SortExpression concatenated with the string "SortDirection" because the SortExpression values for the columns are equal to the field names the columns represent.

After the call to the BindData(SortFieldName) subroutine is made, a call to the SwapDirection(ViewStateKey ) subroutine is made (line 31). This call changes the sort order for the Title column from "DESC" to "ASC" or vice versa, depending upon the current value of the titleSortDirection ViewState variable.

Figures 7.5 and 7.6 depict the code from Listing 7.5 in action. Figure 7.5 shows the page when the Price sort header hyperlink has been clicked for the first time note that the DataGrid is sorted in ascending order by price. Figure 7.6 illustrates what happens when the Price column's sort header is clicked again. This time, the DataGrid is sorted by price in descending order.

Figure 7.5. The DataGrid is ordered by price in ascending order.

Figure 7.6. The DataGrid is ordered by price in descending order.

Improvements to the User Interface

One downside to the user interface we just examined is that, from the user's perspective, it's difficult to determine what column has been sorted on and in what direction. Granted, through simple inspection, it wouldn't take an average user long to determine what column of the DataGrid had been sorted and in what order. Nevertheless, it would be helpful to have some sort of visual cue to indicate this information. For example, if you use Microsoft Outlook, you'll likely have noticed that you can sort a list of emails in a folder by clicking on the From, Subject, or Date Received headers. When clicking on such a field, a small up or down arrow appears next to the field name, indicating that the results are sorted by that column and in what direction (an up arrow indicates ascending order, a down arrow indicates descending order).

It would be nice to provide a similar user interface to our DataGrid control. There are a number of ways to accomplish this task. Personally, I think the easiest would be to create a number of images, three for each sortable DataGrid column. Each sortable DataGrid column would need an image with only the name of the column, an image with the name of the column and a down arrow next to it, and an image with the name of the column and an up arrow next to it. For our Title table, we would have an image reading Title, an image reading Title with an up arrow next to the word Title, and an image reading Title with a down arrow next to the word Title. Similarly, we would have these three images for our Price column as well. These images should be named ColumnName Plain.gif for the image with just the name of the column, ColumnName ASC.gif for the image with the name of the column and an up arrow, and ColumnName DESC.gif for the image with the name of the column and a down arrow.

Next, in our DataGrid declaration, for our sortable DataGrid columns we would replace the HeaderText property in the BoundColumn control with the HeaderImageUrl property, whose value is set to the image without the arrow for that column. That is, in Listing 7.4, our DataGrid declaration had the following BoundColumns:

 <asp:BoundColumn DataField="title" HeaderText="Title"          SortExpression="title" />  <asp:BoundColumn DataField="price" HeaderText="Price"          SortExpression="price" DataFormatString="{0:c}"          ItemStyle-HorizontalAlign="Right" />  <asp:BoundColumn DataField="pubdate" HeaderText="Published Date" /> 

We would replace the HeaderText property in the Title and Price BoundColumns with the HeaderImageUrl property:

 <asp:BoundColumn DataField="title" HeaderImageUrl="TitlePlain.gif"          SortExpression="title" />  <asp:BoundColumn DataField="price" HeaderImageUrl="PricePlain.gif"          SortExpression="price" DataFormatString="{0:c}"          ItemStyle-HorizontalAlign="Right" />  <asp:BoundColumn DataField="pubdate" HeaderText="Published Date" /> 

This would display the Title image and Price image in the header of the Title and Price columns. These images, when clicked, would cause a postback, raising the DataGrid's SortCommand event just as in Listings 7.4 and 7.5, when the HeaderText property was used instead.

Whenever the SortDataGrid event handler is fired, we need to call a subroutine that iterates through the columns of the DataGrid, setting each column's HeaderImageUrl property accordingly. Specifically, the column whose sort header hyperlink was just clicked would have its HeaderImageUrl changed to the appropriate up or down arrow version, whereas all other sortable columns would have their HeaderImageUrl changed back to the plain image. Listing 7.6 provides the code needed to produce such a user interface.

Listing 7.6 An Up or Down Arrow Is Used to Illustrate How the DataGrid's Data Is Sorted

[View full width]

  1: <%@ import Namespace="System.Data" %>   2: <%@ import Namespace="System.Data.SqlClient" %>   3: <script runat="server" language="VB">   4:   ' ... The BindData and SwapDirections subroutines have been   5:   ' ... omitted for brevity.  Refer back to Listings 7.3 and 7.5,   6:            ' ... respectively, for details ...   7:   8:     Sub Page_Load(sender as Object, e as EventArgs)   9:       If Not Page.IsPostBack then  10:         ViewState("titleSortDirection") = "ASC"  11:         ViewState("priceSortDirection") = "ASC"  12:  13:         BindData("title " & ViewState("titleSortDirection"))  14:         ChangeHeaderImageUrl("title", "ASC")  15:         SwapDirection("titleSortDirection")  16:       End If  17:     End Sub  18:  19:  20:     Sub ChangeHeaderImageUrl(fieldName as String, sortDirection as String)  21:       ' Loop through all of the DataGrid columns  22:       Dim iLoop as Integer  23:       Dim column as DataGridColumn  24:       Dim dgRow as DataGridItem = dgTitles.Controls(0).Controls(0)  25:       Dim sortButton as ImageButton  26:  27:       For iLoop = 0 to dgTitles.Columns.Count - 1  28:  29:         column = dgTitles.Columns(iLoop)  30:   31:         ' See if the column has a SortExpression property  32:         If column.SortExpression <> String.Empty then  33:           'Now, check if the SortExpression equals the fieldName  34:  35:           sortButton = dgRow.Cells(iLoop).Controls(0)  36:  37:           If column.SortExpression = fieldName then  38:             'We need to apply a new arrow direction  39:             column.HeaderImageUrl = fieldName & sortDirection & ".gif"  40:             sortButton.ImageUrl = column.HeaderImageUrl  41:           Else  42:             'This column wasn't sorted, we need to display the  43:             'standard image url  44:             column.HeaderImageUrl = column.SortExpression & "Plain.gif"  45:             sortButton.ImageUrl = column.HeaderImageUrl  46:           End If  47:         End If  48:       Next iLoop  49:     End Sub  50:  51:  52:     Sub SortDataGrid(sender as Object, e as DataGridSortCommandEventArgs)  53:       BindData(e.SortExpression & " " & ViewState(e.SortExpression &  "SortDirection"))  54:       ChangeHeaderImageUrl(e.SortExpression, ViewState(e.SortExpression & "SortDirection"))  55:       SwapDirection(e.SortExpression & "SortDirection")  56:     End Sub  57: </script>  58:  59: <form runat="server">  60:   <asp:DataGrid runat="server"   61:       Font-Name="Verdana" Font-Size="9pt" CellPadding="5"  62:       AlternatingItemStyle-BackColor="#dddddd"  63:       AllowSorting="True" AutoGenerateColumns="False"  64:       OnSortCommand="SortDataGrid">  65:  66:     <HeaderStyle BackColor="Navy" ForeColor="White" Font-Size="13pt"  67:               Font-Bold="True" HorizontalAlign="Center" />  68:  69:     <Columns>  70:       <asp:BoundColumn DataField="title" HeaderImageUrl="TitlePlain.gif"  71:               SortExpression="title" />   72:       <asp:BoundColumn DataField="price" HeaderImageUrl="PricePlain.gif"  73:               SortExpression="price" DataFormatString="{0:c}"  74:               ItemStyle-HorizontalAlign="Right" />  75:       <asp:BoundColumn DataField="pubdate" HeaderText="Published Date" />  76:     </Columns>  77:   </asp:DataGrid>  78: </form> 

The first thing to note is that the BoundColumns for the Title and Price columns both have their HeaderImageUrl property set to the "plain" versions of the image files, the ones without an up or down arrow next to the image title (lines 70 and 72, respectively). The Page_Load event handler for Listing 7.6 is nearly identical to that of Listing 7.5; the only difference is that after calling the BindData(SortFieldName ) subroutine and before calling SwapDirection(ViewStateKey ), we call a new subroutine, ChangeHeaderImageUrl(FieldName, SortDirection ) (line 14).

The new subroutine, ChangeHeaderImageUrl(FieldName, SortDirection ) (lines 11 through 28), is called on the first page load, as well as each time one of the DataGrid's column's sort header hyperlinks is clicked. This subroutine is responsible for updating the HeaderImageUrl property of each of the sortable columns in the DataGrid, as well as the ImageUrl property of the ImageButton control that makes up the actual image in the DataGrid column header.

The FieldName input parameter to the ChangeHeaderImageUrl(FieldName, SortDirection ) subroutine specifies what column has just had its sort header hyperlink clicked. The ChangeHeaderImageUrl(FieldName, SortDirection ) subroutine iterates through each column in the DataGrid's Columns collection using a For... Next loop starting on line 27. Before that, however, a number of variables are declared. On line 22, iLoop, a simple integer looping variable, is declared. On line 23, column is declared as a DataGridColumn instance. This variable is used in the For ... Next loop to reference the current DataGrid column that is being iterated over. On line 24, a variable of type DataGridItem called dgRow is created and assigned to the first DataGridItem in the DataGrid, the header row that contains the sort header hyperlinks (as ImageButtons) for each of the sortable columns. To retrieve this header row, we have to first reference the first (0th) child control of the DataGrid, which is a DataGridTable object. The children controls of the DataGridTable are the rows of the DataGrid. Because we want to retrieve the first (0th) row, we reference the DataGridTable's 0th child control. Hence, in the end, we want the 0th child control of the 0th child control of the DataGrid, or dgTitles.Controls(0).Controls(0) (line 24) .

Note that we are only concerned with altering the HeaderImageUrl property and ImageButton ImageUrl property of those columns that are sortable. In each iteration of our For ... Next loop, we set the current column to a local variable column (line 29). On line 32, we then check the column's SortExpression property to determine whether the DataGrid column is sortable. Recall that to make a column sortable when the AutoGenerateColumns property is set to False, you have to specify a SortExpression property for the column. Hence, columns whose SortExpression is a blank string are not sortable. Therefore, lines 33 through 46 will only be reached if the current column being examined is a sortable column.

Assuming the column is sortable, we first grab an instance of the ImageButton used to represent the sort header for the particular column. When supplying a HeaderImageUrl property for a DataGrid column, the header is displayed via an ImageButton control. To physically alter the image displayed by the header, we must directly reference this ImageButton and tweak its ImageUrl property. Line 35 references the ImageButton for the current column being iterated through and assigns it to the local variable sortButton. Note that dgRow.Cells(iLoop) retrieves the particular column we're currently iterating over from the header DataGridItem row; the Controls(0) retrieves the first (0th) child control from this row's column, which is an ImageButton control (because the HeaderImageUrl property was set for this row) .

After referencing the ImageButton control, we check to see whether the column's SortExpression property is identical to the FieldName input parameter (line 37). If it is, we need to set the column's HeaderImageUrl property and the ImageButton's ImageUrl property to the appropriate image. For example, if we are displaying the Title column in ascending order, we want to change the HeaderImageUrl property to "TitleASC.gif". On lines 39 and 40, we set the HeaderImageUrl and ImageUrl properties accordingly; the correct image URL can be determined by concatenating the value of the FieldName input parameter with the SortDirection input parameter, followed by the extension .gif. (The SortDirection input parameter will have the value "ASC" or "DESC". )

If, on the other hand, the current column being iterated over is not the column whose sort header ImageButton was clicked, we want to convert the ImageButton's ImageUrl back to the "plain" image. This is accomplished in the Else statement on lines 44 and 45.

Note that in the SortDataGrid event handler, we make a call to the ChangeHeaderImageUrl(FieldName, SortDirection ) subroutine each time after BindData(SortFieldName ) and before calling SwapDirection(ViewStateKey ) (line 54) .

Figures 7.7 and 7.8 show Listing 7.6 when viewed through a browser. Specifically, Figure 7.7 shows the page on the first page view, when the DataGrid is sorted alphabetically (in ascending order) by the Title column. Figure 7.8 shows what happens after the Title sort header is clicked, which reverses the sort order for the Title column. Note that the arrow in the Title image in Figure 7.7 points up, whereas the arrow in the Title image in Figure 7.8 points down. Furthermore, note that the Price image has no arrows, because we are sorting by the Title column.

Figure 7.7. An up arrow indicates that the DataGrid is sorted in ascending order by the Title column.

Figure 7.8. A down arrow indicates that the DataGrid is sorted in descending order by the Title column.

Providing Multi-Column Sorting

Now that we know how to provide both ascending and descending support for a single column, let's examine how to provide sorting across multiple columns. That is, how can we have it so that when one column is sorted, any ties are broken by a sort order on another column? A specific example of this occurs when sorting the books in Listing 7.6 by price: If two books have the same price, they are ordered alphabetically.

Let's take the code from Listing 7.6 and extend it so that whenever the Price column is sorted, ties are broken by sorting the titles of the tied books in alphabetical order. For simplicity's sake, let's not concern ourselves with breaking ties when sorting the Title column.

Currently when the Price column's sort header is clicked the first time, the DataGrid's data is sorted by the price in ascending order. This is accomplished via the following SQL query:

 SELECT title, price, pubdate  FROM titles  ORDER BY price ASC 

When the Price column's sort header is clicked a second time, the DataGrid is sorted by the price in descending order, which is accomplished via the following SQL query:

 SELECT title, price, pubdate  FROM titles  ORDER BY price DESC 

To sort the Price column in either ascending or descending order with ties being broken by the values of the Title column, we need to use one of the following two ORDER BY clauses in our SQL statement:

 -- for ordering by price in ascending order, breaking ties  -- alphabetically on the title  ORDER BY price ASC, title ASC  -- for ordering by price in descending order, breaking ties  -- alphabetically on the title  ORDER BY price DESC, title ASC 

Currently we are determining the sorting by passing in the field name and sort direction for the ORDER BY clause through the input parameter of the BindData(SortFieldName) subroutine. For our example, we'll need to call BindData(SortFieldName ) like so:

 BindData("price ASC, title ASC") 

In the SortDataGrid event handler in Listing 7.6, we determined the input parameter to BindData(SortFieldName ) by concatenating the value of the clicked DataGrid column's SortExpression, along with the proper ViewState variable that indicates in what direction the data is to be sorted. It is clear that to allow for multi-column sorting, we will need to enhance this call to BindData(SortFieldName ) in some manner, so that when sorting by the Price column, either price ASC, title ASC or price DESC, title ASC gets passed in to the BindData(SortFieldName) subroutine, depending upon what direction the Price column data needs to be sorted.

As you can see in Listing 7.7, the solution uses a new subroutine, PrepareBindData(FieldName , SortDirection ) (lines 20 26), which is called in place of BindData(SortFieldName ) in the SortDataGrid event handler. It is the PrepareBindData(FieldName , SortDirection ) subroutine that makes the appropriate call to BindData(SortFieldName ) when the Price column is being sorted.

Listing 7.7 Ties in the Price Are Broken by Sorting the Titles Alphabetically

[View full width]

  1: <%@ import Namespace="System.Data" %>   2: <%@ import Namespace="System.Data.SqlClient" %>   3: <script runat="server" language="VB">   4:    ' ... The BindData, SwapDirections, and ChangeHeaderImageUrl   5:    ' ... subroutines have been omitted for brevity.  Refer back   6:    ' ... to Listings 7.3, 7.5, and 7.6 respectively, for details   7:   8:     Sub Page_Load(sender as Object, e as EventArgs)   9:       If Not Page.IsPostBack then  10:         ViewState("titleSortDirection") = "ASC"  11:         ViewState("priceSortDirection") = "ASC"  12:  13:         PrepareBindData("title", ViewState("titleSortDirection"))  14:         ChangeHeaderImageUrl("title", "ASC")  15:         SwapDirection("titleSortDirection")  16:       End If  17:     End Sub  18:  19:  20:     Sub PrepareBindData(fieldName as String, sortDirection as String)  21:       If fieldName = "price" then  22:         BindData(fieldName & " " & sortDirection & ", title ASC")  23:       Else  24:         BindData(fieldName & " " & sortDirection)  25:       End If  26:     End Sub   27:  28:  29:     Sub SortDataGrid(sender as Object, e as DataGridSortCommandEventArgs)  30:       PrepareBindData(e.SortExpression, ViewState(e.SortExpression & "SortDirection"))  31:       ChangeHeaderImageUrl(e.SortExpression, ViewState(e.SortExpression &  "SortDirection"))  32:       SwapDirection(e.SortExpression & "SortDirection")  33:     End Sub  34: </script>  35:  36:   <%-- The DataGrid declaration has been for brevity.  37:        Refer back to Listing 7.6 for details ...  --%> 

Note that the call to BindData(SortFieldName ) has been replaced with a call to PrepareBindData(FieldName , SortDirection ) on line 30 in the SortDataGrid event handler and on line 13 in the Page_Load event handler. The PrepareBindData(FieldName , SortDirection ) subroutine (lines 20 through 26) is fairly simple. It checks whether the field to sort on is the price field (line 21); if it is, a call to BindData(SortFieldName ) is made as it normally would, with "title ASC" appended to the end. This will cause our SQL statement's ORDER BY clause in BindData(SortFieldName ) to have the form

 ORDER BY price direction, title ASC 

Here direction is either "ASC" or "DESC", depending on the value of the SortDirection parameter passed in to the PrepareBindData(FieldName , SortDirection ) subroutine. If, on the other hand, we are not sorting on the Price column, we simply call BindData(SortFieldName ), as we did in Listing 7.6 (line 24). Hence, the PrepareBindData(FieldName , SortDirection ) subroutine manages to perform multi-column sorting when sorting on the Price column, and regular single-column sorting when sorting via the Title column.

Figure 7.9 illustrates the DataGrid being sorted by the Price column. Note that in instances where there are tied prices, such as Cooking with Computers and Fifty Years in Buckingham Palace Kitchens, both priced at $11.95, the books are sorted in alphabetical order. Compare this to Figure 7.6, which shows the DataGrid ordered by the Price column, but with single-column ordering. Note that in that example, the Fifty Years in Buckingham Palace Kitchens title precedes the Cooking with Computers title, showing up in reverse alphabetical order.

Figure 7.9. Books that have the same price are ordered alphabetically by their titles.

NOTE

For more information on sorting in ascending and descending order and in using multi-column sorting techniques, refer to Dino Espisito's "Effective Sorting in ASP.NET DataGrids" article mentioned in the "On the Web" section at the end of this chapter.