Practical ASP.NET

Sorting in the ObjectDataSource

Your users may want to get their data in a specific order -- and not get it all at once. You can control both with the ObjectDataSource (and a little code).

• By Peter Vogel
• 07/15/2009

Databinding to tables in single-tier applications has always been easy. ASP.NET's ObjectDataSource does something wonderful: It makes databinding to business objects in n-tier applications just as easy.

Letting users sort a GridView in single-tier databinding applications isn't hard: Just turn on the option in the GridView's SmartTag. You can also let your users sort the rows in a GridView when using the ObjectDataSource -- it only requires a little more work.

Enabling Sorting
As is often the case, turning on functionality in a DataSource and a DataView involves making changes to both controls. For instance, when working with an ObjectDataSource, the Enable Sorting option won't appear in the GridView's SmartTag until you turn on the option in the ObjectDataSource. The option to set isn't obvious: In the Properties window for the ObjectDataSource, you must set the SortParameterName to some string. Once you do that, you'll find that the SmartTag for the GridView has an Enable Sorting option that you can check off.

However, turning on the sort option just enables the user interface support (the column names in the GridView become hyperlinks that the user can click on to sort the rows in the GridView by that column). The actual work of doing the sort is turned over to the factory method of the middle-tier business object so you have to make changes there also. For instance, if you've set the SortParameterName to, say, "MySortExpression," then the factory method that your ObjectDataSource calls must have a parameter called MySortExpression.

After you've set the SortParameterName on the ObjectDataSource and enabled sorting on the GridView, when the user clicks on a column header the value of the column's SortExpression will be passed to the factory method specified in the ObjectDataSource. The default value for each column's SortExpression is the name of the field that the column is bound to.

However, you can change the SortExpression for any column in the Edit Columns dialog. For instance, in a GridView displaying a list of Customer objects, you might want to set the LastName column's SortExpression to "LastName, FirstName." This would position you so that when a user clicks on the LastName column header, you can do a "phone book sort": sort by LastName and FirstName. You can also include the SQL keyword for a descending sort -- for the customer's BirthDate column you might set the SortExpression to "BirthDate DESC."

After configuring the GridView, you can turn your attention to making the changes in your factory method. In addition to whatever parameters you need to retrieve the data for your object, you need that additional string parameter with the name you used in the SortParameterName.

This example accepts an integer parameter called OrderId and a string parameter called MySortExpression. The OrderId parameter is used to retrieve the matching OrderDetail records from the Northwind database. MySortExpression will hold the SortExpression passed from the GridView. If you've been following my suggestions for setting a column's SortExpression, it can be used in the Order By clause of a SQL statement that retrieves your data.

A typical factory method that uses both parameters to create a list of OrderDetail objects would look like this:

Public Shared Function SelectOrderDetailsByOrderId( _
ByVal orderId As Integer, ByVal MySortExpression As String) _
   As List(Of OrderDetail)

Using conn As New System.Data.SqlClient.SqlConnection(connectionString)
            conn.Open()
    Dim SqlStatement As String = _
                  "SELECT * FROM [Order Details] WHERE OrderId = @OrderId"
    Dim comm As System.Data.SqlClient.SqlCommand
    If SortExpression > "" Then
       comm = New System.Data.SqlClient.SqlCommand(SqlStatement & _
                             " Order By " & SortExpression, conn)
    Else
       comm = New System.Data.SqlClient.SqlCommand(SqlStatement, conn)
    End If

    comm.Parameters.Add("@OrderId", Data.SqlDbType.Int).Value = orderId
    Using reader As System.Data.SqlClient.SqlDataReader = _
          comm.ExecuteReader(Data.CommandBehavior.SingleResult And _  
          Data.CommandBehavior.CloseConnection)
       Dim ods As New List(Of OrderDetail)
       Dim RecCount As Integer

       While reader.Read()
           Dim od As OrderDetail = CreateOrderDetailFromReader(reader)
           ods.Add(od)
       End While
       Return ods
    End Using
  End Using
End Function