Dealing with null dates returned from db.

Question

Hi I am trying to return a collection which may potentially have null date values. However, if they are null - I want to keep them that way and not just use a arbitrary date instead.

If it was a string, I'd check for nulls like thus:

calEventDTO.occurrenceType = dr.IsDBNull(10) ? null : dr.GetString(10);

How would I do the same for a date? The following doesn't compile (There is no implicit conversion between 'null' and System.DateTime').

calEventDTO.recurrenceID = dr.IsDBNull(9) ? null : dr.GetDateTime(9);

My dto is set up to deal with null values.

public DateTime? recurrenceID
    {
        get { return _recurrenceID; }
        set { _recurrenceID = value; }
    }

Any hints/help/pointers much appreciated. thanks

Answer 1

Try:

calEventDTO.recurrenceID = dr.IsDBNull(9) ? null : (DateTime?) dr.GetDateTime(9);

The ? operator requires both operands (null, and dr.GetDateTime(9) in this case) to be of the same type.

Answer 2

The conditional operator doesn't consider what you assign the result to, so you have to cast either of the operands to DateTime?:

calEventDTO.recurrenceID = dr.IsDBNull(9) ? (DateTime?)null : dr.GetDateTime(9);

Answer 3

IsDbNull(int) is usually much slower that using methods like GetSqlInt32 and then comparing to DBNull.Value or using it's own .IsNull Like:

    public static int Int32(this SqlDataReader r, int ord)
    {
        var t = r.GetSqlInt32(ord);
        return t.IsNull ? default(int) : t.Value;
    }

Tried a few template solutions but to no avail so far. The problem is that all Sql-types (SqlInt32 here) types are actually structs and while they all have .Value property C# doesn't have real templates to handle that. Also they have their own INullable interface which has only .IsNull and is not conpatible with Nyllable<>.

I suspect that one would need full set of Sql-types as C# templates or to add ICOnvertible to them in order to be able to have just one or two templated methods.

If someone has maybe an idea with a functional trick or two speak up :-)