Have you ever been “greeted” with a message like this?
Server Error in ‘/’ Application.
Operator is not valid for type 'DBNull' and string “”
This is what happens when you work on an ASP.NET application and you try to do some string operation on a field that you expect it to be a string while in fact it is a DBNull. Since DBNull does not support the String methods in .NET the application crashes. I’ve had this sort of problem also when working with WinForms applications but I do not remember what were the error messages in that case. (I am pretty sure that there is a good reason for the distinction between DBNull and Nothing (null), but from my ‘too lazy too dig deeper into it’ perspective it seems like it is not necessary.)
The first thing one can do to prevent problems like this one is to write the queries that retrieve the data in such a way that they do not return null values. For example
SELECT ISNULL(MiddleName, '') as MiddleName
FROM Person
With this simplified query for each row where the MiddleName is null, the database will give us an empty string instead. Quite simple but unfortunately sometimes we do not have access to the query and we are stuck with a result set that contains null values. In that case one way to deal with it is to always check for a DBNull (for the fields that you know that can contain null values) before you want to perform some operation with the database value. For example More...