Remove all columns with no data from DataTable

You can use the Compute method, like this:

if (table.Compute("COUNT(ColumnName)", "ColumnName <> NULL") == 0)
    table.Columns.Remove("ColumnName");

Alternatively, you can use LINQ:

if (table.AsEnumerable().All(dr => dr.IsNull("ColumnName")))
    table.Columns.Remove("ColumnName");

EDIT: To completely answer the question:

foreach(var column in table.Columns.Cast<DataColumn>().ToArray()) {
    if (table.AsEnumerable().All(dr => dr.IsNull(column)))
        table.Columns.Remove(column);
}

You need to call ToArray because the loop will modify the collection.


private static void RemoveUnusedColumnsAndRows(DataTable table)
    {            
        for (int h = 0; h < table.Rows.Count; h++)
        {
            if (table.Rows[h].IsNull(0) == true)
            {
                table.Rows[h].Delete();
            }
            enter code here
        }
        table.AcceptChanges();
        foreach (var column in table.Columns.Cast<DataColumn>().ToArray())
        {
            if (table.AsEnumerable().All(dr => dr.IsNull(column)))
                table.Columns.Remove(column);
        }
        table.AcceptChanges();           
    }

Tags:

C#

Datatable