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

Question

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 OleDbType.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

@Hans it works fine with sqlite, sqlce, oracle, firebird, postgres etc.. I see no reason it should not work with access - unless access does something weird with base .net interfaces — Sam Saffron
Thanks for the feedback, as I say its not a problem with Dapper, its the unexpected problem arising from a combination of OleDbParameters matching DbType.DateTime to OldDbType.DBTimestamp rather than OleDbType.Date and then Access asking for it to be strictly correct. But because Dapper controls all the linking internally I cant override the Parameter prior to give it the correct OleDbType prior to execution. I do have a solution for this outside of Dapper but I was hoping that it might be a simple fix using Dapper, Thanks again though :) — Lski
Lee yeah this is going to be problematic, we have no hooks for you. The big issue is that internally dapper works with IDbCommand and not the ole specific one, so you don't even have the ability to set OleDbTimeStamp on the command you would need to hard cast it. — Sam Saffron
@Sam, that is what I thought, I ended up doing a re-write of PetaPoco to give MSAccess and Sql 2000 support. Thankfully I will not have to worry about MSAccess for any of the other projects I need to do for the company, so I can revert to using Dapper with those projects. Thank you again for the feedback though and for making Dapper open source :) — Lski

PlageMan PlageMan · Accepted Answer · 2015-08-27T08:50:16

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());

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

1 Answers