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?

Answer 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.