Closed
Description
I'm using MySql database and the NuGet version of Dapper. I'm reading from the database like this:
return _connection.Query<Person>(
"SELECT * FROM Person WHERE PersonId=@PersonId",
new { PersonId = 42 }).SingleOrDefault();
The class contains a nullable DateTime property. When the value is NULL in the database, it works fine. When it has a value, it fails complaining:
Error parsing column 14 (LastCommentedAt= - Object)
I managed to solve this by installing my own TypeHandler for DateTime?. I saw how Dapper.NodaTime is doing it so I created my own handler:
public class NullableDateTimeHandler : SqlMapper.TypeHandler<DateTime?>
{
protected NullableDateTimeHandler()
{
}
public static readonly NullableDateTimeHandler Default = new NullableDateTimeHandler();
public override void SetValue(IDbDataParameter parameter, DateTime? value)
{
if (value.HasValue)
{
parameter.Value = value.Value;
}
else
{
parameter.Value = DBNull.Value;
}
}
public override DateTime? Parse(object value)
{
if (value == null)
{
return null;
}
if (value is DateTime)
{
return (DateTime)value;
}
return Convert.ToDateTime(value);
}
}
and I make sure I install this handler before anything else happens:
SqlMapper.AddTypeHandler(NullableDateTimeHandler.Default);
With this in place, I can read nullable dates correctly whether they have a NULL value or an actual value. I think this should be built-in functionality provided by Dapper, so I'm raising this ticket.
The stacktrace I get without my workaround looks like this:
MESSAGE:
System.Data.DataException : Error parsing column 14 (LastCommentedAt= - Object)
----> System.InvalidCastException : Cannot cast from source type to destination type.
+++++++++++++++++++
STACK TRACE:
at Dapper.SqlMapper.ThrowDataException (System.Exception,int,System.Data.IDataReader,object) <0x002e7>
at (wrapper dynamic-method) object.Deserializedf762666-acf8-4d08-832f-f40f4dc76cd7 (System.Data.IDataReader) <0x0105e>
at Dapper.SqlMapper/<QueryImpl>d__61`1<Person>.MoveNext () <0x006c3>
at System.Collections.Generic.List`1<Person>.AddEnumerable (System.Collections.Generic.IEnumerable`1<Person>) <0x0008f>
at System.Collections.Generic.List`1<Person>..ctor (System.Collections.Generic.IEnumerable`1<Person>) <0x000d5>
at System.Linq.Enumerable.ToList<Person> (System.Collections.Generic.IEnumerable`1<Person> <0x0004b>
at Dapper.SqlMapper.Query<Person> (System.Data.IDbConnection,string,object,System.Data.IDbTransaction,bool,System.Nullable`1<int>,System.Nullable`1<System.Data.CommandType>) <0x001fb>
at PersonDataLayer.Read (int) <0x0007f>
at PersonTests.TestCreate () <0x004ef>
--InvalidCastException
at System.Nullable`1<System.DateTime>.Unbox (object) <0x00081>
at (wrapper dynamic-method) object.Deserializedf762666-acf8-4d08-832f-f40f4dc76cd7 (System.Data.IDataReader) <0x00f3d>