How to handle null Datetime using MySQL Reader

Question

I'm trying to iterate my MySQL DB using MySqlDataReader in C# .Net but I'm having trouble checking whether any DateTime columns are null or empty first. I've made a dynamic list ("MyList") and plan to iterate the DB and add each item with custom fields, some of which are DateTimes, but during this process I'm getting the error "This method or property cannot be called on Null values."

With string values I usually use this inline syntax to check if the columns are null:

Value = reader["Name"] != null ? reader["Name"].ToString() : ""

but there seems to be a different process for DateTime columns which I can't figure out, here's a snippet of my code:

using MySql.Data.MySqlClient;

List<dynamic> MyList = new List<dynamic>();

using(MySqlConnection con = new MySqlConnection("server=localhost;database=database;user  id=user;password=password")){
    con.Open(); 

    string sql = "SELECT * FROM Items";
    DateTime defaultDate = DateTime.Now;

    MySqlCommand cmd = new MySqlCommand(sql,con);

    using(MySqlDataReader reader = cmd.ExecuteReader()){
        while(reader.Read()){ 
            MyList.Add(new {
                Value = reader["Name"] != null ? reader["Name"].ToString() : "",
                Date = reader.GetDateTime(reader.GetOrdinal("Date")) != null ? reader.GetDateTime(reader.GetOrdinal("Date")) : defaultDate
            });
        }   
    }   

    con.Close();
}

Take a look at Nullable types. (Hint: datetime?) – Justin Pihony Dec 05 '13 at 16:21 — Justin Pihony, Dec 05 '13 at 16:21

score 4 · Answer 1 · edited May 23 '17 at 10:28

4

You can use a Helper function like the following (that SO User Rein's has written up in a related Question here) that will take care of this requirement for you for any database type that can be Null.

i.e.

Date = ConvertFromDBVal<DateTime>(reader.GetOrdinal("Date"));

using the generic function:

public static T ConvertFromDBVal<T>(object obj)
{
    if (obj == null || obj == DBNull.Value) {
        return default(T); // returns the default value for the type
    }
    else
    {
        return (T)obj;
    }
}

edited May 23 '17 at 10:28

Community

1
1

answered Dec 05 '13 at 16:22

Shiva

20,575
14
82
112

This looks awesome but I'm getting the error "The type or namespace name 'T' could not be found (are you missing a using directive or an assembly reference?)". What could this be? – mmmoustache Dec 05 '13 at 17:02
My bad. `T` is the actual .Net `Type` you want to execute the helper function on. Fixed it to `DateTime`. Try it now. You should be able to use the same function for any other 'Type`. Example: Value = ConvertFromDBVal(reader["Name"]); – Shiva Dec 05 '13 at 17:25

score 2 · Answer 2 · answered Dec 05 '13 at 16:29

Use DateTime.TryParse to detect if value is a valid DateTime,like this:

while(reader.Read()){
        if(reader["Date"] != DBNull.Value)
                DateTime.TryParse(reader["Date"].ToString(), out defaultDate);
        MyList.Add(new {
            Value = reader["Name"] != null ? reader["Name"].ToString() : "",
            Date = defaultDate
        });
}

score 0 · Answer 3 · answered Aug 17 '17 at 08:40

0

I ended up converting the date into string in the Sql Query to parse it myself (see convert timestamp to date).

The sql query then look like :

 select DATE_FORMAT(dateColumn, '%d/%m/%Y %h:%i:%s') as dateColumn from ...

answered Aug 17 '17 at 08:40

Thomas

5,603
5
32
48

Arthur Melo · Answer 4 · 2021-04-13T02:48:17.750

If you are using MySqlReader just like me, you can use this code:

int datahoralocalOrdinal;
if ((datahoralocalOrdinal = objReader.GetOrdinal("DATAHORALOCAL")) > 0 && !objReader.IsDBNull(datahoralocalOrdinal)) {
    object datahoralocal = objReader.GetValue(datahoralocalOrdinal);
        if (datahoralocal is MySqlDateTime msdt && msdt.IsValidDateTime) {
            DateTime.TryParse($"{msdt}", out DateTime dt);
            if (dt != null) {
                    view.date = new DateTimeOffset(dt).ToUnixTimeMilliseconds();
            }
        }
}

How to handle null Datetime using MySQL Reader

4 Answers4