1. 从零开始的存储过程调用指南

作为.NET开发者，当我们需要操作PostgreSQL数据库时，Npgsql无疑是连接C#与PostgreSQL的最佳桥梁。这个开源ADO.NET驱动不仅支持标准SQL操作，更提供了完整的存储过程调用支持。本文将手把手带您掌握从基础到进阶的存储过程调用技巧。

2. 环境准备与技术栈说明

本文采用的技术组合：

  • 开发语言：C# 10.0
  • 数据库：PostgreSQL 15
  • 驱动程序：Npgsql 7.0.1
  • ORM框架：Dapper 2.0.123（仅用于简化示例）

确保已安装NuGet包：

Install-Package Npgsql
Install-Package Dapper

3. 实战示例：用户积分更新系统

3.1 创建示例存储过程

-- 创建更新用户积分并记录日志的存储过程
CREATE OR REPLACE PROCEDURE update_user_points(
    IN user_id INT,
    IN points_change INT,
    IN remark TEXT,
    OUT new_points INT,
    OUT error_code INT)
LANGUAGE plpgsql
AS $$
BEGIN
    -- 初始化错误代码
    error_code := 0;
    
    -- 开启事务
    BEGIN
        -- 更新用户积分
        UPDATE users 
        SET points = points + points_change 
        WHERE id = user_id
        RETURNING points INTO new_points;

        -- 记录积分变更日志
        INSERT INTO points_log(user_id, change_value, remark)
        VALUES(user_id, points_change, remark);

    EXCEPTION
        WHEN others THEN
            error_code := SQLSTATE;
            ROLLBACK;
            RETURN;
    END;
    
    COMMIT;
END $$;

3.2 C#调用完整示例

using Npgsql;
using Dapper;

public class PointsService
{
    private readonly string _connectionString;

    public PointsService(string connectionString)
    {
        _connectionString = connectionString;
    }

    // 带事务的存储过程调用示例
    public (int NewPoints, int ErrorCode) UpdateUserPoints(int userId, int pointsChange, string remark)
    {
        using var connection = new NpgsqlConnection(_connectionString);
        connection.Open();
        
        using var transaction = connection.BeginTransaction();
        try
        {
            var parameters = new DynamicParameters();
            parameters.Add("user_id", userId);
            parameters.Add("points_change", pointsChange);
            parameters.Add("remark", remark);
            parameters.Add("new_points", dbType: DbType.Int32, direction: ParameterDirection.Output);
            parameters.Add("error_code", dbType: DbType.Int32, direction: ParameterDirection.Output);

            // 执行存储过程
            connection.Execute("CALL update_user_points(@user_id, @points_change, @remark, @new_points, @error_code)", 
                parameters, transaction: transaction);

            transaction.Commit();

            return (
                parameters.Get<int>("new_points"),
                parameters.Get<int>("error_code")
            );
        }
        catch (PostgresException ex)
        {
            transaction.Rollback();
            return (0, int.Parse(ex.SqlState));
        }
    }
}

4. 关键技术解析

4.1 参数传递技巧

// 输入输出参数设置模板
var param = new DynamicParameters();
param.Add("input_param", value);  // 默认输入参数
param.Add("output_param", direction: ParameterDirection.Output);
param.Add("inout_param", value, direction: ParameterDirection.InputOutput);

4.2 结果集处理

// 处理返回结果集
using var reader = connection.ExecuteReader("CALL get_user_records()");
while (reader.Read())
{
    var userId = reader.GetInt32(0);
    var userName = reader.GetString(1);
}

5. 应用场景剖析

存储过程特别适合以下场景：

  • 复杂事务操作（如金融交易）
  • 批量数据处理（ETL过程）
  • 敏感操作审计追踪
  • 高频业务逻辑封装
  • 多步骤数据验证

6. 技术方案优劣对比

优势：

  • 网络开销减少（单次调用代替多次查询）
  • 服务端预编译提升性能
  • 业务逻辑集中管理
  • 天然支持事务处理
  • 权限控制更精细

局限：

  • 调试复杂度增加
  • 版本管理需要额外方案
  • 数据库迁移成本升高
  • 复杂逻辑可能影响数据库性能

7. 避坑指南：实战注意事项

  1. 参数类型映射：确保C#类型与PostgreSQL类型严格对应
  2. 连接池管理：推荐使用NpgsqlDataSource进行连接管理
  3. 超时设置：根据操作复杂度合理设置CommandTimeout
  4. 编码规范：始终使用参数化查询防止SQL注入
  5. 错误处理：特别注意PostgresException的SqlState属性
  6. 性能监控：使用Npgsql的日志功能分析执行效率

8. 专家级优化建议

  • 使用UNNEST实现批量操作
// 批量插入示例
connection.Execute("CALL batch_insert_users(@ids)", new { 
    ids = new[] { 1, 2, 3 } 
});

对应的存储过程：

CREATE PROCEDURE batch_insert_users(ids INT[])
AS $$
BEGIN
    INSERT INTO users(id) SELECT unnest(ids);
END;
$$ LANGUAGE plpgsql;

9. 总结与展望

通过Npgsql调用PostgreSQL存储过程，开发者可以在保持代码简洁性的同时获得数据库层的强大功能。这种方案特别适合需要高性能数据处理的场景，但需要注意合理划分业务逻辑的数据库边界。随着.PostgreSQL 16新特性的发布，存储过程的功能将更加强大，值得持续关注。