ASP.NET Cookbook

1.11. Sorting Data Within a DataGrid

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.

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:

1. 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).

2. 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

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.

Warning

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
}