Search code examples
.netdatatabledataview

Remove empty columns from DataTable/DataView


I have a DataTable that returns data from a stored-procedure (it's generated by a dynamic pivot statement, but I don't think that is relevant). The returned data may have columns without data. How can I generate a DataView at runtime that excludes those columns that don't return data?

Edit - sample data

from:

ID  A  B  C
------------
1   1  2  
2   2  4

to:

ID  A  B
---------
1   1  2  
2   2  4

removing column C. If the data looks like this:

ID  A  B  C
------------
1   1     3
2   2     6

then column B should be removed.


Solution

  • You could use this method:

    public static void RemoveNullColumns(ref DataTable tbl, params string[] ignoreCols)
    {
        var columns =  tbl.Columns.Cast<DataColumn>()
            .Where(c => !ignoreCols.Contains(c.ColumnName, StringComparer.OrdinalIgnoreCase));
        var rows = tbl.AsEnumerable();
        var nullColumns = columns.Where(col => rows.All(r => r.IsNull(col))).ToList();
        foreach (DataColumn colToRemove in nullColumns)
            tbl.Columns.Remove(colToRemove);
    }
    

    Your sample:

    DataTable table = new DataTable();
    table.Columns.Add("ID", typeof(int));
    table.Columns.Add("A", typeof(int));
    table.Columns.Add("B", typeof(int));
    table.Columns.Add("C", typeof(int));
    table.Rows.Add(1, 1, 2, null);
    table.Rows.Add(2, 2, 4, null);
    
    RemoveNullColumns(ref table, "ID");
    DataView result = table.DefaultView;
    

    Result (column "C" removed):

    ID  A   B
    1   1   2
    2   2   4
    

    Here is an overload that does not modify the original table but creates a copy:

    public static DataTable RemoveNullColumns(DataTable tbl, params string[] ignoreCols)
    {
        DataTable copy = tbl.Copy();
        var columns = copy.Columns.Cast<DataColumn>()
            .Where(c => !ignoreCols.Contains(c.ColumnName, StringComparer.OrdinalIgnoreCase));
        var rows = copy.AsEnumerable();
        var nullColumns = columns.Where(col => rows.All(r => r.IsNull(col))).ToList();
        foreach (DataColumn colToRemove in nullColumns)
            copy.Columns.Remove(colToRemove);
        return copy;
    }