如何在不检查null或0的情况下为sqlparameter NULL值赋值？

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)

有没有办法我们可以通过这个检查和一个设计,我们可以避免对每个值进行重复检查? (顺便说一句,它只是一个逻辑,0对于我的情况被解释为NULL,但我也要求像字符串这样的可空对象一样)

4 个解决方案

#1

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:

但这对你的情况没有帮助,因为你试图将非null值合并为null。我个人会考虑重新设计你的模型 - 使用可空类型的值可以为null,而不是使用幻数。但是,您仍然可以使用通用扩展方法:

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.

这将把0,'\ 0',false,null等转换为DBNull.Value。你必须非常小心地使用它,因为我认为有很多地方0应该真的是0。

#2

You can use ?: Operator

你可以使用?:Operator

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

#3

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;
    }
}

#4

You can use the ?? operator:

你可以用??运营商:

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

#1