C#/Oracle10g = null vs DBNull.Value vs String.Empty

Question

I'm making my first forays into Accessing Oracle from C#. I've discovered that that Oracle doesn't like VarChar parameters to have value of null (C#). I would have hoped that there would be some implicit conversion, but I appreciate the difference.

So I need to trap these null values and supply DBNull.Value instead, surely? The most obvious method was to use the coalesce operator ??:

param myParm = myObject.MyProperty ?? DBNull.Value;

Except it doesn't accept a value of System.DBNull... so I have to use:

param myParm = myObject.MyProperty ?? DBNull.Value.ToString();

...which is surely the same as:

param myParm = myObject.MyProperty ?? String.Empty;

..which also works.

But I always understood that according to ANSI SQL rules, an empty string ("") != a NULL value... yet it appears to be in Oracle.

Anyway, my question is, what is the best, practical or theoretical, way to handle the case of null string values? Is there a slick alternative that I haven't identified? Or is this just another idiosyncrasy of Oracle that we just accept?

Solution 1

An empty string ("") does, if fact NOT equal a NULL value, but that is because NULL is not equal to anything (not even another NULL) (which is why you say IS NULL in an SQL statement instead of = NULL.

NULL means "No Value", and an string that is empty has no value, so Oracles designers decided that there is no difference between an empty string and NULL.

param myParm = myObject.MyProperty ?? DBNull.Value; fails because both sides of the ?? must be the same type. MyProperty I'll assume is a string, while DBNull.Value is aDBNull object.

Solution 2

There is a much simpler solution here since the ?? will not work and String.Empty will not work for your null value.

param myParm;

if (myObject.MyProperty == null)
{
   myParm  = DBNull.Value;
}
else 
{
   myParm = myObject.MyProperty;
}

It might not be as 'slick' as the null-coalescer but it should work.

Solution 3

If you set the value to null, .NET won't specify the parameter at all in the generated SQL. Therefore, one option is to specify a default value of NULL for the parameter in Oracle. That means that setting the value to null or DBNull.Value have the same effect. One passes no value and NULL will be assumed, while the other explicitly passes a value of NULL.

Obviously that assumes that you can modify the database, and that you don't already need a different default value for that parameter.