如何用C#向PostgreSQL存储过程传递List<object>或DataTable？

一、修复带IF逻辑的SQL语法错误 #

PostgreSQL的普通SQL语句不支持IF...ELSE流程控制，必须用PL/pgSQL块包裹（PostgreSQL的过程化语言）。修改后的执行代码如下：

connection.Execute(
    $@"DO $$
    BEGIN
        IF EXISTS (SELECT 1 FROM Amazon.ProductMaster WHERE PlatformCode = @ASIN AND ChannelId = @ChannelId AND IsActive = true) THEN
            INSERT INTO Amazon.Inventory (
                ValuationDate,
                PlatformProductId,
                Quantity
            ) VALUES (
                @ValuationDate,
                (SELECT Id FROM Amazon.ProductMaster WHERE PlatformCode = @ASIN AND ChannelId = @ChannelId AND IsActive = true),
                @Quantity
            ) ON CONFLICT (ValuationDate, PlatformProductId) DO UPDATE SET
                Quantity = excluded.Quantity;
        ELSE
            INSERT INTO BSC.MissingData (ValuationDate, ProcessId, MissingValue)
            VALUES (
                @ValuationDate,
                (SELECT Id FROM BSC.ProcessList WHERE Name = 'Inventory' AND ChannelId = @ChannelId),
                @ASIN
            );
        END IF;
    END $$;",
    inventoryData,
    transaction
);

更简洁的函数封装方式 #

可以把这段逻辑封装成PostgreSQL函数，后续调用更高效：

CREATE OR REPLACE FUNCTION upsert_inventory_or_log_missing(
    p_asin VARCHAR,
    p_channelId INT,
    p_valuationDate DATE,
    p_quantity INT
) RETURNS VOID AS $$
BEGIN
    IF EXISTS (SELECT 1 FROM Amazon.ProductMaster WHERE PlatformCode = p_asin AND ChannelId = p_channelId AND IsActive = true) THEN
        INSERT INTO Amazon.Inventory (ValuationDate, PlatformProductId, Quantity)
        VALUES (p_valuationDate, 
                (SELECT Id FROM Amazon.ProductMaster WHERE PlatformCode = p_asin AND ChannelId = p_channelId AND IsActive = true),
                p_quantity)
        ON CONFLICT (ValuationDate, PlatformProductId) DO UPDATE SET Quantity = excluded.Quantity;
    ELSE
        INSERT INTO BSC.MissingData (ValuationDate, ProcessId, MissingValue)
        VALUES (p_valuationDate,
                (SELECT Id FROM BSC.ProcessList WHERE Name = 'Inventory' AND ChannelId = p_channelId),
                p_asin);
    END IF;
END $$ LANGUAGE plpgsql;

C#调用函数代码：

connection.Execute(
    "SELECT upsert_inventory_or_log_missing(@ASIN, @ChannelId, @ValuationDate, @Quantity);",
    inventoryData,
    transaction
);

二、列表/数组/DataTable批量操作的标准方式 #

PostgreSQL不支持SQL Server的DataTable参数，但可通过以下两种核心方式实现批量处理：

1. Dapper数组参数 + PostgreSQL unnest()函数 #

Dapper原生支持向PostgreSQL传递数组参数，结合unnest()将数组拆分为行，实现批量插入/更新：

假设你的销售数据实体类为：

public class SalesData
{
    public string ASIN { get; set; }
    public DateTime ValuationDate { get; set; }
    public decimal ShippedRevenue { get; set; }
    public int ShippedUnits { get; set; }
}

批量操作代码：

var salesList = new List<SalesData>();
// 填充salesList数据

connection.Execute(
    @"INSERT INTO amazon.sales (platformproductid, valuationdate, shippedrevenue, shippedunits)
      SELECT 
          (SELECT Id FROM Test.TableName Where Platformcode = s.asin and ChannelId = @ChannelId and IsActive = true),
          s.valuationdate,
          s.shippedrevenue,
          s.shippedunits
      FROM unnest(@SalesArray) s(asin text, valuationdate date, shippedrevenue numeric, shippedunits int)
      ON CONFLICT (platformproductid, valuationdate) DO UPDATE
          SET shippedrevenue = EXCLUDED.shippedrevenue,
              shippedunits = EXCLUDED.shippedunits;",
    new { 
        ChannelId = channelId,
        SalesArray = salesList.Select(s => new { s.ASIN, s.ValuationDate, s.ShippedRevenue, s.ShippedUnits }).ToArray()
    },
    transaction
);

2. 自定义复合类型 + 数组传递 #

如果频繁批量传递同结构数据，可先在PostgreSQL定义复合类型：

CREATE TYPE sales_type AS (
    asin text,
    valuationdate date,
    shippedrevenue numeric,
    shippedunits int
);

C#中直接传递实体数组即可：

connection.Execute(
    @"INSERT INTO amazon.sales (platformproductid, valuationdate, shippedrevenue, shippedunits)
      SELECT 
          (SELECT Id FROM Test.TableName Where Platformcode = (s.asin) and ChannelId = @ChannelId and IsActive = true),
          s.valuationdate,
          s.shippedrevenue,
          s.shippedunits
      FROM unnest(@SalesArray) s
      ON CONFLICT (platformproductid, valuationdate) DO UPDATE
          SET shippedrevenue = EXCLUDED.shippedrevenue,
              shippedunits = EXCLUDED.shippedunits;",
    new { 
        ChannelId = channelId,
        SalesArray = salesList.ToArray()
    },
    transaction
);

三、关键注意事项 #

• 禁止字符串拼接参数：你原代码中用{channelId}直接拼接SQL存在注入风险，必须改为@ChannelId参数化传递。
• 事务原子性：保持现有事务提交/回滚逻辑，确保批量操作要么全部成功，要么全部回滚。
• 性能优化：用unnest()处理数组的方式，比循环执行单条SQL效率高一个数量级，适合大数据量批量操作。

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