Quantcast

Removing Nulls from a DataTable

Get the WebProNews Newsletter:
[ Business]

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 ones and avoid any workaround for handling null values.

Comments

Add to Del.icio.us | Digg | Reddit | Furl

Bookmark WebProNews:

Mads Kristensen currently works as a Senior Developer at Traceworks located
in Copenhagen, Denmark. Mads graduated from Copenhagen Technical Academy with a multimedia degree in
2003, but has been a professional developer since 2000. His main focus is on ASP.NET but is responsible for Winforms, Windows- and
web services in his daily work as well. A true .NET developer with great passion for the simple solution.

http://www.madskristensen.dk/

Removing Nulls from a DataTable
About Mads Kristensen
Mads Kristensen currently works as a Senior Developer at Traceworks located in Copenhagen, Denmark. Mads graduated from Copenhagen Technical Academy with a multimedia degree in 2003, but has been a professional developer since 2000. His main focus is on ASP.NET but is responsible for Winforms, Windows- and web services in his daily work as well. A true .NET developer with great passion for the simple solution.

http://www.madskristensen.dk/ WebProNews Writer
Top Rated White Papers and Resources
  • http://thewizardsguild.com Ralph Wilson

    My question would be, “If the original table design allows NULLS in those columns, haven’t you now altered the data to NOT conform to the original table design?”

    I understand that a NULL in an integer field can be a pain but it also has an intrensic meaning in a database. Essentially, when a column is NULL, there has been no data entered in that column and that can be significantly different from having a zero in that column.

    If the database table’s design allows a columkn to be NULL because, for instance, it may not be provided during a data load, then you will have to keep running this “Clean Up” process. If, on the other hand, the table should NOT allow NULLs in those columns then the table’s design should be modified right after the clean up . . . but that may cause you to learn all of the places where data is inserted into that table with NULL for those columns.

    By the way, how dumb is it to have to have http:// in the URL for my home page when that is, shall we say, fairly constant in the internet realm?