Click here to Skip to main content
13,734,713 members
Click here to Skip to main content
Add your own
alternative version

Stats

87.9K views
5.4K downloads
95 bookmarked
Posted 17 Aug 2015
Licenced CPOL

Audit trail and data versioning with C# and MVC

, 17 Aug 2015
Rate this:
Please Sign up or sign in to vote.
Method for implementing an audit-trail

Introduction

In certain domains, there is 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 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 implimentation 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 occured
"DataModel" is the name of the Data-Model/View-Model that the change occured 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" store 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; // indicates record not yet saved
            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(); // save first so we get back the dbRec.ID for audit tracking
       }

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" (ie: 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 recursivly so can handle quite complex object structures. This package is extremly 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 to
  • OldObject / 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 differance (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.

// get the differance
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, lets 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 detail we require.  (nb: CompareObjects places a field delmiter "." in front of field/property names .. I didnt 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 serialise.
 

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) {
        // 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 dont have either a before or an after value (eg: 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(); // 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 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">&times;</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 darta 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 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 fields are named (for display to the user) by using XML attribute decoration - this would be a good improvement on the implementaiton 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 flexability for audit history searching if it was to be a frequently used part of your solution.

(3) Where the example shows manual mapping between databae record and ViewModel record, it would be more efficent 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 beign stored. You could however choose to implement in some other manner, storing table name, class name, etc.

(5) This implementation only caters for 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.

 

History

17/Aug/2015 - Version 1 published

 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

AJSON
Engineer
United Kingdom United Kingdom
Architect/engineer, student, always learning and refactoring my wetware. Happiest tucking into a big bowl of c# or python sprinkled with a crisp topping of Javascript...started with a single ZX80 a lifetime ago, now happily explore, build and create on interweb scale...



  • 1 Apr 2017 - Awarded Microsoft MVP for 2017
  • 18 Mar 2017 - "Calculating distance using Google Maps in ASP.NET MVC" - article of the day on ASP.net
  • 31 Dec 2016 - "How to improve Single Page App and Hybrid mobile performance" - article of the day on ASP.net
  • 23 Oct 2016 - "Multi-user/resource web diary in C# MVC with repeat events" - article of the day on ASP.net
  • 7 Jun 2016 - "Upload large files to MVC / Web API using partitioning" - article of the day on ASP.net
  • 16 Sep 2015 - "Single Page Application with MVC and SammyJS" - article of the day on ASP.net
  • 01 Sep 2015 - "KnockoutJS nested arrays in MVC" - article of the day on ASP.net
  • 21 Aug 2015 - "Audit trail and data versioning with C# and MVC" - article of the day on ASP.net
  • 6 Oct 2014 - "Full Calendar – A Complete Web Diary System for jQuery and C# MVC" - article of the day on ASP.net



You may also be interested in...

Pro
Pro

Comments and Discussions

 
QuestionPlagiarism Pin
VivekRS29-Nov-16 6:00
memberVivekRS29-Nov-16 6:00 
AnswerRe: Plagiarism Pin
AJSON29-Nov-16 11:57
mvpAJSON29-Nov-16 11:57 
QuestionThank you Pin
kyrie6-Oct-16 4:50
memberkyrie6-Oct-16 4:50 
AnswerRe: Thank you Pin
AJSON11-Oct-16 2:56
mvpAJSON11-Oct-16 2:56 
SuggestionCan you provide the same with ASP.NET Web Forms? Pin
gandhichintan19-Jul-16 2:52
membergandhichintan19-Jul-16 2:52 
GeneralRe: Can you provide the same with ASP.NET Web Forms? Pin
AJSON31-Jul-16 6:06
mvpAJSON31-Jul-16 6:06 
GeneralRe: Can you provide the same with ASP.NET Web Forms? Pin
gandhichintan31-Jan-17 3:02
membergandhichintan31-Jan-17 3:02 
QuestionHi Pin
9-Feb-16 20:35
member9-Feb-16 20:35 
AnswerRe: Hi Pin
AJSON10-Feb-16 0:55
mvpAJSON10-Feb-16 0:55 
GeneralRe: Hi Pin
11-Feb-16 0:50
member11-Feb-16 0:50 
GeneralRe: Hi Pin
AJSON11-Feb-16 1:54
mvpAJSON11-Feb-16 1:54 
QuestionNice Article Pin
Santhakumar Munuswamy @ Chennai11-Sep-15 21:57
professionalSanthakumar Munuswamy @ Chennai11-Sep-15 21:57 
GeneralMy vote of 5 Pin
Humayun Kabir Mamun27-Aug-15 1:05
memberHumayun Kabir Mamun27-Aug-15 1:05 
QuestionIt was nice and one more suggestion Pin
Tridip Bhattacharjee24-Aug-15 2:38
professionalTridip Bhattacharjee24-Aug-15 2:38 
AnswerRe: It was nice and one more suggestion Pin
AJSON24-Aug-15 2:45
mentorAJSON24-Aug-15 2:45 
GeneralRe: It was nice and one more suggestion Pin
Tridip Bhattacharjee24-Aug-15 21:16
professionalTridip Bhattacharjee24-Aug-15 21:16 
GeneralRe: It was nice and one more suggestion Pin
AJSON24-Aug-15 22:50
mentorAJSON24-Aug-15 22:50 
yes, and the code you can download demonstrates its usage.
GeneralMy vote of 5 Pin
D V L23-Aug-15 19:21
professionalD V L23-Aug-15 19:21 
GeneralMy vote of 5 Pin
Duncan Edwards Jones20-Aug-15 7:52
professionalDuncan Edwards Jones20-Aug-15 7:52 
AnswerRe: My vote of 5 Pin
AJSON20-Aug-15 23:02
mentorAJSON20-Aug-15 23:02 
QuestionAudit Not Refreshing Pin
19-Aug-15 5:18
member19-Aug-15 5:18 
AnswerRe: Audit Not Refreshing Pin
AJSON19-Aug-15 5:32
mentorAJSON19-Aug-15 5:32 
GeneralRe: Audit Not Refreshing Pin
19-Aug-15 5:42
member19-Aug-15 5:42 
AnswerRe: Audit Not Refreshing Pin
AJSON19-Aug-15 5:47
mentorAJSON19-Aug-15 5:47 
GeneralRe: Audit Not Refreshing Pin
20-Aug-15 6:16
member20-Aug-15 6:16 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web05-2016 | 2.8.180920.1 | Last Updated 17 Aug 2015
Article Copyright 2015 by AJSON
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid