Remove empty columns from DataTable/DataView

Question

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.

Do you want to remove these columns from the table/view or do you want to remove rows that contain no data? Can you show a little sample? — Tim Schmelter
– Tim Schmelter, Commented Jun 26, 2013 at 12:55
Are all columns of type string? What is the valu that should be treated as "no data", an empty string, null or the default value for that type? — Tim Schmelter
– Tim Schmelter, Commented Oct 7, 2014 at 6:15

Tim Schmelter · Accepted Answer · 2014-10-07 06:55:25Z

3

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;
}

edited Oct 7, 2014 at 6:55

answered Oct 7, 2014 at 6:48

Tim Schmelter

462k79 gold badges720 silver badges981 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user20416 Over a year ago

Does the first method need the datatable passed by ref?

Tim Schmelter Over a year ago

@ZevSpitz: yes, in C# you have to specifiy ref if you use a method that expects a ref parameter. It's just an indication that this method will modify your table.

user20416 Over a year ago

By that reasoning, the static Array.Sort method should also take the first parameter by reference. Isn't it understood that when an object is passed into a method the method might modify properties of the object, even if the method cannot replace the object with another object of the same type?

Tim Schmelter Over a year ago

@ZevSpitz: you're right that my understanding of ref is not set in stone. You could also remove the ref. In this case i've used it for two reasons: 1) it makes it pretty clear that the method removes columns(instead of only sorting like Array.Sort) and 2) it allows to overload both methods. Without ref the compiler wouldn't allow them because they have the same parameters. In general, you're right that it's neither necessary nor common practise to use ref on every method that modifies a passed object.

Collectives™ on Stack Overflow

Remove empty columns from DataTable/DataView

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related