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.13. Combining Sorting and Paging in a DataGrid

Problem

You are implementing a DataGrid with both sorting and pagination, and you are having trouble making the two features work together.

Solution

Enable the sorting features of the DataGrid control, and add custom code to support the sorting along with an indication of the current sort column and order (see Recipe 1.12 for details). Next, with pagination enabled, add a small amount of custom code to track the sort column and sort order so that they can be maintained between client round trips and used any time rebinding is required. Figure 1-16 shows a typical DataGrid with this solution implemented. Example 1-36 through Example 1-38 show the .aspx file and code-behind files for an application that produces this output.

Combining sorting and paging in a DataGrid output

Figure 1-16. Combining sorting and paging in a DataGrid output

Discussion

Getting both sorting and paging to work at the same time is a notorious problem with a DataGrid. The key to making it all work is to track the sort column and sort order so that they can be used any time rebinding is required, whether because of a page change or a sort command. Likewise, it is useful to put the sort column and sort order data in the view state so that they are properly maintained between client round trips.

The DataGrid provides the basic plumbing for sorting and paging the data displayed in the grid. The DataGrid also provides a property (CurrentPageIndex) that is always available to indicate which page is to be displayed. Unfortunately, the DataGrid provides no information regarding the sort column or the sort order, forcing you, as a programmer, to track this information outside of the DataGrid so it will be available when performing pagination operations.

The application we’ve developed for this recipe should give you a good idea of how to handle sorting and paging simultaneously. It tracks the sort column and the sort order so that the proper data can be bound to the DataGrid any time rebinding is required—for example, when the user clicks on a row header to resort a column or selects a page from the DataGrid control’s built-in navigation control. Refer to Recipe 1.9 and Recipe 1.12 for more detailed discussions of the various nuances of this recipe.

See Also

Recipe 1.9; Recipe 1.12

Example 1-36. Combining sorting and paging in a DataGrid (.aspx)

<%@ Page Language="vb" AutoEventWireup="false" 
    Codebehind="CH01DatagridWithSortingAndPagingVB.aspx.vb" 
    Inherits="ASPNetCookbook.VBExamples.CH01DatagridWithSortingAndPagingVB" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
  <head>
    <title>DataGrid With Sorting And Paging</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 Sorting And Paging (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"
                                AllowPaging="True"
                                PageSize="5"
                                PagerStyle-Mode="NumericPages"
                                PagerStyle-PageButtonCount="5"
                                PagerStyle-Position="Bottom"
                                PagerStyle-HorizontalAlign="Center" 
                                PagerStyle-NextPageText="Next"
                                PagerStyle-PrevPageText="Prev">

                                <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-37. Combining sorting and paging in a DataGrid code-behind (.vb)

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

Namespace ASPNetCookbook.VBExamples
  Public Class CH01DatagridWithSortingAndPagingVB
    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 oder
      '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: dgBooks_PageIndexChanged
    '
    '   DESCRIPTION: This routine provides the event handler for the page index
    '                changed event of the datagrid.  It is responsible for 
    '                setting the page index from the passed arguments and
    '                rebinding the data.
    '-------------------------------------------------------------------------
    Private Sub dgBooks_PageIndexChanged(ByVal source As Object, _
                            ByVal e As System.Web.UI.WebControls.DataGridPageChangedEventArgs) _
                            Handles dgBooks.PageIndexChanged

                        Dim currentSortExpression As String
                        Dim currentSortOrder As enuSortOrder

                        'set new page index and rebind the data
                        dgBooks.CurrentPageIndex = e.NewPageIndex

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

                        'rebind the data in the datagrid
                        bindData(currentSortExpression, _
                                 currentSortOrder)
                      End Sub  'dgBooks_PageIndexChanged

    '*************************************************************************
    '
    '   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  'CH01DatagridWithSortingAndPagingVB
End Namespace

Example 1-38. Combining sorting and paging in a DataGrid code-behind (.cs)

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

namespace ASPNetCookbook.CSExamples
{
  public class CH01DatagridWithSortingAndPagingCS : 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 handlers
      this.dgBooks.PageIndexChanged +=
        new DataGridPageChangedEventHandler(this.dgBooks_PageIndexChanged);
      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 oder
      // 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: dgBooks_PageIndexChanged
    //
    //   DESCRIPTION: This routine provides the event handler for the page
    //                index changed event of the datagrid.  It is responsible
    //                for setting the page index from the passed arguments
    //                and rebinding the data.
    //------------------------------------------------------------------------
    private void dgBooks_PageIndexChanged(Object source,
                        System.Web.UI.WebControls.DataGridPageChangedEventArgs e)
                      {
                        String currentSortExpression;
                        enuSortOrder currentSortOrder;

                        // set new page index and rebind the data
                        dgBooks.CurrentPageIndex = e.NewPageIndex;

                        // get the current sort expression and order from the viewstate
                        currentSortExpression = (String)(ViewState[VS_CURRENT_SORT_EXPRESSION]);
                        currentSortOrder = (enuSortOrder)(ViewState[VS_CURRENT_SORT_ORDER]);
                        // rebind the data in the datagrid
                        bindData(currentSortExpression,
                                 currentSortOrder);
                      }  // dgBooks_PageIndexChanged

    //************************************************************************
    //
    //   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);


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

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