Nullable DateTime with SQLDataReader

asp.net, c#

Solution

Here is a helper method to get the value out from the reader

public static class ReaderExtensions {

  public static DateTime? GetNullableDateTime(this SqlDataReader reader, string name){ 
       var col = reader.GetOrdinal(name);
       return reader.IsDBNull(col) ? 
                   (DateTime?)null :
                   (DateTime?)reader.GetDateTime(col);
  }
}

Update on how to use in response to comment

using (SqlConnection con = new SqlConnection(connectionString))
        using (SqlCommand cmd = con.CreateCommand())
        {

            List<DetailsClass> details = new List<DetailsClass>();
            DetailsClass dtl;
            try
            {
                con.Open();
                cmd.CommandText = "Stored Procedure Name";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@MyParameter", myparameter);

                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        dtl = new DetailsClass((
                            reader.GetInt32(reader.GetOrdinal("MEMBERSHIPGEN"))),
                            reader.IsDBNull(1) ? null : reader.GetString(reader.GetOrdinal("EMAIL")),
                            reader.GetNullableDateTime("STARTINGDATE"));


                        details.Add(dtl);
                    }
                    reader.Close();
                    return details;

                }
            }

Also note you are using `reader.IsDBNull(1)` and then `reader.GetOrdinal`. Probably should be `reader.IsDBNull(reader.GetOrdinal("EMAIL"))`

Problem

I almost hate to ask this question seems like it has been asked a million times before but even with me researching the other question I still cant seem to figure this out in my case. I read that DateTime is a nullable type and I tried a few of the examples but I am trying to figure out if it is NULL in the database my SQLDATAREADER is failing. Error System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot 'be called on Null values.' DetailsClass ``` private DateTime? startingDate; public DateTime? StartingDate { get{ return startingDate; } set{ startingDate = value; } } // constructor Public DetailsClass(DateTime? startingDate) { this.startingDate = startingDate; } ``` DBClass ``` using (SqlConnection con = new SqlConnection(connectionString)) using (SqlCommand cmd = con.CreateCommand()) { List<DetailsClass> details = new List<DetailsClass>(); DetailsClass dtl; try { con.Open(); cmd.CommandText = "Stored Procedure Name"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@MyParameter", myparameter); using (SqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { dtl = new DetailsClass(( reader.GetInt32(reader.GetOrdinal("MEMBERSHIPGEN"))), reader.IsDBNull(1) ? null : reader.GetString(reader.GetOrdinal("EMAIL")), reader.GetDateTime(reader.GetOrdinal("STARTINGDATE"))); details.Add(dtl); } reader.Close(); return details; } } ```

Original source

Related problems