作为 C# Npgsql 传递的存储过程输入的复合类型数组 [英] Array of composite type as stored procedure input passed by C# Npgsql

问题描述

我已经阅读了几个与该问题相关的问题和主题，但没有一个真正帮助我解决了我的问题，而且没有一个实际上与 C# 相关.这是实际问题:

我有一个 Postgre 复合类型:

CREATE TYPE law_relation_update_model AS (from_celex 字符变化，from_article 字符变化，to_celex 字符变化，to_article 字符变化，link_ids 整数[]，to_doc_par_id 整数);

我有一个存储过程，用于接受以下类型的数组:

创建或替换函数 insert_law_relations(_items law_relation_update_model[])返回无效作为$$开始结尾;$$语言PLPGSQL；

我删除了正文代码，因为它不相关，因为在 C# 的过程调用时抛出以下错误:

<块引用>

格式错误的数组字面量

还有一个内部信息:

<块引用>

数组值必须以{"或维度信息开头

即将被传递的C#模型(它的一个数组):

公共类 LawRelationUpdateModel{公共字符串 FromCelex { 获取；放;}公共字符串 FromArticle { 获取；放;}公共字符串 ToCelex { 获取；放;}公共字符串 ToArticle { 获取；放;}公共 IEnumerableLinkIds { 获取；放;} = new List();公共 int ToDocParId { 获取；放;}}

以及使用 Npgsql 连接器进行调用的方法:

public static void InsertLawRelations(LawRelationUpdateModel[] updateModel){使用 (NpgsqlConnection conn = new NpgsqlConnection(connPG)){连接.Open();NpgsqlCommand comm = new NpgsqlCommand("insert_law_relations", conn);comm.CommandType = CommandType.StoredProcedure;var testParam = new NpgsqlParameter();testParam.DbType = DbType.Object;testParam.Value = 更新模型；testParam.ParameterName = "_items";comm.Parameters.Add(testParam);comm.ExecuteNonQuery();}}

会不会是连接器中的错误导致在传递时无法正确转换某些单/双引号或括号?

或者与我将 DBType 指定为对象这一事实有关的东西?如果我不这样做，我会收到以下错误:

<块引用>

无法将 LawRelationUpdateModel[] 转换为任何有效的 DbType.

非常感谢有关该问题或其他解决方法的任何帮助！

解决方案

映射您的 CLR 类型

仍然有可能，并且在某些情况下是必要的，为您的 CLR 类型设置显式映射.这样做具有以下优势:

您知道不再需要在参数上指定 DataTypeName 属性.Npgsql 将根据您提供的 CLR 类型推断数据类型.

无类型读取方法，例如 NpgsqlDataReader.GetValue() 将返回您的 CLR 类型，而不是动态对象(见下文).一般来说，您应该使用类型化的 NpgsqlDataReader.GetFieldValue()，所以这不重要.

您可以根据类型自定义名称映射(见下文).要为所有连接设置全局映射，请在第一次打开之前输入以下代码:

 NpgsqlConnection.GlobalTypeMapper.MapEnum("some_enum");NpgsqlConnection.GlobalTypeMapper.MapComposite("some_composite");

<块引用>

这会在您的 CLR 类型 SomeEnum 和 SomeType 之间设置映射到PostgreSQL 类型 some_enum 和 some_composite.

如果您不想为所有连接设置映射，您可以只设置一个连接:

var conn = new NpgsqlConnection(...);conn.TypeMapper.MapEnum("some_enum");conn.TypeMapper.MapComposite("some_composite");

http://www.npgsql.org/doc/types/enums_and_composites.html

I've read several questions and topics related to the issue but none of them actually helped me to solve my problem and none of them actually were related to C#. And here is the actual problem:

I have a Postgre composite type:

CREATE TYPE law_relation_update_model AS (
   from_celex character varying,
   from_article character varying,
   to_celex character varying,
   to_article character varying,
   link_ids integer[],
   to_doc_par_id integer
);

And I have a stored procedure that is meant to accept an array of the following type:

CREATE OR REPLACE FUNCTION insert_law_relations(_items law_relation_update_model[])
RETURNS VOID
AS
$$
BEGIN

END;
$$
LANGUAGE PLPGSQL;

I've removed the body code since it's not relevant cause the following error is thrown at procedure invoke from C#:

malformed array literal

And an inner message of:

array value must start with "{" or dimension information

C# Model that is about to be passed (an array of it):

public class LawRelationUpdateModel
{
    public string FromCelex { get; set; }

    public string FromArticle { get; set; }

    public string ToCelex { get; set; }

    public string ToArticle { get; set; }

    public IEnumerable<int> LinkIds { get; set; } = new List<int>();

    public int ToDocParId { get; set; }
}

And the method that makes the call using Npgsql connector:

public static void InsertLawRelations(LawRelationUpdateModel[] updateModel)
{
    using (NpgsqlConnection conn = new NpgsqlConnection(connPG))
    {
        conn.Open();
        NpgsqlCommand comm = new NpgsqlCommand("insert_law_relations", conn);
        comm.CommandType = CommandType.StoredProcedure;
        var testParam = new NpgsqlParameter();
        testParam.DbType = DbType.Object;
        testParam.Value = updateModel;
        testParam.ParameterName = "_items";
        comm.Parameters.Add(testParam);

        comm.ExecuteNonQuery();
    }
}

Can it be a bug in the connector that somehow does not convert some single/double quotes or brackets correctly when passing?

Or something related to the fact that I asign DBType as an Object? If I don't I get the following error instead:

Can't cast LawRelationUpdateModel[] into any valid DbType.

Any help regarding the issue or other workarounds will be highly appreciated!

解决方案

Mapping your CLR Types

It is still possible, and in some cases necessary, to set up an explicit mapping for your CLR types. Doing so provides the following advantages:

You know longer need to specify the DataTypeName property on your parameter. Npgsql will infer the data type from your provided CLR type.

Untyped read methods such as NpgsqlDataReader.GetValue() will return your CLR type, instead of a dynamic object (see below). In general you should be using the typed NpgsqlDataReader.GetFieldValue(), so this shouldn't be important.

You can customize the name mapping on a per-type basis (see below). To set up a global mapping for all your connections, put this code before your first open:

 NpgsqlConnection.GlobalTypeMapper.MapEnum<SomeEnum>("some_enum");
 NpgsqlConnection.GlobalTypeMapper.MapComposite<SomeType>("some_composite");

This sets up a mapping between your CLR types SomeEnum and SomeType to the PostgreSQL types some_enum and some_composite.

If you don't want to set up a mapping for all your connections, you can set it up one connection only:

var conn = new NpgsqlConnection(...);
conn.TypeMapper.MapEnum<SomeEnum>("some_enum");
conn.TypeMapper.MapComposite<SomeType>("some_composite");

http://www.npgsql.org/doc/types/enums_and_composites.html

这篇关于作为 C# Npgsql 传递的存储过程输入的复合类型数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！