Devart Forums

Hi Guys,

I am testing out dotConnect for MySQL over the MySQL provided connector, but I noticed that I cannot use "Convert Zero Datetime=true;" value in the connection string. Since we have date's stored in our database as all 0's, we need this value with regular MySQL to convert those to DateTime.MinValue when we read them in.

Does dotConnect for MySQL already do the same thing, or is it going to throw an expection if we try to load something from the database where the DATETIME column is set to the special MYSQL value of all 0's?

If your DateTime column has 'null', reader.GetDateTime(i) returns DateTime.MinValue. You can call reader.IsDBNull(i) to check for null values before calling reader.GetDateTime(i).

That is not what I am talking about. I am talking about the "Convert Zero Datetime=true;" connection string parameter to the MySQL Connector/Net connector. You can read about it on this page:

http://dev.mysql.com/doc/refman/5.1/en/ ... tions.html

MySQL allows a special value of 0 in DATETIME columns in the database, which is NOT a null column. It is a value that can be put into a DATETIME column when the column is a non-nullable column, to represent the smallest possible date. However that cannot be converted to a legal System.DateTime class in C#, since the value cannot be represented. So if you don't use this option, and you try to load a row of data from the database that has this special value, you will get an exception "Unable to convert MySQL date/time value to System.DateTime". This option tells Connector/Net to convert illegal DATETIME column values (basically anything prior to DateTime.MinValue) into DateTime.MinValue. This allows us to work around the issue with the legacy data in our systems.

What would happen in dotConnect for MySQL if I try to read a DATETIME column that is the special 0 value? Will I get an exception, or will it convert it to DateTime.MinValue?

Also, if my column is a NULLABLE DATETIME column, I would expect dotConnect to convert it to a null value when it is ready also.

Note that I have been testing dotConnect using Entity Framework, and due to other issues have not been able to actually get it to work with our database (next build in a few weeks should let me test it further).

KendallB wrote:What would happen in dotConnect for MySQL if I try to read a DATETIME column that is the special 0 value? Will I get an exception, or will it convert it to DateTime.MinValue?

If your DateTime column has the special 0 value, reader.GetDateTime(i) returns DateTime.MinValue.

KendallB wrote:Also, if my column is a NULLABLE DATETIME column, I would expect dotConnect to convert it to a null value when it is ready also.

Could you please explain what you mean?
If you have the NULL value in your 'DATETIME DEFAULT NULL' column, reader.GetDateTime(i) cannot return a null value because it returns not nullable System.DateTime.

Excellent to hear it wil convert zero values to DateTime.MinValue.

As for the null columns I am talking about from the Entiry Framework and I assume that will work as expected and return a null value if the column is a nullable DateTime column (and the Entity Type is a nullablr System.DateTime).

Yes. the null values in database will be returned as null values for nullable DateTime properties.