如何将DataTable作为参数传入PostgreSQL函数并实现批量插入？

你当前的C#代码存在命令未执行的问题，补充调整后代码如下：

public void InsertDataInBulk(DataTable dataTable, string functionName ,ILogger logger) 
{
    try
    {
        var connection = _dbConnect.OpenConnection(logger);
        using (DbCommand command = connection.CreateCommand())
        {
            command.CommandText = functionName; // 这里不要写死f_testbulkinsert，用传入的参数
            command.CommandType = CommandType.StoredProcedure;
            if (dataTable != null && dataTable.Rows.Count > 0)
            {
                var parameter = command.CreateParameter();
                parameter.ParameterName = "testtype";
                // 如果你用的是Npgsql驱动，建议指定参数类型为Jsonb，性能更优
                // parameter.DbType = DbType.Object;
                // ((NpgsqlParameter)parameter).NpgsqlDbType = NpgsqlDbType.Jsonb;
                parameter.Value = JsonConvert.SerializeObject(dataTable);
                // 不需要Formatting.Indented，格式化的空白字符无意义还增加传输体积
                command.Parameters.Add(parameter);
            }
            // 补充执行命令的逻辑
            command.ExecuteNonQuery();
        }
        _dbConnect.CloseConnection(connection, logger);
    }
    catch (Exception ex)
    {
        logger.LogError(ex, ex.Message);
        throw;
    }
}

建议将入参类型从varchar改为jsonb，相比字符串类型支持原生JSON操作，性能更高。以下是完整的函数实现示例，假设你需要插入的目标表为public.test_table，结构如下：

CREATE TABLE public.test_table (
    id int4 PRIMARY KEY,
    user_name varchar(50) NOT NULL,
    age int4 NULL,
    create_time timestamp NOT NULL
);

对应的批量插入函数如下：

CREATE OR REPLACE FUNCTION public.f_testbulkinsert(
    testtype jsonb)
    RETURNS void
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
BEGIN
  -- 将JSON数组转为结构化表数据，直接批量插入目标表
  INSERT INTO public.test_table(id, user_name, age, create_time)
  SELECT 
    t.id,
    t.user_name,
    t.age,
    t.create_time
  FROM jsonb_to_recordset(testtype) AS t(
    id int, 
    user_name varchar, 
    age int, 
    create_time timestamp
  );
  
  -- 如果需要处理主键冲突，比如冲突时更新字段，可以加ON CONFLICT逻辑，示例：
  -- ON CONFLICT (id) DO UPDATE SET 
  -- user_name = EXCLUDED.user_name,
  -- age = EXCLUDED.age;
END;
$BODY$;

注意：jsonb_to_recordset后面定义的临时表列名、列类型，必须和你C#中DataTable的列名、数据类型一一对应，否则会出现解析错误。如果你的DataTable列名和目标表列名不一致，在SELECT部分做别名映射即可。

如果你的数据量特别大（单批超过10万行），JSON序列化+解析的方案性能会有损耗，建议用PostgreSQL原生的COPY命令实现批量插入，性能比存储过程方案高3-5倍。

内容的提问来源于stack exchange，提问作者Simran Kaur