DataGridView Custom Sorting

I have a DataGridView with 6 columns.

Example:

column1    column2    column3    column4    column5    column6
J6         RES-0112G  123.123    456.456    180        1111
FID2       FIDUCIAL   5.123     -50.005     90         FIDUCIAL
R100       RES-0113G  1.1       -123.123    90         1111
C12        CAP-1234H -99.99     -987.123    45         2222
Q1         CAP-1234Z -99.99     -987.123    45         4444
J3         RES-0112G  123.123    999.999    0          1111
FID1       FIDUCIAL   23.123     23.123     0          FIDUCIAL
F1         CAP-1234  -88.99     -555.111    45         DDDD
C11        CAP-1234Z -123.99    -123.123    270        abc2222

And I would like to sort it in a special order. Let's say I want to sort it by the last value (column 6) in this sequence:

FIDUCIAL, 1111, 2222, DDDD, 4444

AND then sort it secondly by the 2nd column alpha-numerically. (NOTE THE abc2222 sorts by "2222" not "abc").

So the updated DataGridView would look like this: (For the FIDUCIALS I would like to sort by column 1 instead of column 2)

column1    column2    column3    column4    column5    column6
FID1       FIDUCIAL   23.123     23.123     0          FIDUCIAL
FID2       FIDUCIAL   5.123     -50.005     90         FIDUCIAL
J6         RES-0112开发者_StackOverflow中文版G  123.123    456.456    180        1111
J3         RES-0112G  123.123    999.999    0          1111
R100       RES-0113G  1.1       -123.123    90         1111
C11        CAP-1234C -123.99    -123.123    270        abc2222
C12        CAP-1234H -99.99     -987.123    45         2222
F1         CAP-1234  -88.99     -555.111    45         DDDD
Q1         CAP-1234Z -99.99     -987.123    45         4444

Does anyone know how to properly sort this? I am using a SortableBindingList<>

I've created a sample code to show you the technique itself. I hope you will not find it complicated to adapt to your needs. The basic idea is that you group by required column and then apply custom sort inside the group. I didn't grasp the sorting algorithm for column6, so I've made a simple sorting based on your sample data. Hope it helps!

class Program
{
    public class Row
    {
        public string Column1 { get; set; }
        public string Column2 { get; set; }
        public string Column3 { get; set; }
        public string Column4 { get; set; }
        public string Column5 { get; set; }
        public string Column6 { get; set; }
    }

    static void Main(string[] args)
    {
        var grid = new []
                       {
                           new Row { Column1 = "J6", Column2 = "RES-0112G", Column3 = "123.123", Column4 = "456.456", Column5 = "180", Column6 = "1111"},
                           new Row { Column1 = "FID2", Column2 = "FIDUCIAL", Column3 = "5.123", Column4 = "-50.005", Column5 = "90", Column6 = "FIDUCIAL"},
                           new Row { Column1 = "R100", Column2 = "RES-0113G", Column3 = "1.1", Column4 = "-123.123", Column5 = "90", Column6 = "1111"},
                           new Row { Column1 = "C12", Column2 = "CAP-1234H", Column3 = "-99.99", Column4 = "-987.123", Column5 = "45", Column6 = "2222"},
                           new Row { Column1 = "Q1", Column2 = "CAP-1234Z", Column3 = "-99.99", Column4 = "-987.123", Column5 = "45", Column6 = "4444"},
                           new Row { Column1 = "J3", Column2 = "RES-0112G", Column3 = "123.123", Column4 = "999.999", Column5 = "0", Column6 = "1111"},
                           new Row { Column1 = "FID1", Column2 = "FIDUCIAL", Column3 = "23.123", Column4 = "23.123", Column5 = "0", Column6 = "FIDUCIAL"},
                           new Row { Column1 = "F1", Column2 = "CAP-1234", Column3 = "-88.99", Column4 = "-555.111", Column5 = "45", Column6 = "DDDD"},
                           new Row { Column1 = "C11", Column2 = "CAP-1234C", Column3 = "-123.99", Column4 = "-123.123", Column5 = "270", Column6 = "abc2222"}
                       };

        var result = grid.
            GroupBy(r => GetSortValue(r.Column6)).
            OrderBy(g => g.Key, new Column6Comparer()).
            SelectMany(g => g.OrderBy(r => r, new RowComparer()));

        foreach (var row in result)
        {
            Console.WriteLine("{0,-6}{1,-13}{2,-10}{3,-12}{4,-6}{5,-10}", row.Column1, row.Column2, row.Column3, row.Column4, row.Column5, row.Column6);
        }
    }

    private static string GetSortValue(string source)
    {
        Match match = new Regex(@"[\d]+").Match(source);
        return match.Success ? match.Value : source;
    }

    private class Column6Comparer : IComparer<string>
    {
        private Dictionary<string, int> ValueToOrder { get; set; } 
        public Column6Comparer()
        {
            ValueToOrder = new Dictionary<string, int>();
            ValueToOrder["FIDUCIAL"] = 0;
            ValueToOrder["1111"] = 1;
            ValueToOrder["2222"] = 2;
            ValueToOrder["DDDD"] = 3;
            ValueToOrder["4444"] = 4;
        }

        public int Compare(string x, string y)
        {
            return ValueToOrder[GetSortValue(x)].CompareTo(ValueToOrder[GetSortValue(y)]);
        }

    }

    private class RowComparer : IComparer<Row>
    {
        public int Compare(Row x, Row y)
        {
            if (x.Column2 == "FIDUCIAL" && y.Column2 == "FIDUCIAL")
            {
                return x.Column1.CompareTo(y.Column1);
            }

            if (x.Column2 == "FIDUCIAL" || y.Column2 == "FIDUCIAL")
            {
                return x.Column2 == "FIDUCIAL" ? 0 : 1;
            }

            return x.Column2.Substring(4).CompareTo(y.Column2.Substring(4));
        }
    }
}

SortableBindingList works against you, because it was designed to sort data using one property (column) values. You will have to sort your data before adding to binding list and disable sorting. You can sort your data with a comparer like this.

public class RowComparer : IComparer<Row>
    {
    private List<string> myOrder = new List<string>(new string[] { "FIDUCIAL", "1111", "2222", "DDDD", "4444" });

    private int primaryOrder(Row x)
        {
        int index = myOrder.FindIndex(v => x.Column6.Contains(v));
        return (index >= 0) ? index : myOrder.Count;
        }

    public int Compare(Row x, Row y)
        {
        int result = primaryOrder(x).CompareTo(primaryOrder(y));
        if (result != 0)
            return result;

        return x.Column2.CompareTo(y.Column2);
        }
    }

Here's a way to write Comparer that is even more flexible:

    public class CustomComparer : IComparer<Row>
        {
        Predicate<Row>[] myOrder = new Predicate<Row>[]
        {
            (row) => row.Column6 == "FIDUCIAL",
            (row) => row.Column6.Contains("1111") && !row.Column3.Contains("unwanted"),
            (row) => row.Column6.Contains("2222"),
            (row) => row.Column6.StartsWith("DDDD"),
            (row) => row.Column6 == "4444",
        };

        private int primaryOrder(Row row)
            {
            for (int i = 0; i < myOrder.Length; i++)
                {
                if (myOrder[i](row))
                    return i;
                }
            return myOrder.Length;
            }

        public int Compare(Row x, Row y)
            {
            int result = primaryOrder(x).CompareTo(primaryOrder(y));
            if (result != 0)
                return result;

            return x.Column2.CompareTo(y.Column2);
            }
        }

Here is more generic approach, which can be used for multiple cases like this,

public class Row
    {
        public string Column1 { get; set; }
        public string Column2 { get; set; }
        public string Column3 { get; set; }
        public string Column4 { get; set; }
        public string Column5 { get; set; }
        public string Column6 { get; set; }
    }

public interface IComplexSorter<T> : IComparer<T>
    {
    }

public class ComplexSorter<T> : IComplexSorter<T>
    {
        private IList<IComplexSorter<T>> _rowSorters;

        public ComplexSorter()
        {
            _rowSorters = new ReadOnlyCollectionBuilder<IComplexSorter<T>>();
        }

        public int Compare(T x, T y)
        {
            foreach (var sorter in Sorters)
            {
                int value = sorter.Compare(x, y);
                if (value != 0)
                    return value;
            }
            return 0;
        }

        public IList<IComplexSorter<T>> Sorters
        {
            get { return _rowSorters; }
        }
    }

public class RowColumn1Sorter : IComplexSorter<Row>
    {
        public int Compare(Row x, Row y)
        {
            if (x.Column6 == "FIDUCIAL" && y.Column6 == "FIDUCIAL")
                return x.Column1.CompareTo(y.Column1);

            if (x.Column6 == "FIDUCIAL" || y.Column6 == "FIDUCIAL")
                return x.Column6 == "FIDUCIAL" ? 0 : 1;

            return 0;
        }
    }

public class RowColumn2Sorter : IComplexSorter<Row>
    {
        public int Compare(Row x, Row y)
        {
            return x.Column2.Substring(4).CompareTo(y.Column2.Substring(4));
        }
    }

public class RowColumn6Sorter : IComplexSorter<Row>
    {
        private static IList<string> SortOrder;

        public RowColumn6Sorter()
        {
            SortOrder = new string[] { "FIDUCIAL", "1111", "2222", "DDDD", "4444" }.ToList();
        }

        public int Compare(Row x, Row y)
        {
            string xSortValue = SortOrder.Contains(x.Column6) ? x.Column6 : ExtractNumeric(x.Column6);
            string ySortValue = SortOrder.Contains(y.Column6) ? y.Column6 : ExtractNumeric(y.Column6);
            int xKey = SortOrder.IndexOf(xSortValue);
            int yKey = SortOrder.IndexOf(ySortValue);
            xKey = xKey == -1 ? SortOrder.Count:xKey;
            yKey = yKey == -1 ? SortOrder.Count:yKey;
            return xKey - yKey;
        }

        private string ExtractNumeric(String value)
        {
            Match match = new Regex(@"[\d]+").Match(value);
            return match.Success ? match.Value : value;
        }
    }

class Program
    {
        static void Main(string[] args)
        {
            var data = new[]
            {
                new Row { Column1 = "J6", Column2 = "RES-0112G", Column3 = "123.123", Column4 = "456.456", Column5 = "180", Column6 = "1111"},
                new Row { Column1 = "FID2", Column2 = "FIDUCIAL", Column3 = "5.123", Column4 = "-50.005", Column5 = "90", Column6 = "FIDUCIAL"},
                new Row { Column1 = "R100", Column2 = "RES-0113G", Column3 = "1.1", Column4 = "-123.123", Column5 = "90", Column6 = "1111"},
                new Row { Column1 = "C12", Column2 = "CAP-1234H", Column3 = "-99.99", Column4 = "-987.123", Column5 = "45", Column6 = "2222"},
                new Row { Column1 = "Q1", Column2 = "CAP-1234Z", Column3 = "-99.99", Column4 = "-987.123", Column5 = "45", Column6 = "4444"},
                new Row { Column1 = "J3", Column2 = "RES-0112G", Column3 = "123.123", Column4 = "999.999", Column5 = "0", Column6 = "1111"},
                new Row { Column1 = "FID1", Column2 = "FIDUCIAL", Column3 = "23.123", Column4 = "23.123", Column5 = "0", Column6 = "FIDUCIAL"},
                new Row { Column1 = "F1", Column2 = "CAP-1234", Column3 = "-88.99", Column4 = "-555.111", Column5 = "45", Column6 = "DDDD"},
                new Row { Column1 = "C11", Column2 = "CAP-1234C", Column3 = "-123.99", Column4 = "-123.123", Column5 = "270", Column6 = "abc2222"}
            }.ToList();

            ComplexSorter<Row> rowSorter = new ComplexSorter<Row>();
            rowSorter.Sorters.Add(new RowColumn6Sorter());
            rowSorter.Sorters.Add(new RowColumn1Sorter());
            rowSorter.Sorters.Add(new RowColumn2Sorter());

            data.Sort(rowSorter);

            foreach (var row in data)
            {
                Console.WriteLine("{0,-6}{1,-13}{2,-10}{3,-12}{4,-6}{5,-10}", row.Column1, row.Column2, row.Column3, row.Column4, row.Column5, row.Column6);
            }
            Console.ReadKey();
        }
    }