Extend Sorting in the GridView
Sorting in the GridView is simple to implement, as long as you want simple sorting. For more complex sorts you have to take control of the Sorting event.
In a previous column I discussed handling sorting using the ObjectDataSource. This triggered some comments by readers around sorting, which inspired this article.
To sort on any column, you must first set the GridView's AllowSorting property to True. For each column you want to 'sort-enable,' you must set the column's SortExpression property to some string expression. When a user clicks on a "sort enabled" column, the SortExpression for the column is passed to the underlying data source. For a SQLDataSource, the SortExpression is added to the DataSource's SQL statement, so the SortExpression string must be a valid SQL 'sort clause.' A valid SQL sort clause often works well with an ObjectDataSource, where the SortExpression is passed to developer-written code: The developer can just add the SortExpression to whatever SQL statement is being used to retrieve the data.
When a user clicks on a sort-enabled column a second time, the expectation is that data will be resorted in the reverse direction from the original sort. The GridView supports this (sort of!) by adding the SQL keyword "DESC" as a suffix to the SortExpression property for the clicked column.
There are at least two places where this approach generates an unfortunate result. When clicking on a column containing dates, I suspect that most of the time, most users want the column sorted in descending order, with the most recent dates at the top. You can do that by having the column's SortExpression include the DESC suffix (e.g. "BirthDate DESC"). Unfortunately, when the GridView adds its suffix on the second click, you'll get "BirthDate DESC DESC" -- not a valid SQL sort clause.
Similarly, when users click on a column containing last names, they probably expect the data to be by last name and, where there are duplicate last names, sorted by first name. Setting the SortExpression on the last name column to "LastName, FirstName" does this. On the second click, unfortunately, the SortExpression becomes "LastName, FirstName DESC." This is a valid sort clause, but it's still sorting the data in ascending order by LastName (only the first names are sorted 'descending-ly').
Fixing the Problems
The solution I use handles both problems and still produces a valid SQL sort clause. To implement my solution you'll need to add code to the GridView's Sorting event.
The e parameter passed to the Sorting event has two useful properties: SortDirection and SortExpression. If SortDirection is set to the enumerated value SortDirection.Descending, then the GridView will slap a "DESC" on the end of the SortExpression. So the first thing to do in the Sorting event is to detect that and reset the SortDirection to prevent the GridView from modifying your SortExpression:
If e.SortDirection = SortDirection.Descending Then
e.SortDirection = SortDirection.Ascending
Now that you know that the user has "second clicked" the column (that's why SortDirection is set to descending) you have to build a SortExpression that will reverse the order of the default expression. The first step is to split up the SortExpression into its separate fields after each comma, which is what this code does (if there's only one field, you'll get an array holding just the single field name):
Dim exps As String()
exps = e.SortExpression.Split(",")
Now you iterate through each of those components, adding the "DESC" keyword after the field name (and a comma where required). I use a StringBuilder to assemble the new SortExpression:
Dim finalExpression As New System.Text.StringBuilder
Dim posCount As Integer
For Each exp As String In exps
posCount += 1
finalExpression.Append(exp & " DESC")
If posCount ‹ exps.Length Then
Of course, if the field already had a "DESC" after the field name then it will now have "DESC DESC" after the field name. That's my signal that the field should now be sorted 'ascending-ly', which is the default. I can restore the sort to the default by removing the offending text. I also update the e parameter's SortExpression so that my new SortExpression is returned to the GridView:
finalExpression.Replace("DESC DESC", "").ToString
This routine will not only support 'second-clicking' for columns initially sorted 'descending-ly' and columns with multiple fields, it will also handle SortExpressions with 'mixed direction' sorts (e.g. "BirthDate DESC, LastName, FirstName") -- at least as long as you don't have any columns with commas in their titles.
Peter Vogel is a system architect and principal in PH&V Information Services. PH&V provides full-stack consulting from UX design through object modeling to database design. Peter tweets about his VSM columns with the hashtag #vogelarticles. His blog posts on user experience design can be found at http://blog.learningtree.com/tag/ui/.