C#用Dapper调用PostgreSQL存储过程无法获取json/jsonb出参如何解决

错误点梳理 #

1. 存储过程内json/jsonb赋值逻辑错误
   你当前使用to_json('{"Hello": "World"}'::text)的写法，是将字符串文本转换为json类型的字符串值，而非你预期的json对象，类型不匹配会导致返回时映射异常。
2. C#调用时存储过程名格式错误
   你调用execStoredProcedure时传入的参数是sp_test(:out_param_text, :out_param_json, :out_param_jsonb)，方法内拼接后最终执行的SQL是call sources_v2.sp_test(:out_param_text, :out_param_json, :out_param_jsonb)，多余的占位符会导致参数绑定错位，输出参数无法正确赋值。
3. 参数配置缺少明确的DbType声明
   添加out_param_json和out_param_jsonb参数时，第三个参数DbType传入了null，Dapper无法正确识别PostgreSQL专属的json/jsonb类型映射，会保留传入的默认值。
4. 输出参数取值时前缀不匹配
   你从DynamicParameters中取值时使用了带@前缀的参数名，但添加参数时用的是无前缀的名称，Dapper默认匹配无前后缀的参数名，会导致取值异常（虽然你当前text类型碰巧能取到，但json类型映射会受影响）。
5. 方法执行逻辑缺陷
   你判断只有连接状态为Closed时才执行存储过程，如果连接已经处于Open状态，整个执行逻辑会被跳过，参数自然不会更新。

修复步骤 #

1. 修正PostgreSQL存储过程 #

CREATE OR REPLACE PROCEDURE sources_v2.sp_test(INOUT out_param_text text DEFAULT ''::text, INOUT out_param_json json DEFAULT '{}'::json, INOUT out_param_jsonb jsonb DEFAULT '{}'::jsonb)
 LANGUAGE plpgsql
AS $procedure$
DECLARE
BEGIN
    out_param_text := 'Hello World !';
    -- 直接赋值json类型，不要用to_json转字符串
    out_param_json := '{"Hello": "World"}'::json;
    out_param_jsonb := '{"Hello": "World"}'::jsonb;
    RETURN;
END; $procedure$;

2. 修正存储过程调用代码 #

DynamicParameters parameters = new DynamicParameters();
parameters.Add("out_param_text","", DbType.String, ParameterDirection.InputOutput);
// 明确指定Npgsql专属的DbType
parameters.Add("out_param_json", "{}", NpgsqlDbType.Json, ParameterDirection.InputOutput);
parameters.Add("out_param_jsonb", "{}", NpgsqlDbType.Jsonb, ParameterDirection.InputOutput);
// 仅传入存储过程名，不需要带参数占位符
result = _DbRepositoryCommon.execStoredProcedure<DataListReturnModelRepository<T>>("sp_test", parameters);

3. 修正封装的执行方法 #

public T execStoredProcedure<T>(string spxName, DynamicParameters dyParams)
{
    T result = default(T);
    using (IDbConnection connection = GetConnection)
    {
        // 移除连接状态判断，using会自动管理连接生命周期
        connection.Open();
        try
        {
            // 直接调用存储过程，Dapper自动绑定参数
            connection.Query($"call sources_v2.{spxName}", dyParams, commandType: CommandType.Text);
        }
        catch (Exception ex)
        {
            throw;
        }
        // 取值时去掉@前缀
        var toto = dyParams.Get<string>("out_param_text");
        // 若需要获取json/jsonb参数直接取值即可
        // var jsonVal = dyParams.Get<System.Text.Json.JsonDocument>("out_param_json");
        // var jsonbVal = dyParams.Get<System.Text.Json.JsonDocument>("out_param_jsonb");
        result = returnObjectFromDynamicParameter<T>(dyParams, "returndata");
        // 不需要手动关闭连接，using会自动释放
    }
    return result;
}

4. 额外配置确认 #

确保你使用的Npgsql驱动已开启json类型映射：

• 若使用System.Text.Json：添加NpgsqlConnection.GlobalTypeMapper.UseSystemTextJson();到项目启动配置中
• 若使用Newtonsoft.Json：添加NpgsqlConnection.GlobalTypeMapper.UseJsonNet();到项目启动配置中

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