An Extensive Examination of the DataGrid Web Control: Part 12

The Twelfth Part in a Multi-Part Series

This article is the twelfth piece of a multi-part series on using the DataGrid Web control that will span several months. The ASP.NET DataGrid Web control, which displays database information in an HTML table, is highly versatile. The basics of the DataGrid were discussed in Part 1; information on specifying display properties of the DataGrid was discussed in Part 2. In Part 3 we examined how to associate custom events with the DataGrid. In Part 4 we looked at how to extend Part 3 to provide custom sorting on the results of a DataGrid. In Part 5 we examined how to use templates to further customize the DataGrid's appearance. Part 6 examined how to use the DataGrid's built-in editing capabilities, while Part 7 looked at customizing the editing interface using the EditItemTemplate. In Part 8 we looked at how to add client-side code to a ButtonColumn's client-side onclick event. In Part 9 we examined how to enhance the DataGrid's editing interface by having the editing interface's TextBox receive focus when the page is loaded. In Part 10 we looked at how to (automatically) add filtering buttons so that one can filter the data in a DataGrid. In Part 11 we examined how to create a DataGrid Web control with a column of related radio buttons. In this twelfth part, written by Kishore Ramanathan, we'll examine how to create a sortable DataGrid that can be sorted in ascending and descending order. Read Part 13 Read Part 14 Read Part 15 Read Part 16 Read Part 17 Read Part 18

Introduction

One of the most common user interface requirements is to be able to click on a column header and have the data toggle between being sorted in ascending order and descending order. In this article we will examine how to extend the DataGrid so that it can remember the "current sort state" of each column. Then, when the user clicks on a column the current sort order can be detected and toggled.

The example used in the article displays attributes from the authors table in the pubs database. This article is divided into the following sections:

1. HTML code declaring the DataGrid used for displaying authors details
2. The subroutine used for populating the DataGrid
3. The sort handler which remembers the sort state and sorts the DataGrid in the requisite order.

For More Information on Bi-Directional Sortable DataGrids...
This part of the article series looks at one means to create a bi-directional sortable DataGrid. This same topic is re-addressed in Part 18 of this article series, examining (in my opinion) a better way to accomplish bi-directional sorting. Additionally, Part 18 shows how to gussy up the bi-directional sortable DataGrid, adding an up or down arrow to the column the data is sorted by.

Declaring the DataGrid and Displaying the Contents of the authors Table

The following code creates the DataGrid and displays the appropriate data:

<%@ Import Namespace = "System.Data" %> <%@ Import Namespace = "System.Data.SQLClient" %> <script language="VB" runat="server"> Sub Page_Load(sender As Object, e As EventArgs) ' Only bind the data on the first visit to the page If Not Page.IsPostBack GetAuthors("au_fname asc") End If End Sub Sub GetAuthors(sSortStr as string) Dim cn as SQLConnection Dim cmd as SQLCommand Dim rdr as SQLDataReader Dim sConnectString as String = "server=localhost;database=pubs;user id=sa" Dim sSql as String = "SELECT au_fname, au_lname, city, state, zip " & _ "from authors order by " & sSortStr ' Connect to the database cn = New SQLConnection(sConnectString) cn.open() ' execute the SQL cmd = New SQLCommand(sSQL, cn) rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection) ' Associate the data grid with the data DispAuthors.DataSource = rdr DispAuthors.DataBind() End Sub ' this method is called when user clicks on any of the column headings Sub SortAuthors(sender As Object, e As DataGridSortCommandEventArgs) '... Code coming soon! :-) ... End Sub </script> <form runat="server"> <ASP:Datagrid id="DispAuthors" runat="server" AutoGenerateColumns="False" AllowSorting="true" onSortCommand="SortAuthors"> ... <Columns> <asp:BoundColumn DataField="au_fname" SortExpression="au_fname ASC" HeaderText="First Name" /> <asp:BoundColumn DataField="au_lname" SortExpression="au_lname" HeaderText="Last Name" /> <asp:BoundColumn DataField="city" HeaderText="City" /> <asp:BoundColumn DataField="state" SortExpression="state" HeaderText="State" /> <asp:BoundColumn DataField="zip" HeaderText="Zip" /> </Columns> </asp:datagrid> </form>

[View a Live Demo!]

The HTML code defines a datagrid, DispAuthors, that displays authors information from the pubs database. The columns shown are author's first and last names, city, state and zip code.

Two important things to notice with the above source code:

1. SortAuthors is the event handler invoked when user clicks on a column header. The source code for this event handler has yet to be provided - we'll be examining the needed code soon.
2. Only the first name, last name and state columns have their SortExpression property specified. This means that only these columns are sortable. Also note that the SortExpression property, along with the column name to sort, also contains information on the sort order (i.e., the First Name column's SortExpression property is au_fname ASC).

The GetAuthors() subroutine retrieves the data to be displayed in the DataGrid. This method takes in a string parameter, sSortStr, which specifies how the data from the authors table should be sorted. For example, to sort the results by the au_fname column in ascending order, the argument to GetAuthors() would be au_fname asc. Notice that when the page is first visited, the Page_Load event handler calls GetAuthors() passing in au_fname asc; hence, when the page is first visited, the data will be displayed alphabetically by the authors' first names.

Writing the Code for the DataGrid's Sort Event Handler

In the previous code example, you may have noticed that the SortExpression property for the First Name column indicated the order of the sorting. Specifically, the First Name column's SortExpression property was set to au_fname ASC. This demonstrates how the SortExpression property can be set so that it not only conveys the column to sort, but also the sort direction.

Let's take a look at the code for the SortAuthors event handler, after which we'll discuss what, specifically, the code does.

Sub SortAuthors(sender As Object, e As DataGridSortCommandEventArgs) Dim SortExprs() As String Dim CurrentSearchMode As String, NewSearchMode As String Dim ColumnToSort As String, NewSortExpr as String ' Parse the sort expression - delimiter space SortExprs = Split(e.SortExpression, " ") ColumnToSort = SortExprs(0) ' If a sort order is specified get it, else default is descending If SortExprs.Length() > 1 Then CurrentSearchMode = SortExprs(1).ToUpper() If CurrentSearchMode = "ASC" Then NewSearchMode = "Desc" Else NewSearchMode = "Asc" End If Else ' If no mode specified, Default is descending NewSearchMode = "Desc" End If ' Derive the new sort expression. NewSortExpr = ColumnToSort & " " & NewSearchMode ' Figure out the column index Dim iIndex As Integer Select Case ColumnToSort.toUpper() case "AU_FNAME" iIndex = 0 case "AU_LNAME" iIndex = 1 case "STATE" iIndex = 3 End Select ' alter the column's sort expression DispAuthors.Columns(iIndex).SortExpression = NewSortExpr ' Sort the data in new order GetAuthors(NewSortExpr) End Sub

[View a Live Demo!]

The SortAuthors event handler starts by splitting the SortExpression property on a space. This returns a string array with either one or two elements - one element if the SortExpression property has just the column name to sort on, like au_lname, and two elements if the SortExpression property has both the column name and the direction, like au_fname ASC. In the one-element case, the one element contains the name of the column to sort on; in the two-element case, the first element contains the column to sort on, while the second element specifies the direction.

Next, the second element (if it exists) is examined and then toggled. This has the effect of if the column was sorted in ascending order the previous time, this time it will be sorted in descending order, and vice-a-versa. This toggled value becomes the new SortExpression property for the column that was clicked. Finally, a call to GetAuthors(), passing in the new sort expression, displays the sorted data.

One Caveat...

One thing you need to be aware of here is the Select Case statement, which is used to determine what DataGrid column's sortable header was clicked. Since this Select Case statement contains the various column named hard-coded in there, if you add a new sortable column to the DataGrid, or change the order of the sortable columns in the DataGrid, you will need to update this code. An alternative approach, using the ViewState, alleviates this dependence. For more information on using the ViewState to allow for a DataGrid that provides ascending and descending sortable columns, pick up a copy of my (Scott Mitchell) book: ASP.NET Data Web Controls Kick Start. This ViewState approach is also discussed in more detail in Part 18 of this article series.

Conclusion

Happy Programming!