9.24 数据库操作拦截器

9.24.1 数据库拦截器

Furion 框架提供四种数据库操作拦截器，可以通过拦截器动态修改数据库连接字符串，动态修改 sql，动态更改参数等操作。

Furion 支持这四种拦截器：

• DbConnectionInterceptor：数据库连接拦截器
• DbCommandInterceptor：数据库执行 Sql 拦截器
• SaveChangesInterceptor：提交到数据库拦截器
• 在数据库上下文中重写 SavedChangesEvent 相关事件

9.24.2 支持拦截类型

9.24.2.1 DbConnectionInterceptor

using Microsoft.EntityFrameworkCore.Diagnostics;
using System.Data.Common;
using System.Threading;
using System.Threading.Tasks;

namespace Furion.DatabaseAccessor.Interceptors
{
    public class SqlConnectionInterceptor : DbConnectionInterceptor
    {
        // 数据库连接之前
        public override InterceptionResult ConnectionOpening(DbConnection connection, ConnectionEventData eventData, InterceptionResult result)
        {
            return base.ConnectionOpening(connection, eventData, result);
        }

        // 数据库连接之前（异步）
        public override ValueTask<InterceptionResult> ConnectionOpeningAsync(DbConnection connection, ConnectionEventData eventData, InterceptionResult result, CancellationToken cancellationToken = default)
        {
            return base.ConnectionOpeningAsync(connection, eventData, result, cancellationToken);
        }

        // 数据库连接成功
        public override void ConnectionOpened(DbConnection connection, ConnectionEndEventData eventData)
        {
            base.ConnectionOpened(connection, eventData);
        }

        // 数据库连接成功（异步）
        public override Task ConnectionOpenedAsync(DbConnection connection, ConnectionEndEventData eventData, CancellationToken cancellationToken = default)
        {
            return base.ConnectionOpenedAsync(connection, eventData, cancellationToken);
        }

        // 数据库连接关闭之前
        public override InterceptionResult ConnectionClosing(DbConnection connection, ConnectionEventData eventData, InterceptionResult result)
        {
            return base.ConnectionClosing(connection, eventData, result);
        }

        // 数据库连接关闭之前（异步）
        public override ValueTask<InterceptionResult> ConnectionClosingAsync(DbConnection connection, ConnectionEventData eventData, InterceptionResult result)
        {
            return base.ConnectionClosingAsync(connection, eventData, result);
        }

        // 数据库连接关闭成功
        public override void ConnectionClosed(DbConnection connection, ConnectionEndEventData eventData)
        {
            base.ConnectionClosed(connection, eventData);
        }

        // 数据库连接关闭成功（异步）
        public override Task ConnectionClosedAsync(DbConnection connection, ConnectionEndEventData eventData)
        {
            return base.ConnectionClosedAsync(connection, eventData);
        }

        // 数据库连接失败
        public override void ConnectionFailed(DbConnection connection, ConnectionErrorEventData eventData)
        {
            base.ConnectionFailed(connection, eventData);
        }

        // 数据库连接失败（异步）
        public override Task ConnectionFailedAsync(DbConnection connection, ConnectionErrorEventData eventData, CancellationToken cancellationToken = default)
        {
            return base.ConnectionFailedAsync(connection, eventData, cancellationToken);
        }
    }
}

9.24.2.2 DbCommandInterceptor

using Microsoft.EntityFrameworkCore.Diagnostics;
using System.Data.Common;
using System.Threading;
using System.Threading.Tasks;

namespace Furion.DatabaseAccessor
{
    internal sealed class SqlCommandProfilerInterceptor : DbCommandInterceptor
    {
        // 创建命令对象之前
        public override InterceptionResult<DbCommand> CommandCreating(CommandCorrelatedEventData eventData, InterceptionResult<DbCommand> result)
        {
            return base.CommandCreating(eventData, result);
        }

        // 创建命令对象之后
        public override DbCommand CommandCreated(CommandEndEventData eventData, DbCommand result)
        {
            return base.CommandCreated(eventData, result);
        }

        // 创建命令对象失败
        public override void CommandFailed(DbCommand command, CommandErrorEventData eventData)
        {
            base.CommandFailed(command, eventData);
        }

         // 创建命令对象失败（异步）
        public override Task CommandFailedAsync(DbCommand command, CommandErrorEventData eventData, CancellationToken cancellationToken = default)
        {
            return base.CommandFailedAsync(command, eventData, cancellationToken);
        }

        // 读取数据之前
        public override InterceptionResult<DbDataReader> ReaderExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
        {
            return base.ReaderExecuting(command, eventData, result);
        }

        // 读取数据之前（异步）
        public override ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result, CancellationToken cancellationToken = default)
        {
            return base.ReaderExecutingAsync(command, eventData, result, cancellationToken);
        }

        // 读取数据之后
        public override DbDataReader ReaderExecuted(DbCommand command, CommandExecutedEventData eventData, DbDataReader result)
        {
            return base.ReaderExecuted(command, eventData, result);
        }

        // 读取数据之后（异步）
        public override ValueTask<DbDataReader> ReaderExecutedAsync(DbCommand command, CommandExecutedEventData eventData, DbDataReader result, CancellationToken cancellationToken = default)
        {
            return base.ReaderExecutedAsync(command, eventData, result, cancellationToken);
        }

        // DataReader 对象释放之前
        public override InterceptionResult DataReaderDisposing(DbCommand command, DataReaderDisposingEventData eventData, InterceptionResult result)
        {
            return base.DataReaderDisposing(command, eventData, result);
        }

        // 无查询执行 sql 之前
        public override InterceptionResult<int> NonQueryExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<int> result)
        {
            return base.NonQueryExecuting(command, eventData, result);
        }

        // 无查询执行 sql 之前（异步）
        public override ValueTask<InterceptionResult<int>> NonQueryExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<int> result, CancellationToken cancellationToken = default)
        {
            return base.NonQueryExecutingAsync(command, eventData, result, cancellationToken);
        }

        // 无查询执行 sql 之后
        public override int NonQueryExecuted(DbCommand command, CommandExecutedEventData eventData, int result)
        {
            return base.NonQueryExecuted(command, eventData, result);
        }

        // 无查询执行 sql 之后（异步）
        public override ValueTask<int> NonQueryExecutedAsync(DbCommand command, CommandExecutedEventData eventData, int result, CancellationToken cancellationToken = default)
        {
            return base.NonQueryExecutedAsync(command, eventData, result, cancellationToken);
        }

        // 执行 sql 返回单行单列之前
        public override InterceptionResult<object> ScalarExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<object> result)
        {
            return base.ScalarExecuting(command, eventData, result);
        }

        // 执行 sql 返回单行单列之前（异步）
        public override ValueTask<InterceptionResult<object>> ScalarExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<object> result, CancellationToken cancellationToken = default)
        {
            return base.ScalarExecutingAsync(command, eventData, result, cancellationToken);
        }

        // 执行 sql 返回单行单列之后
        public override object ScalarExecuted(DbCommand command, CommandExecutedEventData eventData, object result)
        {
            return base.ScalarExecuted(command, eventData, result);
        }

        // 执行 sql 返回单行单列之后（异步）
        public override ValueTask<object> ScalarExecutedAsync(DbCommand command, CommandExecutedEventData eventData, object result, CancellationToken cancellationToken = default)
        {
            return base.ScalarExecutedAsync(command, eventData, result, cancellationToken);
        }
    }
}

9.24.2.3 SaveChangesInterceptor

using Microsoft.EntityFrameworkCore.Diagnostics;
using System.Threading;
using System.Threading.Tasks;

namespace Furion.DatabaseAccessor
{
    public class DbContextSaveChangesInterceptor : SaveChangesInterceptor
    {
        // 提交到数据库之前
        public override InterceptionResult<int> SavingChanges(DbContextEventData eventData, InterceptionResult<int> result)
        {
            return base.SavingChanges(eventData, result);
        }

        // 提交到数据库之前（异步）
        public override ValueTask<InterceptionResult<int>> SavingChangesAsync(DbContextEventData eventData, InterceptionResult<int> result, CancellationToken cancellationToken = default)
        {
            return base.SavingChangesAsync(eventData, result, cancellationToken);
        }

        // 提交到数据库之后
        public override int SavedChanges(SaveChangesCompletedEventData eventData, int result)
        {
            return base.SavedChanges(eventData, result);
        }

        // 提交到数据库之后（异步）
        public override ValueTask<int> SavedChangesAsync(SaveChangesCompletedEventData eventData, int result, CancellationToken cancellationToken = default)
        {
            return base.SavedChangesAsync(eventData, result, cancellationToken);
        }

        // 提交数据库失败
        public override void SaveChangesFailed(DbContextErrorEventData eventData)
        {
            base.SaveChangesFailed(eventData);
        }

        // 提交数据库失败（异步）
        public override Task SaveChangesFailedAsync(DbContextErrorEventData eventData, CancellationToken cancellationToken = default)
        {
            return base.SaveChangesFailedAsync(eventData, cancellationToken);
        }
    }
}

9.24.2.4 SavedChangesEvent 拦截

Furion 框架中为所有 AppDbContext 子类都提供了三个可重写的方法，这三个方法分别由三个事件触发：

• 提交更改之前 SavingChanges 事件：触发 void SavingChangesEvent(DbContextEventData eventData, InterceptionResult<int> result) 方法
• 提交更改之后 SavedChanges 事件：触发 void SavedChangesEvent(SaveChangesCompletedEventData eventData, int result) 方法
• 提交更改失败 SaveChangesFailed 事件：触发 void SaveChangesFailedEvent(DbContextErrorEventData eventData) 方法

通过这三个事件我们可以在数据库做增、删、改时候做拦截，比如设置创建时间、更新时间或其他默认操作。

如自动添加租户 Id：

protected override void SavingChangesEvent(DbContextEventData eventData, InterceptionResult<int> result)
{
    // 获取当前事件对应上下文
    var dbContext = eventData.Context;

    // 获取所有新增和更新的实体
    var entities = dbContext.ChangeTracker.Entries()
                .Where(u => u.State == EntityState.Added || u.State == EntityState.Modified);

    foreach (var entity in entities)
    {
        switch (entity.State)
        {
            // 自动设置租户Id
            case EntityState.Added:
                entity.Property(nameof(Entity.TenantId)).CurrentValue = GetTenantId();
                break;
            // 排除租户Id
            case EntityState.Modified:
                entity.Property(nameof(Entity.TenantId)).IsModified = false;
                break;
        }
    }
}

9.24.3 注册自定义过滤器

定义好过滤器之后，我们需要在数据库上下文中注册：

services.AddDbPool<FurionDbContext>(interceptors: new IInterceptor[] {
    new YourSqlConnectionProfilerInterceptor(),
    new YourDbContextSaveChangesInterceptor(),
    new YourSqlCommandProfilerInterceptor()
});

9.24.4 反馈与建议

与我们交流

给 Furion 提 Issue。