26.2.4.7. Handling Date and Time Information in MySQL Connector/NET
MySQL and the .NET languages handle date and time information
differently, with MySQL allowing dates that cannot be
represented by a .NET data type, such as '0000-00-00
00:00:00'. These differences can cause problems if
not properly handled.
In this section we will demonstrate how to properly handle
date and time information when using MySQL Connector/NET.
26.2.4.7.2. Problems when Using Invalid Dates
The differences in date handling can cause problems for
developers who use invalid dates. Invalid MySQL dates cannot
be loaded into native .NET DateTime
objects, including NULL dates.
Because of this issue, .NET DataSet objects
cannot be populated by the Fill method of
the MySqlDataAdapter class as invalid dates
will cause a
System.ArgumentOutOfRangeException
exception to occur.
26.2.4.7.3. Restricting Invalid Dates
The best solution to the date problem is to restrict users
from entering invalid dates. This can be done on either the
client or the server side.
Restricting invalid dates on the client side is as simple as
always using the .NET DateTime class to
handle dates. The DateTime class will only
allow valid dates, ensuring that the values in your database
are also valid. The disadvantage of this is that it is not
useful in a mixed environment where .NET and non .NET code are
used to manipulate the database, as each application must
perform its own date validation.
Users of MySQL 5.0.2 and higher can use the new
traditional SQL mode to restrict invalid
date values. For information on using the
traditional SQL mode, see
Section 5.2.5, “The Server SQL Mode”.
26.2.4.7.4. Handling Invalid Dates
Although it is strongly recommended that you avoid the use of
invalid dates within your .NET application, it is possible to
use invalid dates by means of the
MySqlDateTime datatype.
The MySqlDateTime datatype supports the
same date values that are supported by the MySQL server. The
default behavior of MySQL Connector/NET is to return a .NET
DateTime object for valid date values, and return an error for
invalid dates. This default can be modified to cause
MySQL Connector/NET to return MySqlDateTime
objects for invalid dates.
To instruct MySQL Connector/NET to return a
MySqlDateTime object for invalid dates, add
the following line to your connection string:
Allow Zero Datetime=True
Please note that the use of the
MySqlDateTime class can still be
problematic. The following are some known issues:
Data binding for invalid dates can still cause errors
(zero dates like 0000-00-00 do not seem to have this
problem).
The ToString method return a date
formatted in the standard MySQL format (for example,
2005-02-23 08:50:25). This differs from
the ToString behavior of the .NET
DateTime class.
The MySqlDateTime class supports NULL
dates, while the .NET DateTime class does not. This can
cause errors when trying to convert a MySQLDateTime to a
DateTime if you do not check for NULL first.
Because of the known issues, the best recommendation is still
to use only valid dates in your application.
26.2.4.7.5. Handling NULL Dates
The .NET DateTime datatype cannot handle
NULL values. As such, when assigning values
from a query to a DateTime variable, you
must first check whether the value is in fact
NULL.
When using a MySqlDataReader, use the
.IsDBNull method to check whether a value
is NULL before making the assignment:
[VB]
If Not myReader.IsDBNull(myReader.GetOrdinal("mytime")) Then
myTime = myReader.GetDateTime(myReader.GetOrdinal("mytime"))
Else
myTime = DateTime.MinValue
End If
[C#]
if (! myReader.IsDBNull(myReader.GetOrdinal("mytime")))
myTime = myReader.GetDateTime(myReader.GetOrdinal("mytime"));
else
myTime = DateTime.MinValue;
NULL values will work in a dataset and can
be bound to form controls without special handling.
