Converting System.DBNull to something else?

  • Thread starter Thread starter Jim Bancroft
  • Start date Start date
J

Jim Bancroft

Hi everyone,

My code queries a SQL Server database and retrives a single DataTable.
From that table I loop through the DataRows, reading and making use of its
columns. I convert the values to System datatypes in order to use them, but
that's not a problem.....unless one of the columns contains a null value.

The general sequence is this: 'myBoolean =
Convert.ToBoolean(DR["myColumn"])'. Substitute "ToBoolean" with other
appropriate conversions (depending on the column type) and you've got the
gist of what I'm doing. Again, these conversions work fine unless a DataRow
contains a null column value, in which case I receive an invalid cast error.

Is there any way to force System.DBNull to convert itself to a Boolean
or other type? I'm looking to avoid littering my code with "if" statements
checking for DBnull values, if possible.....thanks.
 
Jim Bancroft said:
Hi everyone,

My code queries a SQL Server database and retrives a single DataTable.
From that table I loop through the DataRows, reading and making use of its
columns. I convert the values to System datatypes in order to use them,
but that's not a problem.....unless one of the columns contains a null
value.

The general sequence is this: 'myBoolean =
Convert.ToBoolean(DR["myColumn"])'. Substitute "ToBoolean" with other
appropriate conversions (depending on the column type) and you've got the
gist of what I'm doing. Again, these conversions work fine unless a
DataRow contains a null column value, in which case I receive an invalid
cast error.

Is there any way to force System.DBNull to convert itself to a Boolean
or other type? I'm looking to avoid littering my code with "if"
statements checking for DBnull values, if possible.....thanks.
Click to expand...

Create a routine in some common class (in this case, Common is the name of
the class). Inside this class, place something like the following public
method:

public static bool DbNullToBoolean(DataRow Row, string ColumnName, bool
DefaultValue)
{
if (Row.IsNull(ColumnName)) {
return DefaultValue;
}

return Convert.ToBoolean(Row[ColumnName]);
}

Once you have one of these for each datatype you wish to have, you can call
this in your code in place of your Convert.ToBoolean statements as follows:

Change 'myBoolean = Convert.ToBoolean(DR["myColumn"]);' to 'myBoolean =
Common.DbNullToBoolean(DR, "myColumn", false);'

Example:

internal class Common
{
private Common() { }

public static bool DbNullToBoolean(DataRow Row, string ColumnName, bool
DefaultValue)
{
if (Row.IsNull(ColumnName)) {
return DefaultValue;
}

return Convert.ToBoolean(Row[ColumnName]);
}

public static int DbNullToInteger(DataRow Row, string ColumnName, int
DefaultValue)
{
if (Row.IsNull(ColumnName)) {
return DefaultValue;
}

return Convert.ToInteger(Row[ColumnName]);
}

public static string DbNullToString(DataRow Row, string ColumnName,
string DefaultValue)
{
if (Row.IsNull(ColumnName)) {
return DefaultValue;
}

return Convert.ToString(Row[ColumnName]);
}

public static object DbNullToObject(DataRow Row, string ColumnName,
object DefaultValue)
{
if (Row.IsNull(ColumnName)) {
return DefaultValue;
}

return Row[ColumnName];
}
}


Hope this helps! :)

Mythran
 
Yeah, you'll most likely want to create a function in a common class to do
the conversion for you. Here is one I use. (I have it on the base class for
our windows forms).

Public Shared Function NVL(ByVal Value As Object, ByVal ValueIfNull As
Object, Optional ByVal TreatEmptyStringAsNull As Boolean = False) As Object
If Value Is Nothing Then Return Nothing
If IsDBNull(Value) Then Return ValueIfNull
If TreatEmptyStringAsNull Then
If TypeOf Value Is String Then
If CStr(Value) = String.Empty Then Return ValueIfNull
End If
End If
Return Value
End Function


This function works the similar to Oracle's NVL SQL function. It takes in an
object (Value) to be tested for DBNull. If the object happens to be Nothing,
then the function returns Nothing. If the object is DBNull, then the
ValueIfNull object passed in is returned. This function also has an optional
parameter to specify whether empty strings should be treated as DBNull.
Finally, if the object "Value" passed in is not DBNull, it is simply returned.

Since this function used objects, it can be used with just about any type.
Most likely you already know what data type you expect back, so you can
simply convert the object to that type.

Here is an example of the function's use:

Private Sub TestNVL()
Dim NullItem As Object = DBNull.Value
Dim NonNullItem As Integer = 80012
'Convert to Integer
Debug.WriteLine(CInt(NVL(NonNullItem, 0)).ToString) 'Returns 80012
Debug.WriteLine(CInt(NVL(NullItem, 0)).ToString) 'Returns 0
'Convert to String
Debug.WriteLine(CStr(NVL(NonNullItem, "Item is DBNull"))) 'Returns
"80012"
Debug.WriteLine(CStr(NVL(NullItem, "DBNull"))) 'Returns "DBNull"
End Sub
 
Another possible approach to the problem might be to ensure
that the backend does not return NULLs in the first place.
Since you are using SQL server, you might be
able to change your queries to convert the NULLs to
something else.

eg.

select cast(coalesce(myColumn, 0) as bit) as myColumn from myTable

Stephen.
 
Back
Top