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, Aug 04 '11 at 00:00
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, Aug 04 '11 at 08:35
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, Aug 19 '11 at 06:08
@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, Aug 19 '11 at 10:15

score 5 · Accepted Answer · answered Aug 27 '15 at 08:50

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

I dont think at the time I realised that milliseconds was the issue, but thought it was simply a limitation of the driver, but with how Access deals with bool Im not shocked lol. The initial Floor option you give is good, with the obvious drawback of being used everywhere, but is really simple to use. The second option is what I more or less went with (but using the PetaPoco ORM changing the DataType handler in the source). If you created your own fork of Dapper and you keep an upstream remote in your fork, you could just keep it up to date with changes in the original too :) — Lski, Aug 28 '15 at 08:35
I couldn't believe it, but sure enough, remove the ms and it's all good. Spent hours on this. Thanks @PlageMan — RichieRich, Jun 29 '16 at 03:21

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

1 Answers1

Linked