Audit Trail And Data Versioning With C# And MVC

In order to store the changes (deltas), I have created two helper classes. "AuditDelta" gives the individual difference between two field-level-value states (before and after), and "AuditChange" is the overall sequence of changes. For example, let's say we have a record with the following changes.

In this case, we would have one AuditChange (the main change event), with a DateTimeStamp of now, and two change deltas - one with the first name, changing from Fred to Frederick, the other with the Last name, changing from Flintstone to Forsyth.

The following classes represent the change and deltas.

public class AuditChange
{
    public string DateTimeStamp { get; set; }
    public AuditActionType AuditActionType { get; set; }
    public string AuditActionTypeName { get; set; }
    public List<AuditDelta> Changes { get; set; }

    public AuditChange()
    {
        Changes = new List<AuditDelta>();
    }
}
public class AuditDelta
{
    public string FieldName { get; set; }
    public string ValueBefore { get; set; }
    public string ValueAfter { get; set; }
}

Once CompareObjects has used its internal reflection code to compare the before/after objects, we can examine the results, and extract the detail we require. (nb: CompareObjects places a field delimiter "." in front of field/property names .. I didn't want this so I removed it).

foreach (var change in compResult.Differences)
{
    AuditDelta delta = new AuditDelta();
    if (change.PropertyName.Substring(0, 1) == ".")
        delta.FieldName = change.PropertyName.Substring(1, change.PropertyName.Length - 1);
    delta.ValueBefore = change.Object1Value;
    delta.ValueAfter = change.Object2Value;
    DeltaList.Add(delta);
}

Once we have our list of deltas, we can then save to our database, serializing the list of change deltas to the "changes" field. In this example, we are using JSON.net to serialize.

AuditTable audit = new AuditTable();
audit.AuditActionTypeENUM = (int)Action;
audit.DataModel = this.GetType().Name;
audit.DateTimeStamp = DateTime.Now;
audit.KeyFieldID = KeyFieldID;
audit.ValueBefore = JsonConvert.SerializeObject(OldObject);
audit.ValueAfter = JsonConvert.SerializeObject(NewObject);
audit.Changes = JsonConvert.SerializeObject(DeltaList);

AuditTestEntities ent = new AuditTestEntities();
ent.AuditTable.Add(audit);
ent.SaveChanges();

Every time we make a change to the data, we just need to call the CreateAuditTrail method, sending in the type of action (Create/Delete/Update) and the before/after values.

In UpdateRecord, we send in the *New* record (Rec) as a parameter and retrieve the old record from the database, then send both into our CreateAuditTrail method as generic objects.

public bool UpdateRecord(SampleDataModel Rec)
{
    bool rslt = false;
    AuditTestEntities ent = new AuditTestEntities();
    var dbRec = ent.SampleData.FirstOrDefault(s => s.ID == Rec.ID);
    if (dbRec != null)
    {
        // audit process 1 - gather old values
        SampleDataModel OldRecord = new SampleDataModel();
        OldRecord.ID = dbRec.ID; // copy data from DB to "OldRecord" ViewModel
        OldRecord.FirstName = dbRec.FirstName;
        OldRecord.lastname = dbRec.LastName;
        OldRecord.DateOfBirth = dbRec.DateOfBirth;
        // update the live record
        dbRec.FirstName = Rec.FirstName;
        dbRec.LastName = Rec.lastname;
        dbRec.DateOfBirth = Rec.DateOfBirth;
        ent.SaveChanges();
        CreateAuditTrail(AuditActionType.Update, Rec.ID, OldRecord, Rec);
        rslt = true;
    }
    return rslt;
}

In situations where we don't have either a before or an after value (eg: in create, we have no prior data state, and in deleted, we have no after state), we send in an empty object.

public void CreateRecord(SampleDataModel Rec)
{
    AuditTestEntities ent = new AuditTestEntities();
    SampleData dbRec = new SampleData();
    dbRec.FirstName = Rec.FirstName;
    dbRec.LastName = Rec.lastname;
    dbRec.DateOfBirth = Rec.DateOfBirth;
    ent.SampleData.Add(dbRec);
    ent.SaveChanges(); // save first so we get back the dbRec.ID for audit tracking
    SampleData DummyObject = new SampleData();

    CreateAuditTrail(AuditActionType.Create, dbRec.ID, DummyObject, dbRec);
}

public void DeleteRecord(int ID)
{
    AuditTestEntities ent = new AuditTestEntities();
    SampleData rec = ent.SampleData.FirstOrDefault(s => s.ID == ID);
    if (rec != null)
    {
        SampleData DummyObject = new SampleData();
        rec.Deleted = true;
        ent.SaveChanges();
        CreateAuditTrail(AuditActionType.Delete, ID, rec, DummyObject);
    }
}

Hansel and Gretel

So, we have our audit trail going into the database. Now, like the fairytale, we need to get those breadcrumbs out and show them to the user (but hopefully, our breadcrumbs will stay put!).

Server-side, we create a method that, for a given record-id, extracts the audit history, and orders the data with the latest change first.

public List<AuditChange> GetAudit(int ID)
{
    List<AuditChange> rslt = new List<AuditChange>();
    AuditTestEntities ent = new AuditTestEntities();
    var AuditTrail = ent.AuditTable.Where(s => s.KeyFieldID == ID).OrderByDescending(s => s.DateTimeStamp);
    var serializer = new XmlSerializer(typeof(AuditDelta));
    foreach (var record in AuditTrail)
    {
        AuditChange Change = new AuditChange();
        Change.DateTimeStamp = record.DateTimeStamp.ToString();
        Change.AuditActionType = (AuditActionType)record.AuditActionTypeENUM;
        Change.AuditActionTypeName = Enum.GetName(typeof(AuditActionType), record.AuditActionTypeENUM);
        List<AuditDelta> delta = JsonConvert.DeserializeObject<List<AuditDelta>>(record.Changes);
        Change.Changes.AddRange(delta);
        rslt.Add(Change);
    }  
    return rslt;
}

We also implement a Controller method to send this data back as a JSON result.

public JsonResult Audit(int id)
{
    SampleDataModel SD = new SampleDataModel();
    var AuditTrail = SD.GetAudit(id);
    return Json(AuditTrail, JsonRequestBehavior.AllowGet);
}

Client-side, we create a Modal popup form in Bootstrap with a DIV called "audit" that we will inject with the audit-trail data.

<div id="myModal" class="modal fade">
    <div class="modal-dialog">
        <div class="modal-content">
            <div class="modal-header">
                <button type="button" class="close" data-dismiss="modal" aria-hidden="true">×</button>
                <h4 class="modal-title">Audit history</h4>
            </div>
            <div class="modal-body">
                <div id="audit"></div>
            </div>
            <div class="modal-footer">
                <button type="button" class="btn btn-primary" data-dismiss="modal">Close</button>
            </div>
        </div>
    </div>
</div>

Attached to each data row, we have a JS function that calls the server-side code using AJAX.

<a href="#" onClick="GetAuditHistory(@rec.ID)">Audit</a>

The JavaScript code calls the server-side controller, passing in the record ID of the table row selected, and receives back a JSON array. It iterates through the array, building up a nicely formatted HTML table that gets displayed in the modal form.

function GetAuditHistory(recordID) {
    $("#audit").html("");
    var AuditDisplay = "<table class='table table-condensed' cellpadding='5'>";
    $.getJSON("/home/audit/" + recordID, function(AuditTrail) {
        for (var i = 0; i < AuditTrail.length; i++) {
            AuditDisplay = AuditDisplay + "<tr class='active'><td colspan='2'>Event date: " + AuditTrail[i].DateTimeStamp + "</td>";
            AuditDisplay = AuditDisplay + "<td>Action type: " + AuditTrail[i].AuditActionTypeName + "</td></tr>";
            AuditDisplay = AuditDisplay + "<tr class='text-warning'><td>Field name</td><td>Before change</td><td>After change</td></tr>";
            for (var j = 0; j < AuditTrail[i].Changes.length; j++) {
                AuditDisplay = AuditDisplay + "<tr>";
                AuditDisplay = AuditDisplay + "<td>" + AuditTrail[i].Changes[j].FieldName + "</td>";
                AuditDisplay = AuditDisplay + "<td>" + AuditTrail[i].Changes[j].ValueBefore + "</td>";
                AuditDisplay = AuditDisplay + "<td>" + AuditTrail[i].Changes[j].ValueAfter + "</td>";
                AuditDisplay = AuditDisplay + "</tr>";
            }
        }
        AuditDisplay = AuditDisplay + "</table>";
        $("#audit").html(AuditDisplay);
        $("#myModal").modal('show');
    });
}

Here is the final result showing the progression from create, to update, and finally delete of a record.

Summary

This article has described useful functionality for implementing an audit-trail system within a C# #-based system. It is based on the assumption that its primary use is for user/security audit and includes enough snapshot information to enable you (depending on the detail needed), to re-create a snapshot of a data record at a single point in time. Try it out yourself by downloading the SQL script and code.

If you find the article useful, please take a few seconds now to give it a vote at the top of the page.

Points of Interest/considerations

1. I have implemented this example using JSON - if you used XML instead, you could have more control over how the data is stored and how the fields are named (for displaying to the user) by using XML attribute decoration. This would be a good improvement on the implementation in this article.
2. The example in SQL is implemented with all of the changes in one field "Changes" - this could be implemented instead, with another relational table between AuditChanges and Deltas, giving further flexibility for audit history searching if it was to be a frequently used part of your solution.
3. Where the example shows manual mapping between the database record and the ViewModel record, it would be more efficient to use something like AutoMapper, to achieve the same result in less code.
4. Where I have a field "AuditActionTypeName" - this is auto-mapped to the Model/Object name passed into the create audit method. This is used to track the user view of data being stored. You could, however, choose to implement in some other manner, storing table names, class names, etc.
5. This implementation only caters to create/update/delete actions - it may also be useful for you to implement and audit of what user has viewed a particular record for security reasons. In this case, you would also need to record the UserID and perhaps other information such as IP address, machine name, etc.

There we go Happy auditing!