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

score 10 · Answer 1 · edited Jan 12 '18 at 18:21

10

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.

edited Jan 12 '18 at 18:21

Michael

8,362
6
61
88

answered Oct 20 '10 at 14:19

abatishchev

98,240
88
296
433

Scott Chamberlain · Accepted Answer · 2010-10-20T14:34:00.467

8

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

edited Oct 20 '10 at 14:34

answered Oct 20 '10 at 14:20

Scott Chamberlain

124,994
33
282
431

So on my Equipment object define the `DateTime` fields as `DateTime?` – Mike Wills Oct 20 '10 at 14:24
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. – vc 74 Oct 20 '10 at 14:29
1

yep - it's a shortcut for `Nullable` – Rob Fonseca-Ensor Oct 20 '10 at 14:30

score 6 · Answer 3 · answered Nov 07 '17 at 15:51

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?;

score 2 · Answer 4 · answered Oct 20 '10 at 14:28

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

score 0 · Answer 5 · edited Nov 19 '14 at 09:09

0

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

E.g.:

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

edited Nov 19 '14 at 09:09

ekostadinov

6,880
3
29
47

answered Nov 19 '14 at 08:34

Quang Le

41
4

Jonathan Applebaum · Answer 6 · 2019-03-05T05:44:26.343

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

Handling a DateTime DBNull

6 Answers6

Linked