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.
