Remove all columns with no data from DataTable

If all the items for a particular column are empty, I want to remove that column from the DataTable. What's the most elegant way to do this operation on all columns in t开发者_运维百科he 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();           
    }

public static void RemoveNullColumnFromDataTable(DataTable dt)
{
    for (int i = dt.Rows.Count - 1; i >= 0; i--)
    {
        if (dt.Rows[i][1] == DBNull.Value)
            dt.Rows[i].Delete();
    }
    dt.AcceptChanges();
}

Function RemoveEmptyColumns(Datatable As DataTable) As Boolean
    Dim mynetable As DataTable = Datatable.Copy
    Dim counter As Integer = mynetable.Rows.Count
    Dim col As DataColumn
    For Each col In mynetable.Columns
        Dim dr() As DataRow = mynetable.Select(col.ColumnName + " is   Null ")
        If dr.Length = counter Then
            Datatable.Columns.Remove(col.ColumnName)
            Datatable.AcceptChanges()
        End If
    return true
end function

You clear all column in datatable.you use like that.

datatable.Columns.Clear();