Reading and writing values to the DB has always been a bit cumbersome when you had to take care of nullable types and DBNull values. Here’s a way to make it easy. Based on this post by Peter Johnson and this post by Adam Anderson I gathered a couple of ideas and combined them to make a completely generic class that will handle DBNulls for both reads and writes, as well as handling nullable types. Let me present the code, I’ll go over it afterwards:
The first To method significantly simplifies the process of setting database values when using SqlParameters (we all do, right?).
This is how I used to handle possible DBNulls when reading into a nullable integer:
And this is how it’s done using my DBConvert class:
Notice how it works for both nullable ints, DateTimes and whatever other nullable types you wish. It also works for normal types like string, int and so forth. It’ll automatically typecast it into the type specified as a generic parameter. However, remember that the database value must match the value being converted to, you cannot use .To(“some string value”), it will fail.
The private changeType() method is a wrapper for the ChangeType() method that takes care of nullable types since the builtin Convert.ChangeType() method does not support casting into nullable types.
The second To simplifies databinding values in the frontend ASPX files. This is how I used to print a DateTime column in a ShortDateString format:
And this is how it can be done using the DBConvert class, generically:
Nullable types, as well as null strings also have to be handled when assigning SqlParameter values. The usual way for both nullable types as well as strings might look like this:
Using the DBConvert class this can be done a bit simpler:
This will automatically convert null strings as well as nulled nullable types to DBNull.
Mark S. Rasmussen
I'm the CTO at iPaper where I cuddle with databases, mold code and maintain the overall technical & team responsibility. I'm an avid speaker at user groups & conferences. I love life, motorcycles, photography and all things technical. Say hi on Twitter, write me an email or look me up on LinkedIn.