Audit Columns

These scenarios demonstrate how to ensure that audit columns are populated.

For each operation, a User object will be supplied along with the object being created or updated.

Scenario Prototype

public interface IAuditColumnsScenario<TDepartment>
   where TDepartment : class, IDepartment, new()
{
    /// <summary>
    /// Creates the department.
    /// </summary>
    /// <param name="department">The department.</param>
    /// <param name="user">The user.</param>
    /// <returns>System.Int32.</returns>
    int CreateDepartment(TDepartment department, User user);

    /// <summary>
    /// Updates the department.
    /// </summary>
    /// <param name="department">The department.</param>
    /// <param name="user">The user.</param>
    void UpdateDepartment(TDepartment department, User user);

    /// <summary>
    /// Gets the department.
    /// </summary>
    /// <param name="departmentKey">The department key.</param>
    /// <returns>TDepartment.</returns>
    TDepartment? GetDepartment(int departmentKey, User user);
}

ADO.NET

ADO requires that audit columns be manually supplied. This is usually done when the parameters are generated.

    public class AuditColumnsScenario : SqlServerScenarioBase, IAuditColumnsScenario<Department>
    {
        public AuditColumnsScenario(string connectionString) : base(connectionString)
        { }

        public int CreateDepartment(Department department, User user)
        {
            if (department == null)
                throw new ArgumentNullException(nameof(department), $"{nameof(department)} is null.");
            if (user == null)
                throw new ArgumentNullException(nameof(user), $"{nameof(user)} is null.");

            const string sql = @"INSERT INTO HR.Department
(
    DepartmentName,
    DivisionKey,
    CreatedDate,
    ModifiedDate,
    CreatedByEmployeeKey,
    ModifiedByEmployeeKey
)
OUTPUT Inserted.DepartmentKey
VALUES
(@DepartmentName, @DivisionKey, @CreatedDate, @ModifiedDate, @CreatedByEmployeeKey, @ModifiedByEmployeeKey);";

            using (var con = OpenConnection())
            using (var cmd = new SqlCommand(sql, con))
            {
                cmd.Parameters.AddWithValue("@DepartmentName", department.DepartmentName);
                cmd.Parameters.AddWithValue("@DivisionKey", department.DivisionKey);
                cmd.Parameters.AddWithValue("@CreatedDate", DateTime.UtcNow);
                cmd.Parameters.AddWithValue("@ModifiedDate", DateTime.UtcNow);
                cmd.Parameters.AddWithValue("@CreatedByEmployeeKey", user.UserKey);
                cmd.Parameters.AddWithValue("@ModifiedByEmployeeKey", user.UserKey);
                return (int)cmd.ExecuteScalar();
            }
        }

        public Department GetDepartment(int departmentKey, User user)
        {
            const string sql = @"SELECT d.DepartmentKey,
       d.DepartmentName,
       d.DivisionKey,
       d.CreatedDate,
       d.ModifiedDate,
       d.CreatedByEmployeeKey,
       d.ModifiedByEmployeeKey FROM HR.Department d WHERE d.DepartmentKey = @DepartmentKey";

            using (var con = OpenConnection())
            using (var cmd = new SqlCommand(sql, con))
            {
                cmd.Parameters.AddWithValue("@DepartmentKey", departmentKey);
                using (var reader = cmd.ExecuteReader())
                {
                    reader.Read();

                    return new Department(reader);
                }
            }
        }

        public void UpdateDepartment(Department department, User user)
        {
            if (department == null)
                throw new ArgumentNullException(nameof(department), $"{nameof(department)} is null.");
            if (user == null)
                throw new ArgumentNullException(nameof(user), $"{nameof(user)} is null.");

            const string sql = @"UPDATE HR.Department SET
    DepartmentName = @DepartmentName,
    DivisionKey = @DivisionKey,
    ModifiedDate = @ModifiedDate,
    ModifiedByEmployeeKey = @ModifiedByEmployeeKey
WHERE DepartmentKey = @DepartmentKey;";

            using (var con = OpenConnection())
            using (var cmd = new SqlCommand(sql, con))
            {
                cmd.Parameters.AddWithValue("@DepartmentKey", department.DepartmentKey);
                cmd.Parameters.AddWithValue("@DepartmentName", department.DepartmentName);
                cmd.Parameters.AddWithValue("@DivisionKey", department.DivisionKey);
                cmd.Parameters.AddWithValue("@ModifiedDate", DateTime.UtcNow);
                cmd.Parameters.AddWithValue("@ModifiedByEmployeeKey", user.UserKey);
                cmd.ExecuteNonQuery();
            }
        }
    }

Chain

Chain requires that the audit columns be configured at the DataSource level.

Then to supply the actual user data, use dataSource.WithUser(user).

public class AuditColumnsScenario :
    IAuditColumnsScenario<Department>
{
    readonly SqlServerDataSource m_DataSource;

    public AuditColumnsScenario(SqlServerDataSource dataSource)
    {
        if (dataSource == null)
            throw new ArgumentNullException(nameof(dataSource), $"{nameof(dataSource)} is null.");

        //Normally this would be configured application-wide, not just for one repository instance.
        m_DataSource = dataSource.WithRules(
            new DateTimeRule("CreatedDate", DateTimeKind.Utc, OperationTypes.Insert),
            new DateTimeRule("ModifiedDate", DateTimeKind.Utc, OperationTypes.InsertOrUpdate),
            new UserDataRule("CreatedByEmployeeKey", "UserKey", OperationTypes.Insert),
            new UserDataRule("ModifiedByEmployeeKey", "UserKey", OperationTypes.InsertOrUpdate)
            );
    }

    public int CreateDepartment(Department department, User user)
    {
        return m_DataSource.WithUser(user).Insert(department).ToInt32().Execute();
    }

    public Department GetDepartment(int departmentKey, User user)
    {
        return m_DataSource.WithUser(user).GetByKey<Department>(departmentKey).ToObject<Department>().Execute();
    }

    public void UpdateDepartment(Department department, User user)
    {
        m_DataSource.WithUser(user).Update(department).Execute();
    }
}

Dapper

Dapper requires that audit columns be manually merged into the object being inserted/updated.

    public class AuditColumnsScenario : ScenarioBase, IAuditColumnsScenario<Department>
    {
        public AuditColumnsScenario(string connectionString) : base(connectionString)
        { }

        public int CreateDepartment(Department department, User user)
        {
            if (department == null)
                throw new ArgumentNullException(nameof(department), $"{nameof(department)} is null.");
            if (user == null)
                throw new ArgumentNullException(nameof(user), $"{nameof(user)} is null.");

            //Manually apply the audit columns
            department.CreatedDate = DateTime.UtcNow;
            department.ModifiedDate = DateTime.UtcNow;
            department.CreatedByEmployeeKey = user.UserKey;
            department.ModifiedByEmployeeKey = user.UserKey;

            const string sql = @"INSERT INTO HR.Department
(
    DepartmentName,
    DivisionKey,
    CreatedDate,
    ModifiedDate,
    CreatedByEmployeeKey,
    ModifiedByEmployeeKey
)
OUTPUT Inserted.DepartmentKey
VALUES
(@DepartmentName, @DivisionKey, @CreatedDate, @ModifiedDate, @CreatedByEmployeeKey, @ModifiedByEmployeeKey);";

            using (var con = OpenConnection())
                return con.ExecuteScalar<int>(sql, department);
        }

        public Department GetDepartment(int departmentKey, User user)
        {
            const string sql = @"SELECT d.DepartmentKey,
       d.DepartmentName,
       d.DivisionKey,
       d.CreatedDate,
       d.ModifiedDate,
       d.CreatedByEmployeeKey,
       d.ModifiedByEmployeeKey FROM HR.Department d WHERE d.DepartmentKey = @DepartmentKey";

            using (var con = OpenConnection())
                return con.QuerySingle<Department>(sql, new { departmentKey });
        }

        public void UpdateDepartment(Department department, User user)
        {
            if (department == null)
                throw new ArgumentNullException(nameof(department), $"{nameof(department)} is null.");
            if (user == null)
                throw new ArgumentNullException(nameof(user), $"{nameof(user)} is null.");

            //Manually apply the audit columns
            department.ModifiedDate = DateTime.UtcNow;
            department.ModifiedByEmployeeKey = user.UserKey;

            const string sql = @"UPDATE HR.Department SET
    DepartmentName = @DepartmentName,
    DivisionKey = @DivisionKey,
    ModifiedDate = @ModifiedDate,
    ModifiedByEmployeeKey = @ModifiedByEmployeeKey
WHERE DepartmentKey = @DepartmentKey;";

            using (var con = OpenConnection())
                con.Execute(sql, department);
        }
    }

DbConnector

DbConnector requires audit columns to be manually merged into the object being inserted/updated.

public class AuditColumnsScenario : ScenarioBase, IAuditColumnsScenario<Department>
{
    public AuditColumnsScenario(string connectionString) : base(connectionString)
    { }

    public int CreateDepartment(Department department, User user)
    {
        if (department == null)
            throw new ArgumentNullException(nameof(department), $"{nameof(department)} is null.");
        if (user == null)
            throw new ArgumentNullException(nameof(user), $"{nameof(user)} is null.");

        //Manually apply the audit columns
        var utcNow = DateTime.UtcNow;

        department.CreatedDate = utcNow;
        department.ModifiedDate = utcNow;
        department.CreatedByEmployeeKey = user.UserKey;
        department.ModifiedByEmployeeKey = user.UserKey;

        const string sql = @"INSERT INTO HR.Department
        (
            DepartmentName,
            DivisionKey,
            CreatedDate,
            ModifiedDate,
            CreatedByEmployeeKey,
            ModifiedByEmployeeKey
        )
        OUTPUT Inserted.DepartmentKey
        VALUES
        (@DepartmentName, @DivisionKey, @CreatedDate, @ModifiedDate, @CreatedByEmployeeKey, @ModifiedByEmployeeKey);";

        return DbConnector.Scalar<int>(sql, department).Execute();
    }

    public Department GetDepartment(int departmentKey, User user)
    {
        const string sql = @"SELECT 
            d.DepartmentKey,
            d.DepartmentName,
            d.DivisionKey,
            d.CreatedDate,
            d.ModifiedDate,
            d.CreatedByEmployeeKey,
            d.ModifiedByEmployeeKey 
        FROM HR.Department d 
        WHERE d.DepartmentKey = @departmentKey;";

        return DbConnector.ReadSingle<Department>(sql, new { departmentKey }).Execute();
    }

    public void UpdateDepartment(Department department, User user)
    {
        if (department == null)
            throw new ArgumentNullException(nameof(department), $"{nameof(department)} is null.");
        if (user == null)
            throw new ArgumentNullException(nameof(user), $"{nameof(user)} is null.");

        //Manually apply the audit columns
        department.ModifiedDate = DateTime.UtcNow;
        department.ModifiedByEmployeeKey = user.UserKey;

        const string sql = @"UPDATE HR.Department SET
            DepartmentName = @DepartmentName,
            DivisionKey = @DivisionKey,
            ModifiedDate = @ModifiedDate,
            ModifiedByEmployeeKey = @ModifiedByEmployeeKey
        WHERE DepartmentKey = @DepartmentKey;";

        DbConnector.NonQuery(sql, department).Execute();
    }
}

Entity Framework 6

To generalize audit column management in Entity Framework, create an interface with the list of audit columns. Then overide the SaveChanges method to provide the audit values.

public interface IAuditableEntity
{
    DateTime? CreatedDate { get; set; }
    DateTime? ModifiedDate { get; set; }
    int? CreatedByEmployeeKey { get; set; }
    int? ModifiedByEmployeeKey { get; set; }
}

public override int SaveChanges()
{
    // Get added entries
    var addedEntryCollection = ChangeTracker.Entries<IAuditableEntity>()
       .Where(p => p.State == EntityState.Added)
       .Select(p => p.Entity);

    // Get modified entries
    var modifiedEntryCollection = ChangeTracker.Entries<IAuditableEntity>()
      .Where(p => p.State == EntityState.Modified)
      .Select(p => p.Entity);

    // Set audit fields of added entries
    foreach (var addedEntity in addedEntryCollection)
    {
        addedEntity.CreatedDate = DateTime.Now;
        addedEntity.CreatedByEmployeeKey = m_User.UserKey;
        addedEntity.ModifiedDate = DateTime.Now;
        addedEntity.ModifiedByEmployeeKey = m_User.UserKey;
    }

    // Set audit fields of modified entries
    foreach (var modifiedEntity in modifiedEntryCollection)
    {
        modifiedEntity.ModifiedDate = DateTime.Now;
        modifiedEntity.ModifiedByEmployeeKey = m_User.UserKey;
    }

    return base.SaveChanges();
}

public class AuditColumnsScenario : IAuditColumnsScenario<Department>
{
    private Func<User, OrmCookbookContextWithUser> CreateDbContext;

    public AuditColumnsScenario(Func<User, OrmCookbookContextWithUser> dBContextFactory)
    {
        CreateDbContext = dBContextFactory;
    }

    public int CreateDepartment(Department department, User user)
    {
        if (department == null)
            throw new ArgumentNullException(nameof(department), $"{nameof(department)} is null.");

        using (var context = CreateDbContext(user))
        {
            context.Department.Add(department);
            context.SaveChanges();
            return department.DepartmentKey;
        }
    }

    public Department GetDepartment(int departmentKey, User user)
    {
        using (var context = CreateDbContext(user))
        {
            return context.Department.Find(departmentKey);
        }
    }

    public void UpdateDepartment(Department department, User user)
    {
        if (department == null)
            throw new ArgumentNullException(nameof(department), $"{nameof(department)} is null.");

        using (var context = CreateDbContext(user))
        {
            context.Entry(department).State = EntityState.Modified;
            context.SaveChanges();
        }
    }
}

Entity Framework Core

To generalize audit column management in EF Core, create an interface with the list of audit columns. Then overide the SaveChanges method to provide the audit values.

public interface IAuditableEntity
{
    DateTime? CreatedDate { get; set; }
    DateTime? ModifiedDate { get; set; }
    int? CreatedByEmployeeKey { get; set; }
    int? ModifiedByEmployeeKey { get; set; }
}

public override int SaveChanges()
{
    // Get added entries
    var addedEntryCollection = ChangeTracker.Entries<IAuditableEntity>()
       .Where(p => p.State == EntityState.Added)
       .Select(p => p.Entity);

    // Get modified entries
    var modifiedEntryCollection = ChangeTracker.Entries<IAuditableEntity>()
      .Where(p => p.State == EntityState.Modified)
      .Select(p => p.Entity);

    // Set audit fields of added entries
    foreach (var addedEntity in addedEntryCollection)
    {
        addedEntity.CreatedDate = DateTime.UtcNow;
        addedEntity.CreatedByEmployeeKey = m_User.UserKey;
        addedEntity.ModifiedDate = DateTime.UtcNow;
        addedEntity.ModifiedByEmployeeKey = m_User.UserKey;
    }

    // Set audit fields of modified entries
    foreach (var modifiedEntity in modifiedEntryCollection)
    {
        modifiedEntity.ModifiedDate = DateTime.UtcNow;
        modifiedEntity.ModifiedByEmployeeKey = m_User.UserKey;
    }

    return base.SaveChanges();
}

public class AuditColumnsScenario : IAuditColumnsScenario<Department>
{
    private Func<User, OrmCookbookContextWithUser> CreateDbContext;

    public AuditColumnsScenario(Func<User, OrmCookbookContextWithUser> dBContextFactory)
    {
        CreateDbContext = dBContextFactory;
    }

    public int CreateDepartment(Department department, User user)
    {
        if (department == null)
            throw new ArgumentNullException(nameof(department), $"{nameof(department)} is null.");

        using (var context = CreateDbContext(user))
        {
            context.Departments.Add(department);
            context.SaveChanges();
            return department.DepartmentKey;
        }
    }

    public Department? GetDepartment(int departmentKey, User user)
    {
        using (var context = CreateDbContext(user))
        {
            return context.Departments.Find(departmentKey);
        }
    }

    public void UpdateDepartment(Department department, User user)
    {
        if (department == null)
            throw new ArgumentNullException(nameof(department), $"{nameof(department)} is null.");

        using (var context = CreateDbContext(user))
        {
            context.Entry(department).State = EntityState.Modified;
            context.SaveChanges();
        }
    }
}

LINQ to DB

TODO

LLBLGen Pro

LLBLGen Pro has a built-in auditing system that works with separated Auditor classes which are provided by the developer and which are injected at runtime into the entity instances. These auditors act like recorders of auditing information on various actions and can provide new entity instances (or existing ones) which are then persisted in the same transaction.

The system is mainly designed to store auditing information separately from the audited entities, however it's also usable in the scenario at hand here: setting auditing information in the entity being audited. The following Auditor class is used for auditing on a DepartmentEntity instance:

/// <summary>
/// Auditor class which is used for auditing on the DepartmentEntity.
/// It's injected into the DepartmentEntity instances using the built-in
/// DI feature of LLBLGen Pro.
/// </summary>
[DependencyInjectionInfo(typeof(DepartmentEntity), "AuditorToUse")]
public class DepartmentAuditor : AuditorBase
{
    private List<DepartmentEntity> _toUpdate;

    public DepartmentAuditor()
    {
        _toUpdate = new List<DepartmentEntity>();
    }


    public override void AuditInsertOfNewEntity(IEntityCore entity)
    {
        var auditedAsDepartment = entity as DepartmentEntity;
        if(auditedAsDepartment == null)
        {
            return;
        }

        var createdDate = DateTime.Now;
        auditedAsDepartment.CreatedDate = createdDate;
        auditedAsDepartment.ModifiedDate = createdDate;
        auditedAsDepartment.CreatedByEmployeeKey = ActiveEmployee;
        auditedAsDepartment.ModifiedByEmployeeKey = ActiveEmployee;
        _toUpdate.Add(auditedAsDepartment);
    }


    public override void AuditUpdateOfExistingEntity(IEntityCore entity)
    {
        var auditedAsDepartment = entity as DepartmentEntity;
        if(auditedAsDepartment == null)
        {
            return;
        }

        auditedAsDepartment.ModifiedDate = DateTime.Now;
        auditedAsDepartment.ModifiedByEmployeeKey = ActiveEmployee;
        // as we've modified the department entity we have to pass it back 
        // as an entity to the caller so it can be saved again. Typically
        // a separated audit entity should be created here but for the 
        // scenario here the information is updated inside the entity itself. 
        _toUpdate.Add(auditedAsDepartment);
    }


    public override bool RequiresTransactionForAuditEntities(SingleStatementQueryAction actionToStart)
    {
        // if we have entities of our own, we have to tell the caller that so it can create a transaction 
        return _toUpdate.Count > 0 && 
               (actionToStart == SingleStatementQueryAction.ExistingEntityUpdate ||
                actionToStart == SingleStatementQueryAction.NewEntityInsert);
    }


    public override void TransactionCommitted()
    {
        _toUpdate.Clear();
    }


    public override IList GetAuditEntitiesToSave()
    {
        // if we've modified entities (or created entities of our own), we have to pass them back
        // so they can be included in the transaction. 
        return _toUpdate;
    }


    /// <summary>
    /// The pk of the employee entity to assign to any department audited by this auditor.
    /// </summary>
    public int ActiveEmployee { get; set; }
}

For every instance of a DepartmentEntity, an instance of this class is injected through the built-in Dependency Injection. This is setup in the AssemblyInit method of the Recipes code:

RuntimeConfiguration.SetDependencyInjectionInfo(
    new List<Assembly>() { typeof(DepartmentAuditor).Assembly}, null);

Usually, user info is passed through e.g. a ThreadPrinciple however for simplicity the user PK is passed directly to the auditor instance of the entity in the scenario as is shown in the scenario code below:

public class AuditColumnsScenario : IAuditColumnsScenario<DepartmentEntity>
{
    public int CreateDepartment(DepartmentEntity department, User user)
    {
        if(department == null) 
            throw new ArgumentNullException(nameof(department), $"{nameof(department)} is null.");
        if(user == null) 
            throw new ArgumentNullException(nameof(user), $"{nameof(user)} is null.");

        using(var adapter = new DataAccessAdapter())
        {
            ConfigureAuditor(department, user);
            adapter.SaveEntity(department);
            return department.DepartmentKey;
        }
    }


    public DepartmentEntity GetDepartment(int departmentKey, User user)
    {
        using(var adapter = new DataAccessAdapter())
        {
            return adapter.FetchNewEntity<DepartmentEntity>(
                                new RelationPredicateBucket(DepartmentFields.DepartmentKey
                                                                            .Equal(departmentKey)));
        }
    }


    public void UpdateDepartment(DepartmentEntity department, User user)
    {
        if(department == null) 
            throw new ArgumentNullException(nameof(department), $"{nameof(department)} is null.");
        if(user == null) 
            throw new ArgumentNullException(nameof(user), $"{nameof(user)} is null.");

        using(var adapter = new DataAccessAdapter())
        {
            ConfigureAuditor(department, user);
            adapter.SaveEntity(department);
        }
    }


    private void ConfigureAuditor(DepartmentEntity department, User user)
    {
        ((DepartmentAuditor)department.AuditorToUse).ActiveEmployee = user.UserKey;
    }
}

For further information about the Auditing system in LLBLGen Pro: Auditing in the official docs

NHibernate

TODO

RepoDb

TODO

ServiceStack

TODO