C#如何利用Npgsql调用以复合类型或表为参数的Function或Procedure

由于Postgresql中不像MSSQL可以有Table类型作为参数，因此自定义的类型可以是类似为一行记录的复合类型，而不能是表格。如果要用表格推荐考虑json数据类型（json的数组也是json类型），再在Functon或Procedure中用 “CREATE TEMP TABLE tmpTasks AS  select * from json_to_recordset(jsonParameter) t ("id" text, "no" text)转换成临时表； 如果要用复合类型，可以采用复合类型数组的方式（参考Npgsql官网对复合类型调用的说明：https://www.npgsql.org/doc/types/enums_and_composites.html ），以下说明各自的调用方法：

                1. Json作参数的Function:

                NpgsqlParameter parameter = new NpgsqlParameter("@tasks", NpgsqlTypes.NpgsqlDbType.Json);
                parameter.Value = JArray.FromObject(dt).ToString();
                paramList.Add(parameter);
                DBMate.DefaultDB.ExecuteNonQuery("select \"InsertPMTasksByArray\"(@tasks)", CommandType.Text, paramList.ToArray());

                2. 复合（Composites）类型的数组作参数的Function:

                NpgsqlParameter parameter = new NpgsqlParameter();
                parameter.ParameterName = "@tasks";
                //parameter.DataTypeName = "PM_InsertTasksType";
                parameter.DataTypeName = "PM_InsertTasksType[]";
                NpgsqlConnection.GlobalTypeMapper.MapComposite<TestTask>("PM_InsertTasksType", new NpgsqlNullNameTranslator()); //The default translation scheme is NpgsqlSnakeCaseNameTranslator 
                                                                                                                                //TestTask taskValue = JArray.FromObject(dt)[0].ToObject<TestTask>();
                                                                                                                                //parameter.Value = taskValue;
                TestTask[] taskValues = JArray.FromObject(dt).ToObject<TestTask[]>();
                parameter.Value = taskValues;
                paramList.Add(parameter);
                //DBMate.DefaultDB.ExecuteNonQuery("select \"InsertPMTasks\"(@tasks)", CommandType.Text, paramList.ToArray());
                DBMate.DefaultDB.ExecuteNonQuery("select \"InsertPMTasks2\"(@tasks)", CommandType.Text, paramList.ToArray());

//以下是相应TestTask类型定义，用于匹配Postgresql中的复合类型，注意当字段以驼峰命名又不想被默认转换时需要以NpgsqlNullNameTranslator选项以及用PgName特性做Mapping，（详见https://www.npgsql.org/doc/types/enums_and_composites.html说明）
public class TestTask
{
        [PgName("ID")]
        public string ID { get; set; }
        [PgName("No")]
        public string No { get; set; }
}

 

                3.动态（Dynamically）类型的数组作参数的函数（注意Dynamic与DB中复合类型的匹配是采用默认的NpgsqlSnakeCaseNameTranslator字段命名转换规则）：           

                NpgsqlParameter parameter = new NpgsqlParameter();
                parameter.ParameterName = "@tasks";
                parameter.DataTypeName = "dynamic_insert_tasks_type[]";
                List<dynamic> expandList = new List<dynamic>();
                expandList = GetExpandDataFromJArray(JArray.FromObject(dt));
                parameter.Value = expandList;
                paramList.Add(parameter);
                //DBMate.DefaultDB.ExecuteNonQuery("select \"InsertPMTasks\"(@tasks)", CommandType.Text, paramList.ToArray());
                DBMate.DefaultDB.ExecuteNonQuery("select \"InsertPMTasksByObjects\"(@tasks)", CommandType.Text, paramList.ToArray());

              【 “Json作参数的存储过程”和“复合（Composites）类型的数组作参数的存储过程”参考相应的函数以及“4. 动态（Dynamically）类型的数组作参数的存储过程”调用。】

                4. 动态（Dynamically）类型的数组作参数的存储过程（注意Dynamic与DB中复合类型的匹配是采用默认的NpgsqlSnakeCaseNameTranslator字段命名转换规则）：

                NpgsqlParameter parameter = new NpgsqlParameter();
                parameter.ParameterName = "@tasks";
                parameter.DataTypeName = "dynamic_insert_tasks_type[]";
                List<dynamic> expandList = new List<dynamic>();
                expandList = GetExpandDataFromJArray(JArray.FromObject(dt));
                parameter.Value = expandList.ToArray();
                paramList.Add(parameter);
                DBMate.DefaultDB.ExecuteNonQuery("call \"SP_InsertPMTasksByObjects\"(@tasks)", CommandType.Text, paramList.ToArray());      

                //以下是DB中的测试Procedure：
                CREATE OR REPLACE PROCEDURE public."SP_InsertPMTasksByObjects"(
                    tasks dynamic_insert_tasks_type[])
                LANGUAGE 'plpgsql'
                AS $BODY$
                begin
                    ...................
                    raise notice '%',  tasks[1]."no";    //默认Postgresql的Array元素序号从1开始，非0
                end;
                $BODY$; 

例子3、4中根据JArray获取Dynamic对象参数值： 

        //GetExpandDataFromJArray：根据JArray获取Dynamic对象参数值
        private List<dynamic> GetExpandDataFromJArray(JArray jArray)
        {
            List<dynamic> listResult = new List<dynamic>();
            foreach (JToken j in jArray)
            {
                dynamic expObj = new ExpandoObject();
                expObj.id = j.Value<string>("ID");
                expObj.no = j.Value<string>("No");
                listResult.Add(expObj);
            }
            return listResult;
        }

     （说明：Dynamic与DB中复合类型的匹配是采用默认的NpgsqlSnakeCaseNameTranslator字段命名转换规则，因此这里为了省事在DB中另外定义了字段名是小写的复合类型）

                 
以下是DBMate.DefaultDB.ExecuteNonQuery的方法：

        public int ExecuteNonQuery(string sqlTextOrstoredProcedureName, CommandType commandType = CommandType.Text, params object[] parameterValues)
        {
            int effectedCount = 0;
            if (commandType == CommandType.Text)
            {
                NpgsqlCommand cmd = new NpgsqlCommand(sqlTextOrstoredProcedureName);
                cmd.CommandType = commandType;
                cmd.Parameters.AddRange(parameterValues);

                effectedCount = _DB.ExecuteNonQuery(cmd);  //ExecuteNonQuery中成功执行PostgreSQL的Function、或Procedrue（不管最终是否更新影响数据）都是返回-1
            }
            else
            {

                //注意目前Npgsql中对procedure的调用只能是SQL语句“call procName(@parameter...)”调用，因此是用CommandType.Text的CommandType，不会跑到这里
                effectedCount = _DB.ExecuteNonQuery(sqlTextOrstoredProcedureName, parameterValues); 
            }

            return effectedCount;
        }

对于采用复合类型的数组，如果在Function或Procedure中需要转换为表格，可以利用unnest得到表格，参考以下例子的用法：

CREATE TYPE public.testtype AS
(
    id character varying(50),
    no character varying(50)
);

select
    id,no
from 
    unnest(array[ ('id1','no1')::testtype,('id2','no2')::testtype]) t1;

结果如下：

对于为json的数组，可以利用unnest函数转换为只有一个json数据列的表格，再用json的元素访问方法来达到操作表字段的目的，例如：

select
    (t1->'id')::text as "ID",t1->'name' as "Name" ,*
from 
    unnest(array[json '{"id":1,"name":2}',json '{"id":4,"name":5}']) t1
    inner join (select 1 as id , 3 as grade) t2 on (t1->'id')::text::int=t2.id

结果如下：