Confused about null strings and DBNull

Question

I'm looking at some code for a .net program I'm working on. The previous author of the program gave the code to me over a year ago. Suddenly, I am seeing an exception thrown from code that I haven't touched:

if ((string)row["LevelName"] != "ABC")

The exception is "Unable to cast object of type 'System.DBNull' to type 'System.String'.

I thought that string was a nullable data type, so how can I possibly be getting this exception?

This code has been working fine for over a year; I feel like I must have messed something up somewhere — Vivian River, Nov 07 '11 at 23:07
Yup, as a matter of fact, I did. The code in question wasn't prepared for a DBNull value because it should never come up for the particular query I was running. I made a mistake in modifying the query, and thus, the error came up. — Vivian River, Nov 08 '11 at 14:51
Thanks for the informative answers, everyone. I up-voted them all. No wonder the SQL book I read calls NULL "that four letter word". — Vivian River, Nov 08 '11 at 14:55

Mike Christensen · Answer 1 · 2011-11-07T23:16:45.147

9

I believe what you're looking for is:

if ((row["LevelName"] as String) != "ABC")

There is no implicit cast between DBNull and String.

It may have worked before because there just happened to be no NULLs in that column in your database. Maybe some data got corrupt, or someone changed a NOT NULL constraint on the table.

Basically if you explicitly cast something, you better make sure they have compatible dynamic types otherwise an exception is thrown. The as operator basically says "cast it to this if possible, otherwise the logical value is null." Obviously, the as operator only works on reference types since structs cannot be null.

edited Nov 07 '11 at 23:16

answered Nov 07 '11 at 23:05

Mike Christensen

88,082
50
208
326

2

Your answer implies this, but it's worth stating: using `variable as Type` will yield `null` in cases where using the explicit cast `(Type) variable` would cause an `InvalidCastException`. – Rob Nov 07 '11 at 23:11

Connell · Accepted Answer · 2012-05-11T17:05:12.573

DBNull is it's own class, which contains a singleton instance of itsself in a property called Value. DBNull.Value is not equal to null, as it is a reference to an instance of this class.

Most, if not all database wrappers will return DBNull.Value instead of null when there is no value. One of the reasons for this is because returning a real null could mean there is no row at all, not just a null value in the column (it depends on what objects you're using to get the values though).

Generally, the as operator is very usefull with DBNull and can be used with any nullable type (including string).

string str = reader["Name"] as string;
int? i = reader["Age"] as int?;

It might also be worth mentioning that the ?? operator is very usefull here too, when you need a non-nullable value type (although it doesn't look too pretty).

int i = reader["Age"] as int? ?? -1;

I find this very handy as a little just-in-case scenario in a LINQ select clause.

My fast fingers I'm afraid. Luckily I edited the post 2 mins before you left that comment so that it **did** have a correct example. Sorry about that. — Connell, Nov 07 '11 at 23:21

score 4 · Answer 3 · answered Nov 07 '11 at 23:11

DBNull means there is no data in the given database column. This can occur if a new record is created in the database but no value is assigned to the "LevelName" column.

DBNull is not the same as a null object reference or null string variable. DBNull means the column data has never been set. If you assign a null to the "LevelName" column, then the column has been initialized and it will return null (not DBNull).

score 3 · Answer 4 · answered Nov 07 '11 at 23:10

DBNull represents a null value in the database. This is not the same as a .NET null, which indicates an empty reference.

The code suddenly fails because someone changed or inserted a record that has nulls in it.

Yes, string is nullable, but you cast a dbnull to string. You must check for dbnull, and replace it with null if it's there.

score 2 · Answer 5 · answered Nov 07 '11 at 23:06

There is a difference between the .NET null and the DBNull.Value. Here you can see what the DBNull class is and the Value field of that class.

You should be able to do something like this:

if (row["LevelName"].Value == DBNull.Value)
    return false;
else if (row["LevelName"].ToString() != "ABC")
    // do something
    // ....

Confused about null strings and DBNull

5 Answers5