.NET 7中使用Dapper调用PostgreSQL函数无需指定参数类型的方法

问题根源 #

Npgsql新版本对CommandType.StoredProcedure的行为做了调整：该类型现在仅对应PostgreSQL中用CREATE PROCEDURE创建的存储过程（通过CALL语句执行），而你之前调用的是CREATE FUNCTION创建的函数，需要用SELECT语句调用，这是原有方式失效的核心原因。

你自行改写的Execute方法因手动拼接参数列表，未充分利用Dapper和Npgsql的自动类型映射能力，导致非string类型（如Int64、bool）和特殊类型（如JSON）出现匹配错误。

最优解决方案 #

方案1：命名参数调用函数（推荐） #

修改Execute方法，生成带命名参数的SQL语句，让Dapper和Npgsql自动处理类型映射，无需手动转换参数：

public virtual async Task<T> Execute<T>(string functionName, object? parameters = null, int commandTimeout = 60)
{
    using var dbConnection = await GetConnection();
    using var transaction = await dbConnection.BeginTransactionAsync();

    string commandText;
    // 用双引号包裹函数名，避免关键字冲突
    var quotedFunctionName = $"\"{functionName}\"";
    
    if (parameters == null)
    {
        commandText = $"SELECT * FROM {quotedFunctionName}();";
    }
    else
    {
        // 生成命名参数赋值语句，确保参数与函数定义一一对应
        var paramAssignments = parameters.GetType().GetProperties()
            .Select(p => $"\"{p.Name}\" := @{p.Name}");
        commandText = $"SELECT * FROM {quotedFunctionName}({string.Join(", ", paramAssignments)});";
    }

    try
    {
        var result = await dbConnection.QueryFirstOrDefaultAsync<T>(
            commandText, 
            parameters, 
            transaction: transaction, 
            commandTimeout: commandTimeout);
        transaction.Commit();
        return result ?? default!;
    }
    catch (Exception)
    {
        await transaction.RollbackAsync();
        throw;
    }
}

关键说明：

• 命名参数格式（paramName := @paramName）确保参数与函数定义精准匹配，避免位置错误。
• Dapper自动完成C#类型到PostgreSQL类型的映射：long→bigint、bool→boolean；安装Npgsql.Json.NET包后，JObject/JArray可自动映射为jsonb/json类型。
• 双引号包裹标识符，支持包含特殊字符或与关键字重名的函数/参数名。

方案2：兼容函数与存储过程的通用调用 #

若需同时支持函数和存储过程，可添加参数区分调用逻辑：

public virtual async Task<T> Execute<T>(string commandName, object? parameters = null, bool isProcedure = false, int commandTimeout = 60)
{
    using var dbConnection = await GetConnection();
    using var transaction = await dbConnection.BeginTransactionAsync();

    string commandText;
    CommandType commandType = CommandType.Text;

    if (isProcedure)
    {
        commandText = commandName;
        commandType = CommandType.StoredProcedure;
    }
    else
    {
        var quotedCommandName = $"\"{commandName}\"";
        commandText = parameters == null 
            ? $"SELECT * FROM {quotedCommandName}();"
            : $"SELECT * FROM {quotedCommandName}({string.Join(", ", parameters.GetType().GetProperties().Select(p => $"\"{p.Name}\" := @{p.Name}"))});";
    }

    try
    {
        var result = await dbConnection.QueryFirstOrDefaultAsync<T>(
            commandText, 
            parameters, 
            transaction: transaction, 
            commandTimeout: commandTimeout,
            commandType: commandType);
        transaction.Commit();
        return result ?? default!;
    }
    catch (Exception)
    {
        await transaction.RollbackAsync();
        throw;
    }
}

调用存储过程时传入isProcedure: true即可。

代码优化建议 #

1. 添加超时控制：保留60秒超时设置，避免长时间阻塞。
2. SQL注入防护：对函数名做合法性验证（如仅允许字母、数字、下划线），并用双引号包裹标识符。
3. 精简事务使用：单条数据库操作无需开启事务，仅在多操作需保证原子性时使用，减少性能开销。
4. 日志记录：在catch块中添加异常日志（记录函数名、参数、异常信息），方便排查问题。
5. 空值处理：对返回值做空值判断，避免Nullable类型的空引用错误。
6. 复用逻辑：将SQL构造逻辑提取为单独方法，供Get、GetMany等其他方法复用。
7. JSON类型支持：安装Npgsql.Json.NET包，实现JSON类型的自动映射。

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