Dapper和System.Data.OleDb DbType.Date抛出"OleDbException:条件表达式中的数据类型不匹配" [英] Dapper And System.Data.OleDb DbType.Date throwing 'OleDbException : Data type mismatch in criteria expression'

问题描述

不确定是否应该对此提出问题，因此以为我会先问是否有人知道一个简单的解决方法.当我尝试与MS Access 2003(Jet.4.0)结合使用Dapper和OleDbConnection时出现错误(不是我选择的数据库！)

在下面运行测试代码时，出现异常'OleDbException:条件表达式中的数据类型不匹配'

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

根据过去使用OleDb日期的经验，我认为将DbType设置为Date时，会在内部将OleDbType属性的值更改为OleDbTimeStamp而不是OleDbType.Date.我了解这不是因为Dapper造成的，而是OleDbParameter类内部可能被认为是一种奇怪的内部链接方式

在使用其他ORM，原始ADO或我自己的工厂对象处理此问题时，我将在运行命令之前清理命令对象，并将OleDbType更改为Date.

就Dapper而言，这是不可能的，因为命令对象似乎是内部的.不幸的是，我还没有时间学习动态生成的东西，所以我可能会错过一些简单的东西，或者我可能会提出解决方案并做出贡献，而不是简单地提出问题.

有什么想法吗?

李

解决方案

这是一个旧线程，但是我遇到了相同的问题:Access不喜欢毫秒级的DateTime，因此您必须添加和扩展方法，如下所示:

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

并在传递参数时使用它:

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

不幸的是，对于当前的Dapper版本(1.42)，我们无法为基本类型添加自定义TypeHandler(请参见#206 ).

如果您可以修改Dapper(使用cs文件而不是DLL)，请合并 pull request ，然后您不必在每个参数上使用Floor:

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

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

解决方案

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和System.Data.OleDb DbType.Date抛出"OleDbException:条件表达式中的数据类型不匹配"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！