使用Dapper获取DateTime为UTC [英] Get DateTime as UTC with Dapper

问题描述

我正在使用Dapper将我的实体映射到SQL Server CE.如果我用Kind=Utc保存DateTime，当我读回它时，会得到DateTime和Kind=Unspecified，这会导致各种问题.

I'm using Dapper to map my entities to SQL Server CE. If I save a DateTime with Kind=Utc, when I read it back I get a DateTime with Kind=Unspecified, which leads to all kind of problems.

示例:

var f = new Foo { Id = 42, ModificationDate = DateTime.UtcNow };
Console.WriteLine("{0} ({1})", f.ModificationDate, f.ModificationDate.Kind);
connection.Execute("insert into Foo(Id, ModificationDate) values(@Id, @ModificationDate)", f);
var f2 = connection.Query<Foo>("select * from Foo where Id = @Id", f).Single();
Console.WriteLine("{0} ({1})", f2.ModificationDate, f2.ModificationDate.Kind);

此代码提供以下输出:

20/09/2012 10:04:16 (Utc)
20/09/2012 10:04:16 (Unspecified)

我知道我应该使用DateTimeOffset，但是不幸的是SQL CE不支持这种类型.

I know I should be using a DateTimeOffset, but unfortunately SQL CE has no support for this type.

有解决方法吗?我可以告诉Dapper假设所有日期都有DateTimeKind.Utc吗?更一般而言，我有哪些自定义映射的选项?

Is there a workaround? Can I tell Dapper to assume that all dates have DateTimeKind.Utc? And more generally, what are my options to customize the mapping?

我当前的解决方法是在Dapper实现结果后修补日期，但是有点味道...

My current workaround is to patch the dates after Dapper has materialized the result, but it kind of smells...

var results = _connection.Query<Foo>(sql, param).Select(PatchDate);

...

static Foo PatchDate(Foo f)
{
    if (f.ModificationDate.Kind == DateTimeKind.Unspecified)
        f.ModificationDate = DateTime.SpecifyKind(f.ModificationDate, DateTimeKind.Utc);
    return f;
}

推荐答案

为寻求简单解决方案的其他任何人添加此答案.现在可以通过在Dapper中添加SqlMapper.TypeHandler来实现.

Adding this answer for anyone else who comes looking for a simple fix. This is possible now with the addition of SqlMapper.TypeHandler in Dapper.

添加此类可将值从db转换为指定为UTC的日期时间.

Add this class to convert the value from the db to a datetime with the kind specified as UTC.

public class DateTimeHandler : SqlMapper.TypeHandler<DateTime>
{
    public override void SetValue(IDbDataParameter parameter, DateTime value)
    {
        parameter.Value = value;
    }

    public override DateTime Parse(object value)
    {
        return DateTime.SpecifyKind((DateTime)value, DateTimeKind.Utc);
    }
}

然后在Web API的Global.asax文件中，将类型处理程序添加到dapper.

Then in my Global.asax file of my Web API I add the type handler to dapper.

SqlMapper.AddTypeHandler(new DateTimeHandler());

如果需要确保始终以UTC插入日期，则可以在SetValue方法上使用:

If you need to ensure you are always inserting dates as UTC, then on the SetValue method you can use:

parameter.Value = DateTime.SpecifyKind(value, DateTimeKind.Utc);

这篇关于使用Dapper获取DateTime为UTC的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！