ASP.NET Core拦截SqlConnection.Open并注入UserID到session_context求助

你编写的DbConnectionInterceptor是EF Core专属的拦截器，仅对EF Core创建和管理的数据库连接生效。而你当前的代码是直接使用ADO.NET原生的SqlConnection手动创建连接，完全绕开了EF Core的连接管理流程，所以拦截器根本不会被触发。

根据你的代码使用场景，提供三种可行方案：

方案1：改用EF Core执行SQL操作（推荐） #

如果可以切换到EF Core执行SQL，拦截器就能正常工作，同时能统一管理连接和用户ID注入。

步骤1：修改拦截器，添加用户ID注入逻辑 #

using Microsoft.EntityFrameworkCore.Diagnostics;
using Microsoft.AspNetCore.Http;
using System.Security.Claims;
using System.Data.SqlClient;

public class DbLogUserIdInterceptor : DbConnectionInterceptor
{
    private readonly IHttpContextAccessor _httpContextAccessor;

    // 通过依赖注入获取当前请求上下文
    public DbLogUserIdInterceptor(IHttpContextAccessor httpContextAccessor)
    {
        _httpContextAccessor = httpContextAccessor;
    }

    // 同步打开连接时注入UserID
    public override InterceptionResult ConnectionOpening(
            DbConnection connection,
            ConnectionEventData eventData,
            InterceptionResult result)
    {
        if (connection is SqlConnection sqlConnection)
        {
            var userId = _httpContextAccessor.HttpContext?.User?.FindFirstValue(ClaimTypes.NameIdentifier);
            if (!string.IsNullOrEmpty(userId))
            {
                using var command = sqlConnection.CreateCommand();
                command.CommandText = "EXEC sp_set_session_context @key = N'UserID', @value = @userId";
                command.Parameters.AddWithValue("@userId", userId);
                command.ExecuteNonQuery();
            }
        }
        return base.ConnectionOpening(connection, eventData, result);
    }

    // 异步打开连接时注入UserID
    public override async ValueTask<InterceptionResult> ConnectionOpeningAsync(
        DbConnection connection,
        ConnectionEventData eventData,
        InterceptionResult result,
        CancellationToken cancellationToken = default)
    {
        if (connection is SqlConnection sqlConnection)
        {
            var userId = _httpContextAccessor.HttpContext?.User?.FindFirstValue(ClaimTypes.NameIdentifier);
            if (!string.IsNullOrEmpty(userId))
            {
                using var command = sqlConnection.CreateCommand();
                command.CommandText = "EXEC sp_set_session_context @key = N'UserID', @value = @userId";
                command.Parameters.AddWithValue("@userId", userId);
                await command.ExecuteNonQueryAsync(cancellationToken);
            }
        }
        return await base.ConnectionOpeningAsync(connection, eventData, result, cancellationToken);
    }
}

步骤2：注册服务与拦截器 #

在Program.cs中注册IHttpContextAccessor和EF Core上下文：

builder.Services.AddHttpContextAccessor();
builder.Services.AddDbContext<Database1DbContext>((serviceProvider, options) =>
{
    options.UseSqlServer(builder.Configuration.GetConnectionString("YourConnectionString"))
           .AddInterceptors(serviceProvider.GetRequiredService<DbLogUserIdInterceptor>());
});

// 注册拦截器本身
builder.Services.AddScoped<DbLogUserIdInterceptor>();

步骤3：用EF Core执行更新操作 #

替换原ADO.NET代码为EF Core的SQL执行方式：

// 注入Database1DbContext
private readonly Database1DbContext _dbContext;

public YourService(Database1DbContext dbContext)
{
    _dbContext = dbContext;
}

// 执行更新逻辑
public async Task UpdateCustomer(Customer customer)
{
    var sql = "UPDATE Customer SET CompanyName = @CompanyName, EmailAddress = @EmailAddress WHERE CustomerID = @CustomerID";
    await _dbContext.Database.ExecuteSqlRawAsync(sql, 
        new SqlParameter("@CompanyName", customer.CompanyName),
        new SqlParameter("@EmailAddress", customer.EmailAddress),
        new SqlParameter("@CustomerID", customer.CustomerID));
}

方案2：为ADO.NET创建扩展方法（兼容原有代码） #

如果必须保留原生ADO.NET代码，可以创建扩展方法，在打开连接时自动注入UserID。

步骤1：编写扩展方法 #

using System.Data.SqlClient;

public static class SqlConnectionExtensions
{
    public static void OpenWithUserId(this SqlConnection connection, string userId)
    {
        connection.Open();
        if (!string.IsNullOrEmpty(userId))
        {
            using var command = connection.CreateCommand();
            command.CommandText = "EXEC sp_set_session_context @key = N'UserID', @value = @userId";
            command.Parameters.AddWithValue("@userId", userId);
            command.ExecuteNonQuery();
        }
    }

    public static async Task OpenWithUserIdAsync(this SqlConnection connection, string userId, CancellationToken cancellationToken = default)
    {
        await connection.OpenAsync(cancellationToken);
        if (!string.IsNullOrEmpty(userId))
        {
            using var command = connection.CreateCommand();
            command.CommandText = "EXEC sp_set_session_context @key = N'UserID', @value = @userId";
            command.Parameters.AddWithValue("@userId", userId);
            await command.ExecuteNonQueryAsync(cancellationToken);
        }
    }
}

步骤2：修改原有ADO.NET代码 #

替换connection.Open()为扩展方法，同时传入当前用户ID：

// 注入IHttpContextAccessor获取当前用户ID
private readonly IHttpContextAccessor _httpContextAccessor;

public YourService(IHttpContextAccessor httpContextAccessor)
{
    _httpContextAccessor = httpContextAccessor;
}

public void UpdateCustomer(Customer customer, string connectionString)
{
    var userId = _httpContextAccessor.HttpContext?.User?.FindFirstValue(ClaimTypes.NameIdentifier);

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        // 替换为扩展方法
        connection.OpenWithUserId(userId);

        string sql = "UPDATE Customer SET CompanyName = @CompanyName, EmailAddress = @EmailAddress WHERE CustomerID = @CustomerID";

        using (SqlCommand command = new SqlCommand(sql, connection))
        {
            command.Parameters.Clear();
            command.Parameters.AddWithValue("@CompanyName", customer.CompanyName);
            command.Parameters.AddWithValue("@EmailAddress", customer.EmailAddress);
            command.Parameters.AddWithValue("@CustomerID", customer.CustomerID);

            command.ExecuteNonQuery();
        }
    }
}

方案3：自定义SqlConnection类（高级统一拦截） #

如果需要全局拦截所有原生SqlConnection的打开操作，可以继承SqlConnection并重写Open方法：

using System.Data.SqlClient;
using System.Threading;
using System.Threading.Tasks;

public class UserTrackingSqlConnection : SqlConnection
{
    private readonly string _userId;

    public UserTrackingSqlConnection(string connectionString, string userId) : base(connectionString)
    {
        _userId = userId;
    }

    public override void Open()
    {
        base.Open();
        SetSessionContext();
    }

    public override async Task OpenAsync(CancellationToken cancellationToken)
    {
        await base.OpenAsync(cancellationToken);
        await SetSessionContextAsync(cancellationToken);
    }

    private void SetSessionContext()
    {
        if (!string.IsNullOrEmpty(_userId))
        {
            using var command = CreateCommand();
            command.CommandText = "EXEC sp_set_session_context @key = N'UserID', @value = @userId";
            command.Parameters.AddWithValue("@userId", _userId);
            command.ExecuteNonQuery();
        }
    }

    private async Task SetSessionContextAsync(CancellationToken cancellationToken)
    {
        if (!string.IsNullOrEmpty(_userId))
        {
            using var command = CreateCommand();
            command.CommandText = "EXEC sp_set_session_context @key = N'UserID', @value = @userId";
            command.Parameters.AddWithValue("@userId", _userId);
            await command.ExecuteNonQueryAsync(cancellationToken);
        }
    }
}

使用时替换原SqlConnection：

var userId = _httpContextAccessor.HttpContext?.User?.FindFirstValue(ClaimTypes.NameIdentifier);

using (SqlConnection connection = new UserTrackingSqlConnection(connectionString, userId))
{
    connection.Open();
    // 后续代码保持不变
}

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