Reordering all rows of a datatable based on one of the fields

Question

I have a DataTable with columns such as the following:

Field1 | val 1 | val 2 | val 3
Field2 | val 1 | val 2 | val 3
Field3 | val 1 | val 2 | val 3
Field4 | val 1 | val 2 | val 3

and so so on.. all the way to Field20

What would be the best way to completely reorder the rows before binding to the grid on my page. I would use the Field column to determine where each row should go.

I did research and saw that one of the only techniques was to Clone, Delete, Re-insert the row where it should go.

If I have to do this for all of the rows since none of the rows are in their correct position does anyone have a good way of doing it?

Here is what I tried:

    DataTable dataTable2 = new DataTable();
    DataRow[] row = dataTable1.Select("Field ='" + "Field2" +"'");
    dataTable2 .Rows.InsertAt(row, 0);

Answer 1

Another solution could be:

1. Add a new column "IntegerKey" in the data table.
2. Update value of column "Integer Key" based on the desired column's value e.g. when desired Column value is say "column2" then column "IntegerKey" value = 1 when desired Column value is say "column1" then column "IntegerKey" value = 2 when desired Column value is say "column4" then column "IntegerKey" value = 3
3. datarows[] arrROWS = Datatable.Select(string.empty, "IntegerKey desc")

Answer 2

You can use LINQ to sort your DataTable. The result of the LINQ operation (dt2) is IEnumerable, but you can still it to bind your grid.

var dt = new DataTable();
dt.Columns.Add("Key", typeof(string));
dt.Columns.Add("Position", typeof(int));

dt.Rows.Add("Key1Value", 2);
dt.Rows.Add("Key2Value", 3);
dt.Rows.Add("Key3Value", 4);
dt.Rows.Add("Key4Value", 5);
dt.Rows.Add("Key5Value", 0);
dt.Rows.Add("Key6Value", 1);

var dt2 = dt.AsEnumerable().OrderBy(x => x.Field<int>("Position"));
foreach (DataRow dr in dt2)
{
    Console.WriteLine("{0} {1}", dr["Key"], dr["Position"]);
}

I edited my code so it matches your sort order. I add a column named Position, so I can sort it anyway I want. Just change the value of Position if you want different order.

If you don't want to add an extra column, you can use switch statement in the OrderBy method.

var dt = new DataTable();
dt.Columns.Add("Key", typeof(string));

dt.Rows.Add("Key1Value");
dt.Rows.Add("Key2Value");
dt.Rows.Add("Key3Value");
dt.Rows.Add("Key4Value");
dt.Rows.Add("Key5Value");
dt.Rows.Add("Key6Value");

var dt2 = dt.AsEnumerable().OrderBy(x =>
    {
        switch (x.Field<string>("Key"))
        {
            case "Key5Value": return 0;
            case "Key6Value": return 1;
            case "Key1Value": return 2;
            case "Key2Value": return 3;
            case "Key3Value": return 4;
            case "Key4Value": return 5;
            default : return -1;
        }
    }
);

foreach (DataRow dr in dt2)
{
    Console.WriteLine(dr["Key"]);
}

Basically, the OrderBy() method accepts an anonymous method that define the sort order. Since "Key5Value" returns 0, then it is placed before "Key6Value" (returns 1). Use OrderByDescending() method if you want to sort it from largest value to smallest value.

I also edit your code. The getPosition method is similar to what the OrderBy() method in my LINQ code.

using System;
using System.Data;
using System.Linq;

class Program
{
    static void Main()
    {
        var dt = new DataTable();
        dt.Columns.Add("Key", typeof(string));

        dt.Rows.Add("Key1Value");
        dt.Rows.Add("Key2Value");
        dt.Rows.Add("Key3Value");
        dt.Rows.Add("Key4Value");
        dt.Rows.Add("Key5Value");
        dt.Rows.Add("Key6Value");

        var dt2 = dt.Clone();
        foreach (DataRow dr in dt.Rows)
        {
            string key = dr["Key"].ToString();
            var dRow = dt2.NewRow();
            dRow.ItemArray = dr.ItemArray;
            dt2.Rows.InsertAt(dRow, getPosition(key));
        }

        foreach (DataRow dr in dt2.Rows)
        {
            Console.WriteLine(dr["Key"]);
        }

        Console.Write("Press any key to continue . . . ");
        Console.ReadKey(true);
    }

    static int getPosition(string key)
    {
        switch (key)
        {
            case "Key5Value":
                return 0;
            case "Key6Value":
                return 1;
            case "Key1Value":
                return 2;
            case "Key2Value":
                return 3;
            case "Key3Value":
                return 4;
            case "Key4Value":
                return 5;
            default:
                return -1;
        }
    }
}

Version 2: I add a dictionary which contains the key value (eg. Key1Value) and a pair of values. The pair values are the new key value (eg. Key 1 Value) and the position. The reason I use a dictionary is to replace the switch-case expression. You can add additional value if you want. Just add more type to the tuple.

Tuple<string, int, int, bool> // holds a sequence of a string, two integers and a boolean.

Here's the complete code.

using System;
using System.Data;
using System.Linq;
using System.Collections.Generic;

class Program
{
    static void Main()
    {
        // create table
        var dt = new DataTable();
        dt.Columns.Add("Key", typeof(string));
        dt.Columns.Add("Val1", typeof(int));
        dt.Columns.Add("Val2", typeof(int));
        dt.Columns.Add("Val3", typeof(int));
        dt.Columns.Add("Position", typeof(int));

        // populate table
        dt.Rows.Add("Key1Value", 100, 200, 300);
        dt.Rows.Add("Key2Value", 100, 200, 300);
        dt.Rows.Add("Key3Value", 100, 200, 300);
        dt.Rows.Add("Key4Value", 100, 200, 300);
        dt.Rows.Add("Key5Value", 100, 200, 300);
        dt.Rows.Add("Key6Value", 100, 200, 300);

        // initialize dictionary
        var dict = new Dictionary<string, Tuple<string, int>>() {
            { "Key1Value", new Tuple<string, int>("Key 1 Value", 2) }, // Key1Value's new value is Key 1 Value, position is 2
            { "Key2Value", new Tuple<string, int>("Key 2 Value", 3) },
            { "Key3Value", new Tuple<string, int>("Key 3 Value", 4) },
            { "Key4Value", new Tuple<string, int>("Key 4 Value", 5) },
            { "Key5Value", new Tuple<string, int>("Key 5 Value", 0) },
            { "Key6Value", new Tuple<string, int>("Key 6 Value", 1) }
        };

        // set position and new key value
        dt.AsEnumerable()
          .ToList()
            .ForEach(x => {
                        x["Position"] = dict[x.Field<string>("Key")].Item2;
                        x["Key"] = dict[x.Field<string>("Key")].Item1; // must be the last because the dictionary key is "KeyXValue", not "Key X Value"
                     });

        // sort by Position
        var dt2 = dt.AsEnumerable().OrderBy(x => x.Field<int>("Position"));

        foreach (DataRow dr in dt2)
        {
            Console.WriteLine("{0}, {1}, {2}, {3}, {4}", dr["Key"], dr["Val1"], dr["Val2"], dr["Val3"], dr["Position"]);
        }

        Console.Write("Press any key to continue . . . ");
        Console.ReadKey(true);
    }
}

Answer 3

Here is the best solution that I could some up with due to time being a factor. Please comment if you feel like it can be done in a better way.

This technique involves creating a clone of the source datatable. Then looping through the source datatable and re-adding rows to datatable2 in the desired order.

// Make a clone of the source datatable
DataTable dataTable2 = dataTable1.Clone();
// Loop through each row in the source datatable and check for key
foreach (DataRow dr in dataTable1.Rows)
{
    string key = dr["Key"].ToString();
    DataRow dRow = dataTable2.NewRow();
    switch (key)
    {
        case "Key5Value":
            // Rename actual key value if needed
            dr["Key"] = "Key Rename if needed";
            // Set new datarow item array to current row in loop item array
            dRow.ItemArray = dr.ItemArray;
            // Add datarow to desired position in dataTable 2
            dataTable2.Rows.InsertAt(dRow, 0);
            break;
        case "Key6Value":
            dr["Key"] = "Key Rename if needed";
            dRow.ItemArray = dr.ItemArray;
            dataTable2.Rows.InsertAt(dRow, 1);
            break;
        case "Key1Value":
            dr["Key"] = "Key Rename if needed";
            dRow.ItemArray = dr.ItemArray;
            dataTable2.Rows.InsertAt(dRow, 2);
            break;
        case "Key2Value":
            dr["Key"] = "Key Rename if needed";
            dRow.ItemArray = dr.ItemArray;
            dataTable2.Rows.InsertAt(dRow, 3);
            break;
        case "Key4Value":
            dr["Key"] = "Key Rename if needed";
            dRow.ItemArray = dr.ItemArray;
            dataTable2.Rows.InsertAt(dRow, 4);
            break;
        case "Key3Value":
            dr["Key"] = "Key Rename if needed";
            dRow.ItemArray = dr.ItemArray;
            dataTable2.Rows.InsertAt(dRow, 5);
            break;
    }
}