views:

215

answers:

3

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?

+1  A: 

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.

James Curran
I appreciate your answer James, but you are just repeating my learning journey... But IMHO It makes more sense to infer a DB NULL from a C# null, than it does to infer a DB NULL from an empty string. Nevertheless, I have two alternative solutions; the question is which is better (does it matter?) and are there alternatives.
CJM
Hmm... that sounded a little too critical, but it wasn't meant to be. I simply meant that I was describing my train of thought at the time, and I realised that String != System.DBNull.
CJM
+1  A: 

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.

msarchet
I can't say I've exhaustively tested it (though I was exhausted when I tested it!), but String.Empty does appear to work. I'm not sure it should, mind you....
CJM
@CJM ahh interesting...
msarchet
String.Empty will work because .NET will pass an empty string as the value, and Oracle considers an empty string to be NULL. That is, `'' IS NULL` is false in SQL Server but true in Oracle.
stevemegson
@stevemegson and @CJM I missed the line in your question where your said that String.Empty also worked, which threw the rest of my understanding of your question.
msarchet
+1  A: 

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.

stevemegson
My colleague controls the DB so this would be equally possible, thanks.
CJM