Providing Simple Sorting Support in the DataGrid Web Control

Recall from Chapter 1, "An Introduction to the DataGrid, DataList, and Repeater," that the DataGrid contains an AutoGenerateColumns property that defaults to True. When AutoGenerateColumns is True, a column is added to the DataGrid control for each field in the DataSource. In the vast majority of our DataGrid examples up to this point, we've set the AutoGenerateColumns property to False and explicitly specified the columns we want to appear in our DataGrid using BoundColumns and TemplateColumns.

The steps required for providing sorting support for a DataGrid whose AutoGenerateColumns property is set to True differs from the steps required for a DataGrid whose AutoGenerateColumns property is set to False. In the next two subsections, we will examine sorting a DataGrid whose AutoGenerateColumns property is set to True (the default). In the "Sorting a DataGrid with AutoGenerateColumns Set to False" section, we'll look at how to provide sorting capabilities to a DataGrid whose AutoGenerateColumns property is set to False.

Using the DataGrid's AllowSorting Property

Providing sorting support in a DataGrid whose AutoGenerateColumns property is set to True is fairly straightforward, requiring the following three steps:

1. Set the DataGrid's AllowSorting property to True.

2. Provide an event handler for the DataGrid's SortCommand event. This event handler should obtain the data in the properly sorted order and rebind the data to the DataGrid.

3. Wire up the DataGrid's SortCommand event to the event handler you authored in step 2.

Recall that the typical DataGrid displays the heading of each column as a simple textual label, displaying the name of the DataSource field that the column represents. However, when providing sorting support, we want each column's textual header to be converted into a hyperlink, so that when a particular row's header's hyperlink is clicked, the DataGrid's contents are sorted by that particular column's values.

Converting the DataGrid's column headers from textual labels to hyperlinks is quite simple: All we have to do is set the AllowSorting property to True. When the AutoGenerateColumns property is set to True, setting the AllowSorting property to True will convert the DataGrid's column header portion to a HyperLink command button. Listing 7.1 illustrates this concept.

Listing 7.1 When AllowSorting Is Set to True, the DataGrid's Column Headers Become Hyperlinks

  1: <%@ import Namespace="System.Data" %>   2: <%@ import Namespace="System.Data.SqlClient" %>   3: <script runat="server" language="VB">   4:   5:     Sub Page_Load(sender as Object, e as EventArgs)   6:       If Not Page.IsPostBack then   7:         BindData()   8:       End If   9:     End Sub  10:  11:  12:     Sub BindData()  13:       '1. Create a connection  14:        Const strConnString as String = "server=localhost;uid=sa;pwd=; database=pubs"  15:        Dim objConn as New SqlConnection(strConnString)  16:  17:       '2. Create a command object for the query  18:       Const strSQL as String = "SELECT title, price, pubdate FROM titles"   19:       Dim objCmd as New SqlCommand(strSQL, objConn)  20:  21:       objConn.Open()   'Open the connection  22:  23:       'Finally, specify the DataSource and call DataBind()  24:       dgTitles.DataSource = objCmd.ExecuteReader(CommandBehavior. CloseConnection)  25:       dgTitles.DataBind()  26:  27:       objConn.Close()   'Close the connection  28:     End Sub  29:  30: </script>  31: <form runat="server">  32:   <asp:DataGrid runat="server"   33:       Font-Name="Verdana" Font-Size="9pt" CellPadding="5"  34:       AlternatingItemStyle-BackColor="#dddddd"  35:       AllowSorting="True" AutoGenerateColumns="True">  36:  37:     <HeaderStyle BackColor="Navy" ForeColor="White" Font-Size="13pt"  38:               Font-Bold="True" HorizontalAlign="Center" />  39:   </asp:DataGrid>  40: </form> 

The key line in Listing 7.1 is line 35, where the DataGrid's AllowSorting and AutoGenerateColumns DataGrid properties are set to True.

NOTE

We didn't need to explicitly set the AutoGenerateColumns property to True; rather, we could have simply omitted the property from the DataGrid's declaration, as it defaults to True.

By simply setting the AllowSorting and AutoGenerateColumns DataGrid properties to True, the default header labels for each DataGrid column are turned into HyperLink command buttons. Figure 7.1 displays a screenshot of Listing 7.1 when viewed through a browser. Note that each DataGrid column header is underlined, signifying a hyperlink.

Figure 7.1. With AllowSorting set to True, hyperlinks are displayed in the headers of each DataGrid column.

To implement sorting in a DataGrid whose AutoGenerateColumns property is set to True, we need to perform the following steps:

1. Set the DataGrid's AllowSorting property to True.

2. Provide an event handler for the DataGrid's SortCommand event. This event handler should obtain the data in the properly sorted order and rebind the data to the DataGrid.

3. Wire up the DataGrid's SortCommand event to the event handler you authored in step 2.

Listing 7.1 accomplishes step 1. At this point, if a user clicks on one of these hyperlinks, the ASP.NET page is posted back and the DataGrid's SortCommand event is raised. Because we have yet to complete parts 2 and 3, nothing happens from the user's perspective. Now we need to tackle part 2: writing an event handler for the SortCommand event.

Writing a SortCommand Event Handler

To actually have anything happen when a DataGrid column's sort header hyperlink is clicked, we need to provide a SortCommand event handler. This event handler will fire every time one of the DataGrid's column's sort header hyperlinks is clicked.

The SortCommand event handler must have the following signature:

 Sub EventHandlerName(sender as Object, e as DataGridSortCommandEventArgs) 

In this event handler, we need to reissue our SQL query using an appropriate ORDER BY clause. This new SQL query should then be used to populate a DataSet or DataReader, which is then set to our DataGrid's DataSource property. Finally, we need to rebind the DataGrid by calling its DataBind() method.

NOTE

Each time a user clicks on one of the DataGrid's columns' sort hyperlinks to sort the DataGrid, the DataSource must be adjusted so that its contents are correctly sorted, and the DataGrid must be rebound to the DataSource. Typically, re-sorting the DataSource requires that the SQL query used to populate the DataSource be reissued. However, in the section "Caching the DataGrid's DataSource at the Page-Level," we will discuss how to cache the DataSource so that re-sorting the DataSource does not require an additional database query.

To accomplish this, it is clear that in the event handler we'll need some way of determining what DataGrid column was clicked. To aid with this need, the DataGridColumn class, which abstractly defines a DataGrid's column, contains a SortExpression property. When a particular DataGrid column's header's sort hyperlink is clicked, the DataGrid columns SortExpression value is passed along to the SortCommand event handler. To facilitate this, the DataGridSortCommandEventArgs class contains a SortExpression property that contains this value.

With the AutoGenerateColumns property set to True, each column has its SortExpression property automatically set to the name of the DataSource field the column represents. In Listing 7.1, the SortExpression value for the price DataGrid column is price, while the SortExpression value for the pubdate column is pubdate.

Hence, our SortCommand event handler will have to do the following:

1. Create a SQL query whose ORDER BY clause is determined by the SortExpression property of the event handler's DataGridSortCommandEventArgs parameter.

2. Populate a DataSet or DataReader with the SQL query from step 1.

3. Set the DataGrid's DataSource property to the DataSet or DataReader generated in step 2.

4. Call the DataGrid's DataBind() method.

It should be clear that the tasks our SortCommand event handler needs to perform are nearly identical to those performed by the BindData() subroutine (see lines 12 28 in Listing 7.1). Specifically, the BindData() subroutine in Listing 7.1 performs steps 2, 3, and 4, but not step 1; that is, the BindData() subroutine does not contain a variable ORDER BY clause. However, if we make some small changes to our BindData() subroutine so that it does perform step 1, our SortCommand event handler will be quite simple indeed all it will need to do is defer to the BindData() subroutine.

To accomplish this, our BindData() subroutine will have to take as an input parameter a string indicating the name of the database field that we want to order our SQL results upon. Listing 7.2 contains the new code for our BindData(SortFieldName ) subroutine.

Listing 7.2 The BindData(SortFieldName) Subroutine Sorts the SQL Results by a Specified Field Name

  1:   Sub BindData(SortFieldName as String)   2:     '1. Create a connection   3:      Const strConnString as String = "server=localhost;uid=sa;pwd=; database=pubs"   4:      Dim objConn as New SqlConnection(strConnString)   5:   6:     '2. Create a command object for the query   7:     Dim strSQL as String   8:     strSQL = "SELECT title, price, pubdate " & _   9:              "FROM titles " & _  10:              "ORDER BY " & SortFieldName  11:  12:     Dim objCmd as New SqlCommand(strSQL, objConn)  13:  14:     objConn.Open()   'Open the connection  15:  16:     'Finally, specify the DataSource and call DataBind()  17:     dgTitles.DataSource = objCmd.ExecuteReader(CommandBehavior. CloseConnection)  18:     dgTitles.DataBind()  19:  20:     objConn.Close()   'Close the connection  21:   End Sub 

Note that on line 1 the definition of the BindData(SortFieldName) subroutine has been altered to accept a string input parameter SortFieldName, which is the name of the DataSource field that we want to sort the results on. To accomplish this, the SQL statement (lines 8 through 10) adds an ORDER BY clause, with the order based upon the value of the string variable SortFieldName (line 10). The remainder of the code in the BindData(SortFieldName) subroutine is the same as it was in Listing 7.1.

With the new BindData(SortFieldName) subroutine, our SortCommand event handler becomes one single line of code we simply need to call BindData(SortFieldName ), passing in the value of the SortExpression of the DataGridSortCommandEventArgs parameter. Listing 7.3 contains the complete source code needed to provide a sortable DataGrid.

Listing 7.3 With the Modified BindData(SortFieldName) Subroutine, the SortCommand Event Handler Is Quite Simple

  1: <%@ import Namespace="System.Data" %>   2: <%@ import Namespace="System.Data.SqlClient" %>   3: <script runat="server" language="VB">   4:    ' The BindData(SortFieldName) subroutine has been omitted for brevity ...   5:    ' ... see Listing 7.2 for details ...   6:   7:     Sub Page_Load(sender as Object, e as EventArgs)   8:       If Not Page.IsPostBack then   9:         BindData("title")  10:       End If  11:     End Sub  12:  13:  14:     Sub SortDataGrid(sender as Object, e as DataGridSortCommandEventArgs)  15:       BindData(e.SortExpression)  16:     End Sub  17: </script>  18:  19: <form runat="server">  20:   <asp:DataGrid runat="server"   21:       Font-Name="Verdana" Font-Size="9pt" CellPadding="5"  22:       AlternatingItemStyle-BackColor="#dddddd"  23:       AllowSorting="True" AutoGenerateColumns="True"  24:       OnSortCommand="SortDataGrid">  25:  26:     <HeaderStyle BackColor="Navy" ForeColor="White" Font-Size="13pt"  27:               Font-Bold="True" HorizontalAlign="Center" />  28:   </asp:DataGrid>  29: </form> 

There are just three lines of code in Listing 7.3 that are worthy of our attention. The first is line 9, where we call the BindData(SortFieldName) subroutine on the first page load. Note that here we pass in "title" as the SortFieldName parameter, which initially sorts the DataGrid by the title of the books. We could have provided a different titles field name, but, with the way we coded BindData(SortFieldName ), we would need to provide some field name.

The second is the SortCommand event handler's code on line 15. Note that with our new BindData(SortFieldName) subroutine, our SortCommand event handler only needs this one line of code. It simply calls BindData(SortFieldName ), passing in the SortExpression value of the DataGrid whose column sort header was clicked. Because the SortExpression of each DataGrid column is set equal to the DataSource field name whose data fills the column, we can simply pass in this SortExpression property into the BindData(SortFieldName) subroutine.

The final line of code to examine is line 24. This ties the DataGrid's SortCommand event to the SortDataGrid event handler (lines 14 through 16).

Figures 7.2 and 7.3 illustrate Listing 7.3 in action. Figure 7.2 shows the DataGrid when the page is first loaded. Note that the DataGrid is sorted alphabetically by the books' titles. Figure 7.3 is the output when the Price column sort hyperlink is clicked. Note that the DataGrid is sorted by price, from the lowest to the highest values.

Figure 7.2. The initial ordering of the DataGrid is by the title field.

Figure 7.3. When the Price column's sort header hyperlink is clicked, the DataGrid's data is ordered by the price field.

Sorting a DataGrid with AutoGenerateColumns Set to False

As we saw in the last two sections, when both the AutoGenerateColumns and AllowSorting properties are set to True, the DataGrid automatically converts every field in the DataSource into a column in the DataGrid, making each column sortable by converting the column's header from a textual label to a hyperlink. Although this is very handy, there are instances when we want to specify the columns to appear in our DataGrid through the use of BoundColumn controls. By using BoundColumn controls, we can specify things like what fields of the DataSource should appear in the columns of the DataGrid (as well as in what order), and the display properties for each column, such as background color, horizontal alignment, and formatting. With these advantages, it is clear that there are times when we'd like to provide sorting support to a DataGrid that has its AllowSorting property set to True, but its AutoGenerateColumns property set to False.

When both the AutoGenerateColumns and AllowSorting properties are set to True, the SortExpression property of each DataGrid column is automatically set to the DataSource's field name that the column represents. When we want to provide sorting support to a DataGrid whose AutoGenerateColumns is set to False, we need to explicitly provide the SortExpression property. All the column types derived from the DataGridColumn class contain the SortExpression property. The column types, which we've examined in previous chapters, include BoundColumn, ButtonColumn, HyperLinkColumn, and TemplateColumn.

Listing 7.4 illustrates how to provide sorting for a DataGrid whose AutoGenerateColumns property is set to True. The server-side script block in Listing 7.4 has been omitted for brevity it is identical to that in Listing 7.3.

Listing 7.4 A DataGrid Whose AutoGenerateColumns Property Is Set to True Can Be Sorted as Well

  1: <form runat="server">   2:   <asp:DataGrid runat="server"    3:       Font-Name="Verdana" Font-Size="9pt" CellPadding="5"   4:       AlternatingItemStyle-BackColor="#dddddd"   5:       AllowSorting="True" AutoGenerateColumns="False"   6:       OnSortCommand="SortDataGrid">   7:   8:     <HeaderStyle BackColor="Navy" ForeColor="White" Font-Size="13pt"   9:               Font-Bold="True" HorizontalAlign="Center" />  10:  11:     <Columns>  12:       <asp:BoundColumn DataField="title" HeaderText="Title"  13:               SortExpression="title" />  14:       <asp:BoundColumn DataField="price" HeaderText="Price"   15:               SortExpression="price" DataFormatString="{0:c}"  16:               ItemStyle-HorizontalAlign="Right" />  17:       <asp:BoundColumn DataField="pubdate" HeaderText="Published Date" />  18:     </Columns>  19:   </asp:DataGrid>  20: </form> 

The first thing to note is that the AutoGenerateColumns property has been set to False, and the AllowSorting property has been set to True (line 5). Additionally, the DataGrid's SortCommand event has been wired up to the SortDataGrid event handler on line 6. To see the code for the SortDataGrid event handler, refer back to Listing 7.3, lines 14 16.

The DataGrid's columns are specified on lines 12 through 17. Note that the first two BoundColumn controls the Title and Price columns, lines 12 through 16 contain a SortExpression property that has been set to the DataSource field name the column represents. However, the third BoundColumn, Published Date (line 17), does not have a SortExpression property. When setting AutoGenerateColumns to False, you can specify which columns are sortable: To make a column sortable, simply provide a SortExpression property value; to make a column unsortable, simply omit the SortExpression property from the BoundColumn.

Figure 7.4 illustrates this concept. Note that the Published Date column's header is a text label and not a hyperlink, whereas the Title and Price columns' headers are indeed hyperlinks. This makes both the Title and Price columns sortable, and the Published Date column unsortable.

Figure 7.4. Lacking the SortExpression property, the Published Date column is unsortable.

Note that the SortExpression property values for the Title and Price columns are equivalent to the DataSource field name the column represents. This is important because our BindData(SortFieldName) subroutine expects as its input parameter the specific DataSource field name that the results are to be ordered by. Hence, if we give the Title column a SortExpression value of, say, TitleSort, we would get a runtime error when trying to sort the Title column because the BindData(SortFieldName) subroutine would be trying to execute the following SQL statement:

 SELECT title, price, pubdate  FROM titles  ORDER BY TitleSort 

Because TitleSort is not the name of a field in the SELECT clause, SQL Server will raise an error. Of course, we could set the Title column's SortExpression property to something like TitleSort and then alter our SortDataGrid event handler to check the SortExpression property and, based on the value, call the BindData(SortFieldName) subroutine with the appropriate field name. This might look like

 Sub SortDataGrid(sender as Object, e as DataGridSortCommandEventArgs)     If e.SortExpression = "TitleSort" then        BindData("title")     ElseIf e.SortExpression = "PriceSort" then       BindData("price")     End If  End Sub 

This approach seems overly verbose and anything but robust. Clearly, the cleanest method is to just use the approach shown in Listing 7.4, where the SortDataGrid event handler is one line of code and the BoundColumns' SortExpression properties are set to the DataSource field name they represent.

NOTE

For more information on the basics of sorting a DataGrid's contents, be sure to check out the article "An Extensive Examination of the DataGrid Web Control: Part 4" listed in the "On the Web" section at the end of this chapter.