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

`