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

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 2

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 3

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 4

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

Answer 5

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 6

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.