Introduction
In certain domains, there is a frequent requirement to implement an audit trail of data. A good example of this would be a medical records application, where the data is critical, and any changes to it could have not only legal implications on the business, but also health consequences on the patient. This article describes a simple but effective implementation of an audit trail and data versioning system using C# reflection with data stored in an SQL database.
Screenshot showing the final result:
Setting Things Up
SQL Setup
To setup and test, we create two database tables. One stores simple "Person" information, the other stores "Audit trail / version" information:
Person "SampleData"
ID int
FirstName nvarchar(10)
LastName nvarchar(10)
DateOfBirth date
Deleted bit
In the sample data table, we indicate if a core record is live, or "deleted" using the "deleted" field. From a data management point of view, it can be cleaner to only flag critical records as deleted - we will see an implementation of this later in the article.
"Audit trail" Data
ID int
KeyFieldID int
AuditActionTypeENUM int
DateTimeStamp datetime
DataModel nvarchar(100)
Changes nvarchar(MAX)
ValueBefore nvarchar(MAX)
ValueAfter nvarchar(MAX)
In our audit trail table, we use the fields as follows:
- "
KeyFieldID" stores a link between the Person-SampleData.ID field - "
AuditActionTypeENUM" tells us what type of audit record this is (create, edit, delete) - "
DateTimeStamp" gives us a point in time when the event occurred - "
DataModel" is the name of the Data-Model/View-Model that the change occurred in that we are logging - "
Changes" is an XML/JSON representation of the delta/diff between the previous data-state and the change - "
ValueBefore/ValueAfter" stores an XML/JSON snapshot of the DataModel data before/after the change event
The ValueBefore/After is optional - depending on the complexity of the system, it may be useful to have a before/after snapshot to enable you to rebuild data on a granular level.
Basic Scaffolding
To test the system as designed, I created a simple MVC application that uses Entity Framework. I setup very basic controllers and data model methods to serve the index data up, and allow the crud process. There are also supporting ViewModels:
ViewModel
public class SampleDataModel
{
public int ID { get; set; }
public string FirstName { get; set; }
public string lastname { get; set; }
public DateTime DateOfBirth { get; set; }
public bool Deleted { get; set; }
...
}
Controllers
public ActionResult Edit(int id)
{
SampleDataModel SD = new SampleDataModel();
return View(SD.GetData(id));
}
public ActionResult Create()
{
SampleDataModel SD = new SampleDataModel();
SD.ID = -1;
SD.DateOfBirth = DateTime.Now.AddYears(-25);
return View("Edit", SD);
}
public void Delete(int id)
{
SampleDataModel SD = new SampleDataModel();
SD.DeleteRecord(id);
}
public ActionResult Save(SampleDataModel Rec)
{
SampleDataModel SD = new SampleDataModel();
if (Rec.ID == -1)
{
SD.CreateRecord(Rec);
}
else
{
SD.UpdateRecord(Rec);
}
return Redirect("/");
}
CRUD Methods
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();
}
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) {
dbRec.FirstName = Rec.FirstName;
dbRec.LastName = Rec.lastname;
dbRec.DateOfBirth = Rec.DateOfBirth;
ent.SaveChanges();
rslt = true;
}
return rslt;
}
public void DeleteRecord(int ID)
{
AuditTestEntities ent = new AuditTestEntities();
SampleData rec = ent.SampleData.FirstOrDefault(s => s.ID == ID);
if (rec != null)
{
rec.Deleted = true;
ent.SaveChanges();
}
}
For the UI example, I have tweaked the MVC default bootstrap giving a very basic EDIT and Index view:
The index view is built using MVC Razor syntax on a table, that is styled with boostrap. There are also three action buttons to show "Live records" (i.e.: non-deleted), all records, and to create a new record.
You will recall the "Deleted" field for the SampleData table. When we call the controller and subsequent model to load the data, we send back a list of records where the "deleted" flag is true or false.
public List<SampleDataModel> GetAllData(bool ShowDeleted)
{
List<SampleDataModel> rslt = new List<SampleDataModel>();
AuditTestEntities ent = new AuditTestEntities();
List<SampleData> SearchResults = new List<SampleData>();
if (ShowDeleted)
SearchResults = ent.SampleData.ToList();
else SearchResults = ent.SampleData.Where(s => s.Deleted == false).ToList();
foreach (var record in SearchResults)
{
SampleDataModel rec = new SampleDataModel();
rec.ID = record.ID;
rec.FirstName = record.FirstName;
rec.lastname = record.LastName;
rec.DateOfBirth = record.DateOfBirth;
rec.Deleted = record.Deleted;
rslt.Add(rec);
}
return rslt;
}
Using Razor syntax, when creating the index view, we can set the colour of a table row to highlight deleted records:
<table class='table table-condensed' >
<thead></thead>
@foreach (var rec in Model)
{
<tr id="@rec.ID" @(rec.Deleted == false ?
String.Empty : "class=alert-danger" )>
<td><a href="/home/edit/@rec.ID">Edit</a>
<a href="#"
onClick="DeleteRecord(@rec.ID)">Delete</a> </td>
<td>
@rec.FirstName
</td>
<td>
@rec.lastname
</td>
<td>
@rec.DateOfBirth.ToShortDateString()
</td>
<td><a href="#" onClick="GetAuditHistory(@rec.ID)">Audit</a></td>
</tr>
}
</table>
This outputs highlighting the record in a red colour:
Auditing
Once we have the scaffolding implemented, we can implement the auditing. The concept is simple - before we post a change to the database, we have a "before" and "after" knowledge of the state of the data. Since we are in C#, we can use reflection to examine the data object we have in the database, and compare it to the one we are about to post, and view the differences between the two.
I looked at writing my own reflection code to examine the before/after object state, and found numerous good starting points on slack. Having tried a few, and my own version, I decided to utilise an existing nuget package Compare net objects. It compares objects recursively so can handle quite complex object structures. This package is extremely useful and provides everything we need, its open source and saved me time #JobDone.
Using CompareObjects, here is the core code that generates the audit information and inserts it into the database.
In the "CreateAuditTrail" method, we send in the following parameters:
AuditActionType = Create/Delete/Update...KeyFieldID = Link to the table record this audit belongs toOldObject / NewObject = the existing (database) and new (ViewModel) states of the data before saving the update to the database
public void CreateAuditTrail
(AuditActionType Action, int KeyFieldID, Object OldObject, Object NewObject)
The first thing we do in the method is to compare the objects and get the difference between them. The first time I used the class, I thought it was not working as only one differeance was returned but I had sent in numerous. It turns out that by default, the class only sends back one difference (for testing), so we need to explicitly define a max number of differences to find. I set this to 99, but the value is up to your own needs.
CompareLogic compObjects = new CompareLogic();
compObjects.Config.MaxDifferences = 99;
The next step is to compare the objects, and iterate through the differences identified.
ComparisonResult compResult = compObjects.Compare(OldObject, NewObject);
List<AuditDelta> DeltaList = new List<AuditDelta>();
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:
| Field name | Value before | Value after |
| First name | Fred | Frederick |
| Last name | Flintstone | Forsyth |
In this case, we would have one AuditChange (the main change event), with a DateTimeStamp of now, and two change deltas, one with the firstname 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 details we require. (NB: CompareObjects places a field delimiter "." in front of field/property names .. I didn't want this so I remove 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 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) {
SampleDataModel OldRecord = new SampleDataModel();
OldRecord.ID = dbRec.ID;
OldRecord.FirstName = dbRec.FirstName;
OldRecord.lastname = dbRec.LastName;
OldRecord.DateOfBirth = dbRec.DateOfBirth;
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 (e.g.: in create, we have no prior data state, and in delete, 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();
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 bread-crumbs 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 = new 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 detail needed), to re-create a snapshop 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 and give it a vote at the top of the page!
Points of Interest / Considerations
- 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 fields are named (for display to the user) by using XML attribute decoration - this would be a good improvement on the implementation in this article.
- 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. - Where the example shows manual mapping between database record and
ViewModel record, it would be more efficent to use something like AutoMapper to achieve the same result in less code. - 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 name, class name, etc. - This implementation only caters to create/update/delete actions - it may also be useful for you to implement and audit 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.
History
- 17th August, 2015 - Version 1 published
