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 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" OnPageIndexChanged="DataGrid1_PageIndexChanged" AllowSorting="True" OnSortCommand="DataGrid1_SortCommand"> 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: SortExpression="CustomerId" HeaderText="CustomerId"> 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") DataGrid1.DataBind() 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=Session("Customers") dv.Sort = e.SortExpression DataGrid1.DataSource = dv DataGrid1.DataBind() 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 http://www.JustPC.com 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.
