How to change DataTable columns order

Question

How to change Datatable columns order in c#.

Example:

am created sql table type order is Qty,Unit,Id but in program DataTable order is Id,Qty,Unit. In code Behind am directly pass DataTable to sql table type so the table order is different.

DataTable columns are: `Id,Qty,Unit.`  I want this to be: `Qty,Unit,Id` 

Please help

Answer 1

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

dataTable.Columns["Qty"].SetOrdinal(0);
dataTable.Columns["Unit"].SetOrdinal(1); 

UPDATE: This answer received much more attention than I expected. To avoid confusion and make it easier to use I decided to create an extension method for column ordering in DataTable:

Extension method:

public static class DataTableExtensions
{
    public static void SetColumnsOrder(this DataTable table, params String[] columnNames)
    {
        int columnIndex = 0;
        foreach(var columnName in columnNames)
        {
            table.Columns[columnName].SetOrdinal(columnIndex);
            columnIndex++;
        }
    }
}

Usage:

table.SetColumnsOrder("Qty", "Unit", "Id");

or

table.SetColumnsOrder(new string[]{"Qty", "Unit", "Id"});

Answer 2

This is based off of "default locale"'s answer but it will remove invalid column names prior to setting ordinal. This is because if you accidentally send an invalid column name then it would fail and if you put a check to prevent it from failing then the index would be wrong since it would skip indices wherever an invalid column name was passed in.

public static class DataTableExtensions
{
    /// <summary>
    /// SetOrdinal of DataTable columns based on the index of the columnNames array. Removes invalid column names first.
    /// </summary>
    /// <param name="table"></param>
    /// <param name="columnNames"></param>
    /// <remarks> http://stackoverflow.com/questions/3757997/how-to-change-datatable-colums-order</remarks>
    public static void SetColumnsOrder(this DataTable dtbl, params String[] columnNames)
    {
        List<string> listColNames = columnNames.ToList();

        //Remove invalid column names.
        foreach (string colName in columnNames)
        {
            if (!dtbl.Columns.Contains(colName))
            {
                listColNames.Remove(colName);
            }
        }

        foreach (string colName in listColNames)
        {
            dtbl.Columns[colName].SetOrdinal(listColNames.IndexOf(colName));
        }
}

Answer 3

I know this is a really old question.. and it appears it was answered.. But I got here with the same question but a different reason for the question, and so a slightly different answer worked for me. I have a nice reusable generic datagridview that takes the datasource supplied to it and just displays the columns in their default order. I put aliases and column order and selection at the dataset's tableadapter level in designer. However changing the select query order of columns doesn't seem to impact the columns returned through the dataset. I have found the only way to do this in the designer, is to remove all the columns selected within the tableadapter, adding them back in the order you want them selected.

Answer 4

If you have more than 2-3 columns, SetOrdinal is not the way to go. A DataView's ToTable method accepts a parameter array of column names. Order your columns there:

DataView dataView = dataTable.DefaultView;
dataTable = dataView.ToTable(true, "Qty", "Unit", "Id");

Answer 5

We Can use this method for changing the column index but should be applied to all the columns if there are more than two number of columns otherwise it will show all the Improper values from data table....................

Answer 6

Re-Ordering data Table based on some condition or check box checked. PFB :-

 var tableResult= $('#exampleTable').DataTable();

    var $tr = $(this).closest('tr');
    if ($("#chkBoxId").prop("checked")) 
                    {
                        // re-draw table shorting based on condition
                        tableResult.row($tr).invalidate().order([colindx, 'asc']).draw();
                    }
                    else {
                        tableResult.row($tr).invalidate().order([colindx, "asc"]).draw();
                    }