DataTable remove columns and re-order the columns

Question

I want to remove unwanted columns from a datatable and arrange the order of the columns in pre-defined order

For example, my table columns are like below,

 Col2|Col1|Col3|Test|Test1|Col5|Col4|Some col name|Col6

I want to remove Test, Test1 and Some col name and reorder by datatable into below format

Col1|Col2|Col3|Col4|Col5|Col6

// I need the below columns
List<string> tableColumns = new List<string>();
tableColumns.Add("Col1");
tableColumns.Add("Col2");
tableColumns.Add("Col3");
tableColumns.Add("Col4");
tableColumns.Add("Col5");
tableColumns.Add("Col6");

List<DataColumn> tblColumns = MyDataTable.Columns.Cast<DataColumn>().ToList();

//Remove unwanted columns
foreach (DataColumn col in tblColumns)
{
  if (!tableColumns.Contains(col.ColumnName.Trim()))
  {
   MyDataTable.Columns.Remove(col);
  }
}

Now how do I re-order the columns in the below order?

Col1|Col2|Col3|Col4|Col5|Col6

I tried like in the below code, but it fails if all items in tableColumns doesn’t exists in datatable. Also some times data table column name has some empty space (ex “ Col1”)

foreach (var col in tableColumns)
{

     MyDataTable.Columns[col].SetOrdinal(tableColumns.IndexOf(col));

}

Which is the best way to remove unwanted columns and re-arrange the columns?

Answer 1

After you remove the unwanted columns, here is how you can order them:

int index = 0;

foreach (var col in
    MyDataTable.Columns
    .Cast<DataColumn>()
    .OrderBy(x => tableColumns.IndexOf(x.ColumnName))
    .ToList())
{
    col.SetOrdinal(index);
    index ++;
}

This selects the columns from the DataTable, and orders them by their corresponding index in the tableColumns list.

Then it invokes SetOrdinal on each one of them, incrementing the index each time.