In last two week's I have shown you how to implement both built-in and custom paging functionality using the ASP.NET DataGrid server control (See DataGrid Paging Part 1 and DataGrid Paging Part 2). This week I will show you how to implement column sorting using the DataGrid.

Downloadable source code including versions in both Visual Basic .NET and C# can be found here.

What Is Column Sorting?

Many times it is necessary to provide some sort of functionality enabling the user to resort a table of data based on a column in the table. For instance, in an online CD Store you may have a product catalog that lists CD's. The columns of data may include the artist name, CD title, the record label name and a price. For user convenience you want to allow the visitor to resort the table of products by any of the columns....for instance, a visitor who wants to view the list sorted alphabetically by CD title should be able to do so. The ASP.NET DataGrid server control has built-in functionality to handle this with only a small amount of code on your part.

Figure 1.1 - The DataGrid has built-in Column Sorting functionality

Bring It On - How to Sort

To use the DataGrid column sorting functionality you need to set a couple DataGrid properties, and provide the event handlers for the OnSortCommand event. The .NET Framework will track what column to sort by, but you need to provide the data access functionality to physically sort the data. The easiest way to do this is by dynamically building the SQL statement.

First things first. You need to create the basic framework for capturing and displaying the data. Like the two previous tutorials, I am going to work with the Customers table in the Northwind database of a SQL 7.0 server. Listing 1.1 shows the basic code to get and display the data.

Listing 1.1 - Getting and Displaying the Data

Break It Down

In Listing 1.1 you create a basic DataGrid to retrieve and display the Customer information from the database. Nothing to fancy here. Notice that the SQL statement (_sqlStmt) has been defined as a page-level variable. This enables access to the variable from any procedure or function on the page. You use the _sqlStmt variable in the BindData() method when creating the SqlDataAdapter. Since the _sqlStmt variable is defined as a page-level variable it is redefined and created on every page request. This will be used to your advantage when you create the OnSortCommand event handler.

To enable column sorting, much like DataGrid paging, you must set the AllowSorting property of the DataGrid to True and specify the event handler for the OnSortCommand. Listing 1.2 shows the revised DataGrid properties.

Listing 1.2 - The Revised DataGrid Properties

In Listing 1.2 you set the AllowSorting property of the DataGrid to True. This property changes all of the column header text to hyperlinks. A click on the header text hyperlink causes a post-back and a call to the OnSortCommand event handler.

In the OnSortCommand event handler you simply need to specify how the data should be sorted, recreate the data source and bind the data to the DataGrid. Currently creating the data source and the data binding are done in the BindData() method, so really all you need to do is specify how the data should be sorted before calling the BindData() method. In other words, redefine the SQL statement. Since the SQL statement is a page-level variable you have access to it in the OnSortCommand event handler. Listing 1.3 shows the OnSortCommand event handler.

Listing 1.3 - The SortCommand_OnClick() Event Handler

The name of the column is passed into the event handler as an event argument, E.SortExpression. For instance, if the user clicked on the ContactTitle column, then E.SortExpression is ContactTitle. By concatenating the SQLStmt variable with ORDER BY and the E.SortExpression value you create a SQL statement that sorts the data based on the column the user clicked on. Figure 1.2 shows the resulting page.

Figure 1.2 - Column Sorting with the DataGrid

This Is Cool, But Can I Use It With Paging?

The column sorting functionality of the DataGrid and the paging functionality can be used together. The challenge is maintaining the SortExpression information from one page to the next. As you learned in DataGrid Paging Part 1 the data source is recreated every time the used clicks a page-navigation link. To maintain the column sorting you must maintain the state of the SortExpression value. The easiest way to do this is with a hidden ASP.NET Label control. The Label control's Visible property can be set to False, hiding it from the user, but maintaining its state with ViewState. You can hold the entire SQL statement in the Label's Text property from one request to the next. There is a slight change required to the code in the previous example. Rather than using the _sqlStmt variable in the SqlDataAdapter, you need to use the Text value of the Label control. Additionally you need to set the Label's Text value when the page is first requested, and any time the OnSortCommand event handler is triggered. Listing 1.4 shows how to implement paging and sorting on the same DataGrid. See DataGrid Paging Part 1 for more information on implementing paging.

Listing 1.4 - Column Sorting and Paging Together

Figure 1.3 shows the output from Listing 1.4

Figure 1.3 - Paging and Column Sorting with the DataGrid

In Listing 1.4 you implement both paging and column sorting. This is done by maintaining the SQL statement in an invisible Label control. The state of the Label control is maintained via ViewState. Regardless of how the user has chosen to sort the DataGrid, the SQL statement from the Label control is used. When the OnSortCommand event handler is triggered, the SQL statement in the Label control is updated. This allows a user to sort the DataGrid on any column, and brows from page to page maintaining the sort order.

Summary

The DataGrid server control, in my humble opinion, is one of the most powerful server controls in the ASP.NET arsenal. Through property settings and a small amount of event handler code you can generate interactive tables of data that would have taken a lot more code in classic ASP.