Sort Data on the Fly

Help your users find what they’re looking for with a sorted DataGrid.





Sort Data on the Fly

Help your users find what they're looking for with a sorted DataGrid.


By Wayne S. Freeze


Many years ago, a database administrator I worked with had a simple rule regarding sorting data on the fly: "no sorting on the fly." You had to display the data in the same order it was retrieved from the database. If you had to display sorted data, you had to incorporate the sort into the database design, then justify it during the database design review.


This rule made sense at the time because of the limited power available, and sorting is an expensive process. In today's world, however, computers have far more power; on-the-fly sorting is something you might want to consider adding to your Web site, especially if it helps your visitors to find the information they're looking for quickly.


Know the Rules

Here are my three simple rules for sorting:


1. If you must sort, sort as few rows as possible. How much impact on-the-fly sorting has depends on how many rows you're sorting. Although this might seem obvious, it might not be obvious that the cost of sorting data is not linear. Sorting 500 rows uses more than double the resources needed to sort 250 rows of data. On the other hand, the cost to sort a handful of rows is small enough that it doesn't matter.


2. If you must display sorted information, always sort your data as you retrieve it. The database server is the best place to sort large datasets, especially if you are retrieving the data for the first time. Database vendors spend a lot of time tuning their sort algorithms for optimum performance, so you should take advantage of this if at all possible.


3. Sometimes memory is cheaper than sorting. If your application uses a DataGrid with paging, there's a good chance you have kept a copy of the data locally in the Web server. If so, it makes sense to sort your data on the Web server as long as you are not sorting large volumes of data. If your data is stored in the Web server's cache, you can store multiple copies of the data in cache, each sorted a different way. This is a good solution when you have small amounts of data that are sorted only a few different ways.


Create a DataGrid for Sorting

One of the features I like about the DataGrid is it has the tools you need to implement sorting in your application already. Each of the header titles is a hyperlink that triggers the DataGrid's SortCommand event (see Figure 1).


Figure 1. The header fields in the DataGrid contain LinkButtons that trigger the DataGrid's SortCommand event.


To enable sorting, you need to set the AllowSorting DataGrid attribute to True. Then you need to define the event that will be fired when the user clicks on one of the hyperlinks in the header using the OnSortCommand attribute:


   AllowPaging="True" AutoGenerateColumns="False"





Note that the DataGrid fires a single event no matter which column header the visitor clicked on. The information about which column was clicked on is contained in a value named SortExpression. By default, SortExpression is the same as the HeaderText value displayed on the Web page. If the HeaderText value isn't specified, the name of the column retrieved from the database is used. You can override this value by defining the SortExpression attribute explicitly in the column template:





Explicitly specifying the SortExpression value in the column template also allows you to specify more complex sorting operations. For instance, you could specify a value of "CustomerId Desc", which would be used to sort the data in descending order. Also, you could sort on more than one column by specifying multiple columns separated by commas, such as " LastName, FirstName, MiddleInitial". Of course, the exact value used for SortExpression really is dictated by how the SortCommand is processed.


Implement Sorting With the DataGrid

Initializing the DataGrid is fairly straightforward. A stored procedure containing a Select statement like this is used to retrieve the data from the database. Notice that the Order By clause is included to ensure the data is sorted before it's returned to the application:


Select CustomerId, Name, Street, City, State,

   ZipCode, Phone, EMailAddress

From Customers

Order By CustomerId


The code in Figure 2 is used to call the stored procedure when the Web page is displayed initially. A SqlConnection object is created using a connection string from the Web.Config file. Then, a SqlCommand object is created using the connection object. Next, a SqlDataAdapter is created using the SqlCommand object.


Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)


Dim ds As DataSet

Dim ConnectionStr As String = ConfigurationSettings.AppSettings("ConnStr")

Dim adpt As SqlDataAdapter

Dim conn As SqlConnection

Dim cmd As SqlCommand


If Not IsPostBack Then

   ds = New DataSet()

   conn = New SqlConnection(ConnectionStr)

   cmd = New SqlCommand("GetCustomers", conn)

   cmd.CommandType = CommandType.StoredProcedure

   adpt = New SqlDataAdapter(cmd)

   adpt.Fill(ds, "Customers")

   Session("Customers") = ds.Tables("Customers").DefaultView


   DataGrid1.DataSource = Session("Customers")



End If


End Sub

Figure 2. To initialize the DataGrid for sorting, call a stored procedure to return the values to be displayed in the grid, and save the DataView object in the Session object.


The data adapter's Fill method is used to get the information from the database into a DataSet object. A reference to the table's DefaultView is saved in a Session object before it's bound to the DataGrid. Rather than saving the DataTable object, the DataView object is saved because you can use the DataView object to perform many useful operations against the data, including changing the way the data is sorted.


After the DataGrid has been displayed, the SortCommand event is fired each time the visitor clicks on one of the header's hyperlinks. When the SortCommand event is fired, the SortExpression associated with the column is passed to the event in a DataGridSortCommandArgs object(see Figure 3).


Public Sub DataGrid1_SortCommand(ByVal source As Object,

   ByVal e As System.Web.UI.WebControls.DataGridSortCommandEventArgs)


Dim dv As DataView


DataGrid1.CurrentPageIndex = 0


dv.Sort = e.SortExpression

DataGrid1.DataSource = dv



End Sub

Figure 3. The SortCommand event is triggered to handle the sort request. It first must perform the sort, then bind the newly sorted data to the DataGrid control.


Because this particular DataGrid supports paging, the CurrentPageIndex value is reset to 0 so that the user sees the first page after the sort. Next, the SortExpression value from the DataGridSortCommandEventArgs object is assigned to the DataView object's Sort property. Changing this value resorts the data in the DataView automatically. Finally, the DataGrid object is re-bound to the DataView object, thus resorting the data.


The sample code in this article is available for download.


Wayne S. Freeze is a full-time computer book author with more than a dozen titles to his credit, including Windows Game Programming with Visual Basic and DirectX (Que) and Unlocking OLAP with SQL Server and Excel 2000 (Hungry Minds). He has more than 25 years of experience using all types of computers, from small, embedded microprocessor control systems to large-scale IBM mainframes. Freeze has a master's degree in management information systems as well as degrees in computer science and engineering. You can visit his Web site at and send him e-mail at mailto:[email protected]. He loves reading e-mail from his readers, whose ideas, questions, and insights often provide inspiration for future books and articles.




Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.