如何使用Dapper将JSON作为原始PostgreSQL类型传递给函数？ [英] How do I pass JSON as a primitive PostgreSQL type to a function using Dapper?

问题描述

我有一个PostgreSQL函数，它带有一个 json 类型的参数。使用Dapper，如何执行将对象传递给PostgreSQL函数的调用，以便PostgreSQL将类型识别为 json 而不是 text ？

I have a PostgreSQL function that takes in a parameter of type json. Using Dapper, how do I execute a call that passes the object to the PostgreSQL function such that PostgreSQL recognizes the type as json instead of as text?

CREATE OR REPLACE FUNCTION testfuncthattakesinjson(heroes json)
    RETURNS SETOF characters 
    LANGUAGE 'sql'

    STABLE
    ROWS 100
AS $BODY$

    SELECT  c.*
    FROM    characters c
    JOIN    json_array_elements(heroes) j
    ON      c.first_name = j->>'first_name'
    AND     c.last_name = j->>'last_name';

$BODY$;

破碎的示例C＃集成测试

Broken Example C# Integration Test

[Test]
public void Query_CallFunctionThatTakesInJsonParameter_FunctionUsesJsonType()
{
    using (var conn = new NpgsqlConnection(Db.GetConnectionStringToDatabase()))
    {
        var funcName = "testfuncthattakesinjson";
        var expect = CharacterTestData.Where(character => character.id <= 3);
        var jsonObj = JArray.FromObject(CharacterTestData
            .Where(character => character.id <= 3)
            .Select(character => new Hero(character))
            .ToList());

        SqlMapper.AddTypeHandler(new JArrayTypeHandler());

        // Act 
        var results = conn.Query<Character>(funcName, new
        {
            heroes = jsonObj
        }, commandType: CommandType.StoredProcedure);

        // Assert
        CollectionAssert.AreEquivalent(expect, results);
    }
}

支持JArrayTypeHandler

Supporting JArrayTypeHandler

internal class JArrayTypeHandler : SqlMapper.TypeHandler<JArray>
{
    public override JArray Parse(object value)
    {
        throw new NotImplementedException();
    }

    public override void SetValue(IDbDataParameter parameter, JArray value)
    {
        parameter.Value = value;
    }
}

在此当前迭代中，我添加了 SqlMapper.TypeHandler 。 （目前，我只关心将 JArray 传递给 PostgreSQL，因此 NotImplmentedException 表示解析。）

In this current iteration, I've added a SqlMapper.TypeHandler. (At the moment, I'm only concerned with passing the JArray to PostgreSQL, hence the NotImplmentedException for Parse.)

在此示例中，出现以下异常：

With this example, I get the following exception:

System.NotSupportedException ：'Npgsql或PostgreSQL不支持CLR数组类型Newtonsoft.Json.Linq.JArray。如果您希望将其映射到PostgreSQL复合类型数组，则需要在使用前进行注册，请参考文档。'

System.NotSupportedException: 'The CLR array type Newtonsoft.Json.Linq.JArray isn't supported by Npgsql or your PostgreSQL. If you wish to map it to an PostgreSQL composite type array you need to register it before usage, please refer to the documentation.'

在过去的迭代中，我还尝试过使用 List< Hero> 类型处理程序，并让该类型处理程序处理Json转换。

In past iterations, I've also tried things like using a List<Hero> type handler and letting that type handler deal with the Json conversion.

我还尝试为Npgsql添加 Npgsql.Json.NET Nuget软件包扩展，并调用 conn.TypeMapper。在我的测试方法中使用UseJsonNet（），但这似乎没有任何效果。

I've also tried adding the Npgsql.Json.NET Nuget package extension for Npgsql and call conn.TypeMapper.UseJsonNet() in my test method, but that didn't seem to have any effect.

如果我执行任何操作来序列化对象

And if I do anything to serialize the object to a JSON string, then I get a different error (below), which makes sense.

Npgsql.PostgresException >： 42883：函数testfuncthattakesinjson（heroes => text）不存在

Npgsql.PostgresException: '42883: function testfuncthattakesinjson(heroes => text) does not exist'

因此，可以使用Dapper将JSON对象作为PostgreSQL原语传递给函数？

推荐答案

您可以使用Dapper的ICustomQueryParameter接口。

You can use Dapper's ICustomQueryParameter interface.

public class JsonParameter : ICustomQueryParameter
{
    private readonly string _value;

    public JsonParameter(string value)
    {
        _value = value;
    }

    public void AddParameter(IDbCommand command, string name)
    {
        var parameter = new NpgsqlParameter(name, NpgsqlDbType.Json);
        parameter.Value = _value;

        command.Parameters.Add(parameter);
    }
}

然后，您的Dapper呼叫将变为：

Then your Dapper call becomes:

var results = conn.Query<Character>(funcName, new
    {
        heroes = new JsonParameter(jsonObj.ToString())
    }, commandType: CommandType.StoredProcedure);

这篇关于如何使用Dapper将JSON作为原始PostgreSQL类型传递给函数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！