Thursday, 18 March 2010

Serialization issue with MySqlDateTime in a DataSet

If you encountered the error "Unable to convert MySQL date/time value to System.DateTime" this article is related. So is this one .Net Exception: Unable to convert MySQL date/time value to System.DateTime


-- UPDATE 2: And here's the final answer. set Allow Zero Datetime=false (the default i think) in your connection string. And make sure you don't put meaningless zero valued dates into your database and all works as it should. Datetime and null values are inserted and selected successfully from date columns. Serialization works.

-- UPDATE 1: Having looked into this a bit more it would appear that System.DateTime is unable to represent a zero date i.e. 0/0/0000 00:00:00 (which makes no sense anyway). I suspect for legacy / backward compatibility reasons MySql Connecter has to represent this, therefore the reason for MySqlDateTime existing. Would make sense if it used System.DateTime when Allow Zero Datetime = false, not tried this yet.

------------

While adding MySQL support to our DAL I came across a DataSet serialization issue. We have an object that represents a stored search. This object is hydrated by passing it a serialised version of a dataset representing a search returned from the database.

MySqlDataAdapter seems to setup any column if type Datetime as MySql.Data.Types.MySqlDateTime rather than System.DateTime - as is the case with the SQL Server and Oracle providers. So far I've not found any options to control this or an explanation as to why the MySql ADO.net provider had been implemented as such. This hasn't posed a significant issue till I've had to serialize the DataSet to find that values for fields of type MySqlDateTime serialize differently to System.DateTime. Rather than representing the date value as simple string it is a chunk of XML. This XML (which is the inner XML of the field element. default behaviour) contains an XML decleration and is entity escaped. Therefore not valid and one cannot do much with it.

In order to get around this problem I implemented my own code to populate a dataset from a DataReader which converts all MySqlDateTime columns to System.DateTime in the DataTable and then populates the datatable.

I will be taking a closer look at the provider code with the hope to at least understand why the MySqlDateTime type is required.

Note: I'm using MySQL Connector.Net 6.1.1 here.

Public Shared Sub FillDataTable(ByRef DataTable As DataTable, ByVal Command As MySqlCommand)

Dim r As MySqlDataReader
'Dim DT As DataTable
If DataTable Is Nothing Then
DataTable = New DataTable
End If

Try

r = Command.ExecuteReader

'-- Create the schema
Dim col As DataColumn
For i As Integer = 0 To r.FieldCount - 1
col = New DataColumn
col.ColumnName = r.GetName(i)
If r.GetFieldType(i) Is GetType(MySqlDateTime) Then
col.DataType = GetType(System.DateTime)
Else
col.DataType = r.GetFieldType(i)
End If
DataTable.Columns.Add(col)
Next


'-- Populate the datatable
Dim row As DataRow
While r.Read
row = DataTable.NewRow
For Each c As DataColumn In DataTable.Columns
Dim colName As String = c.ColumnName
row.Item(c) = DBNull.Value '-- default to DBNull

If Not r.IsDBNull(r.GetOrdinal(colName)) AndAlso _ r.GetFieldType(r.GetOrdinal(colName)) Is GetType(MySqlDateTime) Then
If r.GetMySqlDateTime(colName).IsValidDateTime Then '-- not Zero
row.Item(c) = r.GetDateTime(colName)
End If
Else
row.Item(c) = r.Item(colName)
End If
Next
DataTable.Rows.Add(row)
End While

If r IsNot Nothing Then
r.Close()
r = Nothing
End If
Catch ex As Exception

End Try
End Sub

Saturday, 13 March 2010

MySQL INSERT & UPDATE date mask

Use 'yyyy-MM-dd  HH:mm:ss' as the date mask (i.e. format the date using this format string) in insert and update statements.

.Net Exception: Unable to convert MySQL date/time value to System.DateTime

When using DataAdapter.Fill()

I encountered the exception "Unable to convert MySQL date/time value to System.DateTime" while I was implementing MySQL support to our .Net data access component.When return the results(into a DataTable) from a select statement which include a date/time column that has a 0/0/0000 00:00:00 value i.e. you inserted zero.

You can avoid this exception either by 

1) adding the parameter 'Allow Zero Date=true;' to your connection string (worked for me)
2) setting the values to a none zero date (kind of obviously)
3) setting the values to null rather than zero.

solutions 2) and 3) are probably more suited for new databases where as 1) if you are planning on migrating a bunch of data from else where.

When implicitly conversting / cast  a Datetime DataTable column to string

Once I got past the above I hit the exception "Conversion from type 'MySqlDateTime' to type 'String' is not valid" while trying to implicitly convert the zero datetime value (i.e. 0/0/0000 00:00:00) in the DataTable date column.

I solved this by using the .ToString.

Worth mentioning that this exact code run against MS SQL Server and Oracle Database without encountering the above issues.

Thanks to Aleksandar's Blog
http://vucetica.blogspot.com/2009/01/unable-to-convert-mysql-datetime-value.html

and Tjitjing Blog
http://blog.tjitjing.com/index.php/2007/04/unable-to-convert-mysql-datetime-value.html