How to assign a value to sqlparameter NULL values without checking for null or 0?

Question

The following check is needed to check for null values. is there a way i can do it directly?

 if(node.ID==0)
 {
   cmd.Parameters["@ID"].Value = DBNull.Value;
 }
  else
  {
    cmd.Parameters["@ID"].Value = node.ID;
  }

is there way we can cicumvent this check and a design by which we can avoid this repetitive check for each value? (by the way its just a logic that 0 to be interpreted as NULL for my case but am asking the same about nullable objects like string too)

You could use the ternary operator (? :) to write it all in one lime, would that help? — Dirk, Feb 07 '14 at 08:29
Note that it would be a pretty different answer for nullable types (reference or nullable value types). Is there any reason you haven't made your `ID` property a `Nullable` or whatever? — Jon Skeet, Feb 07 '14 at 08:32
How if you accidentally write into the database what you don't really want? You may appreciate a prior check is required then. It's sometimes not a bad thing when an exception fires. — Johnny, Feb 07 '14 at 08:35
@JonSkeet I was not sure if Nullable is safe to use (not familiar with it) in case someone else uses it later, but I Will try it out. — Dexters, Feb 23 '14 at 03:26

ekad · Answer 1 · 2014-02-07T08:42:53.193

5

You can use ?: Operator

cmd.Parameters["@ID"].Value = node.ID == 0 ? (object)DBNull.Value : node.ID;

edited Feb 07 '14 at 08:42

answered Feb 07 '14 at 08:29

ekad

14,436
26
44
46

score 4 · Accepted Answer · answered Feb 07 '14 at 08:36

For nullable types, I'd just use an extension method:

public static object CoalesceNullToDBNull(this object input)
{
    return input == null ? DBNull.Value : input;
}

Then use it as:

cmd.Parameters["Foo"].Value = someExpression.CoalesceNullToDBNull();

(You could give it a shorter name, of course.)

That won't help for your case though, because you're trying to coalesce a non-null value to null. I would personally consider redesigning your model - use a nullable type for values which can be null, rather than using a magic number. However, you could still use a generic extension method:

public static object CoalesceDefaultToDBNull<T>(this T input)
{
    return EqualityComparer<T>.Default.Equals(input, default(T))
        ? DBNull.Value : (object) input;
}

That will convert 0, '\0', false, null etc to DBNull.Value. You'd have to use that with extreme care though, as I assume there are plenty of places where a 0 should really be a 0.

score 0 · Answer 3 · answered Feb 07 '14 at 08:35

If you are doing this regularly, try putting the check inside of a method.

For instance:

// ...
SetFormattedValue(ref cmd, node.ID);
// ...

private void SetFormattedValue(ref IDBCommand cmd, int id) // IDBCommand - or whatever your command type is
{
    if(node.ID==0)
    {
        cmd.Parameters["@ID"].Value = DBNull.Value;
    }
    else
    {
        cmd.Parameters["@ID"].Value = node.ID;
    }
}

score 0 · Answer 4 · edited Apr 04 '16 at 21:10

0

You can use the ?? operator:

cmd.Parameters["@ID"].Value = node.ID ?? (object)DBNull.Value

edited Apr 04 '16 at 21:10

David Medenjak

33,993
14
106
134

answered Apr 04 '16 at 18:40

Shved

1

How to assign a value to sqlparameter NULL values without checking for null or 0?

4 Answers4