Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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;
like image 776
Stewart Johnson Avatar asked Oct 20 '08 15:10

Stewart Johnson


5 Answers

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

datePrm.Value = nullableDate ?? (object)DBNull.Value;
like image 86
Stewart Johnson Avatar answered Oct 23 '22 18:10

Stewart Johnson


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;
like image 7
Gian Marco Avatar answered Oct 23 '22 17:10

Gian Marco


It would work if you used

datePrm.Value = nullableDate.HasValue ? (object)nullableDate.Value : DBNull.Value;
like image 5
dnolan Avatar answered Oct 23 '22 17:10

dnolan


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 );
    }
}
like image 5
Pop Catalin Avatar answered Oct 23 '22 17:10

Pop Catalin


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;
like image 1
Dan Avatar answered Oct 23 '22 17:10

Dan