Handling Null values

by Les Smith

How is an easy way to guarantee the Nulls do not cause my program to raise an exception?  Nulls can be the bane of the database programmer who does not plan for the inevitable Null value in a data set.  

Obviously, the best way to ensure that unexpected Null values do not surprise you in a dataset is to impose validation rules in the database.  However, it has been my sad experience to have exceptions raised by Null values when I had been ensured that they could not happen.

The following example code shows how to call three functions that I have written to automatically return a valid value (default according to datatype), even if the value retrieved from the database is Null.  Assuming that DT is a DataTable with string, integer, and decimal values in it, the code shown below will not fail, even if Null values are encountered.

myString = MNS(dt.Rows(0).Item("string_field")       myInteger = MNI(dt.Rows(0).Item("integer_field")       myDecimal = MND(dt.Rows(0).Item("decimal_field")

The function calls will always return default, non Null values, even if the value returned in the datatset is Null.  This may or may not mean that the program is performing correctly, depending on the requirement for the presence of valid data in the database field.  However, it does guarantee that the program will not be caught off guard by the appearance of a Null value in the table.

A normal way to protect against this is shown below and is obviously much more cumbersome and mistake prone.  Two ways are demonstrated, neither of which is desirable from my point of view.  Obviously, they will run faster than the function calls shown above, but with the speed of today's computers, I maintain that conserving the time of the developer is much more important than worrying about a few machine cycles.

If dt.Rows(0).Item("string_field") Then          myString = String.Empty       Else          mystring = CType(dt.Rows(0).Item("string_field")       End If       myString = IIf(IsDBNull(dt.Rows(0).Item("string_field")), _          String.Empty, dt.Rows(0).Item("string_field"))

The code for the three functions is shown below in both VB and C#.  You can take the development of other functions further yourself.  A date function is needed, but the development of that function or functions will depend on what you want to return in the case of a Null date value.
VB.Net Code:

' Return 0 if object is null, else decimal value    Public Function MND(ByVal o As Object) As Decimal       If IsDBNull(o) Then          Return 0       Else          Return CType(o, Decimal)       End If    End Function       ' Return 0 if null, else integer value of object.    Public Function MNI(ByVal i As Object) As Integer       If IsDBNull(i) Then          Return 0       Else          Return CType(i, Integer)       End If    End Function    ''' Return String if object is not null, else return empty.string    Public Function MNS(ByVal s As Object) As String       If IsDBNull(s) Then          Return String.Empty       Else          Return CType(s, String)       End If    End Function

C# Code:

// return default value for integer if the object is null   public int MNI(object i)    {       if(i==null)              return 0;       else         return (int) i;    }    // return default value for decimal if the object is null   public decimal MND(object o)    {       if(o==null)          return 0;       else         return (decimal) o;    }    // return empty string if the object is null   public string MNS(object s)    {          if(s==null)          return string.Empty;       else         return (string) s;    }

Back to Top