如何在不使用Dapper/EF的C#中向PostgreSQL存储过程传表值参数

首先明确：PostgreSQL没有SQL Server那样的原生表值参数，而是通过复合类型数组实现类似功能。以下是完整实现步骤：

1. 修正PostgreSQL端定义 #

1.1 先定义复合类型（匹配表结构） #

如果还未定义Teste.ListingTableType，先执行：

CREATE TYPE Teste.ListingTableType AS (
    ValuationDate DATE,
    WHSKU VARCHAR(100),
    PlatformCode VARCHAR(50),
    Price NUMERIC(10,2),
    MRP NUMERIC(10,2)
);

1.2 调整存储过程（支持批量数据） #

将参数改为复合类型数组，用unnest展开数组实现批量插入：

CREATE OR REPLACE PROCEDURE Test.UpsertListingData(
    p_data Teste.ListingTableType[]
)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO Test.Listing (ValuationDate, WHSKU, PlatformCode, Price, MRP)
    SELECT ValuationDate, WHSKU, PlatformCode, Price, MRP
    FROM unnest(p_data);
END;
$$;

2. C#端实现（两种方式） #

方式一：使用DataTable传递 #

适合已有DataTable数据源的场景：

using System.Data;
using Npgsql;

// 1. 构造与复合类型匹配的DataTable
var dataTable = new DataTable();
dataTable.Columns.Add("ValuationDate", typeof(DateTime));
dataTable.Columns.Add("WHSKU", typeof(string));
dataTable.Columns.Add("PlatformCode", typeof(string));
dataTable.Columns.Add("Price", typeof(decimal));
dataTable.Columns.Add("MRP", typeof(decimal));

// 添加数据行
dataTable.Rows.Add(new DateTime(2024, 5, 20), "SKU001", "AMZ", 99.99m, 129.99m);
dataTable.Rows.Add(new DateTime(2024, 5, 20), "SKU002", "EBY", 49.99m, 69.99m);

// 2. 建立连接并执行存储过程
await using var connection = new NpgsqlConnection("你的PostgreSQL连接字符串");
await connection.OpenAsync();

await using var command = new NpgsqlCommand("CALL Test.UpsertListingData(@p_data)", connection);

// 3. 设置表值参数
var parameter = new NpgsqlParameter
{
    ParameterName = "p_data",
    NpgsqlDbType = NpgsqlDbType.Composite,
    CompositeTypeName = "Teste.ListingTableType", // 必须与PG端复合类型完全一致（含schema）
    Value = dataTable,
    Direction = System.Data.ParameterDirection.Input
};

command.Parameters.Add(parameter);

// 4. 执行命令
await command.ExecuteNonQueryAsync();

方式二：使用自定义类的List传递 #

适合面向对象的业务场景：

步骤1：定义匹配的C#类

public class ListingData
{
    public DateTime ValuationDate { get; set; }
    public string WHSKU { get; set; }
    public string PlatformCode { get; set; }
    public decimal Price { get; set; }
    public decimal MRP { get; set; }
}

步骤2：注册复合类型映射（全局仅需一次）

在应用启动时（如Program.cs）注册：

NpgsqlConnection.GlobalTypeMapper.MapComposite<ListingData>("Teste.ListingTableType");

步骤3：调用存储过程

using Npgsql;

// 1. 构造数据列表
var listingList = new List<ListingData>
{
    new ListingData { ValuationDate = new DateTime(2024,5,20), WHSKU = "SKU001", PlatformCode = "AMZ", Price = 99.99m, MRP = 129.99m },
    new ListingData { ValuationDate = new DateTime(2024,5,20), WHSKU = "SKU002", PlatformCode = "EBY", Price = 49.99m, MRP = 69.99m }
};

// 2. 建立连接并执行
await using var connection = new NpgsqlConnection("你的PostgreSQL连接字符串");
await connection.OpenAsync();

await using var command = new NpgsqlCommand("CALL Test.UpsertListingData(@p_data)", connection);

// 3. 设置参数
var parameter = new NpgsqlParameter
{
    ParameterName = "p_data",
    NpgsqlDbType = NpgsqlDbType.Composite | NpgsqlDbType.Array,
    Value = listingList.ToArray(),
    Direction = System.Data.ParameterDirection.Input
};

command.Parameters.Add(parameter);

await command.ExecuteNonQueryAsync();

原有代码问题说明 #

1. 存储过程调用语法错误：PostgreSQL调用存储过程必须用CALL，而非SELECT。
2. 参数类型不匹配：仅指定NpgsqlDbType.Array但未关联复合类型信息，Npgsql无法识别数据结构。
3. 存储过程参数定义问题：原参数只能接收单条复合类型数据，改为数组才能批量传递多条数据。

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