Introduction
If you are sick of constantly checking for DBNull.Value
in your database results, here are a series of helpers to alleviate all that effort, and make your code more readable.
Background
Simple - I got sick of writing code like...
object result = cmd.ExecuteScalar();
int n = (result is DBNull) ? 0 : (int)result;
...and when you're dealing with a dozen or more columns like this in a data row, not only does it add up to lots of mundane typing, but the resulting code tends to look messy.
Odds are that this code will not handle all the DBNull
s you have to deal with (e.g. I don't have anything here for BLOB data), but it has certainly made my coding and maintenance that much simpler.
Using the Code
The attached source is just a static DbConvert
class with a set of data type conversions based on the System.Convert.ChangeType
function, that checks for DBNull
before attempting the conversion. They will still throw the normal InvalidCastException
if you try to do something silly like converting "ABC
" to an integer, but will quietly return a default value if they encounter either DBNull
or null
.
The method that does all the work is T DBConvert.GetValue<T>(object dbResult, [T defaultValue = Default(T)])
. There are a pile of helpers that call this method to convert the standard .NET types. The bottom line is that the above two lines of code are now written simply as:
int n = DbConvert.GetInt(cmd.ExecuteScalar());
There is also an embedded Nullable
class that has helper for the nullable data types, for those times when null
is a valid value.
double? value = DbConvert.Nullable.GetDouble(columnValue[2]);
if (value == null) {
}
There is also one other simple function, DBConvert.GetObject(object dbResult, [object defaultValue = null])
, that does not perform any type conversions apart from changing DBNull
to defaultValue
.
Been programming for 40 years now, starting when I was 13 on DEC PDP 11 (back in the day of paper tape storage, and hex switch boot procedures). Got right into micro-computers from an early age, with machines like the Dick Smith Sorcerer and the CompuColor II. Started CP/M and MS-DOS programming in the mid 1980's. By the end of the '80's, I was just starting to get a good grip on OOP (Had Zortech C++ V1.0).
Got into ATL and COM programming early 2002. As a result, my gutter vocabulary has expanded, but it certainly keeps me off the streets.
Recently, I have had to stop working full time as a programmer due to permanent brain damage as a result of a tumour (I just can't keep up the pace required to meet KPI's). I still like to keep my hand in it, though, and will probably post more articles here as I discover various tricky things.