Handling a DateTime DBNull

Question

I have seen many, many versions of this on SO, but none of them seem to quite work for my needs.

My data comes from a vendor database that allows null for DateTime fields. First I pull my data into a DataTable.

using (SqlCommand cmd = new SqlCommand(sb.ToString(), conn))
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
    da.Fill(dt);
}

I am converting a DataTable to a List<> for processing.

var equipment = from i in dt.AsEnumerable()
    select new Equipment()
    {
        Id = i.Field<string>("ID"),
        BeginDate = i.Field<DateTime>("BeginDate"),
        EndDate = i.Field<DateTime>("EndDate"),
        EstimatedLife = i.Field<double>("EstimatedLife")
    }

So, how do I check for DBNull in this instance? I tried to write a method.

    public DateTime CheckDBNull(object dateTime)
    {
        if (dateTime == DBNull.Value)
            return DateTime.MinValue;
        else
            return (DateTime)dateTime;
    }

Ilmenite

Answer 1

One possible option is store it as a nullable date time with the syntax DateTime?

Here is a link to the MSDN about using nullable types

Mismanage

So on my Equipment object define the DateTime fields as DateTime? – Ilmenite

If it is a valid business case for your Equipment to not have a begin or end date then yes. Otherwise your db layer should throw an exception. – Marvellamarvellous

yep - it's a shortcut for Nullable<DateTime> – Leblanc

Answer 2

Use IsDBNull()

System.Convert.IsDBNull(value);

or if you have a SqlDataReader

reader.IsDBNull(ordinal);

And make your DateTime properties to be nullable (DateTime?) and set null in case of DBNull. Field<T>() will automatically do this.

Cocklebur

Answer 3

I have found that the easiest way to handle this is to cast the field as your data type using the "as" keyword. This works great for database fields that can be null, and is nice and simple.

Here is more detail on this: Direct casting vs 'as' operator?

Example:

    IDataRecord record = FromSomeSqlQuerySource;
    string nullableString;
    DateTime? nullableDateTime;

    nullableString = record["StringFromRecord"] as string;
    nullableDateTime = record["DateTimeFromRecord"] as DateTime?;

Waistline

Answer 4

here is an example of some code i use to read Datetimes

im sure it could be written better but runs fine for me

   public DateTime? ReadNullableDateTimefromReader(string field, IDataRecord data)
    {

        var a = data[field];
        if (a != DBNull.Value)
        {
            return Convert.ToDateTime(a);
        }
        return null;
    }

    public DateTime ReadDateTimefromReader(string field, IDataRecord data)
    {
        DateTime value;
        var valueAsString = data[field].ToString();
        try
        {
            value = DateTime.Parse(valueAsString);
        }
        catch (Exception)
        {
            throw new Exception("Cannot read Datetime from reader");
        }

        return value;
    }

Calamanco

Answer 5

You should use DataRow["ColumnName"] is DBNull to compare DateTime null.

E.g.:

 if(studentDataRow["JoinDate"] is DBNull) { // Do something here }

Hirohito

Answer 6

I wrote a generic extension method that I use in all of my projects:

public static object GetValueSafely<T>(this System.Data.DataTable dt, string ColumnName, int index)
{
    if (typeof(T) == typeof(int))
    {
        if (dt.Rows[index][ColumnName] != DBNull.Value)
            return dt.Rows[index][ColumnName];
        else
            return 0;
    }
    else if (typeof(T) == typeof(double))
    {
        if (dt.Rows[index][ColumnName] != DBNull.Value)
            return dt.Rows[index][ColumnName];
        else
            return 0;
    }
    else if (typeof(T) == typeof(decimal))
    {
        if (dt.Rows[index][ColumnName] != DBNull.Value)
            return dt.Rows[index][ColumnName];
        else
            return 0;
    }
    else if (typeof(T) == typeof(float))
    {
        if (dt.Rows[index][ColumnName] != DBNull.Value)
            return dt.Rows[index][ColumnName];
        else
            return 0;
    }
    else if (typeof(T) == typeof(string))
    {
        if (dt.Rows[index][ColumnName] != DBNull.Value)
            return dt.Rows[index][ColumnName];
        else
            return string.Empty;
    }
    else if (typeof(T) == typeof(byte))
    {
        if (dt.Rows[index][ColumnName] != DBNull.Value)
            return dt.Rows[index][ColumnName];
        else
            return 0;
    }
    else if (typeof(T) == typeof(DateTime))
    {
        if (dt.Rows[index][ColumnName] != DBNull.Value)
            return dt.Rows[index][ColumnName];
        else
            return DateTime.MinValue;
    }
    else if (typeof(T) == typeof(bool))
    {
        if (dt.Rows[index][ColumnName] != DBNull.Value)
            return dt.Rows[index][ColumnName];
        else
            return false;
    }
    if (dt.Rows[index][ColumnName] != DBNull.Value)
        return dt.Rows[index][ColumnName];
    else
        return null;
}

Usage example:

private void Example()
{
    DataTable dt = GetDataFromDb() // get data from database...
    for (int i = 0; i < dt.Rows.Count; i++)
    {
        Console.WriteLine((DateTime)dt.GetValueSafely<DateTime>("SomeDateColumn", i));
        Console.WriteLine((int)dt.GetValueSafely<int>("SomeIntColumn", i));
        Console.WriteLine((string)dt.GetValueSafely<string>("SomeStringColumn", i));
    }
}

Cinerary