Sort Data on the Fly

DataStream

LANGUAGES: VB

TECHNOLOGIES: ASP.NET | ADO.NET | SQL Server

Sort Data on the Fly

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

By Wayne S. Freeze

Many years ago, a database administrator I worked with hada simple rule regarding sorting data on the fly: "no sorting on the fly." Youhad 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 thedatabase design, then justify it during the database design review.

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

Know the Rules

Here are my three simple rules for sorting:

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

2. If you mustdisplay sorted information, always sort your data as you retrieve it. Thedatabase server is the best place to sort large datasets, especially if you areretrieving the data for the first time. Database vendors spend a lot of timetuning their sort algorithms for optimum performance, so you should takeadvantage of this if at all possible.

3. Sometimes memoryis 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 Webserver. If so, it makes sense to sort your data on the Web server as long asyou are not sorting large volumes of data. If your data is stored in the Webserver's cache, you can store multiple copies of the data in cache, each sorteda different way. This is a good solution when you have small amounts of datathat are sorted only a few different ways.

Create a DataGrid for Sorting

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

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

To enable sorting, you need to set the AllowSortingDataGrid attribute to True. Then you need to define the event that will befired when the user clicks on one of the hyperlinks in the header using theOnSortCommand attribute:

   AllowPaging="True" AutoGenerateColumns="False"    OnPageIndexChanged="DataGrid1_PageIndexChanged"    AllowSorting="True"    OnSortCommand="DataGrid1_SortCommand">   Note that the DataGrid fires a single event no matterwhich column header the visitor clicked on. The information about which columnwas 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 fromthe database is used. You can override this value by defining theSortExpression attribute explicitly in the column template:      SortExpression="CustomerId"    HeaderText="CustomerId">   Explicitly specifying the SortExpression value in thecolumn template also allows you to specify more complex sorting operations. Forinstance, you could specify a value of "CustomerId Desc", which would be usedto sort the data in descending order. Also, you could sort on more than onecolumn by specifying multiple columns separated by commas, such as " LastName,FirstName, MiddleInitial". Of course, the exact value used for SortExpressionreally is dictated by how the SortCommand is processed.   Implement Sorting With theDataGrid Initializing the DataGrid is fairly straightforward. Astored procedure containing a Select statement like this is used to retrievethe data from the database. Notice that the Order By clause is included toensure the data is sorted before it's returned to the application:   Select CustomerId, Name, Street, City, State,    ZipCode, Phone, EMailAddressFrom CustomersOrder By CustomerId   The code in Figure 2 is used to call the stored procedurewhen the Web page is displayed initially. A SqlConnection object is createdusing a connection string from the Web.Config file. Then, a SqlCommand objectis created using the connection object. Next, a SqlDataAdapter is created usingthe SqlCommand object.   Private Sub Page_Load(ByVal sender AsSystem.Object, ByVal e As System.EventArgs)   Dim ds As DataSetDim ConnectionStr As String =ConfigurationSettings.AppSettings("ConnStr") Dim adpt As SqlDataAdapterDim conn As SqlConnectionDim cmd As SqlCommand  If Not IsPostBack Then   ds = New DataSet()   conn = NewSqlConnection(ConnectionStr)    cmd = NewSqlCommand("GetCustomers", conn)    cmd.CommandType =CommandType.StoredProcedure   adpt = New SqlDataAdapter(cmd)    adpt.Fill(ds,"Customers")    Session("Customers") =ds.Tables("Customers").DefaultView     DataGrid1.DataSource =Session("Customers")    DataGrid1.DataBind()  End If  End SubFigure 2. Toinitialize the DataGrid for sorting, call a stored procedure to return thevalues to be displayed in the grid, and save the DataView object in the Sessionobject.   The data adapter's Fill method is used to get theinformation from the database into a DataSet object. A reference to the table'sDefaultView is saved in a Session object before it's bound to the DataGrid.Rather than saving the DataTable object, the DataView object is saved becauseyou can use the DataView object to perform many useful operations against thedata, including changing the way the data is sorted.   After the DataGrid has been displayed, the SortCommandevent is fired each time the visitor clicks on one of the header's hyperlinks.When the SortCommand event is fired, the SortExpression associated with thecolumn is passed to the event in a DataGridSortCommandArgs object(see Figure3).   Public Sub DataGrid1_SortCommand(ByVal source As Object,    ByVal e AsSystem.Web.UI.WebControls.DataGridSortCommandEventArgs)   Dim dv As DataView  DataGrid1.CurrentPageIndex = 0dv=Session("Customers") dv.Sort = e.SortExpressionDataGrid1.DataSource = dvDataGrid1.DataBind()  End SubFigure 3. TheSortCommand event is triggered to handle the sort request. It first mustperform the sort, then bind the newly sorted data to the DataGrid control.   Because this particular DataGrid supports paging, theCurrentPageIndex value is reset to 0 so that the user sees the first page afterthe sort. Next, the SortExpression value from the DataGridSortCommandEventArgsobject is assigned to the DataView object's Sort property. Changing this valueresorts the data in the DataView automatically. Finally, the DataGrid object isre-bound to the DataView object, thus resorting the data.   The sample code in thisarticle is available for download.   Wayne S. Freeze isa full-time computer book author with more than a dozen titles to his credit,including Windows Game Programming with VisualBasic and DirectX (Que) and UnlockingOLAP with SQL Server and Excel 2000 (Hungry Minds). He has more than 25years of experience using all types of computers, from small, embeddedmicroprocessor control systems to large-scale IBM mainframes. Freeze has amaster's degree in management information systems as well as degrees incomputer science and engineering. You can visit his Web site at http://www.JustPC.com and send him e-mail at mailto:[email protected]. He lovesreading e-mail from his readers, whose ideas, questions, and insights oftenprovide inspiration for future books and articles.