Handling Null values |   | 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
|
