C#向PostgreSQL的DO块传递参数报错的解决方法

一、DO块传参失败的根本原因及修复 #

PostgreSQL的DO匿名块不支持直接接收外部传入的参数，不管你用命名参数:USER_ID还是位置参数$1都不行，这就是你碰到语法错误和参数找不到错误的核心原因。要让DO块用上外部参数，得在块内部声明变量，再把外部参数传递进去，同时要注意避免SQL注入风险。

修复后的DO块代码 #

if (oUSERDTO.EVENT_ACTION == (int)Enums.EventAction.Create && result.Equals("true"))
{
    result = "false";
    using (DBConnector oCloudConnection = new DBConnector(connectionValues[0], Convert.ToInt32(connectionValues[1])))
    {
        string sql = @"
DO $$
DECLARE
    p_rec_id INT := $1;
    p_user_id VARCHAR := $2; -- 这里根据你的实际字段类型调整，比如INT
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM SUSER WHERE USER_ID = p_user_id
    ) THEN
        INSERT INTO SUSER (USER_ID, USER_NAME, USER_DESCRIPTION)
        SELECT USER_ID, USER_NAME, USER_DESCRIPTION
        FROM SUSERTEMP
        WHERE USER_ID = p_user_id AND REC_ID = p_rec_id;
    END IF;
END $$;";

        oCloudConnection.CommandText = sql;
        oCloudConnection.Parameters.Clear();
        // 按$1、$2的顺序添加参数，部分数据库驱动可能不需要写$，直接按顺序加即可
        oCloudConnection.Parameters.Add(new Parameter { Name = "$1", Value = oUSERDTO.REC_ID });
        oCloudConnection.Parameters.Add(new Parameter { Name = "$2", Value = oUSERDTO.USER_ID });                        
        if (oCloudConnection.ExecuteQuery() > 0)
        {
            result = "true";
        }
    }
}

提示：如果你的DBConnector是基于Npgsql开发的，添加参数时不需要写$，直接按顺序添加，驱动会自动映射到$1、$2。

二、更简单的无DO块实现方案 #

其实你的业务逻辑完全可以用单条INSERT语句实现，根本不需要DO块，这样既绕开了参数传递的坑，又更高效：

if (oUSERDTO.EVENT_ACTION == (int)Enums.EventAction.Create && result.Equals("true"))
{
    result = "false";
    using (DBConnector oCloudConnection = new DBConnector(connectionValues[0], Convert.ToInt32(connectionValues[1])))
    {
        string sql = @"
INSERT INTO SUSER (USER_ID, USER_NAME, USER_DESCRIPTION)
SELECT st.USER_ID, st.USER_NAME, st.USER_DESCRIPTION
FROM SUSERTEMP st
WHERE st.USER_ID = :USER_ID 
  AND st.REC_ID = :REC_ID
  AND NOT EXISTS (
      SELECT 1 FROM SUSER su WHERE su.USER_ID = st.USER_ID
  );";

        oCloudConnection.CommandText = sql;
        oCloudConnection.Parameters.Clear();
        oCloudConnection.Parameters.Add(new Parameter { Name = "REC_ID", Value = oUSERDTO.REC_ID });
        oCloudConnection.Parameters.Add(new Parameter { Name = "USER_ID", Value = oUSERDTO.USER_ID });                        
        if (oCloudConnection.ExecuteQuery() > 0)
        {
            result = "true";
        }
    }
}

这个方案的好处 #

• 直接用标准命名参数，不用纠结DO块的参数限制
• 单条SQL是原子操作，逻辑更简洁
• 数据库执行效率更高，少了解析PL/pgSQL的开销

三、注意事项 #

• 确认USER_ID、REC_ID的字段类型和传入参数类型匹配，比如是整数还是字符串
• 如果你的数据库驱动要求参数用@USER_ID而不是:USER_ID，记得修改SQL里的参数格式
• 为了避免并发场景下重复插入，建议给SUSER表的USER_ID字段加唯一约束

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