Concise usage of DBNull? (Ternary?)

Question

It seems that there's some type confusion in the ternary operator. I know that this has been addressed in other SO threads, but it's always been with nullables. Also, for my case I'm really just looking for a better way.

I'd like to be able to use

proc.Parameters[PARAM_ID].Value = 
    string.IsNullOrEmpty(dest.Id) ? DBNull.Value : dest.Id;

but instead I'm stuck with this:

if (string.IsNullOrEmpty(dest.Id))
{
    proc.Parameters[PARAM_ID].Value = DBNull.Value;
}
else
{
    proc.Parameters[PARAM_ID].Value = dest.Id;
}

The ternary operator fails because there's no conversion possible between DBNull and string, and as silly as that seems considering Value is object, the compiler kicks it back to me and I'm forced to care. The answer to the nullable version of this question is to just cast null to string and be done with it; DBNull can't be cast to string, though, so no luck there.

Is there a more concise way to do this (without using nullables, by the way?)

Thanks!

score 23 · Accepted Answer · answered Aug 09 '10 at 22:59

23

You could change your first statement to:

proc.Parameters[PARAM_ID].Value = 
    string.IsNullOrEmpty(dest.Id) ? (object)DBNull.Value : dest.Id;

answered Aug 09 '10 at 22:59

Jacob

77,566
24
149
228

4

Oh. Duh. Aaand that's what stackoverflow is for. Thanks! – bwerks Aug 09 '10 at 23:01

score 6 · Answer 2 · answered Aug 09 '10 at 22:59

6

The Value property is of type object, so you should cast to object, not string:

proc.Parameters[PARAM_ID].Value = string.IsNullOrEmpty(dest.Id)
    ? (object)DBNull.Value
    : (object)dest.Id;

answered Aug 09 '10 at 22:59

Mark Byers

811,555
193
1,581
1,452

I'm going to give the answer to Jacob since he apparently answered first by about 20 seconds. Thanks to you too mark! – bwerks Aug 09 '10 at 23:02

score 6 · Answer 3 · answered Aug 09 '10 at 23:15

6

Or you could add an extension method such as:

public static class DBNullExtensions
{
    public static object AsDBNullIfEmpty(this string value)
    {
        if (String.IsNullOrEmpty(value))
        {
            return DBNull.Value;
        }
        return value;
    }
}

And then you could just say

proc.Parameters[PARAM_ID].Value = dest.Id.AsDBNullIfEmpty();

(Adapted from Phil Haack)

Readable and concise, no?

answered Aug 09 '10 at 23:15

Kelly Adams

750
4
15

I dig the use of extension methods; sadly this project is on C#2.0 though. Boo hiss. – bwerks Aug 10 '10 at 23:52
3

Bummer! Maybe you can chisel something out of stone obelisks ;) – Kelly Adams Aug 12 '10 at 21:15

score 3 · Answer 4 · answered Oct 23 '14 at 17:13

3

What about using the ?? null-coalescing operator More details about ?? operator

proc.Parameters[PARAM_ID].Value = dest.Id ?? (object)DBNull.Value;

answered Oct 23 '14 at 17:13

elsueco

61
3

Operator '??' cannot be applied to the type 'bool' and 'object'. If you remove '(object)', compiler will complain about the 'DBNull' type instead of 'object'. – ivorykoder Jul 13 '16 at 07:29
@ivorykoder the ?? operator can only be used on nullable types, as `lhs ?? rhs` is essentially equivalent to `lhs != null ? lhs : rhs`. That is why you are getting "cannot be applied to the type 'bool'" - `bool` is not nullable! – FireSBurnsmuP Aug 10 '16 at 16:27
@ivorykoder if you want to pass null to the DB its supposed to be a nullable value. the ?? operator con only be used with nullable types. Makes sense, right? – elsueco Nov 17 '18 at 03:01

Concise usage of DBNull? (Ternary?)

4 Answers4