CODEWITHSUNDEEP
c#mysql.net
mmmoustache
mmmoustache

Reputation: 2323

How to handle null Datetime using MySQL Reader

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

Upvotes: 2

Views: 4808

Answers (4)

Arthur Melo
Arthur Melo

Reputation: 462

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

Upvotes: 0

Thomas
Thomas

Reputation: 5911

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 ...

Upvotes: 0

Shiva
Shiva

Reputation: 20935

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

Upvotes: 4

Jota WA
Jota WA

Reputation: 64

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

Upvotes: 2

Related Questions