Nullable DateTime with SQLDataReader [duplicate]

Question

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;

                    }
                }

Answer 1

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"))

Answer 2

replace

DateTime startingDate;

with

DateTime? startingDate;

The question mark marks it as a nullable value and your reader should be able to set startingdate to null instead of throwing an exception.

you could also check for null values while your reader is working and replace null values with empty strings

while(reader.read())
{
  //column is an int value of your column. I.e: if the column ist the 8th column, set column to 7 (0-based)
  StartingDate = (reader.IsDBNull(column)) ? null : reader.GetOrdinal("STARTINGDATE"));
 //instead of null you could also return a specific date like 1.1.1900 or String.Empty
}

Answer 3

I know this question has been answered, but further simplifying the code to deal with other nullable variable type. The answer above only limits to one specific variable type. The nice thing about this is that it also allows you to include a default value rather than setting it as null.

public static T GetDataType<T>( this SqlDataReader r, string name, object def = null )
{
     var col = r.GetOrdinal(name);
     return r.IsDBNull(col) ? (T)def : (T)r[name];
}

then in the code, you can use such as

...
while(reader.Read())
{
    data1Bool = reader.GetDataType<bool?>("data1"); // if it's null then we'll set it as null
    data2intNotNull = reader.GetDataType<int>("data2", 0); // if the data is null, then we'll set to 0
    data3date = reader.GetDataType<DateTime?>("data3", DateTime.Now); // same example as above, but instead of setting to 0, I can default it to today's date.
}
...

I had other issue regarding of null-able value object, and this saved me hours of trying to figure out what's going on. (WPF doesn't explain the issue when compiling runtime.)