Sorting DataTable-columns

Question

I have a datatable something like this:

     |  Col1  |  Col6  |  Col3  |  Col43 |  Col0  |
---------------------------------------------------
RowA |   1    |    6   |   54   |    4   |   123  |

As you see, the Cols are not sorted by their numbers. That is what I want it to look like after the "magic":

     |  Col0  |  Col1  |  Col3  |  Col6  |  Col43 |
---------------------------------------------------
RowA |   123  |    1   |   54   |    6   |    4   |

Is there a built-in function for such things in C#? And if not, how could I get started with this?

Solution 1

You don't need to sort the columns in the DataTable object, just copy the column names to an array and sort the array. Then use the array to access the column values in the right order.

Sample:

class Program
    {
        static void Main(string[] args)
        {
            var dt = new DataTable { Columns = { "A3", "A2", "B1", "B3", "B2", "A1" } };
            dt.BeginLoadData();
            dt.Rows.Add("A3val", "A2val", "B1val", "B3val", "B2val", "A1val");
            dt.EndLoadData();

            string[] names=new string[dt.Columns.Count];
            for (int i = 0; i < dt.Columns.Count;i++ )
            {
                names[i] = dt.Columns[i].ColumnName;
            }
            Array.Sort(names);

            foreach (var name in names)
            {
                Console.Out.WriteLine("{0}={1}", name, dt.Rows[0][name]);
            }
            Console.ReadLine();
        }

Solution 2

You can do the column sorting in the table itself:

dt.Columns["Col0"].SetOrdinal(0);
dt.Columns["Col1"].SetOrdinal(1);
dt.Columns["Col2"].SetOrdinal(2);

Solution 3

Here is my code, surely not is the best solution but works. In my case I let a fixed column that could be "Nombre" or "Problem", this always be the first in column order.

// class
public class stringInt
{
    public string Nombre;
    public int orden;
}

// function

static public DataTable AlphabeticDataTableColumnSort(DataTable dtTable)
{
    //vamos a extraer todos los nombres de columnas, meterlos en una lista y ordenarlo
    int orden = 1;
    List<stringInt> listaColumnas = new List<stringInt>();

    foreach (DataColumn dc in dtTable.Columns)
    {
        stringInt columna = new stringInt();
        columna.Nombre = dc.Caption;
        if ((dc.Caption != "Problema") && (dc.Caption != "Nombre")) columna.orden = 1;
        else columna.orden = 0;
        listaColumnas.Insert(0,columna);
     }
     listaColumnas.Sort(delegate(stringInt si1, stringInt si2) { return si1.Nombre.CompareTo(si2.Nombre); });

     // ahora lo tenemos ordenado por nombre de columna
     foreach (stringInt si in listaColumnas)
     { 
         // si el orden es igual a 1 vamos incrementando
         if (si.orden != 0)
         {
             si.orden = orden;
             orden++;
         }
      }
      listaColumnas.Sort(delegate(stringInt si1, stringInt si2) { return si1.orden.CompareTo(si2.orden); });

       // tenemos la matriz con el orden de las columnas, ahora vamos a trasladarlo al datatable
       foreach(stringInt si in listaColumnas)
          dtTable.Columns[si.Nombre].SetOrdinal(si.orden);


        return dtTable;
}

Solution 4

var columnArray = new DataColumn[table.Columns.Count];
table.Columns.CopyTo(columnArray, 0);
var ordinal = -1;
foreach (var orderedColumn in columnArray.OrderBy(c => c.ColumnName))
    orderedColumn.SetOrdinal(++ordinal);

Solution 5

You'll probably need to implement your IComparer<T>, as "natural" order would be: Col0, Col1, Col3, Col43 and Col6. ("4" comes before "6")

View more solutions

Comments

[F.P]

Okay, I know what you mean, and it would work, quite well, but it's not quite... "genuine"... Still, thanks for the advice.

[Ariel Popovsky]

If you want something more "genuine" perhaps you can work on the other end. Who is producing the datatable and why the columns are not ordered? You probably can't control that query or process but ir you can, then sort the columns there.

[F.P]

Problem is, that I can't control the order in which the user adds new columns to the data-table. So, e.g. if he adds a new column that should be in between two already existing ones, but is added as last column, I'd have to compare each one until it fits :-/

[F.P]

Still, I like the idea. I'll add the sort-thing to the get-accessor of the string[], so it's done without much overhead. Nice idea!

[Ariel Popovsky]

What about sorting when the user adds the column and setting the ordinal like JBrooks suggested. Suppose you have cols "A1", "A4" and user adds "A2", you go through the column names look for the place to insert the new column and set the ordinal manually. You should check what happens when you set the ordinal to an already existing value, maybe the are all renumbered automatically or maybe you have to increase the ordinal on the following columns manually.

[mattpm]

agreed Andy, if people are going to vote down, they should at least have the courtesy to explain why. One reason might be that the Sort will sort alphabetically but if you had Col1, Col2 and Col11, you'd find the resulting sort would be Col1, Col11, Col2.