Remove nulls from a DataTable

The individual cells in a DataTable can have a null value in the form of System.DbNull.Value. If the DataTable is created by querying a database through ADO.NET, you can write the SQL statement in a way eliminates nulls. It could look like this “SELECT isnull(name, 'n/a') AS name FROM products”.

However, there can be scenarios where you don’t have the chance to manipulate the DataTable before you use it. Such a scenario have I recently been involved in and the problem was that the data retrieved from a database could contain nulls in any of the integer type columns. If I then bind the DataTable to a GridView in ASP.NET, I had to do a lot of workarounds to calculate footers and other values based on those columns.

Instead of doing the workarounds in a lot of different places in the code, I decided it was a better idea to clean the DataTable for nulls before it is used. That led to the CleanDataTable method below, that replaces null values with zeros for a few integer type columns.

/// <summary>

/// In the case of null values in a data table, this method

/// will turn all nulls into zeros instead.

/// </summary>

public static DataTable CleanDataTable(DataTable dt)

{

  for (int a = 0; a < dt.Rows.Count; a++)

  {

    for (int i = 0; i < dt.Columns.Count; i++)

    {

      if (dt.Rows[a][i] == DBNull.Value)

      {

        Type type = dt.Columns[i].DataType;

        if (type == typeof(int) || type == typeof(float) || type == typeof(double))

        {

          dt.Columns[i].ReadOnly = false;

          dt.Rows[a][i] = 0.0F;

        }

      }

    }

  }

 

  return dt;

}

The point is that you only have to clean it once to avoid any workaround for handling null values.