使用Dapper和Postgresql-citext数据类型 [英] Using Dapper and Postgresql - citext data type

问题描述

我不确定是否有方法可以支持此功能，但是由于Dapper似乎正在使用文本类型，因此我无法让Dapper将字符串参数值映射到Postgresql citext数据类型.

I'm not sure if there is a way to support this, but I'm having trouble getting Dapper to map string parameter values to the Postgresql citext data type as it seems to be using the text type.

特别是，我试图调用一个带有citext参数的函数-我得到的错误是:

In particular, I'm trying to call a function that takes in citext parameters - the error I get back is:

var c = ConnectionManager<T>.Open();
string sql = @"select * from ""dbo"".""MyFunction""(@schemaName, @tableName);";
var param = new
{
    schemaName = schema,
    tableName = table
};

string insecureSalt = c.QueryMultiple(sql, param).Read<string>().FirstOrDefault();
ConnectionManager<T>.Close(c);

Error: Npgsql.PostgresException: 42883: function dbo.MyFunction(text, text) does not exist.

将匹配的签名是函数dbo.MyFunction(citext，citext)，因此很明显它无法使用默认映射找到它.

The signature that would match is function dbo.MyFunction(citext, citext) so clearly it can't find it using the default mapping.

根据Npgsql- http://www.npgsql.org/doc/types.html 我需要能够将NpgsqlDbType.Citext指定为类型，但是我找不到使用Dapper进行此操作的方法.

According to Npgsql - http://www.npgsql.org/doc/types.html I need to be able to specify NpgsqlDbType.Citext as the type but I can't find a way to do this using Dapper.

已解决，感谢Shay的回答，此处提供了完整的解决方案:

Solved thanks to answer from Shay, complete solution here:

var c = ConnectionManager<T>.Open();
string sql = @"select * from ""dbo"".""MyFunction""(@schemaName, @tableName);";
var param = new
{
    schemaName = new CitextParameter(schema),
    tableName = new CitextParameter(table)
};

string insecureSalt = c.QueryMultiple(sql, param).Read<string>().FirstOrDefault();
ConnectionManager<T>.Close(c);

public class CitextParameter : SqlMapper.ICustomQueryParameter
{
    readonly string _value;

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

    public void AddParameter(IDbCommand command, string name)
    {
        command.Parameters.Add(new NpgsqlParameter
        {
            ParameterName = name,
            NpgsqlDbType = NpgsqlDbType.Citext,
            Value = _value
        });
    }
}

推荐答案

您可能需要创建一个扩展ICustomQueryParameter的CitextParameter.该API允许您将任意DbParameter实例传递给Dapper-在这种情况下，它将是NpgsqlParameter的实例，其NpgsqlDbType设置为Citext.

You probably need to create create a CitextParameter which extends ICustomQueryParameter. This API allows you to pass an arbitrary DbParameter instance to Dapper - in this case it would be an instance of NpgsqlParameter with its NpgsqlDbType set to Citext.

类似的事情应该起作用:

Something like this should work:

class CitextParameter : SqlMapper.ICustomQueryParameter
{
    readonly string _value;

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

    public void AddParameter(IDbCommand command, string name)
    {
        command.Parameters.Add(new NpgsqlParameter
        {
            ParameterName = name,
            NpgsqlDbType = NpgsqlDbType.Citext,
            Value = _value
        });
    }
}

这篇关于使用Dapper和Postgresql-citext数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！