Using SQL Server or similar, you can setup a combination of triggers and stored procedures to automatically audit each change, but the simple approach presented here is to maintain the audit trail in application server code using a modified DbRepository pattern (as per NerdDinner). Once setup as described below, no other code changes or database changes are required (other than provision of appropriate views / reports etc. of the audit data).
Create an Audits Table
For example, using SQL Server:
Column Name | Data Type | Allow Nulls | Comments |
---|---|---|---|
AuditID | int | no | Primary key. Auto-incrementing. |
AuditNewState | nvarchar(MAX) | no | |
AuditOperation | nvarchar(10) | no | |
AuditPrimaryKey | int | no | This is a foreign key for one of your business tables. |
AuditTable | nvarchar(30) | no | The name of the business table for this item. |
AuditTime | datetime | no | The time of the change. |
AuditUser | int | no | The user instigating this change. |
Intercept Each Database Save
If you are using the DbRepository pattern described for NerdDinner, you can simply create a base Repository class for your app, and add a Save method to the base class:
public virtual void Save(int user) { try { if (user == 0) { // Don't audit these changes. DataContext.SubmitChanges(); } else { // Audit these changes. var deletes = DataContext.GetChangeSet().Deletes; var inserts = DataContext.GetChangeSet().Inserts; var updates = DataContext.GetChangeSet().Updates; DataContext.SubmitChanges(); if (deletes.Count() + inserts.Count() + updates.Count() > 0) AuditChanges(user, deletes, inserts, updates); } } catch (Exception e) { throw e; } }
The Save method is passed some User ID which is saved with the audit item. Passing a user ID of zero will suppress the auditing, for example if you don't want to audit when importing another database.
The AuditChanges method can be something like:
protected virtual bool AuditChanges(int user, IList<object> deletes, IList<object> inserts, IList<object> updates) { // Audit multiple change. var auditRepos = new AuditDbRepository(this); // Audit the deletes. foreach (var item in deletes) AuditChange(item, user, "delete", auditRepos); // Audit the inserts. foreach (var item in inserts) AuditChange(item, user, "insert", auditRepos); // Audit the updates. foreach (var item in updates) AuditChange(item, user, "update", auditRepos); auditRepos.Save(user); // OK. return true; }
and the AuditChange method could be:
protected virtual bool AuditChange(object item, int user, string operation, IAuditRepository auditRepos) { // Audit a single change. string typeName = item.GetType().Name.ToUpper(); string newState = ""; int primaryKey = 0; string table = ""; DateTime time = DateTime.Now; switch (typeName) { // Add all business classes to be audited. case "CUSTOMERS": table = "Customers"; primaryKey = ((Customer)item).CustomerID; newState = ((Customer)item).AuditState(); break; case "PRODUCTS": table = "Products"; primaryKey = ((Product)item).ProductID; newState = ((Product)item).AuditState(); break; } if (primaryKey == 0) { // Ignore this change. return true; } return AuditHelpers.AddItem(time, table, primaryKey, user, operation, newState, auditRepos); }
Add an Audit Item
Each audited database change causes one new item to be added to the Audits table. A new Audit object is created using the helper method below and added to the Audits repository.
namespace AuditHelpers { public static class AuditHelpers { public static bool AddItem(DateTime time, string table, int primaryKey, int user, string operation, string newState, IAuditRepository repository) { var audit = new Audit(); audit.AuditNewState = newState; audit.AuditOperation = operation; audit.AuditPrimaryKey = primaryKey; audit.AuditTable = table; audit.AuditTime = time; audit.AuditUser = user; repository.Add(audit); // OK. return true; } } }
Serialize a Record
Each business class to be audited should have an AuditState extension method, probably implemented in a helper class, e.g.
public static string AuditState(this Customer cust) { // Serialize the current field values of 'cust' for storage in an audit item. string result = "Name: " + cust.CustomerName + "\r\n"; if (!String.IsNullOrWhiteSpace(cust.CustomerAddress)) result += "Address: " + cust.CustomerAddress + "\r\n"; result += "Tel: " + cust.CustomerTel + "\r\n"; result += "Fax: " + cust.CustomerFax + "\r\n"; return result; }