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<FurDbContext>(interceptors: new IInterceptor[] {
new YourSqlConnectionProfilerInterceptor(),
new YourDbContextSaveChangesInterceptor(),
new YourSqlCommandProfilerInterceptor()
});

9.24.4 反馈与建议#

与我们交流

给 Furion 提 Issue