DataView.Sort - more than just asc/desc (need custom sort)

Question

I've got a report being built from a dataset. The dataset uses the Sort property to order the data. I know that I can create a sort expression like this:

"field desc, field2 asc"

But what I need now is a way to do a custom sort. In SQL, I can perform a custom sort by doing something like this:

order by 
    case when field = 'Some Value' then 0 end
    case when field = 'Another Value' then 1 end

To basically re-define my sort (i.e, Some Value comes before Another Value).

Is it possible to do something similar as a sort expression against a DataView?

Answer 1

If you are using data view then you can use:

DataView.Sort = "Column ASC" for 'Ascending' and

DataView.Sort = "Column DESC" for 'Descending'

Answer 2

Based on Stuart's great suggestion about implementing IComparable, I implemented a simple wrapper which can be used when you are not able to modify the class you want to store in the table.

public struct Comparable<T> : IComparable<Comparable<T>>, IComparable<T>, IComparable, IEquatable<Comparable<T>>, IEquatable<T>
    {
    private readonly IComparer<T> _Comparer;

    public readonly T Value;

    /// <summary>
    /// Creates a comparable wrapper.
    /// </summary>
    /// <param name="value">Original value being wrapped.</param>
    /// <param name="comparer">Comparer to use.</param>
    public Comparable(T value, IComparer<T> comparer)
        {
        if (comparer == null)
            throw new ArgumentNullException(nameof(comparer));

        Value = value;
        _Comparer = comparer;
        }

    /// <summary>
    /// Pass-through ToString() to wrapped object. Will throw NullReferenceException if object is null.
    /// </summary>
    public override String ToString() => Value.ToString();

    /// <summary>
    /// Pass-through GetHashCode() to wrapped object. Will throw NullReferenceException if object is null.
    /// </summary>
    public override Int32 GetHashCode() => Value.GetHashCode();

    public Int32 CompareTo(Comparable<T> other) => Compare(Value, other.Value);

    public Int32 CompareTo(T other) => Compare(Value, other);

    public Int32 CompareTo(Object other)
        {
        if (other is Comparable<T> otherComparer)
            return CompareTo(otherComparer);

        if (other is T otherValue)
            return CompareTo(otherValue);

        throw new InvalidCastException($"Cannot convert value to type {typeof(T).FullName}");
        }

    private Int32 Compare(T value1, T value2) => _Comparer.Compare(value1, value2);

    public Boolean Equals(Comparable<T> other) => CompareTo(other) == 0;

    public Boolean Equals(T other) => CompareTo(other) == 0;
    }

Going further, I made another wrapper which allows for a value to have a separate sortable and formatted value, so you can for example wrap up some Decimal items which represent money, have them sorted according to the underlying Decimal value but formatted as with currency symbol.

public struct Comparable<TSource, TComparable> : IComparable<Comparable<TSource, TComparable>>, IComparable<Comparable<TComparable>>, IComparable<TComparable>, IComparable, IEquatable<Comparable<TSource, TComparable>>, IEquatable<Comparable<TComparable>>, IEquatable<TComparable>
    {
    private readonly IComparer<TComparable> _Comparer;

    public readonly TSource Value;
    public readonly TComparable CompareValue;

    /// <summary>
    /// Creates a comparable wrapper.
    /// </summary>
    /// <param name="value">Original value being wrapped.</param>
    /// <param name="compareValue">Value used for comparison.</param>
    /// <param name="comparer">Comparer to use. Null uses default comparer for <typeparamref name="TComparable"/>.</param>
    public Comparable(TSource value, TComparable compareValue, IComparer<TComparable> comparer = null)
        {
        Value = value;
        CompareValue = compareValue;
        _Comparer = comparer;
        }

    /// <summary>
    /// Pass-through ToString() to wrapped <see cref="Value"/>. Will throw NullReferenceException if object is null.
    /// </summary>
    public override String ToString() => Value.ToString();

    /// <summary>
    /// Pass-through GetHashCode() to wrapped <see cref="CompareValue"/>. Will throw NullReferenceException if object is null.
    /// </summary>
    public override Int32 GetHashCode() => CompareValue.GetHashCode();

    public Int32 CompareTo(Comparable<TSource, TComparable> other) => Compare(CompareValue, other.CompareValue);

    public Int32 CompareTo(Comparable<TComparable> other) => Compare(CompareValue, other.Value);

    public Int32 CompareTo(TComparable other) => Compare(CompareValue, other);

    public Int32 CompareTo(Object other)
        {
        if (other is Comparable<TSource, TComparable> otherComparer)
            return CompareTo(otherComparer);

        if (other is Comparable<TComparable> otherComparerSimple)
            return CompareTo(otherComparerSimple);

        if (other is TComparable otherValue)
            return CompareTo(otherValue);

        throw new InvalidCastException($"Cannot convert value to type {typeof(TComparable).FullName}");
        }

    private Int32 Compare(TComparable value1, TComparable value2) => (_Comparer ?? Comparer<TComparable>.Default).Compare(value1, value2);

    public Boolean Equals(Comparable<TSource, TComparable> other) => CompareTo(other) == 0;

    public Boolean Equals(Comparable<TComparable> other) => CompareTo(other) == 0;

    public Boolean Equals(TComparable other) => CompareTo(other) == 0;
    }

Using the example above, you can wrap up an array of values with separate formatting like so:

var Payments = new[] { 1.23m, 5.67m, 9.99m };
var Wrapped = Payments
    .Select(v => new Comparable<String, Decimal>(v.ToString("C"), v))
    .ToArray();

var MyDataTable = new DataTable();
MyDataTable.Columns.Add("Payments", typeof(Comparable<Decimal, String>));

foreach (var value in Wrapped)
    MyDataTable.Rows.Add(new[] { value });

You can now use this DataTable in a DataGridView or similar and the rows will be sorted according to their underlying Decimal value, but displayed as the formatted string we generated.

Hope this will be useful for someone!

Answer 3

If your context allows it (.NET 4+), you can simply use something like this :

DataView orderedDataTableView = sourceDataTable.AsEnumerable()
                                               .OrderByDescending(row => !string.IsNullOrWhiteSpace(row.Field<string>("COLUMN_NAME")))
                                               .ThenBy(row => row.Field<string>("COLUMN_NAME"))
                                               .AsDataView();

In this example, null values are placed at the end of the table (note that both empty and white spaces strings will have the same rank) and then order the requested column in ascending order.

Answer 4

I know this post is a bit older, but I went about this slightly different by implementing IComparable. In this example, I wanted to sort by version (which is in the format 0.0.0.0 as a string).

Here is the Versioning class which implements IComparable:

public class Versioning : IComparable {
    string _version;

    int _major;
    public int Major { 
        get { return (_major); } 
        set { _major = value; } 
    }

    int _minor;
    public int Minor {
        get { return (_minor); }
        set { _minor = value; }
    }

    int _beta;
    public int Beta {
        get { return (_beta); }
        set { _beta = value; }
    }

    int _alpha;
    public int Alpha {
        get { return (_alpha); }
        set { _alpha = value; }
    }

    public Versioning(string version) {
        _version = version;

        var splitVersion = SplitVersion();
        if (splitVersion.Length < 4) {
            Major = Minor = Beta = Alpha = 0;
        }

        if (!int.TryParse(splitVersion[0], out _major)) _major = 0;
        if (!int.TryParse(splitVersion[1], out _minor)) _minor = 0;
        if (!int.TryParse(splitVersion[2], out _beta)) _beta = 0;
        if (!int.TryParse(splitVersion[3], out _alpha)) _alpha = 0;
    }

    string[] SplitVersion() {
        return (_version.Split('.'));
    }

    int GetCompareTo(Versioning versioning) {
        var greater = -1;
        var equal = 0;
        var less = 1;

        if (Major > versioning.Major) return (greater);
        if (Major < versioning.Major) return (less);
        if (Minor > versioning.Minor) return (greater);
        if (Minor < versioning.Minor) return (less);
        if (Beta > versioning.Beta) return (greater);
        if (Beta < versioning.Beta) return (less);
        if (Alpha > versioning.Alpha) return (greater);
        if (Alpha < versioning.Alpha) return (less);

        return (equal);
    }

    public int CompareTo(Versioning versioning) {
        return (GetCompareTo(versioning));
    }

    public override string ToString() {
        return (_version);
    }

    public int CompareTo(object obj) {
        if (obj == null) return (1);
        return (GetCompareTo((Versioning)obj));
    }
}

And when you add the column to the table, instead of adding Version as a string, you add it as the Versioning class:

_table.Columns.Add("Version", typeof(Versioning));
_view = new View(_table);

And then sort normally:

_view.Sort = "Version";

Answer 5

I like BFree's answer, though I'd worry about the risk that my code would end up updating the cloned table rather than the real one. (I haven't thought through it enough to know if that's actually an issue if you're only using the extension method in a DataView.)

You can do this on the original DataTable by adding a calculated DataColumn to it (using the Expression property) and then sorting on its value.

In your case it would be something like:

DataColumn c = myTable.Columns.Add("Sort", typeof(int));
c.Expression = "iif(field='SomeValue', 0, iif(field='AnotherValue', 1, 2))";

which sorts SomeValue first, AnotherValue second, and everything else after that.

Answer 6

Ok, I just whipped this up real quick, and didn't do all the neccessary error handling and null checking, but it should give you an idea and should be enough to get you started:

public static class DataTableExtensions
{
    public static DataView ApplySort(this DataTable table, Comparison<DataRow> comparison)
    {

        DataTable clone = table.Clone();
        List<DataRow> rows = new List<DataRow>();
        foreach (DataRow row in table.Rows)
        {
            rows.Add(row);    
        }

        rows.Sort(comparison);

        foreach (DataRow row in rows)
        {
            clone.Rows.Add(row.ItemArray);
        }

        return clone.DefaultView;
    }


}

Usage:

    DataTable table = new DataTable();
    table.Columns.Add("IntValue", typeof(int));
    table.Columns.Add("StringValue");

    table.Rows.Add(11, "Eleven");
    table.Rows.Add(14, "Fourteen");
    table.Rows.Add(10, "Ten");
    table.Rows.Add(12, "Twelve");
    table.Rows.Add(13, "Thirteen");

//Sort by StringValue:

 DataView sorted = table.ApplySort((r, r2) =>
        {
            return ((string)r["StringValue"]).CompareTo(((string)r2["StringValue"]));
        });

Result:

11 Eleven

14 Fourteen

10 Ten

13 Thirteen

12 Twelve

//Sort by IntValue:

DataView sorted = table.ApplySort((r, r2) =>
            {
                return ((int)r["IntValue"]).CompareTo(((int)r2["IntValue"]));
            });

Result:

10 Ten

11 Eleven

13 Thirteen

12 Twelve

14 Fourteen

EDIT: Changed it to extension method.

Now in your Lambda, (or you can create a full blown Comparison method) you can do any kind of custom sorting logic that you need. Remember, -1 is less than, 0 is equal to, and 1 is greater than.

Answer 7

You could use an if or a switch statement to get similar functionality to the select case statement:

            if (Something == "1")
                MyView.Sort = "Field1 ASC";
            else
                MyView.Sort = "Field2 ASC";

OR

            switch (MyProperty)
            {
                case 1:
                    MyView.Sort = "Field1 ASC";
                    break;
                case 2:
                    MyView.Sort = "Field2 ASC";
                    break;
                default:
                    MyView.Sort = "Field3 ASC";
                    break;
            }

Answer 8

I don't think so. You could however change you SQL to return a "CustomSort" column that is the result of your case statement:

select
    (case when f = 'a' then 0 else 1 end) as CustomSort
from MyTable