21

I have the following bit of code to set a parameter that will be used in an INSERT statement to set a VARCHAR column in a SQL Server database. My value object (named ilo) has a property called Description that gets initialized to String.Empty, and then either gets set to some value read from XML, or if that XML element is empty it just stays as String.Empty.

So when inserting into the database, if the property is still set to String.Empty, I'd like to have it insert a null value.

database.AddInParameter(cmd, "@description", DbType.String, 
                           (ilo.Description.Equals(string.Empty)) ?
                            DBNull.Value :
                            ilo.Description);

So basically I'm saying, if ilo.Description equals string.empty, set the parameter to DBNull.Value, otherwise set it to ilo.Description.

This gives the following error in Visual Studio...

Error 141 Type of conditional expression cannot be determined because there is no implicit conversion between 'System.DBNull' and 'string'

Why?

The curious part is that I can do the following with no error, which should be exactly the same thing as using inline conditional syntax like above!?!

if(ilo.Description.Equals(string.Empty))
{
    database.AddInParameter(cmd, "@description", DbType.String, DBNull.Value);
}
else
{
    database.AddInParameter(cmd, "@description", DbType.String, ilo.Description);
}

I searched other posts, and found the one below, but it doesn't really answer my question.

EntLib Way to Bind "Null" Value to Parameter

I'm more interested in WHY, because the obvious workaround is to just use an if/else statement instead of the inline (ternary) syntax?

There's sort of an answer at this link, but I'd like a better explanation because it seems to be BS to me that this doesn't work; I'd call it a bug!

http://msdn.microsoft.com/en-us/library/ty67wk28.aspx

EDIT: 3/30/2021, it's been 9 years since I wrote this question. Finally as of C# 9, this issue is fixed. Ternary expressions now support mixed types so long as they can be implicitly converted.

Jim
  • 6,753
  • 12
  • 44
  • 72
  • 1
    You could get details on ternary operator and this kind of problem [in this answer](http://stackoverflow.com/questions/4290203/simple-c-why-assigning-null-in-ternary-operator-fails-no-implicit-conversion) – Steve Jun 01 '12 at 15:44
  • Thanks Steve. Your link was what I was looking for really. I think this is a stupid aspect of .NET though; why don't they just evaluate whether the types of each possible result fit the statement. i.e. if you say Object o = (someBool) ? someInt32 : someString; you get an error, but how easy would it be to evaluate that both results can be implicitly cast to an Object, instead of evaluating whether someString can be cast to someInt32? It seems stupid to me, but I guess that's how it is. Thanks! – Jim Jun 01 '12 at 16:17
  • I'd also like to comment that I'm very impressed with the speed and accuracy of replies to this thread. This community is awesome! Thanks everyone! – Jim Jun 01 '12 at 16:24

4 Answers4

56

This is a common error people receive when using the conditional operator. To fix it, simply cast one or both of the results to a common base type. 

ilo.Description.Equals(string.Empty) 
     ? (object)DBNull.Value 
     : ilo.Description

The issue is revealed in the error message you saw.

Type of conditional expression cannot be determined because there is no implicit conversion between 'System.DBNull' and 'string'

A string is not a DBNull, and a DBNull is not a string. Therefore, the compiler cannot determine the type of the expression. By using a cast to a common base type (in this case, object), you create a scenario where the compiler can then determine that string is also convertible to object, so the type of the expression can be determined as object, which also nicely fits with what your line of code also expects as the DbParameter argument.

Anthony Pegram
  • 123,721
  • 27
  • 225
  • 246
  • Steve's comment gives a link with more details, but your answer is also very correct. Just seems dumb to me to have to do that. I believe in Java this doesn't happen. – Jim Jun 01 '12 at 16:19
6

This is what I found in another thread and it worked great for me:

yourVariable ?? (object)DBNull.Value

I hope it helps.

Sagar
  • 579
  • 5
  • 7
  • This is wrong - ?? tests if `yourVariable` is **null**, not if a string is empty, as the OP asked. – Conrad Feb 03 '20 at 23:23
3

You get the compilation error because both parts of the ternary expression must be of the same type. The easiest workaround for this particular situation is to cast both to an object:

database.AddInParameter(cmd, "@description", DbType.String, 
                           (ilo.Description.Equals(string.Empty)) ?
                            (object) DBNull.Value :
                            (object) ilo.Description);
Tuan
  • 5,382
  • 1
  • 22
  • 17
  • As lazyberezovsky suggested, it's also better to use null rather than DBNull.Value here. I think you only need to use DBNull.Value when reading from a a datareader or dataset. – Tuan Jun 01 '12 at 15:42
3

Anthony is indeed correct so he should get the correct answer, however here's an extension method because I'm bored...

    public static object NullCheck(this string self)
    {
        return (string.IsNullOrEmpty(self)) ? (object)DBNull.Value : self;
    }

usage in your scenario:

database.AddInParameter(cmd, "@description", DbType.String, 
                           ilo.Description.NullCheck());
Marlon
  • 2,129
  • 3
  • 21
  • 40
  • Good stuff. Even though I probably won't use this method, it just taught me a few things about C# that I didn't know before; like I didn't even notice the IsNullOrEmpty method on String before. I'm just very used to Java, and am glad to learn more C#. Thanks! – Jim Jun 01 '12 at 16:29
  • No worries, after thinking about it, might be better to put the extension method on the ParameterCollection / database object itself to provide a wrapper which handles nulls in the way you want. – Marlon Jun 06 '12 at 14:04