Inserting a DBNull value into a database

Question

I have code which works pretty well. I just need some of your opinions on how to write it better with fewer line of code. I want to use a ternary operator but I couldn't make it work so I did an if else instead.

Basically, I want to insert a price on my database. The price column is a nullable decimal. Even in my C# code, it's a nullable decimal as well.

if (items.Price == null)
{
    SqlParameter[] p =
    {
        new SqlParameter("@purchaseorder", items.PurchaseOrderItems),
        new SqlParameter("@modelnumber", items.ModelNumber), 
        new SqlParameter("@quantity", items.Quantity),
        new SqlParameter("@Price",  DBNull.Value),
        new SqlParameter("@Description", items.Description),
        new SqlParameter("@OrderNumber", items.OrderNumber),
        new SqlParameter("@Unit", items.Unit)
    };

    i = Dal.ExecuteNonQuery(sql,p);
}
else
{
    SqlParameter[] p =
    {
        new SqlParameter("@purchaseorder", items.PurchaseOrderItems),
        new SqlParameter("@modelnumber", items.ModelNumber), 
        new SqlParameter("@quantity", items.Quantity),
        new SqlParameter("@Price",  items.Price),
        new SqlParameter("@Description", items.Description),
        new SqlParameter("@OrderNumber", items.OrderNumber),
        new SqlParameter("@Unit", items.Unit)
    };

    i = Dal.ExecuteNonQuery(sql,p);
}
return i;

Can this be written without the else block?

Answer 1

All you have to do is to use the ?: (ternary operator):

new SqlParameter("@Price", items.Price == null ? DBNull.Value : (object)items.Price)

Alternatively you could write an extension for it:

public static object PriceOrDefault(this ItemsType items)
{
    return items.Price == null ? DBNull.Value : (object)items.Price;
}

and use it instead:

new SqlParameter("@Price", items.PriceOrDefault())

---

As it turns out the Price is a decimal? so you may consider using the HasValue property and swap the values:

items.Price.HasValue ? (object)items.Price : DBNull.Value;

I find this looks better then the null-check.

Answer 2

The most terse and declarative way to write this is to use C#'s ?? operator. Your line becomes:

new SqlParameter("@Price",  (object)items.Price ?? DbNull.Value),

The expression here will either return the non-null value of the left operand (items.Price), or return the operand to the right instead.

If you hadn't guessed, the ?? operator is basically syntactic sugar for if operl != null ? operl : operr.

Answer 3

This is down to personal taste, but if you do go down the extension method route suggested by t3chb0t, I'd consider making it generic so that you can reuse it for any future Nullable types you might use and you don't end up with a bunch of very similar looking functions for different fields. Something like this:

public static object DbValueOrNull<T>(this T? value) where T : struct
{
    return value.HasValue ? (object)value.Value : DBNull.Value;
}

Would allow:

new SqlParameter("@Price", items.Price.DbValueOrNull())
new SqlParameter("@SomeOtherNullableVar", items.SomeOtherNullableVar.DbValueOrNull())

etc.

Answer 4

Have you taken a look at the System.Data.SqlTypes namespace - particularly SqlDecimal?

Building on some of the other answers, you can use SqlDecimal and the ?? operator as follows:

new SqlParameter("@Price",  items.Price ?? SqlDecimal.Null),