How to read back a nulllable DateTime using SQL Data Reader?

Question

I've declared a nullable DateTime? NextUpdateproperty in my model and database.

I can update the DateTime value fine on my DB as it allows null for this field.

But when I try to get the value of NextUpdate field from the database using SQL Data Reader it bombs out because the value of NextUpdate is null.

I did try to init the NextUpdate value if it is null using the following assignment but the error is still thrown telling me that field is null:

NextUpdate = dataReader.GetDateTime(dataReader.GetOrdinal("NextUpdate")) != null ? dataReader.GetDateTime(dataReader.GetOrdinal("NextUpdate")) : DateTime.MinValue,

Error:

Data is Null. This method or property cannot be called on Null values -  at System.Data.SqlClient.SqlBuffer.get_DateTime()

Question:

Is there a short method of reading back and initializing a nullable **DateTime?** value?

Code:

using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
   if (dataReader.Read())
   {
      esc = new Escalation
      {
          NextUpdate = dataReader.GetDateTime(dataReader.GetOrdinal("NextUpdate")),
          RootCause = dataReader["RootCause"] != null ? dataReader["EM"].ToString() : ""
      };
   }
}

Property in Model:

    public DateTime? NextUpdate { get; set; }

Are you sure that your IDataReader always have a column named NextUpdate ? — Moumit
– Moumit, Commented Jun 2, 2016 at 11:24

Cee McSharpface · Accepted Answer · 2016-06-02 11:17:00Z

1

Compare the value against DBNull.Value instead of null:

NextUpdate = dataReader["NextUpdate"].Equals(DBNull.Value) ? (DateTime?)null : (DateTime?)dataReader["NextUpdate"]

(assuming that the NextUpdate member is a DateTime?)

answered Jun 2, 2016 at 11:17

Cee McSharpface

8,7133 gold badges43 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

nick_n_a · Accepted Answer · 2016-06-02 11:17:35Z

0

Try following expression:

RootCause = (dataReader["RootCause"] is DBNull) ? (DateTime?)null :  (DateTime?)dataReader["RootCause"] ;

answered Jun 2, 2016 at 11:17

nick_n_a

2061 silver badge16 bronze badges

2 Comments

Brian Var Over a year ago

NextUpdate is the DateTime? field not RootCause.

nick_n_a Over a year ago

@Brian J exactly.

Collectives™ on Stack Overflow

How to read back a nulllable DateTime using SQL Data Reader?

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related