使用自定义 Dapper ITypeHandler 解析数据，但不处理 SQL 参数

发布于 2025-01-19 04:48:56 字数 1528 浏览 1 评论 0原文

假设有一个存储过程sp_legacy无法更改。结果集除其他外部包含一个称为ids的列。该列以json的形式列出整数列表。带有三行的示例结果集可能看起来像这样：

Ids
'[1, 2, 3]'
'[2, 3, 4]'
'[-1, 0, 42]'

Dapper用于执行sp_legacy并将结果映射到类legacyrow：

public class LegacyRow
{
    public IEnumerable<int> { get; set; }
}

为了完成此类型处理程序：

public class JsonTypeHandler : SqlMapper.ITypeHandler
{
    public object Parse(Type destinationType, object value)
    {
        return JsonConvert.Deserialize((string)value, destinationType);
    }

    public void SetValue(IDbDataParameter parameter, object value)
    {
        throw new NotImplementedException();
    }
}

使用iEnumerable＆lt; int类型进行注册：

SqlMapper.AddTypeHandler(typeof(IEnumerable<int>), new JsonTypeHandler());

这使得可以做类似下面的事情。这是可取的，应该很容易查询。

var rows = await DbConnection.QueryAsync<LegacyRow>("sp_legacy", commandType: CommandType.StoredProcedure);

但是，这具有令人讨厌的意外副作用，即类型iEnumerable的SQL参数将由jsontypehandler来处理。例如，这将导致notimplempledexception被扔在jsontypehandler.setValue中：

IEnumerable<int> ids = ...
await Dapper.ExecuteAsync("select * from foo where Id in @Ids", new { Ids = ids }, CommandType.Text);

有哪些替代方法可以保持自动避免ids iDS columt不修改Ieneumerable的默认处理＆lt; int＆gt; sql参数吗？

原文

Assume there is a stored procedure sp_legacy that is not possible to change. The result set contains, among other things, a column called Ids. The column carries a list of integers as JSON. An example result set with three rows could look like this:

Ids
'[1, 2, 3]'
'[2, 3, 4]'
'[-1, 0, 42]'

Dapper is used to execute sp_legacy and map the result to the class LegacyRow:

public class LegacyRow
{
    public IEnumerable<int> { get; set; }
}

To accomplish this a type handler is used:

public class JsonTypeHandler : SqlMapper.ITypeHandler
{
    public object Parse(Type destinationType, object value)
    {
        return JsonConvert.Deserialize((string)value, destinationType);
    }

    public void SetValue(IDbDataParameter parameter, object value)
    {
        throw new NotImplementedException();
    }
}

Which is registered using the IEnumerable<int> type:

SqlMapper.AddTypeHandler(typeof(IEnumerable<int>), new JsonTypeHandler());

This makes it possible to do something like the below. This is desirable, it should be easy to query.

var rows = await DbConnection.QueryAsync<LegacyRow>("sp_legacy", commandType: CommandType.StoredProcedure);

However, this has the nasty unintended side effect that SQL parameters of type IEnumerable<int> will be handled by JsonTypeHandler. For example this will cause a NotImplementedException to be thrown in JsonTypeHandler.SetValue:

IEnumerable<int> ids = ...
await Dapper.ExecuteAsync("select * from foo where Id in @Ids", new { Ids = ids }, CommandType.Text);

What alternatives are there for keeping the automatic deserialization of the Ids column while not modifying the default handling of IEneumerable<int> SQL parameters?

分享到QQ

分享到微博