由于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结果如下: