Binding Columns

Sorting Columns

The DataGrid control does not actually sort rows, but it provides good support for sorting as long as the sorting capabilities of the underlying data source are adequate. The data source is always responsible for returning a sorted set of records based on the sort expression selected by the user through the DataGrid control s user interface. The built-in sorting mechanism is triggered by setting the AllowSorting property to true.

<asp:datagrid runat="server"  AllowSorting="True" OnSortCommand="SortCommand">

When AllowSorting is set to true, the DataGrid control offers a link button for rendering the column caption. When users click one of these sortable columns, the SortCommand event is fired. In the body of the SortCommand event handler, you have to refresh the contents of the DataGrid control so that it reflects the requested sort.

Setting Up Sorting

You make a column sortable by setting the SortExpression property. You can set it declaratively in the ASP.NET page layout or you can set it programmatically. SortExpression evaluates to any expression that the data source understands as a sort command. Typically, SortExpression is the name of a field in the data source. If needed, though, you can sort by multiple fields by separating them with a comma. You can specify DESC (or DESCENDING) and ASC (or ASCENDING) to indicate a direction for sorting. By default, sorting is ascending.

<asp:BoundColumn runat="server" DataField="lastname" HeaderText="Last Name" SortExpression="lastname, firstname" /> <asp:BoundColumn runat="server" DataField="country" HeaderText="Last Name" SortExpression="country DESC, lastname, firstname" />

After you set up the columns to sort by and the relative expressions, you d better take care of the SortCommand event handler. The typical logic for this handler is to sort the list of items and then rebind the data to the DataGrid control.

void SortCommand(Object sender, DataGridSortCommandEventArgs e) { ViewState["SortExpression"] = e.SortExpression; UpdateDataView(); }

The SortCommand event handler knows about the sort expression through the SortExpression property, which is provided by the DataGridSortCommandEventArgs class. In the preceding code, the sort information is persisted because it is stored in a slot in the page s ViewState collection. The current sort expression, in fact, is yet another piece of information that the DataGrid control does not automatically persist in the control s view state. If you need it to be persistent, you must do the work yourself. Figure 2-11 shows sorting in action. The full source code for the SortColumns.aspx application is available on the companion CD.

Figure 2-11

A DataGrid control with sortable columns.

The DataGrid control in Figure 2-11 shows rows sorted by the lastname field, but you can t figure that out easily just by looking at the grid, even with such simple data. You would probably want to modify the DataGrid control s user interface to indicate the column used for sorting.

Auto-Reverse Sorting

The typical way to identify the sorted column is by means of a little glyph in the header. This glyph also reflects the direction of the sort (ascending or descending). To add such a glyph to the column header, you again need to use our old acquaintance ItemCreated and look up the Header item type. A marker that quickly identifies the sort column makes especially good sense when you implement an auto-reverse sort. In an auto-reverse sort, the column reverses the direction of the data when the user clicks the marker twice in sequence. In this situation, persisting the current sort expression across multiple page requests is vital. To implement the auto-reverse sorting feature, the SortCommand handler changes slightly, as follows:

void SortCommand(Object sender, DataGridSortCommandEventArgs e) { // Caches the current information String strSortBy = (String) ViewState["SortExpression"]; String strSortAscending = (String) ViewState["SortAscending"]; // Sets the new sort expression ViewState["SortExpression"] = e.SortExpression; // If you click on the sorted column, the order reverses if (e.SortExpression == strSortBy) ViewState["SortAscending"] = (strSortAscending=="yes" ? "no" :"yes"); else // Defaults to ascending order ViewState["SortAscending"] = "yes"; UpdateDataView(); }

The DataGrid control now stores two pieces of information in ViewState: the sort expression and a yes/no flag that indicates whether or not the direction is ascending. When the sort expression of the column just clicked matches the current sort expression, the same column has been clicked twice consecutively, and the order reverses. Note that this procedure has not performed the actual sorting. The ViewState collection has just been holding up-to-date information about how to sort data.

Sorting would not be possible without the help of the ADO.NET DataView class. The DataView class is a view class built on top of a DataTable class. It enables filtering and sorting. You can obtain a default, unfiltered, and unsorted view object from any DataTable object simply by accessing the DefaultView property. Once you have a DataView object, you just set the Sort property, assign it to the DataGrid control, and rebind.

void UpdateDataView() { DataSet ds = (DataSet) Session["MyDataSet"]; DataView dv = ds.Tables["MyList"].DefaultView; // Apply sort information to the view dv.Sort = (String) ViewState["SortExpression"]; if (ViewState["SortAscending"] == "no") dv.Sort += " DESC"; // Rebind data grid.DataSource = dv; grid.DataBind(); }

The Sort property is set with the sort expression taken from ViewState. Then the sort expression is appended with DESC if the required order is descending. By default, the DataView class sorts in ascending order.

	If you don t use the DataView object to sort, both the sort expression and the use of DESC and ASC can be radically different, depending on the query syntax of the database you use for sorting. Although I don t wholeheartedly recommend requerying the database directly for performance reasons, using it to sort data is still a valid option.

Notice that in our code example, I used the Session object to persist on the Web server the DataGrid control s data source. Later in the chapter, I ll review the various options available for data persistence.

Marking the Sort Column

To add a glyph near the column caption, you handle the ItemCreated event and look for an element type of ListItemType.Header. The ItemCreated event fires when the grid heading has been completely set up. To locate the column to mark, you have no other choice but to loop through the Columns collection.

if (elemType == ListItemType.Header) { String strSortBy = (String) ViewState["SortExpression"]; String strSortAscending = (String) ViewState["SortAscending"]; String strOrder = (strSortAscending=="yes" ? " 5" : " 6"); for (int i=0; i<grid.Columns.Count; i++) { // Draw the glyph to reflect sorting if (strSortBy == grid.Columns[i].SortExpression) { TableCell cell = e.Item.Cells[i]; Label lblSorted = new Label(); lblSorted.Font.Name = "webdings"; lblSorted.Font.Size = FontUnit.XSmall; lblSorted.Text = strOrder; cell.Controls.Add(lblSorted); } } }

The column to mark is the column whose SortExpression matches the current sort expression. When the column is found, you create a new label control, set its font to Webdings, select the font size and text (usually the fifth and the sixth characters), and finally add the label to the table cell that hosts the column heading. Figure 2-12 shows the results. The full source code for the AutoReverse.aspx application is available on the companion CD.

Figure 2-12

A DataGrid control with the auto-reverse sort feature.

Despite appearances, the code in AutoReverse.aspx has severe drawbacks, not the least of which is that it assumes you never use ASC in the sort expression to indicate ascending order. I will explain what else is wrong with the code in the next section.

Sorting Multiple Fields

Auto-reverse sorting, at least as we ve implemented it so far, poses a serious issue when you use it with expressions that involve multiple columns. Suppose that in a database of products you have a column sorted by unitprice, productname. When the user reverses the direction, the user expects to sort by this:

unitprice DESC, productname DESC

The grid does sort the data correctly in ascending mode, but when the user reverses the order, the sort expression becomes rather anomalous and clearly wrong:

unitprice, productname DESC

The incriminating code is located in the UpdateDataView subroutine and is as follows:

if (ViewState["SortAscending"] == "no") dv.Sort += " DESC";

The code clearly assumes that the sort expression is composed of exactly one field, even without a trailing ASC sort order. Let s see how to fix it to fully support both auto-reverse and any valid sort expression with as many fields and sort orderings as you need.

Auto-Reverse Sorting for Multiple Columns

To work around the insidious bug I just mentioned, you need to step back from the details of sorting and look at the bigger picture. In particular, you need to keep separate the fields to sort by and their respective directions. A simple string comparison between the clicked columns and the current sort expression is insufficient to determine whether the order has to be reversed. Due to the sorting syntax supported by DataView objects and the majority of database systems, the sort expression must have direction information embedded. On the other hand, auto-reverse just means that if the fields involved in the sorting are the same, you change their order. This clearly implies a separation between fields and directions.

When the SortCommand event occurs, you process the sort information, taking into account auto-reverse sorting. The sort expression is composed from two comma-separated strings that are both stored in ViewState. The first string contains the names of the columns involved in the sorting. In the simplest case, this string contains a single field name, as in the preceding example. The second string contains, for each column, the sort order direction. The ASC sort order is always used whether or not you specified it in the sort expression. You modify your SortCommand event handler as follows:

void SortCommand(Object s, DataGridSortCommandEventArgs e) { // Processes the sort expression. Determines whether auto-reverse is // needed and stores in ViewState two comma-separated strings. One // contains the names of the involved columns and one contains in the // same order the respective sort order directions. ProcessSortExpression(e.SortExpression); // Refreshes the view UpdateDataView(); }

The ProcessSortExpression routine performs boilerplate code that stores in ViewState a comma-separated string with the names of the fields involved in the sort, and a comma-separated string with the order required for each field by the current state of the grid (auto-reverse). The former string uses a slot named SortingFields, and the latter string uses a slot named SortingOrders. (Of course, these names are totally arbitrary.) ProcessSortExpression also fills a third slot, named ColumnSortExpression, that contains the original sort expression associated with the clicked column, that is, e.SortExpression. This information will be useful later when you add a glyph to the column s header.

When the grid is going to be refreshed, you process the information stored in ViewState and end up with a tailor-made string that the DataView object can easily understand.

void UpdateDataView() { DataSet ds = (DataSet) Session["MyDataSet"]; DataView dv = ds.Tables["MyList"].DefaultView; // Apply sort information to the view dv.Sort = PrepareSortExpression(); // Rebind data grid.DataSource = dv; grid.DataBind(); }

The PrepareSortExpression routine retrieves from ViewState the information about sorting fields and orders, and it merges that information in a string that the DataView object knows how to process. The following is a sample string:

unitprice asc, productname desc

This final sort expression is also explicitly stored in ViewState in light of another future enhancement that I ll discuss in a moment. When you add the glyph, you recognize the column that is used to sort the DataGrid control by comparing the column s sort expression to the string stored in ViewState ["ColumnSortExpression"]. Next, you get the right WebDing character (indicating ascending or descending order) based on the order requested for the primary column in the sort expression. If a column has to be sorted by unitprice, productname, you might want to consider the order of only the first primary column to decide which glyph to display. Figure 2-13 shows that multicolumn, auto-reverse sorting now is working just fine.

Figure 2-13

A DataGrid control that supports multicolumn, auto-reverse sorting.

Showing Sorting Information

Showing a little bitmap in the column heading helps users understand how the column is sorted, but users of more sophisticated applications might find this contrivance insufficient. When you need to reveal more information, you can provide a tooltip that shows the column s sort expression. In this tooltip, you can also distinguish between the currently sorted columns and all the other sortable ones. Let s see how to accomplish this, bearing in mind that you can use the next trick to show any type of information, not just information about sorting.

You add a tooltip to an ASP.NET control by setting its ToolTip property. The tooltip cannot be added to the column as a whole; instead it must be defined only for the link button used to render the caption. To get your hands on this control, once again you have to resort to the ItemCreated event handler.

if (elemType == ListItemType.Header) { for (int i=0; i<grid.Columns.Count; i++) { // Grab the cell with the link button TableCell cell = e.Item.Cells[i]; // Add a tooltip with the sort expression if (grid.Columns[i].SortExpression != "") cell.ToolTip = "Sort by: " + grid.Columns[i].SortExpression; // Draw the glyph to reflect sorting String strSortBy = (String)ViewState["ColumnSortExpression"]; if (strSortBy == grid.Columns[i].SortExpression) { cell.ToolTip = "Sorted by: " + ViewState["ColumnSortExpression"]ToString(); // Code that draws the glyph ... } } }

Figure 2-14 shows the final result. The full source code for the MfieldSorting.aspx application is available on the companion CD.

Figure 2-14

Tooltips let users know about the sort expression of each column.

When you use custom pagination, you are responsible for providing all the records that fit in every page. This task is even more complex if you have to provide sorting too.