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

Question

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 nulls.

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;

Roderich

#1546211 – Bronchi

Answer 1

Ah ha! I found an even more efficient solution than @Trebz's!

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

Roderich

I wouldn't call it more "efficient" perhaps "concise is a better word? – Megalo

Right -- you know what I meant. :-) – Roderich

This is really great! I have Been looking for this. Thanks – Discommend

"Efficient" makes sense here as well, if the solution helps the OP code faster. – Monophyletic

I was just looking for exactly this! – Milt

Awesome thank you I was getting irritated that decimal? which is nullable wouldn't play nice with null coalesce! – Behold

This is a great solution because DbParameter.Value is of type object anyway – Diamagnetism

Answer 2

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;

Execute

+1 @Gian Marco Gherardi's answer is ideal. You can still use the ternary operator, just use the helper classes provided by System.Data.SqlTypes – Fourdrinier

Answer 3

It would work if you used

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

Bregenz

Yep, that one works alright, and is 8 characters shorter than @Dan's solution. :-) I wish there was a way to get the ?? solution to work since it's much shorter syntax. – Roderich

Update: I found an even more efficient solution using ??. – Roderich

Answer 4

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

Klatt

Answer 5

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;

Caravan

Answer 6

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

Categorical