tags:

views:

8084

answers:

6

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.

A: 

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!

Dillie-O
+1  A: 

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.

John Smithers
+11  A: 

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.

Dan Herbert
+6  A: 

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. ;-)

jeremcc
I think there are still some uses for DBNull. At least with sqlite I can test if a value returned by ExecuteScalar() is explicitly stored in the database or not. Consider Table foo(a int, b int); with a single row (1, null). If we were to ExecuteScalar on a command "SELECT b FROM foo WHERE A = 1", it would return DBNull.Value (the row exists a null is stored). If I were to call ExecuteScalar on "SELECT b FROM foo WHERE A = 2" it would return null (no such row exists). Presumably you could achieve the same result with a DataReader but I do appreciate the convenience.
fostandy
Interesting. While using an ORM for DB access, I don't often explicitly use ExecuteScalar in my code. I usually end up retrieving full entity objects and looking at their values that way. So if the entity itself is null that's one thing, and if the value is null, that's something else. Just different ways of doing it. I admit that ExecuteScalar is more efficient if you just need one single value.
jeremcc
A: 

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

Ch00k
A: 

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

John Smithers