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
– Justin Pihony

2013-12-05 16:21:25 +00:00
Commented Dec 5, 2013 at 16:21 — Justin Pihony
– Justin Pihony, Commented Dec 5, 2013 at 16:21

Community · Accepted Answer · 2017-05-23 10:28:43Z

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, 2017 at 10:28

CommunityBot

11 silver badge

answered Dec 5, 2013 at 16:22

Shiva

21.1k15 gold badges88 silver badges120 bronze badges

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

2 Comments

mmmoustache Over a year ago

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?

Shiva Over a year ago

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<string>(reader["Name"]);

Jota WA · Accepted Answer · 2013-12-05 16:29:32Z

2

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

answered Dec 5, 2013 at 16:29

Jota WA

643 bronze badges

Comments

Thomas · Accepted Answer · 2017-08-17 08:40:14Z

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, 2017 at 8:40

Thomas

5,9616 gold badges35 silver badges49 bronze badges

Comments

Arthur Melo · Accepted Answer · 2021-04-13 02:48:17Z

0

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

edited Apr 13, 2021 at 2:48

answered Apr 13, 2021 at 2:41

Arthur Melo

4625 silver badges13 bronze badges

Collectives™ on Stack Overflow

How to handle null Datetime using MySQL Reader

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related