O'Reilly logo

ASP.NET Cookbook by Geoffrey T. LeBlond, Michael A Kittel

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

1.12. Sorting Data in Ascending/Descending Order Within a DataGrid

Problem

You are displaying a table of data in a DataGrid, and you want to let the user sort the data and change the sort order by clicking on the column headers.

Solution

Enable the sorting features of the DataGrid control and add custom coding to support the sorting along with an indication of the current sort column and order. An example of the output that is possible with this approach is shown in Figure 1-15. Example 1-33 through Example 1-35 show the .aspx file and code-behind files for an application that produces this output.

DataGrid with ascending/descending sorting output

Figure 1-15. DataGrid with ascending/descending sorting output

Discussion

To add sorting to a DataGrid control, you must first enable its sorting features by setting the AllowSorting attribute of the DataGrid element to True. In addition, set the SortExpression attribute of the asp:BoundColumn element to the expression that will be used in your code to perform the sorting. See Recipe 1.11 for details on these steps.

Next, remove the HeadingText attribute in the asp:BoundColumn element. You will want to set the heading text in the code-behind, so it is not needed in the .aspx file.

To support sorting in both ascending and descending order as well as visually indicating the sort order requires a bit more code in the code-behind class. This is driven by needing to know the current sort column and the current sort order to determine what needs to be done when the user clicks a column header. From here on, it’s useful to examine our example application to see how we’ve juggled these needs.

We’ve added an enumeration and several constants to the top of the code-behind class shown in Example 1-34 (VB) and Example 1-35 (C#). The enuSortOrder enumeration defines the available sort orders that are used to store and compare sort order data. The sortExpression and columnTitle arrays are used to define the sort expression and column title for each column in the DataGrid. As their names imply, the VS_CURRENT_SORT_EXPRESSION and VS_CURRENT_SORT_ORDER constants define the names of variables stored in the ViewState to track the current sort expression and order between page submittals.

In our example application, the Page_Load method performs two operations. First, the view state variables used to store the current sort expression and sort order (VS_CURRENT_SORT_EXPRESSION and VS_CURRENT_SORT_ORDER) are set to their default values. For this example, the title column is sorted in ascending order by default. Second, the bindData method is called to pass the current sort expression and sort order.

Two features of the bindData method are worth special note. First, the SQL statement used to query the database includes an ORDER BY clause that reflects the current sort order.

Second, the bindData method also loops through each column in the grid, determines which column is the sort column, and marks the sort order for the column. The sort column is determined by comparing the current sort expression passed to the bindData method with the sort expression for each of the columns. The sort expression for the one column that matches the current sort expression is the sort column. After finding the sort column, the sort order is checked to determine whether the ascending or descending image should be output in the header for the sort column. Finally, the header text is set to the title for the column and, when relevant, the HTML image tag used to indicate the sort order is also set. For columns that are not the current sort column, the image-related HTML is simply set to an empty string.

When the user clicks a column header in the grid, the dgBooks_SortCommand method is called and determines the changes that need to be made prior to rebinding the data. The first step is to get the current sort expression and sort order from the view state, as shown here:

               Discussion
currentSortExpression = CStr(viewstate(VS_CURRENT_SORT_EXPRESSION))
currentSortOrder = CType(viewstate(VS_CURRENT_SORT_ORDER), enuSortOrder)

Discussion
currentSortExpression = (String)(this.ViewState[VS_CURRENT_SORT_EXPRESSION]);
currentSortOrder = (enuSortOrder)(this.ViewState[VS_CURRENT_SORT_ORDER]);

After getting the current information, we determine if a column other than the current sort column has been clicked. If so, the clicked column is set as the new sort column and the sort order is set to ascending. If not, we need to change the sort order of the original column.

After determining the sort expression and sort order, the view state is updated to reflect what will be the current information once the page is rendered.

Finally, the data is rebound to the grid by calling the bindData method with the new sort information.

See Also

Recipe 1.11

Example 1-33. DataGrid with ascending/descending sorting (.aspx)

<%@ Page Language="vb" AutoEventWireup="false" 
         Codebehind="CH01DatagridAscDescSortingVB.aspx.vb" 
         Inherits="ASPNetCookbook.VBExamples.CH01DatagridAscDescSortingVB" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
  <head>
    <title>DataGrid With Ascend/Descend 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 Ascend/Descend 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 DataField="Title" 
                                                   SortExpression="Title" />
                                  <asp:BoundColumn DataField="ISBN" 
                                                   ItemStyle-HorizontalAlign="Center" 
                                                   SortExpression="ISBN" />
                                  <asp:BoundColumn DataField="Publisher" 
                                                   ItemStyle-HorizontalAlign="Center" 
                                                   SortExpression="Publisher" />
                                </Columns>
                              </asp:DataGrid>
          </td>
        </tr>
      </table>
    </form>
  </body>
</html>

Example 1-34. DataGrid with ascending/descending sorting code-behind (.vb)

Option Explicit On 
Option Strict On
'-----------------------------------------------------------------------------
'
'   Module Name: CH01DatagridAscDescSortingVB.aspx.vb
'
'   Description: This class provides the code behind for
'                CH01DatagridAscDescSortingVB.aspx
'
'*****************************************************************************
Imports Microsoft.VisualBasic
Imports System.Configuration
Imports System.Data
Imports System.Data.OleDb
Imports System.Web.UI.WebControls

Namespace ASPNetCookbook.VBExamples
  Public Class CH01DatagridAscDescSortingVB
    Inherits System.Web.UI.Page

    'controls on form
    Protected WithEvents dgBooks As System.Web.UI.WebControls.DataGrid

    'the following enumeration is used to define the sort orders
                      Private Enum enuSortOrder
                        soAscending = 0
                        soDescending = 1
                      End Enum

                      'strings to use for the sort expressions and column title
                      'separate arrays are used to support the sort expression and titles
                      'being different
                      Private ReadOnly sortExpression( ) As String = {"Title", "ISBN", "Publisher"}
                      Private ReadOnly columnTitle( ) As String = {"Title", "ISBN", "Publisher"}
                      'the names of the variables placed in the viewstate
                      Private Const VS_CURRENT_SORT_EXPRESSION As String = "currentSortExpression"
                      Private Const VS_CURRENT_SORT_ORDER As String = "currentSortOrder"

    '*************************************************************************
    '
    '   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

      Dim defaultSortExpression As String
                        Dim defaultSortOrder As enuSortOrder

                        If (Not Page.IsPostBack) Then
                          'sort by title, ascending as the default
                          defaultSortExpression = sortExpression(0)
                          defaultSortOrder = enuSortOrder.soAscending

                          'store current sort expression and order in the viewstate then
                          'bind data to the DataGrid
                          viewstate(VS_CURRENT_SORT_EXPRESSION) = defaultSortExpression
                          viewState(VS_CURRENT_SORT_ORDER) = defaultSortOrder
                          bindData(defaultSortExpression, _
                                   defaultSortOrder)
                        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

                        Dim newSortExpression As String
                        Dim currentSortExpression As String
                        Dim currentSortOrder As enuSortOrder

                        'get the current sort expression and order from the viewstate
                        currentSortExpression = CStr(viewstate(VS_CURRENT_SORT_EXPRESSION))
                        currentSortOrder = CType(viewstate(VS_CURRENT_SORT_ORDER), enuSortOrder)
                        'check to see if this is a new column or the sort order
                        'of the current column needs to be changed.
                        newSortExpression = e.SortExpression
                        If (newSortExpression = currentSortExpression) Then
                          'sort column is the same so change the sort order
                          If (currentSortOrder = enuSortOrder.soAscending) Then
                            currentSortOrder = enuSortOrder.soDescending
                          Else
                            currentSortOrder = enuSortOrder.soAscending
                          End If
                        Else
                          'sort column is different so set the new column with ascending
                          'sort order
                          currentSortExpression = newSortExpression
                          currentSortOrder = enuSortOrder.soAscending
                        End If

                        'update the view state with the new sort information
                        viewstate(VS_CURRENT_SORT_EXPRESSION) = currentSortExpression
                        viewstate(VS_CURRENT_SORT_ORDER) = currentSortOrder

                        'rebind the data in the datagrid
                        bindData(currentSortExpression, _
                                 currentSortOrder)
                      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, _
                         ByVal sortOrder As enuSortOrder)
      Dim dbConn As OleDbConnection
      Dim da As OleDbDataAdapter
      Dim ds As DataSet
      Dim strConnection As String
      Dim strSQL As String
      Dim index As Integer
      Dim col As DataGridColumn
      Dim colImage As String
      Dim strSortOrder 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
        If (sortOrder = enuSortOrder.soAscending) Then
                            strSortOrder = " ASC"
                          Else
                            strSortOrder = " DESC"
                          End If

                          strSQL = "SELECT Title, ISBN, Publisher " & _
                                   "FROM Book " & _
                                   "ORDER BY " & sortExpression & _
                                   strSortOrder

        da = New OleDbDataAdapter(strSQL, dbConn)
        ds = New DataSet
        da.Fill(ds)

        'loop through the columns in the datagrid updating the heading to 
                          'mark which column is the sort column and the sort order
                          For index = 0 To dgBooks.Columns.Count - 1
                            col = dgBooks.Columns(index)

                            'check to see if this is the sort column
                            If (col.SortExpression = sortExpression) Then
                              'this is the sort column so determine whether the ascending or
                              'descending image needs to be included
                              If (sortOrder = enuSortOrder.soAscending) Then
                                colImage = " <img src='images/sort_ascending.gif' border='0'>"
                              Else
                                colImage = " <img src='images/sort_descending.gif' border='0'>"
                              End If
                            Else
                              'This is not the sort column so include no image html
                              colImage = ""
                            End If  'If (col.SortExpression = sortExpression)
                            'set the title for the column
                            col.HeaderText = columnTitle(index) & colImage
                          Next index

        '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  'CH01DatagridAscDescSortingVB
End Namespace

Example 1-35. DataGrid with ascending/descending sorting code-behind (.cs)

//----------------------------------------------------------------------------
//
//   Module Name: CH01DatagridAscDescSortingCS.aspx.cs
//
//   Description: This class provides the code behind for
//                CH01DatagridAscDescSortingCS.aspx
//
//****************************************************************************
using System;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Web.UI.WebControls;

namespace ASPNetCookbook.CSExamples
{
  public class CH01DatagridAscDescSortingCS : System.Web.UI.Page
  {
    // controls on form
    protected System.Web.UI.WebControls.DataGrid dgBooks;

    // the following enumeration is used to define the sort orders
                      private enum enuSortOrder : int
                      {
                        soAscending = 0,
                        soDescending = 1
                      }

                      // strings to use for the sort expressions and column title
                      // separate arrays are used to support the sort expression and titles
                      // being different
                      static readonly String [] sortExpression =
                                                  new String [] {"Title", "ISBN", "Publisher"};
                      static readonly String[] columnTitle =
                                                  new String [] {"Title", "ISBN", "Publisher"};

                      // the names of the variables placed in the viewstate
                      static readonly String VS_CURRENT_SORT_EXPRESSION =
                                                  "currentSortExpression";
                      static readonly String VS_CURRENT_SORT_ORDER = "currentSortOrder";

    //************************************************************************
    //
    //   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)
    {
      String defaultSortExpression;
                        enuSortOrder defaultSortOrder;
                        // wire the event handler for the sort command
                        this.dgBooks.SortCommand +=
                          new DataGridSortCommandEventHandler(this.dgBooks_SortCommand);

                        if (!Page.IsPostBack)
                        {
                          // sort by title, ascending as the default
                          defaultSortExpression = sortExpression[0];
                          defaultSortOrder = enuSortOrder.soAscending;

                          // bind data to the DataGrid
                          this.ViewState.Add(VS_CURRENT_SORT_EXPRESSION, defaultSortExpression);
                          this.ViewState.Add(VS_CURRENT_SORT_ORDER, defaultSortOrder);
                          bindData(defaultSortExpression,
                                   defaultSortOrder);
                        }
    }  // 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)
                      {
                        String newSortExpression = null;
                        String currentSortExpression = null;
                        enuSortOrder currentSortOrder;

                        // get the current sort expression and order from the viewstate
                        currentSortExpression =
                                          (String)(this.ViewState[VS_CURRENT_SORT_EXPRESSION]);
                        currentSortOrder =
                                          (enuSortOrder)(this.ViewState[VS_CURRENT_SORT_ORDER]);

                        // check to see if this is a new column or the sort order
                        // of the current column needs to be changed.
                        newSortExpression = e.SortExpression;
                        if (newSortExpression == currentSortExpression)
                        {

                          // sort column is the same so change the sort order
                          if (currentSortOrder == enuSortOrder.soAscending)
                          {
                            currentSortOrder = enuSortOrder.soDescending;
                          }
                          else
                          {
                            currentSortOrder = enuSortOrder.soAscending;
                          }
                        }
                        else
                        {
                          // sort column is different so set the new column with ascending
                          // sort order
                          currentSortExpression = newSortExpression;
                          currentSortOrder = enuSortOrder.soAscending;
                        }

                        // update the view state with the new sort information
                        this.ViewState.Add(VS_CURRENT_SORT_EXPRESSION, currentSortExpression);
                        this.ViewState.Add(VS_CURRENT_SORT_ORDER, currentSortOrder);

                        // rebind the data in the datagrid
                        bindData(currentSortExpression,
                                 currentSortOrder);
                      }  // 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,
                          enuSortOrder sortOrder)
    {
      OleDbConnection dbConn = null;
      OleDbDataAdapter da = null;
      DataSet ds = null;
      String strConnection = null;
      String strSQL =null;
      int index = 0;
      DataGridColumn col = null;
      String colImage = null;
      String strSortOrder = 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
                          if (sortOrder == enuSortOrder.soAscending)
                          {
                            strSortOrder = " ASC";
                          }
                          else
                          {

                            strSortOrder = " DESC";
                          }
                          strSQL = "SELECT Title, ISBN, Publisher " +
                                   "FROM Book " +
                                   "ORDER BY " + sortExpression +
                                   strSortOrder;

        da = new OleDbDataAdapter(strSQL, dbConn);
        ds = new DataSet( );
        da.Fill(ds, "Table");


        // loop through the columns in the datagrid updating the heading to
                          // mark which column is the sort column and the sort order
                          for (index = 0; index < dgBooks.Columns.Count; index++)
                          {
                            col = dgBooks.Columns[index];
                            // check to see if this is the sort column
                            if (col.SortExpression == sortExpression)
                            {
                              // this is the sort column so determine whether the ascending or
                              // descending image needs to be included
                              if (sortOrder == enuSortOrder.soAscending)
                              {
                                colImage = " <img src='images/sort_ascending.gif' border='0'>";
                              }
                              else
                              {
                                colImage = " <img src='images/sort_descending.gif' border='0'>";
                              }
                            }
                            else
                            {
                              // This is not the sort column so include no image html
                              colImage = "";
                            }  // if (col.SortExpression == sortExpression)

          // set the title for the column
          col.HeaderText = columnTitle[index] + colImage;
        }  // for index

        // 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
  }  // CH01DatagridAscDescSortingCS
}

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required