PostgreSQL+C#获取受影响行数：无version参数时的增改逻辑问题

问题背景 #

原本的C#方法通过传入version参数，判断是插入新记录还是递增version字段。现在移除version参数后，改用PostgreSQL的IF EXISTS分支逻辑实现，但ExecuteNonQuery无法正确返回INSERT/UPDATE的受影响行数，导致无法判断操作是否成功。

当前代码的核心问题 #

1. DO块不返回受影响行数：PostgreSQL的DO匿名代码块执行后，ExecuteNonQuery只会返回代码块的执行状态（通常是0），不会返回内部INSERT/UPDATE操作的实际行数，所以affectedRows永远无法等于1。
2. SQL注入风险：直接拼接{id}和'{DateTime.Now}'会导致SQL注入漏洞，同时DateTime.Now的字符串格式可能与数据库时间格式不兼容。
3. 并发安全问题：先查询再修改的分支逻辑不是原子操作，高并发场景下可能出现竞态条件。

解决方案：使用UPSERT原子操作（推荐） #

PostgreSQL的INSERT ... ON CONFLICT ... DO UPDATE（UPSERT）是原子性的增改操作，会直接返回实际受影响的行数，同时避免并发问题和SQL注入。

修改后的C#代码：

public void Increment(int id)
{
    var affectedRows = _db.ExecuteNonQuery($@"
        INSERT INTO ""table"" (id, version, updated)
        VALUES (:id, 0, :updatedTime)
        ON CONFLICT (id) DO UPDATE 
        SET version = ""table"".version + 1, updated = :updatedTime
        WHERE ""table"".updated IS NULL; -- 保留原逻辑中的UPDATE条件
    ", 
    _db.CreateParameter("id", id),
    _db.CreateParameter("updatedTime", DateTime.Now));

    if (affectedRows != 1)
        throw new Exception("None updated");
}

代码说明 #

• 用ON CONFLICT (id)指定冲突字段（需确保id是唯一约束/主键），当记录存在时执行UPDATE，不存在时执行INSERT。
• 所有变量都用参数化传递，彻底避免SQL注入。
• ExecuteNonQuery会直接返回INSERT或UPDATE操作的受影响行数，可正常用于判断操作是否成功。

备选方案：保留IF EXISTS分支并返回行数 #

如果一定要保留分支逻辑，可通过PL/pgSQL的GET DIAGNOSTICS获取行数并返回，改用ExecuteScalar获取结果：

public void Increment(int id)
{
    var cmd = _db.CreateCommand();
    cmd.CommandText = $@"
        DECLARE
            affected_rows INTEGER := 0;
        BEGIN
            IF EXISTS (SELECT 1 FROM ""table"" WHERE id = :id) THEN
                UPDATE ""table""
                SET version = version + 1, updated = :updatedTime
                WHERE id = :id AND updated IS NULL;
                GET DIAGNOSTICS affected_rows = ROW_COUNT;
            ELSE 
                INSERT INTO ""table"" (id, version, updated)
                VALUES (:id, 0, :updatedTime);
                GET DIAGNOSTICS affected_rows = ROW_COUNT;
            END IF;
            SELECT affected_rows;
        END;
    ";
    cmd.Parameters.Add(_db.CreateParameter("id", id));
    cmd.Parameters.Add(_db.CreateParameter("updatedTime", DateTime.Now));

    var result = cmd.ExecuteScalar();
    if (result == null || Convert.ToInt32(result) != 1)
        throw new Exception("None updated");
}

代码说明 #

• 通过GET DIAGNOSTICS获取UPDATE/INSERT的行数，最后用SELECT返回该值。
• 使用ExecuteScalar获取返回的行数，替代ExecuteNonQuery。

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