how to sort DataTable columns order base on the condition

Question

I have a dynamic DataTable by executing Stored Procedure like this:

A B C D

1 0 0 1

0 0 0 1

1 1 0 1

how can I sort in descending order base on the sum of each column ? I want it:

D A B C

1 1 0 0

1 0 0 0

1 1 1 0

I have created a List to store the sum of each column:

List<Column> temptArray;
public class Column
{
    public int columnIndex;
    public int count;
}

I calculate the sum

private void calculateSum()
    {
        temptArray= new List<Column>();
        for (int column = 0; column <= dataTable.Columns.Count -1; column++)
            temptArray.Add(new Column{ columnIndex = column });

        for (int row = 0; row <= dataTable.Rows.Count - 1; row++)
            for (int column = 0; column <= dataTable.Columns.Count - 1; column++)
                temptArray[column].count+= (int)dataTable.Rows[row].ItemArray.GetValue(column);
}

it works fine.

then I sort my temptArray:

temptArray = temptArray.OrderByDescending(x => x.count).ToList();

my temptArray now should be ordered: DABC

how can I change the DataTable order like the temptArray?

Answer 1

Try to use the DataColumn.SetOrdinal method. For example:

dataTable.Columns["D"].SetOrdinal(0); 
dataTable.Columns["A"].SetOrdinal(1);
dataTable.Columns["B"].SetOrdinal(2); 
dataTable.Columns["C"].SetOrdinal(3);

Here is sample code:

// this is sample C# console application 
using System.Data;
using System.Linq; 

namespace SO
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable dt = GetTable();
            // get list of Data Table column names like A, B, C, D...
            var columnNames = (from DataColumn col in dt.Columns
                               select col.ColumnName).ToList();
            // compute sum for each column and get list of objects which having sum and column name as property. 
            var computed = columnNames.Select(c => new { ColumnName = c, Sum = dt.Compute(string.Format("Sum({0})", c), "") }).OrderByDescending(p => p.Sum).ToList();

            // set the column position based on Sum of the column 
            for (int i = 0; i < computed.Count(); i++)
            {
                dt.Columns[computed[i].ColumnName].SetOrdinal(i);
            }

        }
        // testing I have added this method to create Data Table with testing data
        static DataTable GetTable()
        {
            DataTable table = new DataTable();
            table.Columns.Add("A", typeof(int));
            table.Columns.Add("B", typeof(int));
            table.Columns.Add("C", typeof(int));
            table.Columns.Add("D", typeof(int));

            table.Rows.Add(1, 0, 0, 1);
            table.Rows.Add(0, 0, 0, 1);
            table.Rows.Add(1, 1, 0, 1);

            return table;
        }
    }
}