1.11.1 Problem You are displaying a table of data and you want to let the user sort the data in a DataGrid by clicking on its column headers. 1.11.2 Solution Enable the DataGrid control's sorting features, and create a routine that binds the appropriate data to the control when it is initially displayed and whenever the user clicks a column header. In the .aspx file, enable the DataGrid control's sorting features. In the code-behind class for the page, use the .NET language of your choice to: -
Create a data-binding method ( bindData in our example) that performs the actual sorting based on the value of a sortExpression parameter and binds a dataset to the DataGrid (this parameter is used in the ORDER BY clause of the SQL statement). -
Call the data-binding method from the Page_Load method (to support the initial display of the grid) and from the event that is fired when the user clicks on a column header (the dgBooks_SortCommand event in our example). Figure 1-12 shows the appearance of a typical DataGrid sorted by title, the information in the first column. Example 1-30 through Example 1-32 show the .aspx and code-behind files for an example application that produces this result. Figure 1-12. DataGrid with column sorting output 
1.11.3 Discussion The DataGrid control provides the basic plumbing required to support sorting. It will generate the links for the column headers that will raise the SortCommand server-side event when a column header is clicked. The DataGrid does not provide the code required to perform the actual sorting, but very little code is required to complete that job. To enable sorting, the AllowSorting attribute of the DataGrid element must be set to True . In addition, the SortExpression attribute of the BoundColumn element must be set to the expression that will be used in your code to perform the sorting. This would normally be set to the name of the database column displayed in the DataGrid column; however, it can be set to any value required by your code to perform the sorting. Your code will need to perform the actual sorting. For example, the application that we developed for this recipe supports sorting in a centralized manner by using a sortExpression parameter with its bindData method. This parameter is used in the ORDER BY clause of the SQL statement. The bindData method is called from two places: -
In the Page_Load method to support the initial display of the grid. The value " Title " is passed to provide the default sorting by title. -
From the dgBooks_SortCommand method. This method is called when the user clicks on a column header. The e argument contains the SortExpression value for the clicked column that was set in the BoundColumn element. This value is passed to the bindData method where it is used in the SQL statement to perform the sorting by the selected column. Our solution is quick and easy but lacks polish, in that it does not identify the current sort column. With the addition of a few lines of code to the bindData method, the sort column can be highlighted, as shown in Figure 1-13 (sort column is shown in yellow when displayed on the screen). The code added to the bindData method is shown next . It loops through the columns in the DataGrid , comparing the SortExpression for the column to the sortExpression passed to the bindData method. If the values match, the foreground color of the HeaderStyle for the color is set to yellow to highlight the column. Otherwise, the color is set to white.  | When working with this recipe's sample application, be aware that the following code must be placed before the DataBind statement. As a general rule, changes of this sort made to a DataGrid control that are placed after data binding may not be displayed as intended. | |
Dim col As DataGridColumn ... For Each col In dgBooks.Columns If (col.SortExpression = sortExpression) Then 'this is the sort column so highlight it col.HeaderStyle.ForeColor = Color.Yellow Else 'this is not the sort column so use the normal coloring col.HeaderStyle.ForeColor = Color.White End If Next col foreach (DataGridColumn col in dgBooks.Columns) { if (col.SortExpression == sortExpression) { //this is the sort column so highlight it col.HeaderStyle.ForeColor = Color.Yellow; } else { //this is not the sort column so use the normal coloring col.HeaderStyle.ForeColor = Color.White; } } // foreach
Figure 1-13. DataGrid with highlighted sort column output 
Another possibility for highlighting the sort column is to place an image beside the header title of the current sort column. In our application, this is accomplished by changing two lines of code (and the associated comments) in the highlighting solution. The code changes are shown here: For Each col In dgBooks.Columns If (col.SortExpression = sortExpression) Then 'this is the sort column so add an image to mark it col.HeaderText = col.SortExpression & _ " <img src='images/asterisk.gif' border='0'>" Else 'this is not the sort column so just display the title col.HeaderText = col.SortExpression End If Next col foreach (DataGridColumn col in dgBooks.Columns) { if (col.SortExpression == sortExpression) { //this is the sort column so add an image to mark it col.HeaderText = col.SortExpression + " <img src='images/asterisk.gif' border='0'>"; } else { //this is not the sort column so just display the title col.HeaderText = col.SortExpression; } } // foreach
The DataGrid header is rendered as a table row, with each column header appearing as a cell in the row. When sorting is enabled, the header text is rendered within an anchor tag, as shown in Figure 1-14. Placing the HTML for an image tag in the header text simply places the image within an anchor tag, which is a common way to make an image a link in a standard HTML page. Figure 1-14. DataGrid with sort column highlighted with an image (in this case, an asterisk) 
Example 1-30. DataGrid with column sorting (.aspx) <%@ Page Language="vb" AutoEventWireup="false" Codebehind="CH01DatagridSortingVB1.aspx.vb" Inherits="ASPNetCookbook.VBExamples.CH01DatagridSortingVB1" %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <html> <head> <title>DataGrid With Sorting</title> <link rel="stylesheet" href="css/ASPNetCookbook.css"> </head> <body leftmargin="0" marginheight="0" marginwidth="0" topmargin="0"> <form id="frmDatagrid" method="post" runat ="server"> <table width="100%" cellpadding ="0" cellspacing="0" border="0"> <tr> <td align="center"> <img src="images/ASPNETCookbookHeading_blue.gif"> </td> </tr> <tr> <td class="dividerLine"> <img src="images/spacer.gif" height="6" border="0"></td> </tr> </table> <table width="90%" align="center" border="0"> <tr> <td><img src="images/spacer.gif" height="10" border="0"></td> </tr> <tr> <td align="center" class="PageHeading"> DataGrid With Column Sorting (VB) </td> </tr> <tr> <td><img src="images/spacer.gif" height="10" border="0"></td> </tr> <tr> <td align="center"> <asp:DataGrid id="dgBooks" runat="server" BorderColor="000080" BorderWidth="2px" AutoGenerateColumns="False" width="100%" AllowSorting="True"> <HeaderStyle HorizontalAlign="Center" ForeColor="#FFFFFF" BackColor="#000080" Font-Bold=true CssClass="TableHeader" /> <ItemStyle BackColor="#FFFFE0" cssClass="TableCellNormal" /> <AlternatingItemStyle BackColor="#FFFFFF" cssClass="TableCellAlternating" /> <Columns> <asp:BoundColumn HeaderText="Title" DataField="Title" SortExpression="Title" /> <asp:BoundColumn HeaderText="ISBN" DataField="ISBN" ItemStyle-HorizontalAlign="Center" SortExpression="ISBN" /> <asp:BoundColumn HeaderText="Publisher" DataField="Publisher" ItemStyle-HorizontalAlign="Center" SortExpression="Publisher" /> </Columns> </asp:DataGrid> </td> </tr> </table> </form> </body> </html>
Example 1-31. DataGrid with column sorting code-behind (.vb) Option Explicit On Option Strict On '----------------------------------------------------------------------------- ' ' Module Name: CH01DatagridSortingVB1.aspx.vb ' ' Description: This class provides the code behind for ' CH01DatagridSortingVB1.aspx ' '***************************************************************************** Imports Microsoft.VisualBasic Imports System.Configuration Imports System.Data Imports System.Data.OleDb Imports System.Web.UI.WebControls Namespace ASPNetCookbook.VBExamples Public Class CH01DatagridSortingVB1 Inherits System.Web.UI.Page 'controls on form Protected WithEvents dgBooks As System.Web.UI.WebControls.DataGrid '************************************************************************* ' ' ROUTINE: Page_Load ' ' DESCRIPTION: This routine provides the event handler for the page load ' event. It is responsible for initializing the controls ' on the page. '------------------------------------------------------------------------- Private Sub Page_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles MyBase.Load If (Not Page.IsPostBack) Then 'sort by title and bind data to DataGrid bindData("Title") End If End Sub 'Page_Load '************************************************************************* ' ' ROUTINE: dgBooks_SortCommand ' ' DESCRIPTION: This routine provides the event handler for the datagrid ' sort event. It is responsible re-binding the data to the ' datagrid by the selected column. '------------------------------------------------------------------------- Private Sub dgBooks_SortCommand(ByVal source As Object, _ ByVal e As DataGridSortCommandEventArgs) _ Handles dgBooks.SortCommand 'sort the data by the selected column and re-bind the data bindData(e.SortExpression) End Sub 'dgBooks_SortCommand '************************************************************************* ' ' ROUTINE: bindData ' ' DESCRIPTION: This routine queries the database for the data to ' displayed and binds it to the datagrid '------------------------------------------------------------------------- Private Sub bindData(ByVal sortExpression As String) Dim dbConn As OleDbConnection Dim da As OleDbDataAdapter Dim ds As DataSet Dim strConnection As String Dim strSQL As String Try 'get the connection string from web.config and open a connection 'to the database strConnection = _ ConfigurationSettings.AppSettings("dbConnectionString") dbConn = New OleDbConnection(strConnection) dbConn. Open ( ) 'build the query string and get the data from the database strSQL = "SELECT Title, ISBN, Publisher " & _ "FROM Book " & _ "ORDER BY " & sortExpression da = New OleDbDataAdapter(strSQL, dbConn) ds = New DataSet da.Fill(ds) 'set the source of the data for the datagrid control and bind it dgBooks.DataSource = ds dgBooks.DataBind( ) Finally 'cleanup If (Not IsNothing(dbConn)) Then dbConn.Close( ) End If End Try End Sub 'bindData End Class 'CH01DatagridSortingVB1 End Namespace
Example 1-32. DataGrid with column sorting code-behind (.cs) //---------------------------------------------------------------------------- // // Module Name: CH01DatagridSortingCS1.aspx.cs // // Description: This class provides the code behind for // CH01DatagridSortingCS1.aspx // //**************************************************************************** using System; using System.Configuration; using System.Data; using System.Data.OleDb; using System.Web.UI.WebControls; namespace ASPNetCookbook.CSExamples { public class CH01DatagridSortingCS1 : System.Web.UI.Page { // controls on form protected System.Web.UI.WebControls.DataGrid dgBooks; //************************************************************************ // // ROUTINE: Page_Load // // DESCRIPTION: This routine provides the event handler for the page // load event. It is responsible for initializing the // controls on the page. //------------------------------------------------------------------------ private void Page_Load(object sender, System.EventArgs e) { // wire the event handler for the sort command this.dgBooks.SortCommand += new DataGridSortCommandEventHandler(this.dgBooks_SortCommand); if (!Page.IsPostBack) { // sort by title and bind data to DataGrid bindData("Title"); } } // Page_Load //************************************************************************ // // ROUTINE: dgBooks_SortCommand // // DESCRIPTION: This routine provides the event handler for the // datagrid sort event. It is responsible re-binding // the data to the datagrid by the selected column. //------------------------------------------------------------------------ private void dgBooks_SortCommand(Object source, System.Web.UI.WebControls.DataGridSortCommandEventArgs e) { // sort the data by the selected column and re-bind the data bindData(e.SortExpression); } // dgBooks_SortCommand //************************************************************************ // // ROUTINE: bindData // // DESCRIPTION: This routine queries the database for the data to // displayed and binds it to the repeater //------------------------------------------------------------------------ private void bindData(String sortExpression) { OleDbConnection dbConn = null; OleDbDataAdapter da = null; DataSet ds = null; String strConnection = null; String strSQL =null; try { // get the connection string from web.config and open a connection // to the database strConnection = ConfigurationSettings.AppSettings["dbConnectionString"]; dbConn = new OleDbConnection(strConnection); dbConn.Open( ); // build the query string and get the data from the database strSQL = "SELECT Title, ISBN, Publisher " + "FROM Book " + "ORDER BY " + sortExpression; da = new OleDbDataAdapter(strSQL, dbConn); ds = new DataSet( ); da.Fill(ds); // set the source of the data for the datagrid control and bind it dgBooks.DataSource = ds; dgBooks.DataBind( ); } // try finally { //clean up if (dbConn != null) { dbConn.Close( ); } } // finally } // bindData } // CH01DatagridSortingCS1 }
| 
