Deborah's Developer MindScape

Last night, my husband (who is also a .NET developer) asked me about sorting a DataTable by user-defined columns. (Yes, we are a pretty exciting couple!)

Most developers that work with DataTables know how to sort the DataTable using a DataView.Sort. If you are interested in that technique, you can view the msdn documentation here. This post is about sorting using Linq.

NOTE: Be sure to set a reference to System.Data.DatasetExtensions.

If you know ahead of time which columns of the DataTable should be sorted, you can use LINQ to DataTables like this.

In C#:

var query = from c in dt.AsEnumerable()
            orderby c.Field<DateTime?>("LastPurchaseDate"),
                    c.Field<string>("LastName") descending
            select c;
DataView dv   = query.AsDataView();

In VB:

Dim query = From c In dt.AsEnumerable _
            Order By c.Field(Of DateTime?)("LastPurchaseDate"), _
                     c.Field(Of String)("LastName") Descending
Dim dv As DataView = query.AsDataView

The dt variable represents the table you wish to sort. The AsEnumerable is an extension method on the DataTable that allows you to use Linq with it. The Order By clause takes any number of columns. You must specify the data type and then the column name or index. To sort descending, use the Descending keyword.

A nullable DateTime (represented by DateTime?) ensures that the code correctly handles any null values in the table.

In the above examples, the data is sorted first by LastPurchaseDate (ascending) and then by LastName (descending).

The resulting DataView can be bound to a grid or other control.

If you prefer Lambda expressions, you can do this same sort as follows:

In C#:

var query2 = dt.AsEnumerable()
           .OrderBy(c=> c.Field<DateTime?>("LastPurchaseDate"))
           .ThenByDescending(c=> c.Field<string>("LastName"));
DataView dv2   = query.AsDataView();

In VB:

Dim query2 = dt.AsEnumerable _
    .OrderBy(Function(c) c.Field(Of DateTime?)("LastPurchaseDate")) _
    .ThenByDescending(Function(c) c.Field(Of String)("LastName"))
Dim dv2 As DataView = query.AsDataView

This code performs the same sort as the prior examples.

But neither of these techniques work well if you want the user to select any number of columns to use for the sort. In that case, you need something a little more full-featured.

The first step to building a more generalized sort is to build your own comparer class.

In C#:

private class RowComparer : IComparer<DataRow>
{
    public Dictionary<int, SortOrder> SortColumns { get; set; }

    public int Compare(System.Data.DataRow x, System.Data.DataRow y)
    {
        int returnValue  = 0;
        foreach (int key in SortColumns.Keys)
        {
            int compareResult ;

            // Check for nulls
            if (x.ItemArray[key] == DBNull.Value
                       && y.ItemArray[key] == DBNull.Value)
                compareResult = 0;
            else if (x.ItemArray[key] == DBNull.Value)
                compareResult = -1;
            else if (y.ItemArray[key] == DBNull.Value)
                compareResult = 1;
            else
            {
                // Execute the compare based on the column type
                if (x.Table.Columns[key].DataType.Name ==
                                            typeof(Decimal).Name)
                    compareResult =
                         Decimal.Compare((decimal)x.ItemArray[key],
                                         (decimal)y.ItemArray[key]);

                else if (x.Table.Columns[key].DataType.Name ==
                                            typeof(DateTime).Name)
                    compareResult =
                         DateTime.Compare((DateTime)x.ItemArray[key],
                                          (DateTime)y.ItemArray[key]);
                else
                    // Compare anything else as a string
                    compareResult =
                         String.Compare(x.ItemArray[key].ToString(),
                                        y.ItemArray[key].ToString());
            }

            if (compareResult != 0)
            {
                returnValue =
                   SortColumns[key] == SortOrder.Ascending ?
                                  compareResult: -compareResult;
                break;
            }
        }
        return returnValue;
    }
}

In VB:

Public Class RowComparer
    Implements IComparer(Of DataRow)

    Private _sortColumns As Dictionary(Of Integer, SortOrder)
    Public Property SortColumns() As Dictionary(Of Integer, SortOrder)
        Get
            Return _sortColumns
        End Get
        Set(ByVal value As Dictionary(Of Integer, SortOrder))
            _sortColumns = value
        End Set
    End Property

    Public Function Compare(ByVal x As System.Data.DataRow, _
         ByVal y As System.Data.DataRow) As Integer _
         Implements System.Collections.Generic.IComparer( _
                            Of System.Data.DataRow).Compare
        Dim returnValue As Integer = 0
        For Each key As Integer In SortColumns.Keys
            Dim compareResult As Integer

            ‘ Handle DBNull.
            Dim xValue As Object = _
              If(x.Item(key) Is DBNull.Value, Nothing, x.Item(key))
            Dim yValue As Object = _
              If(y.Item(key) Is DBNull.Value, Nothing, y.Item(key))

            ‘ Execute the appropriate compare based on the column type
            Select Case x.Table.Columns(key).DataType.Name
                Case GetType(Decimal).Name
                    compareResult = _
                        Decimal.Compare(CType(xValue, Decimal), _
                                       CType(yValue, Decimal))

                Case GetType(DateTime).Name
                    compareResult = _
                        DateTime.Compare(CType(xValue, DateTime), _
                                         CType(yValue, DateTime))

                Case Else
                    ‘ Compare anything else as a string
                    compareResult = _
                         String.Compare(x.Item(key).ToString, _
                                        y.Item(key).ToString)
            End Select

            If compareResult <> 0 Then
                returnValue = _
                  If(SortColumns(key) = SortOrder.Ascending, _
                          compareResult, -compareResult)
                Exit For
            End If
        Next
        Return returnValue

    End Function
End Class

This class defines a comparer to use when sorting a DataRow. The sortColumns property is a Dictionary that stores the index of the column to use as the sort, and a SortOrder to define whether to sort ascending or descending. Presumably, the values for this Dictionary were obtained from the user.

The Compare method does all of the work. It compares any two DataRows to determine how each row should be sorted against any other row. The method processes each of the sortColumns.

First, it checks for a DBNull. The DBNull checking is different in the C# code and VB code. The C# code checks for a DBNull and manually sets the result of the compare. The VB code simply sets the value to Nothing if it is DBNull.

The code performs a compare based on the type of column. This is necessary because the user would expect decimals to sort as numbers, not as strings. You can add any other data types here as you require. Any data types not specifically handled will be handled as a string.

Note that a Select/Case statement was used in VB, but if/else if was used in C#. This is because C# requires its switch/case statements to switch based on constant values, not variables.

Regardless of the datatype, the result of the type-specific compare method is:

• -1: If the value of x is less than the value of y.
• 1: if the value of x is great than the value of y.
• 0: if the value of x and y are equal.

If the resulting value is not 0, the loop can exit because the comparison is complete. If the resulting value is 0, meaning the columns are equal, the loop continues and the next column in the set of sort columns is checked.

You then use this class as follows.

In C#:

Dictionary<int, SortOrder> sortColumns =
                    new Dictionary<int, SortOrder>();
sortColumns.Add(2,SortOrder.Ascending);
sortColumns.Add(1, SortOrder.Descending);

RowComparer comp = new RowComparer();
comp.SortColumns = sortColumns;

var query3 = dt.AsEnumerable().OrderBy(q => q, comp);
DataView dv3 = query3.AsDataView();

In VB:

Dim sortColumns As New Dictionary(Of Integer, SortOrder)
sortColumns.Add(2, SortOrder.Ascending)
sortColumns.Add(1, SortOrder.Descending)

Dim comp As New RowComparer
comp.SortColumns = sortColumns

Dim query3 = dt.AsEnumerable.OrderBy(Function(q) q, comp)
Dim dv3 As DataView = query3.AsDataView

The first set of code sets up the Dictionary of sort columns. The sort columns are hard-coded in this example, but presumably they would come from the user.

The code then creates an instance of the new RowComparer class and passes in the set of columns.

NOTE: You could define a parameterized constructor and pass in the sorted columns instead of using a property, if desired.

The Lambda expression to perform the sort is then greatly simplified. It just passes in the desired comparer.

That’s it. You now have the ability to sort your DataTable using any user-defined set of columns.

Enjoy!