如何记录Hangfire发送至SQL Server数据库的所有SQL查询

方法1：数据库层面捕获（SQL Server Profiler/Extended Events） #

• 无需修改项目代码，直接通过SQL Server自带工具抓取目标数据库的所有查询
• SQL Server Profiler：
  • 新建跟踪，选择你的Hangfire数据库，在事件选择中勾选TSQL_SPs、TSQL_Duration等事件，添加DatabaseName过滤器匹配目标库，启动跟踪后即可查看所有Hangfire执行的SQL语句
• Extended Events（推荐，性能比Profiler更优）：
  • 创建针对sql_statement_completed事件的会话，添加database_name筛选条件指向你的Hangfire数据库，启动会话后就能在事件文件中查看所有执行的SQL

方法2：通过Hangfire日志输出SQL #

Hangfire.SqlServer组件会在Debug/Trace级别日志中输出执行的SQL，只需配置日志框架启用对应级别：

• 在.NET Core的appsettings.json中添加日志配置：

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Hangfire.SqlServer": "Debug"
    }
  }
}

• 确保你的日志提供者（如Serilog、NLog）配置了输出Debug级别日志，即可在日志文件或控制台看到Hangfire执行的所有SQL语句

方法3：自定义数据库连接拦截 #

通过包装SqlConnection和SqlCommand手动记录SQL，适合需要自定义日志格式或额外处理的场景：

1. 实现带日志功能的连接和命令包装类：

public class LoggingDbConnection : DbConnection
{
    private readonly DbConnection _innerConn;
    private readonly ILogger _logger;

    public LoggingDbConnection(DbConnection innerConn, ILogger logger)
    {
        _innerConn = innerConn;
        _logger = logger;
    }

    // 委托所有DbConnection抽象成员到_innerConn
    public override string ConnectionString { get => _innerConn.ConnectionString; set => _innerConn.ConnectionString = value; }
    public override string Database => _innerConn.Database;
    public override string DataSource => _innerConn.DataSource;
    public override string ServerVersion => _innerConn.ServerVersion;
    public override ConnectionState State => _innerConn.State;

    public override void Open() => _innerConn.Open();
    public override void Close() => _innerConn.Close();
    public override void Dispose() => _innerConn.Dispose();

    public override DbCommand CreateCommand()
    {
        return new LoggingDbCommand(_innerConn.CreateCommand(), _logger);
    }
}

public class LoggingDbCommand : DbCommand
{
    private readonly DbCommand _innerCmd;
    private readonly ILogger _logger;

    public LoggingDbCommand(DbCommand innerCmd, ILogger logger)
    {
        _innerCmd = innerCmd;
        _logger = logger;
    }

    // 委托所有DbCommand抽象成员到_innerCmd
    public override string CommandText { get => _innerCmd.CommandText; set => _innerCmd.CommandText = value; }
    public override int CommandTimeout { get => _innerCmd.CommandTimeout; set => _innerCmd.CommandTimeout = value; }
    public override CommandType CommandType { get => _innerCmd.CommandType; set => _innerCmd.CommandType = value; }
    public override UpdateRowSource UpdatedRowSource { get => _innerCmd.UpdatedRowSource; set => _innerCmd.UpdatedRowSource = value; }
    protected override DbConnection DbConnection { get => _innerCmd.Connection; set => _innerCmd.Connection = value; }
    protected override DbParameterCollection DbParameterCollection => _innerCmd.Parameters;
    protected override DbTransaction DbTransaction { get => _innerCmd.Transaction; set => _innerCmd.Transaction = value; }
    public override bool DesignTimeVisible { get => _innerCmd.DesignTimeVisible; set => _innerCmd.DesignTimeVisible = value; }

    public override void Cancel() => _innerCmd.Cancel();
    public override void Dispose() => _innerCmd.Dispose();
    public override int ExecuteNonQuery()
    {
        _logger.LogDebug("Hangfire SQL执行: {Sql}", CommandText);
        return _innerCmd.ExecuteNonQuery();
    }
    public override DbDataReader ExecuteReader()
    {
        _logger.LogDebug("Hangfire SQL执行: {Sql}", CommandText);
        return _innerCmd.ExecuteReader();
    }
    public override DbDataReader ExecuteReader(CommandBehavior behavior)
    {
        _logger.LogDebug("Hangfire SQL执行: {Sql}", CommandText);
        return _innerCmd.ExecuteReader(behavior);
    }
    public override object ExecuteScalar()
    {
        _logger.LogDebug("Hangfire SQL执行: {Sql}", CommandText);
        return _innerCmd.ExecuteScalar();
    }
    public override void Prepare() => _innerCmd.Prepare();
}

2. 在Hangfire配置中使用自定义连接：

services.AddHangfire(config =>
{
    config.UseSqlServerStorage(options =>
    {
        options.UseDbConnectionFactory(() =>
        {
            var conn = new SqlConnection(Configuration.GetConnectionString("HangfireConnection"));
            var logger = services.BuildServiceProvider().GetRequiredService<ILogger<LoggingDbConnection>>();
            return new LoggingDbConnection(conn, logger);
        });
    });
});

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