To read the article online, visit http://www.4GuysFromRolla.com/articles/012903-1.aspx

An Extensive Examination of the DataGrid Web Control: Part 12

By Kishore Ramanathan and Scott Mitchell


The Twelfth Part in a Multi-Part Series
This article is the twelfth piece of a multi-part series on using the DataGrid Web control that will span several months. The ASP.NET DataGrid Web control, which displays database information in an HTML table, is highly versatile. The basics of the DataGrid were discussed in Part 1; information on specifying display properties of the DataGrid was discussed in Part 2. In Part 3 we examined how to associate custom events with the DataGrid. In Part 4 we looked at how to extend Part 3 to provide custom sorting on the results of a DataGrid. In Part 5 we examined how to use templates to further customize the DataGrid's appearance. Part 6 examined how to use the DataGrid's built-in editing capabilities, while Part 7 looked at customizing the editing interface using the EditItemTemplate. In Part 8 we looked at how to add client-side code to a ButtonColumn's client-side onclick event. In Part 9 we examined how to enhance the DataGrid's editing interface by having the editing interface's TextBox receive focus when the page is loaded. In Part 10 we looked at how to (automatically) add filtering buttons so that one can filter the data in a DataGrid. In Part 11 we examined how to create a DataGrid Web control with a column of related radio buttons. In this twelfth part, written by Kishore Ramanathan, we'll examine how to create a sortable DataGrid that can be sorted in ascending and descending order.

  • Read Part 13
  • Read Part 14
  • Read Part 15
  • Read Part 16
  • Read Part 17
  • Read Part 18
  • Introduction


    In Part 4 of this article series we looked at how to provide sorting capabilities for the DataGrid Web control. However, the sorting only allowed sorting of each column in one direction. That is, when a user clicked on a DataGrid column's header, the DataGrid's data was redisplayed, being sorted by the column whose header was clicked. Unfortunately, the sorting could only be applied in one direction. For example, if we created a DataGrid that had two columns, a list of products in one column, and their prices in another, we might want to let the user sort the list of products by their price. Using the techniques in Part 4 we could make it so that the user could sort the contents by their price in either ascending order (from cheapest to most expensive) or descending order (from most expensive to cheapest). However, with what we learned in Part 4, we could not provide a means for the user to sort it in either way.

    One of the most common user interface requirements is to be able to click on a column header and have the data toggle between being sorted in ascending order and descending order. In this article we will examine how to extend the DataGrid so that it can remember the "current sort state" of each column. Then, when the user clicks on a column the current sort order can be detected and toggled.

    The example used in the article displays attributes from the authors table in the pubs database. This article is divided into the following sections:

    1. HTML code declaring the DataGrid used for displaying authors details
    2. The subroutine used for populating the DataGrid
    3. The sort handler which remembers the sort state and sorts the DataGrid in the requisite order.

    For More Information on Bi-Directional Sortable DataGrids...
    This part of the article series looks at one means to create a bi-directional sortable DataGrid. This same topic is re-addressed in Part 18 of this article series, examining (in my opinion) a better way to accomplish bi-directional sorting. Additionally, Part 18 shows how to gussy up the bi-directional sortable DataGrid, adding an up or down arrow to the column the data is sorted by.

    Declaring the DataGrid and Displaying the Contents of the authors Table


    The first order of business is to create an ASP.NET Web page that displays a DataGrid that contains the rows from the authors table of the pubs database. This first task should be fairly straightforward for you, the reader. If it is not, I would encourage you to start with Part 1 and work up toward this installment.

    The following code creates the DataGrid and displays the appropriate data:

    <%@ Import Namespace = "System.Data" %>
    <%@ Import Namespace = "System.Data.SQLClient" %>
    <script language="VB" runat="server">
    Sub Page_Load(sender As Object, e As EventArgs)
      ' Only bind the data on the first visit to the page
      If Not Page.IsPostBack
        GetAuthors("au_fname asc")
      End If
    End Sub
    
    Sub GetAuthors(sSortStr as string)
      Dim cn as SQLConnection
      Dim cmd as SQLCommand
      Dim rdr as SQLDataReader
      Dim sConnectString as String = "server=localhost;database=pubs;user id=sa"
      Dim sSql as String = "SELECT au_fname, au_lname, city, state, zip " & _
                           "from authors order by " & sSortStr
      
      ' Connect to the database
      cn = New SQLConnection(sConnectString)
      cn.open()
    
      ' execute the SQL 
      cmd = New SQLCommand(sSQL, cn)
      rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
    
      ' Associate the data grid with the data
      DispAuthors.DataSource = rdr
      DispAuthors.DataBind()
    End Sub
    
    ' this method is called when user clicks on any of the column headings
    Sub SortAuthors(sender As Object, e As DataGridSortCommandEventArgs)
      '... Code coming soon! :-) ...
    End Sub
    </script>
    
    <form runat="server">
      <ASP:Datagrid id="DispAuthors" runat="server" AutoGenerateColumns="False" 
            AllowSorting="true" onSortCommand="SortAuthors">
        ...
        <Columns>
            <asp:BoundColumn DataField="au_fname" SortExpression="au_fname ASC" 
                                                       HeaderText="First Name" />
            <asp:BoundColumn DataField="au_lname" SortExpression="au_lname" 
                                                       HeaderText="Last Name" />
            <asp:BoundColumn DataField="city" HeaderText="City" />
            <asp:BoundColumn DataField="state" SortExpression="state" 
                                                       HeaderText="State" />
            <asp:BoundColumn DataField="zip" HeaderText="Zip" />
        </Columns>
      </asp:datagrid>
    </form>
    
    [View a Live Demo!]

    The HTML code defines a datagrid, DispAuthors, that displays authors information from the pubs database. The columns shown are author's first and last names, city, state and zip code.

    Two important things to notice with the above source code:

    1. SortAuthors is the event handler invoked when user clicks on a column header. The source code for this event handler has yet to be provided - we'll be examining the needed code soon.
    2. Only the first name, last name and state columns have their SortExpression property specified. This means that only these columns are sortable. Also note that the SortExpression property, along with the column name to sort, also contains information on the sort order (i.e., the First Name column's SortExpression property is au_fname ASC).

    The GetAuthors() subroutine retrieves the data to be displayed in the DataGrid. This method takes in a string parameter, sSortStr, which specifies how the data from the authors table should be sorted. For example, to sort the results by the au_fname column in ascending order, the argument to GetAuthors() would be au_fname asc. Notice that when the page is first visited, the Page_Load event handler calls GetAuthors() passing in au_fname asc; hence, when the page is first visited, the data will be displayed alphabetically by the authors' first names.

    Writing the Code for the DataGrid's Sort Event Handler


    Whenever one of the DataGrid's sortable column headers is clicked, the ASP.NET Web page is posted back and the DataGrid's SortCommand event fires. (Which causes the specified event handler, SortAuthors, to execute.) In order to be able to toggle between sorting a column in ascending and descending order, we need to be able to remember how the column was last sorted. To accomplish this we have a couple of options. One way is to store a bit of information in the ViewState for each sortable column, indicating if the column has last been sorted in ascending or descending order. Another way, and the way we will be doing it in this article, is storing this extra information in the column's SortExpression property.

    In the previous code example, you may have noticed that the SortExpression property for the First Name column indicated the order of the sorting. Specifically, the First Name column's SortExpression property was set to au_fname ASC. This demonstrates how the SortExpression property can be set so that it not only conveys the column to sort, but also the sort direction.

    Let's take a look at the code for the SortAuthors event handler, after which we'll discuss what, specifically, the code does.

    Sub SortAuthors(sender As Object, e As DataGridSortCommandEventArgs)
      Dim SortExprs() As String
      Dim CurrentSearchMode As String, NewSearchMode As String
      Dim ColumnToSort As String, NewSortExpr as String
    
      '  Parse the sort expression - delimiter space
      SortExprs = Split(e.SortExpression, " ")  
      ColumnToSort = SortExprs(0)
    
      ' If a sort order is specified get it, else default is descending
      If SortExprs.Length() > 1 Then
          CurrentSearchMode = SortExprs(1).ToUpper()
          If CurrentSearchMode = "ASC" Then
             NewSearchMode = "Desc"
          Else
             NewSearchMode = "Asc"
          End If
      Else   ' If no mode specified, Default is descending
          NewSearchMode = "Desc"
      End If
    
      '  Derive the new sort expression. 
      NewSortExpr = ColumnToSort & " " & NewSearchMode
    
      ' Figure out the column index 
      Dim iIndex As Integer
      Select Case ColumnToSort.toUpper()
        case "AU_FNAME"
          iIndex = 0
        case "AU_LNAME"
          iIndex = 1
        case "STATE"
          iIndex = 3
      End Select
    
      ' alter the column's sort expression 
      DispAuthors.Columns(iIndex).SortExpression = NewSortExpr
    
      ' Sort the data in new order
      GetAuthors(NewSortExpr)
    End Sub
    
    [View a Live Demo!]

    The SortAuthors event handler starts by splitting the SortExpression property on a space. This returns a string array with either one or two elements - one element if the SortExpression property has just the column name to sort on, like au_lname, and two elements if the SortExpression property has both the column name and the direction, like au_fname ASC. In the one-element case, the one element contains the name of the column to sort on; in the two-element case, the first element contains the column to sort on, while the second element specifies the direction.

    Next, the second element (if it exists) is examined and then toggled. This has the effect of if the column was sorted in ascending order the previous time, this time it will be sorted in descending order, and vice-a-versa. This toggled value becomes the new SortExpression property for the column that was clicked. Finally, a call to GetAuthors(), passing in the new sort expression, displays the sorted data.

    One Caveat...
    One thing you need to be aware of here is the Select Case statement, which is used to determine what DataGrid column's sortable header was clicked. Since this Select Case statement contains the various column named hard-coded in there, if you add a new sortable column to the DataGrid, or change the order of the sortable columns in the DataGrid, you will need to update this code.

    An alternative approach, using the ViewState, alleviates this dependence. For more information on using the ViewState to allow for a DataGrid that provides ascending and descending sortable columns, pick up a copy of my (Scott Mitchell) book: ASP.NET Data Web Controls Kick Start. This ViewState approach is also discussed in more detail in Part 18 of this article series.

    Conclusion


    In this article we saw how to enhance the DataGrid's sorting capabilities so that the DataGrid's columns could be toggled from being sorted in ascending order to being sorted in descending order. To accomplish this we adjusted the SortExpression property of the clicked DataGrid column in the DataGrid's SortCommand event handler.

    Happy Programming!

  • By Kishore Ramanathan and Scott Mitchell

  • Article Information
    Article Title: ASP.NET.An Extensive Examination of the DataGrid Web Control: Part 12
    Article Author: Scott Mitchell
    Published Date: January 29, 2003
    Article URL: http://www.4GuysFromRolla.com/articles/012903-1.aspx


    Copyright 2014 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers