C# Database Access: DBNull vs null

Question

We have our own ORM we use here, and provide strongly typed wrappers for all of our db tables. We also allow weakly typed ad-hoc SQL to be executed, but these queries still go through the same class for getting values out of a data reader.

In tweaking that class to work with Oracle, we've come across an interesting question. Is it better to use DBNull.Value, or null? Are there any benefits to using DBNull.Value? It seems more "correct" to use null, since we've separated ourselves from the DB world, but there are implications (you can't just blindly ToString() when a value is null for example) so its definitely something we need to make a conscious decision about.

Answer 1

From the experience I've had, the .NET DataTables and TableAdapters work better with DBNull. It also opens up a few special methods when strongly typed, such as DataRow.IsFirstNameNull when in place.

I wish I could give you a better technical answer than that, but for me the bottom line is use DBNull when working with the database related objects and then use a "standard" null when I'm dealing with objects and .NET related code.

Hope this helps!

Answer 2

Use DBNull.
We encouintered some sort of problems when using null.
If I recall correctly you cannot INSERT a null value to a field, only DBNull.
Could be Oracle related only, sorry, I do not know the details anymore.

Answer 3

I find it better to use null, instead of DB null.

The reason is because, as you said, you're separating yourself from the DB world.

It is generally good practice to check reference types to ensure they aren't null anyway. You're going to be checking for null for things other than DB data, and I find it is best to keep consistency across the system, and use null, not DBNull.

In the long run, architecturally I find it to be the better solution.

Answer 4

If you've written your own ORM, then I would say just use null, since you can use it however you want. I believe DBNull was originally used only to get around the fact that value types (int, DateTime, etc.) could not be null, so rather than return some value like zero or DateTime.Min, which would imply a null (bad, bad), they created DBNull to indicate this. Maybe there was more to it, but I always assumed that was the reason. However, now that we have nullable types in C# 3.0, DBNull is no longer necessary. In fact, LINQ to SQL just uses null all over the place. No problem at all. Embrace the future... use null. ;-)

Answer 5

Thank you all. I've accepted Dan's answer, as its a nice explanation. Jeremy, thank you for yours too, if its good enough for Linq to Sql, I guess its good enough for our home grown Linq implementation (which by the way, is really fun to write!). I'd accept your answer too if I could, but I can't, and besides, Dan has less reputation... just

Answer 6

@Ch00k:
If you like Jeremy`s answer, upvote him.