Remove columns from datatable

Question

I have a datatable with 20 columns. But i don't need all the columns for the current processing except 5. So i did the below to remove the columns

List<string> clmnames = new List<string>() { "clm6","clm7"..."clm20" };
foreach (string dcName in clmnames)
{
  TestAndRemoveColumn(dcName, ds.Tables["TestTable"]);
}


 private void TestAndRemoveColumn(string dcName,DataTable datatable)
 {
       DataColumnCollection dcCollection = datatable.Columns;
       if (dcCollection.Contains(dcName))
       {
           dcCollection.Remove(dcName);
       }
 }

Instead of looping through the 15 times is there any other way to achieve using easily ?

Answer 1

Problem seems to be in your code, you get all the comlumns from the datatable then remove the columns but you have not again assign the columns to that datatable first you get columns

   DataColumnCollection dcCollection = datatable.Columns; // get cols
   if (dcCollection.Contains(dcName))
   {
       dcCollection.Remove(dcName); /// remove columns
     // but you have not updated you datatable columns.
        here should be something like this
       datatable.Columns = dcCollection; /// i don't know this will work or not check it
   }

Try this

 DataTable dt;
 dt.Columns.Remove("columnName");
 dt.Columns.RemoveAt(columnIndex);

you can use them as

private void TestAndRemoveColumn(string dcName,DataTable datatable)
{
    DataTable dt = datatable; 
    dt.Columns.Remove("dcName");      
}

Answer 2

You could join the columns you want remove with the available columns:

var keepColNames = new List<String>(){ "clm5" };
var allColumns   = tbl.Columns.Cast<DataColumn>();
var allColNames  = allColumns.Select(c => c.ColumnName);
var removeColNames = allColNames.Except(keepColNames);
var colsToRemove = from r in removeColNames
                   join c in allColumns on r equals c.ColumnName
                   select c;
while (colsToRemove.Any())
    tbl.Columns.Remove(colsToRemove.First());

If you know that you only have few remaining columns, you could add the column(s):

var colsToAdd = (from keepCol in keepColNames
                 join col in tbl.Columns.Cast<DataColumn>()
                 on keepCol equals col.ColumnName
                 select col).ToList();
tbl.Columns.Clear();
foreach (var colToAdd in colsToAdd)
    tbl.Columns.Add(colToAdd);

Answer 3

In some scenarios may be preferable to clone DataTable and specify columns to copy.

DataView view = new DataView(table);
DataTable table2 = view.ToTable(false, "clm6", "clm7", ...);

Answer 4

Alternatively you can select only the required columns(Only 5 in your case) like this.

        DataTable dt = new DataTable();
        dt.Columns.Add("ID");
        dt.Columns.Add("Value");

        dt.Rows.Add("1", "One");
        dt.Rows.Add("2", "Two");

        string[] arr= new string[1];
        arr[0] = "Value";//add the required columns to the array

        //return only the required columns.
        DataTable dt2 = dt.DefaultView.ToTable(false, arr);

Hope this helps.

Answer 5

try this

List<string> listtoRemove = new List<string> { "CLM6", "CLM7", "CLM20" };
for (int i = dt.Columns.Count - 1; i >= 0; i--)
{
   DataColumn dc = dt.Columns[i];
   if (listtoRemove.Contains(dc.ColumnName.ToUpper()))
   {
       dt.Columns.Remove(dc);
   }
}