C # ADO.NET: nulls and DbNull - is there a more efficient syntax?

Question

Do I have a DateTime? which I am trying to insert into a field using DbParameter . I create a parameter like this:

 DbParameter datePrm = updateStmt.CreateParameter(); datePrm.ParameterName = "@change_date"; 

And then I want to put a DateTime? value DateTime? in dataPrm.Value , given null s.

At first I thought I would be smart:

 datePrm.Value = nullableDate ?? DBNull.Value; 

but error with error

The operator '??' can't apply to operands like "System.DateTime"? and 'System.DBNull'

Therefore, I suppose that only works if the second argument is a version of the first argument that does not allow null. So I went for:

 datePrm.Value = nullableDate.HasValue ? nullableDate.Value : DBNull.Value; 

but this does not work either:

The conditional expression type cannot be determined because there is no implicit conversion between "System.DateTime" and "System.DBNull"

But I do not want to convert between these types!

So far I can only work:

 if (nullableDate.HasValue) datePrm.Value = nullableDate.Value; else datePrm.Value = DBNull.Value; 

Is this really the only way to write this? Is there a way to get single-line traffic using a ternary operator?

Update: I really don't understand why ??? version does not work. MSDN says:

the operator returns the left operand if it is not NULL, or returns the correct operand.

This is exactly what I want!

Update2: Well, in the end, it was obvious:

 datePrm.Value = nullableDate ?? (object)DBNull.Value; 

Answer 1

Yeah! I found an even more effective solution than @Trebz!

 datePrm.Value = nullableDate ?? (object)DBNull.Value; 

Answer 2

If you are using C # 3.0, you can create an extension method for this:

 public static class DBNullableExtensions { public static object ToDBValue<T>(this Nullable<T> value) where T:struct { return value.HasValue ? (object)value.Value : DBNull.Value; } } class Program { static void Main(string[] args) { int? x = null; Console.WriteLine( x.ToDBValue() == DBNull.Value ); } } 

Answer 3

This will work if you used

 datePrm.Value = nullableDate.HasValue ? (object)nullableDate.Value : DBNull.Value; 

Answer 4

If you are using SQLServer, the System.Data.SqlTypes namespace contains some utility classes that avoid the annoying type. For example, instead:

 var val = (object) "abc" ?? DBNull.Value; 

you can write this:

 var val = "abc" ?? SqlString.Null; 

Answer 5

I think the error with your second attempt is due to the fact that nullableDate.Value and DBNull.Value are different types, and the ternary operator must choose the same type to return in both cases. I don't have a testing environment, but I think this should work for you:

 datePrm.Value = nullableDate.HasValue ? (object)nullableDate.Value : (object)DBNull.Value; 

Answer 6

The way I do this is that I have a static utility class that just goes through and checks to see if the parameter value is null, and then I set the value to DBNull. I just do this before I call Execute.