Bi-directional Sorting and Paging in the ASP.NET Datagrid...

Combining two-way sorting and paging in the datagrid.

By: John Kilgo Date: February 15, 2004 Download the code. Printer Friendly Version

Without some special handling you end up with a mess when you try to combine sorting and paging in the same ASP.NET datagrid. When you try to implement two-way, or bi-directional, sorting the mess gets even worse. This article shows a way to combine the two.

We'll first take a look at the .aspx page where we define the datagrid. The only things to notice here is that we are implementing paging and sorting in the grid and are using BoundColumns.

<%@ Page Language="vb" AutoEventWireup="false" Codebehind="SortAscDesc.aspx.vb" Inherits="DotNetJohn.SortAscDesc1" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
  <HEAD>
    <title>SortAscDesc</title>
    <meta name="GENERATOR" content="Microsoft Visual Studio .NET 7.1">
    <meta name="CODE_LANGUAGE" content="Visual Basic .NET 7.1">
    <meta name="vs_defaultClientScript" content="JavaScript">
    <meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">
  </HEAD>
  <body MS_POSITIONING="GridLayout">
    <form id="Form1" method="post" runat="server">
      <asp:datagrid ID="dtg"
              Runat="server"
              CellPadding="2"
              Font-Name="Verdana"
              Font-Size="8pt"
              Width="600px"
              AllowPaging="True"
              AllowSorting="True"
              Autogeneratecolumns="False"
              PageSize="10">
        <HeaderStyle BackColor="coral" ForeColor="white" Font-Bold="true" HorizontalAlign="center" />
        <Columns>
          <asp:BoundColumn DataField="CompanyName" SortExpression="CompanyName" HeaderText="Company Name"></asp:BoundColumn>
          <asp:Boundcolumn DataField="ContactName" SortExpression="ContactName" HeaderText="Contact Name"></asp:BoundColumn>
          <asp:BoundColumn DataField="ContactTitle" SortExpression="ContactTitle" HeaderText="Contact Title"></asp:BoundColumn>
          <asp:BoundColumn DataField="City" SortExpression="City" HeaderText="City"></asp:BoundColumn>
        </Columns>
        <Pagerstyle Mode="NumericPages" HorizontalAlign="Center"></Pagerstyle>
        <AlternatingItemStyle BackColor="beige" />
      </asp:datagrid>
    </form>
  </body>
</HTML>

We will be using the Top 30 rows from the Northwind Customers table for this example application. The codebehind file is shown in two parts to ease discussion of the code. The first part of the code is shown below. Here we are just showing the Page_load event and the GetTableData subroutine to actually retrieve the data from the database.

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Public Class SortAscDesc1
  Inherits System.Web.UI.Page

  ' Web Form Designer Generated Code omitted

  Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    dtg.DataSource = GetTableData()
    dtg.DataBind()
  End Sub

  Function GetTableData() As DataTable
    Dim ds As New DataSet
    Dim conn As New SqlConnection(ConfigurationSettings.AppSettings("NorthwindConnection"))
    Dim strSQL As String

    strSQL = "SELECT TOP 30 CompanyName, ContactName, ContactTitle, City FROM Customers ORDER BY CompanyName"
    Dim da As New SqlDataAdapter(strSQL, conn)
    da.Fill(ds, "tblCustomers")
    Return ds.Tables("tblCustomers")

  End Function

It is in the SortCommand and PageIndexChanged event handlers that we must play our tricks to get bi-directional sorting and paging to work together. That code is shown below. I won't go through the code in great detail. Basically all we are doing is checking the current sort expression of the datagrid and setting it to the opposite value (" DESC" or " ASC") each time a column is clicked for sorting. If the current sort expression is "ASC" we change it to "DESC" and vice-versa. In the PageIndexChanged event handler we are changing the paging as we normally would, but also setting the sort expression appropriately.

  Private Sub dtg_SortCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridSortCommandEventArgs) Handles dtg.SortCommand
    Dim dataGrid As DataGrid = source
    Dim strSort = dataGrid.Attributes("SortExpression")
    Dim strASC = dataGrid.Attributes("SortASC")

    dataGrid.Attributes("SortExpression") = e.SortExpression
    dataGrid.Attributes("SortASC") = "Yes"

    If e.SortExpression = strSort Then
      If strASC = "Yes" Then
        dataGrid.Attributes("SortASC") = "No"
      Else
        dataGrid.Attributes("SortASC") = "Yes"
      End If
    End If

    Dim dt As DataTable = GetTableData()
    Dim dv As DataView = New DataView(dt)
    dv.Sort = dataGrid.Attributes("SortExpression")

    If dataGrid.Attributes("SortASC") = "No" Then
      dv.Sort &= " DESC"
    End If

    dataGrid.CurrentPageIndex = 0
    dataGrid.DataSource = dv
    dataGrid.DataBind()
  End Sub

  Private Sub dtg_PageIndexChanged(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridPageChangedEventArgs) Handles dtg.PageIndexChanged
    Dim dt As DataTable = GetTableData()
    Dim dv As DataView = New DataView(dt)
    Dim dataGrid As DataGrid = source

    dataGrid.CurrentPageIndex = e.NewPageIndex
    dv.Sort = dataGrid.Attributes("SortExpression")

    If dataGrid.Attributes("SortASC") = "No" Then
      dv.Sort &= " DESC"
    End If

    dataGrid.DataSource = dv
    dataGrid.DataBind()
  End Sub

End Class

I hope you find some value in this coding technique. You may well be required to come up with a bi-directional, pageable datagrid at some point.

You may download the code here.