Implementing the Audit Trail in 3 Tier ASP.NET Application

Introduction

In this article we are going to discuss about implementing the Audit Trail in 3 tier asp.net application. Also this can used for single tier application also.

3-Tier architecture

3-Tier architecture generally contains UI or Presentation Layer, Business Access Layer (BAL) or Business Logic Layer and Data Access Layer (DAL).

Presentation Layer (UI)

Presentation layer contains pages like .aspx or windows form where data is presented to the user or input is taken from the user.

Business Access Layer (BAL) or Business Logic Layer

BAL contains business logic, validations or calculations related with the data, if needed. I will call it Business Access Layer in my demo.

Data Access Layer (DAL)

DAL contains methods that helps business layer to connect the data and perform required action, might be returning data or manipulating data (insert, update, delete etc). For this demo application, I have taken a very simple example. I am assuming that I have to play with record of persons (FirstName, LastName, Age) and I will refer only these data through out this article.

Below are steps and procedure to implement the Audit Trail in our application.

I have seen few Audit Trails examples in web, they explained only about the screen name, Action, IP address and Time Stamp.

Here I have used What data changed, when and changes by Whom with IP address and TimeStamp

Step 1

Create table for Audit Trail (I have mentioned my table below) in SQL database

Table: SampleAuditTrail

• Id

• UserName - Who logged in

• ProcessName - What is the process they did

• Page – Page Name

• Operation - What operation like (Insert, Delete, Update)

• Description

• IP Address

Step 2

Create Interface for AuditTrail (In DAL) and declare below function

Function Insert_AuditTrail(ByVal Data As tblAuditTrail) As IResult(Of String)

Step 3

Create Class for declared Interface(In DAL) and Implement the Declared function in the Class (Code Given Below)

Public Function Insert_AuditTrail(ByVal Data As tblAuditTrail) As IResult(Of String) Implements IDAOAuditTrailValues.Insert_AuditTrail

        Dim Db As New //Your DBContext
    Dim Res As IResult(Of String) = New QueryResult(Of String)
    Try
        Db.tblAuditTrails.InsertOnSubmit(Data)
        Db.SubmitChanges()
        Res.Output = (From var In Db.tblAuditTrails Where var.ProcessTime = Data.ProcessTime And var.UserName = Data.UserName And
var.Operation = Data.Operation).ToList(0).ID
        Res.Result = DALResult.Success
        Res.Message = Msg.CreateMessage(MessageKeys.MSG_SAVE, New String() {"Audit Trail"}) 
    Catch ex As Exception
        Dim dex As New DALException(Msg.CreateMessage(MessageKeys.MSG_SAVE, New String() {"AuditTrail", "Saved"}), ex)
        Res.Result = DALResult.Error
        Res.Message = dex.Message
        Res.Exception = dex
        Dim ep As New ExceptionPublisher()
    Finally
        Db.Dispose()
    End Try
    Return Res
End Function

Step 4

Create the Separate Function in DAL for getting Audit Trail values (Below are code)

Public Function GetAuditTrailInsertandUpdate(ByVal data As SampleAuditTrail, ByVal Operation As String) As IResult(Of String) Implements IDaoReviewer.GetAuditTrailInsertandUpdate

        Dim db As New CodePointDataContext
        Dim Res1 As IResult(Of String) = New QueryResult(Of String)
        Try 

            Dim strHostName = System.Net.Dns.GetHostName

            Dim ta As New tblAuditTrail
            ta.UserName = HttpContext.Current.Session.Item(2).ToString
            ta.Operation = "Update"
            ta.Page = "Page Name"
            ta.IPAddress = HttpContext.Current.Request.ServerVariables("REMOTE_ADDR")
            ta.ProcessTime = DateTime.Now
            For Each a In GetType(tblname).GetProperties()
                If a.Name.ToLower <> "id" Then                  
                    If a.PropertyType.IsValueType = True Or a.PropertyType.Equals(GetType(String)) Then
                        If a.GetValue(Data, Nothing) = Nothing Then
                            ta.Description += a.Name + ":" + " " & vbCrLf
                        Else
                            ta.Description += a.Name + ":" + a.GetValue(data, Nothing).ToString & vbCrLf
                        End If
                    End If
                End If
            Next 

            Dim res1 = (New daoAuditTrailValues).Insert_AuditTrail(ta)

 Catch ex As Exception       
End Try
        Return Res1
    End Function

Step 5

Now we need to call this function in every DAL function which is (Insert, Delete, and Update Methods)

Dim operation = "Insert"

Dim res1 = GetAuditTrailInsertandUpdate(p, operation)                                 

here p is refers which data came from function

Like this we can call for Update and Delete Function

Step 6

Output we will receive like this,

Sno	Username	Time	Form Name	Action	Description	IPAddress
31	John	03/13/2013	User Creation	Insert	Name: Arun UserID: 102 Address:Chennai Gender:Male Remarks: Nil	192.168.0.88

Conclusion

This will be the simplest method to implement with all application. Any Suggestion or feedback is highly welcome.