Datatable Compute Function for multiple colmuns

Question

I want to count the number of non-null values per column in a Datatable. I could loop through the columns and use the compute function on each column, but I was wondering if there is a more efficient way to do this.

Answer 1

You could add a column with an expression that checks whether the rests of the columns are null, see http://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression(VS.80).aspx Then you can Compute on that column.

Answer 2

I think that the Compute function is quite appropriate in this context. You could use code similar to the following:

For Each col as DataColumn in myTable
  Dim aggExpr as string = string.format("Count{0}", col.ColumnName)
  Dim filterExpr as string = string.format("{0} IS NULL", col.ColumnName)
  Dim myCount as integer = CInt(myTable.Compute(aggExpr, filterExpr))
  Console.WriteLine(myCount)
Next

(Typed in here, watch for syntax)

Note that I say "similar to the following". Please add appropriate error/null value checks.