如何用C#实现数据库的审计日志？步骤是什么？

在C#中实现数据库审计日志，核心是记录数据变更（增、删、改）的时间、操作人、变更内容等信息。常见做法结合实体框架（Entity Framework）的拦截机制，在保存更改前自动捕获变化并写入日志表。以下是具体实现步骤。

1. 设计审计日志表结构

先在数据库中创建用于存储审计信息的表，包含关键字段：

CREATE TABLE AuditLog (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    TableName NVARCHAR(100),
    RecordId NVARCHAR(50),
    Action NVARCHAR(10),
    ChangedBy NVARCHAR(100),
    ChangedAt DATETIME2 DEFAULT GETUTCDATE(),
    OldValues NVARCHAR(MAX),
    NewValues NVARCHAR(MAX)
);

2. 创建审计日志实体类

在C#项目中定义与日志表对应的实体类：

public class AuditLog
{
    public int Id { get; set; }
    public string TableName { get; set; }
    public string RecordId { get; set; }
    public string Action { get; set; } // Insert, Update, Delete
    public string ChangedBy { get; set; }
    public DateTime ChangedAt { get; set; }
    public string OldValues { get; set; }
    public string NewValues { get; set; }
}

3. 在DbContext中重写SaveChanges方法

利用EF的ChangeTracker获取所有被修改的实体，并生成审计记录。

在你的DbContext中添加如下代码：

public override int SaveChanges()
{
    var auditEntries = OnBeforeSaving("system"); // 可替换为实际用户
    var result = base.SaveChanges();
    OnAfterSaving();
    return result;
}
private List<AuditEntry> OnBeforeSaving(string userId)
{
    var auditEntries = new List<AuditEntry>();
    foreach (var entry in ChangeTracker.Entries())
    {
        if (entry.Entity is AuditLog || entry.State == EntityState.Detached || entry.State == EntityState.Unchanged)
            continue;
        var auditEntry = new AuditEntry(entry)
        {
            TableName = entry.Entity.GetType().Name,
            ChangedBy = userId
        };
        auditEntries.Add(auditEntry);
        foreach (var property in entry.Properties)
        {
            string propertyName = property.Metadata.Name;
            if (property.Metadata.IsPrimaryKey())
            {
                auditEntry.RecordId = property.CurrentValue?.ToString();
                continue;
            }
            switch (entry.State)
            {
                case EntityState.Added:
                    auditEntry.NewValues[propertyName] = property.CurrentValue;
                    break;
                case EntityState.Deleted:
                    auditEntry.OldValues[propertyName] = property.OriginalValue;
                    break;
                case EntityState.Modified:
                    if (property.IsModified)
                    {
                        auditEntry.OldValues[propertyName] = property.OriginalValue;
                        auditEntry.NewValues[propertyName] = property.CurrentValue;
                    }
                    break;
            }
        }
    }
    foreach (var auditEntry in auditEntries)
    {
        AuditLogs.Add(auditEntry.ToAudit());
    }
    return auditEntries;
}
private void OnAfterSaving()
{
    // 可用于清理或异步写入
}

4. 创建临时AuditEntry类辅助处理

用于中间收集变更数据，再转换为AuditLog实体。

public class AuditEntry
{
    public AuditEntry(EntityEntry entry)
    {
        Entry = entry;
        OldValues = new Dictionary<string, object>();
        NewValues = new Dictionary<string, object>();
    }
    public EntityEntry Entry { get; }
    public string TableName { get; set; }
    public string RecordId { get; set; }
    public string Action { get; set; }
    public string ChangedBy { get; set; }
    public Dictionary<string, object> OldValues { get; } = new();
    public Dictionary<string, object> NewValues { get; } = new();
    public AuditLog ToAudit()
    {
        var jsonSerializerOptions = new JsonSerializerOptions { WriteIndented = true };
        return new AuditLog
        {
            TableName = TableName,
            RecordId = RecordId,
            Action = Entry.State.ToString(),
            ChangedBy = ChangedBy,
            ChangedAt = DateTime.UtcNow,
            OldValues = OldValues.Count != 0 ? JsonSerializer.Serialize(OldValues, jsonSerializerOptions) : null,
            NewValues = NewValues.Count != 0 ? JsonSerializer.Serialize(NewValues, jsonSerializerOptions) : null
        };
    }
}

5. 注册DbSet和迁移

确保在DbContext中添加：

public DbSet<AuditLog> AuditLogs { get; set; }

然后使用EF Core命令添加迁移并更新数据库：

dotnet ef migrations add AddAuditLogTable
dotnet ef database update