I've got a DateTime?
that I'm trying to insert into a field using a DbParameter
. I'm creating the parameter like so:
DbParameter datePrm = updateStmt.CreateParameter();
datePrm.ParameterName = "@change_date";
And then I want to put the value of the DateTime?
into the dataPrm.Value
while accounting for null
s.
I thought initially I'd be clever:
datePrm.Value = nullableDate ?? DBNull.Value;
but that fails with the error
Operator '??' cannot be applied to operands of type 'System.DateTime?' and 'System.DBNull'
So I guess that only works if the second argument is a non-nullable version of the first argument. So then I went for:
datePrm.Value = nullableDate.HasValue ? nullableDate.Value : DBNull.Value;
but that doesn't work either:
Type of conditional expression cannot be determined because there is no implicit conversion between 'System.DateTime' and 'System.DBNull'
But I don't want to convert between those types!
So far the only thing I can get to work is:
if (nullableDate.HasValue)
datePrm.Value = nullableDate.Value;
else
datePrm.Value = DBNull.Value;
Is that really the only way I can write this? Is there a way to get a one-liner using the ternary operator to work?
Update: I don't really get why the ?? version doesn't work. MSDN says:
The ?? operator returns the left-hand operand if it is not null, or else it returns the right operand.
That's exactly what I want!
Update2: Well it was kind of obvious in the end:
datePrm.Value = nullableDate ?? (object)DBNull.Value;
Ah ha! I found an even more efficient solution than @Trebz's!
datePrm.Value = nullableDate ?? (object)DBNull.Value;
If you are using SQLServer, the System.Data.SqlTypes
namespace contains some utility classes that avoid the annoying type casting. For example instead of this:
var val = (object) "abc" ?? DBNull.Value;
you can write this:
var val = "abc" ?? SqlString.Null;
It would work if you used
datePrm.Value = nullableDate.HasValue ? (object)nullableDate.Value : DBNull.Value;
If you're using C# 3.0 you can create an extension method to do this easy:
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 );
}
}
I think the error with your second attempt is due to nullableDate.Value and DBNull.Value being different types and the ternary operator needing to pick one type to return in both cases. I don't have the environment to test this but I think this should work for you:
datePrm.Value = nullableDate.HasValue ? (object)nullableDate.Value : (object)DBNull.Value;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With