Dapper And System.Data.OleDb DbType.Date throwing 'OleDbException : Data type mismatch in criteria expression'

Not sure if I should raise an issue regarding this, so thought I would ask if anybody knew a simple workaround for this first. I am getting an error when I try to use Dapper with OleDbConnection when used in combination with MS Access 2003 (Jet.4.0) (not my choice of database!)

When running the test code below I get an exception 'OleDbException : Data type mismatch in criteria expression'

var count = 0;

using (var conn = new OleDbConnection(connString)) {

    conn.Open();
    var qry = conn.Query<TestTable>("select * from testtable where CreatedOn <= @CreatedOn;", new { CreatedOn = DateTime.Now });
    count = qry.Count();
}

I believe from experience in the past with OleDb dates, is that when setting the DbType to Date, it then changes internally the value for OleDbType property to OleDbTimeStamp instead of O开发者_运维知识库leDbType.Date. I understand this is not because of Dapper, but what 'could' be considered a strange way of linking internally in the OleDbParameter class

When dealing with this either using other ORMs, raw ADO or my own factory objects, I would clean up the command object just prior to running the command and change the OleDbType to Date.

This is not possible with Dapper as far as I can see as the command object appears to be internal. Unfortunately I have not had time to learn the dynamic generation stuff, so I could be missing something simple or I might suggest a fix and contribute rather than simply raise an issue.

Any thoughts?

Lee

---

It's an old thread but I had the same problem: Access doesn't like DateTime with milliseconds, so you have to add and extension method like this :

public static DateTime Floor(this DateTime date, TimeSpan span)
{
    long ticks = date.Ticks / span.Ticks;
    return new DateTime(ticks * span.Ticks, date.Kind);
}

And use it when passing parameters:

var qry = conn.Query<TestTable>("select * from testtable where CreatedOn <= @CreatedOn;", new { CreatedOn = DateTime.Now.Floor(TimeSpan.FromSeconds(1)) });

Unfortunately, with current Dapper version (1.42), we cannot add custom TypeHandler for base types (see #206).

If you can modify Dapper (use the cs file and not the DLL) merge this pull request and then you do not have to use Floor on each parameters :

public class DateTimeTypeHandler : SqlMapper.TypeHandler<DateTime>
{
    public override DateTime Parse(object value)
    {
        if (value == null || value is DBNull) 
        { 
            return default(DateTime); 
        }
        return (DateTime)value;
    }

    public override void SetValue(IDbDataParameter parameter, DateTime value)
    {
        parameter.DbType = DbType.DateTime;
        parameter.Value = value.Floor(TimeSpan.FromSeconds(1));
    }
}

SqlMapper.AddTypeHandler<DateTime>(new DateTimeTypeHandler());