# Inserting a DBNull value into a database

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?

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


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.

• Sorry, but i got a squiggly line underneath. s15.postimg.org/xmy26aduz/werw.png – Dikong Prigm Oct 10 '16 at 6:31
• @DikongPrigm Just cust it to object. I edited the examples. SqlParameter excpects it anyway. – t3chb0t Oct 10 '16 at 6:36
• That works.. Thank. I will wrap this to extension method. Thank you – Dikong Prigm Oct 10 '16 at 6:40
• If you go with the helper method, I'd rather go with the more general object NulltoDBNull(object o) { return o != null ? o : DBNull.Value; }. – CodesInChaos Oct 10 '16 at 14:47

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.

• nice, I actually know this one too but didn't think it would work like this with a nullable :-] – t3chb0t Oct 10 '16 at 11:27
• Using null coalescing is definitely the cleanest option. – William Mariager Oct 10 '16 at 11:53

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.

• wow, I believe I've never come up with the idea to use the nullable ? on a T + struct ;-) – t3chb0t Oct 10 '16 at 10:35

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),