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;
}
50 ms lag
100 ms lag
200 ms lag
500 ms lag
1000 ms lag