How to Create a Simple Audit Trail (Change Log) in LightSwitch

How to Create a Simple Audit Trail (Change Log) in LightSwitch

Rate This
  • Comments 12

A common requirement in business applications is to capture changes to data records and save them in a history table of some sort. For instance, when an employee’s HR record is changed, you need to log the original and new field values as well as who made the changes so that there is a visible audit trail. Typically you want to log changes to records that are updated, inserted and deleted. In this post I’ll show you how you can create a simple audit trail to track changes to records in Visual Studio LightSwitch.

Create a Logging Table

The first thing to do is to create a table that stores the changes. Let’s say we already have an application with an Employee table. We want to be able to capture any updates, inserts or deletes to this table. The first thing to do is add a table that captures the changes. Add a new table to your application using the Data Designer. I’ll name it EmployeeChange and it will have the following fields:

Field Type
ChangeType String (required)
ChangedBy String (required)
OriginalValues String (not required)
NewValues String (not required)
Updated Date Time (required)

Then in the property window under the Appearance section, set the Summary Property of the EmployeeChange table to the “Updated” field. Next we need to add a new relationship to this table from our Employee table. Click the “Relationship…” button at the top of the designer and in the To column select the Employee table. Then set the Multiplicity to “Zero or One”. This means that our change log record doesn’t require an employee parent record. Why not? Well because in this example we want to also track deleted record information but we want to allow the deletion of the actual record in the Employee table. So we also need to set the On Delete Behavior to “Dissociate” so that when the employee record is deleted, our audit trail remains intact.

image

So here’s what the schema looks like now.

image

Write Code to Capture Changes

Next we need to write some code into the save pipeline on the data service to intercept when inserts, updates and deletes occur and write appropriate values to the EmployeeChange table. To do this, double-click on the Employee table in the Data Designer and then drop down the “Write Code” button in the top right. There you will see in the General Methods section _Updated / ing, _Inserted / ing, _Deleted / ing methods.

image

Select _Inserting, _Updating, and _Deleting methods to create the method stubs on your data service. Next we need to write some code that selects the storage properties of the Employee. Storage properties are the actual fields on the underlying table. In other words, we don’t want to include any calculated properties. For Employee records that are updated, we also need to compare the original and current values to determine if we should record a change. The way we do this is by drilling into the “Details” property on the Employee entity. Here you can get at a lot of the underlying framework methods and properties of LightSwitch’s data runtime. In our case I’m just recording the new and original values as strings by concatenating each field’s name and value and separating them by carriage return/line feeds (vbCrLf). You can choose to do this differently depending on how you want to log information to your change log table.

Private Sub Employees_Updating(entity As Employee)
   Dim change = entity.EmployeeChanges.AddNew()
   change.ChangeType = "Updated" change.Employee = entity
   change.Updated = Now()
   change.ChangedBy = Me.Application.User.FullName

   Dim newvals = "New Values:" 
Dim oldvals = "Original Values:"
For Each prop In entity.Details.Properties.All(). OfType(Of Microsoft.LightSwitch.Details.IEntityStorageProperty)() If prop.Name <> "Id" Then
If Not Object
.Equals(prop.Value, prop.OriginalValue) Then
oldvals += String.Format("{0}{1}: {2}", vbCrLf, prop.Name, prop.OriginalValue) newvals += String.Format("{0}{1}: {2}", vbCrLf, prop.Name, prop.Value) End If
End If
Next
change.OriginalValues = oldvals change.NewValues = newvals
End Sub
Private Sub Employees_Inserting(entity As Employee)
    Dim change = entity.EmployeeChanges.AddNew()
    change.ChangeType = "Inserted" change.Employee = entity
    change.Updated = Now()
    change.ChangedBy = Me.Application.User.FullName

    Dim newvals = "Inserted Values:" 
For Each prop In entity.Details.Properties.All(). OfType(Of Microsoft.LightSwitch.Details.IEntityStorageProperty)() If prop.Name <> "Id" Then
newvals += String.Format("{0}{1}: {2}", vbCrLf, prop.Name, prop.Value) End If
Next

change.NewValues = newvals
End Sub
Private Sub Employees_Deleting(entity As Employee)
    Dim change = entity.EmployeeChanges.AddNew()
    change.ChangeType = "Deleted" change.Updated = Now()
    change.ChangedBy = Me.Application.User.FullName

    Dim oldvals = "Deleted Values:" 
For Each prop In entity.Details.Properties.All(). OfType(Of Microsoft.LightSwitch.Details.IEntityStorageProperty)() If prop.Name <> "Id" Then
oldvals += String.Format("{0}{1}: {2}", vbCrLf, prop.Name, prop.Value) End If
Next

change.OriginalValues = oldvals End Sub

Create a Screen to View the Audit Trail

Last but not least we need to create a screen to view the audit trail. You don’t want users to modify these records, just view them, so it’s best to just pick the Search Data Screen template.

image

Then in the screen designer, select the Updated field and uncheck “Show as Link” so that users cannot modify records. Assuming that you already have an employee screen defined, hit F5 to run the application and open your employee screen. Make some changes to a current record, add a new one and delete another one to test all three scenarios. Then open your audit trail search screen and take a look at the results. You should see something similar to this:

image

If you needed to track changes on multiple tables then you could add relations from the single audit trail table to other tables the same way as Employee in this example. Experiment with the code provided in order to log changes exactly how you want. I hope this gives you some ideas on how you can create simple audit trails for your LightSwitch applications.

Enjoy!

Leave a Comment
  • Please add 3 and 3 and type the answer here:
  • Post
  • Very nice.

    What would be your recommendation to generalize that mechanism to all tables of an application (without managing 1 audit table per business table and duplicating the code in all entities)?

    LS is based on EF, right? Does it support inheritance?

    Best regards, Erix.

  • Hi Erix,

    If you needed to track changes on multiple tables then you could add relations from the single audit trail table to other tables the same way as Employee in this example.

    HTH,

    -Beth

  • amazing

  • Great Article as always - making ls so much easier to learn

    One way to make it more generic would be to use a class like this in the server usercode folder - this is only slightly tested and no doubt could be vastly improved

    Public Class ChangeLogger

       Public Enum ChangeType

           Insert

           update

           Delete

       End Enum

       Public Sub TrackChange(AppDataSvc As LightSwitchApplication.ApplicationDataService,

                              UpdateType As ChangeType,

                              entity As IEntityObject,

                              userId As String)

           Dim change = AppDataSvc.ChangeLogs.AddNew

           change.ChangeType = UpdateType.ToString

           change.ChangeDate = Now()

           change.ChangedBy = userId

           Dim newvals = "New Values:"

           Dim oldvals = "Original Values:"

           For Each prop In entity.Details.Properties.All().OfType(Of Microsoft.LightSwitch.Details.IEntityStorageProperty)()

               If prop.Name <> "Id" Then

                   Select Case UpdateType

                       Case ChangeType.Delete

                           If prop.Name <> "Id" Then

                               oldvals += String.Format("{0}{1}: {2}", vbCrLf, prop.Name, prop.Value)

                           End If

                           change.OriginalValues = oldvals

                           change.NewValues = "Deleted"

                       Case ChangeType.Insert

                           newvals += String.Format("{0}{1}: {2}", vbCrLf, prop.Name, prop.Value)

                           change.NewValues = newvals

                           change.OriginalValues = "Insert"

                       Case ChangeType.update

                           If Not Object.Equals(prop.Value, prop.OriginalValue) Then

                               oldvals += String.Format("{0}{1}: {2}", vbCrLf, prop.Name, prop.OriginalValue)

                               newvals += String.Format("{0}{1}: {2}", vbCrLf, prop.Name, prop.Value)

                               change.OriginalValues = oldvals

                               change.NewValues = newvals

                           End If

                   End Select

               End If

           Next

       End Sub

    End Class

    From the ing events call with appropriate values for example

    Private Sub Employees_Deleting(entity As Employee)

               Dim ob As New ChangeLogger

               ob.TrackChange(Me, ChangeLogger.ChangeType.Delete, entity, Me.Application.User.FullName)

           End Sub

    Private Sub Employees_Inserting(entity As Employee)

               Dim ob As New ChangeLogger

               ob.TrackChange(Me, ChangeLogger.ChangeType.Insert, entity, Me.Application.User.FullName)

    End Sub

    This code doesn't rely on a relationship between entities.

    just a thought :-)

  • how to create setup file?  and i got error that name is "private global::system.web.ui.scriptmanager.    How it solve?

  • hi beth.can i get mike's mune and mach shells coz i thought they were quite cute.pliz...:(

  • Hi Beth, that's for the pointer here. As for making it more generic, and also showing 'reference types' (i.e. your drop down list selection types changed) I've done this that other might like.

    Add a new Method on you ApplicationDataServerice to get the username

    Public ReadOnly Property CurrentUserName As String

               Get

                   Return Me.Application.User.Name

               End Get

           End Property

    Then Create the ChangeLogger.vb file and it looks like.

    Imports LightSwitchApplication

    Public Class ChangeLogger

       Private Class PropChangeInfo

           Public Property Name As String

           Public Property NewValue As String

           Public Property OldValue As String

       End Class

       ''' <summary>

       ''' Tracks the change.

       ''' </summary>

       ''' <param name="AppDataSvc">The app data SVC.</param>

       ''' <param name="entity">The entity.</param>

       Friend Shared Sub TrackChange(ByVal AppDataSvc As ApplicationDataService, ByVal entity As IEntityObject)

           'Create new row

           Dim change = AppDataSvc.AuditTrails.AddNew

           'Set table name

           change.Table = entity.Details.Name

           'Set change type

           change.ChangeType = entity.Details.EntityState.ToString

           'Set when the change happend

           change.Updated = DateTime.Now()

           'Set who changed it.

           change.ChangedBy = AppDataSvc.CurrentUserName

           Dim newValues As New System.Text.StringBuilder

           Dim oldValues As New System.Text.StringBuilder

           Select Case entity.Details.EntityState

               Case EntityState.Added

                   oldValues.Append("Didn't Exist")

                   For Each prop In entity.Details.Properties.All().OfType(Of Microsoft.LightSwitch.Details.IEntityStorageProperty)()

                       newValues.Append(String.Format("{1}: {2}{0}", vbCrLf, prop.Name, prop.Value))

                   Next

               Case EntityState.Deleted

                   For Each prop In entity.Details.Properties.All().OfType(Of Microsoft.LightSwitch.Details.IEntityStorageProperty)()

                       oldValues.Append(String.Format("{1}: {2}{0}", vbCrLf, prop.Name, prop.Value))

                   Next

                   newValues.Append("Deleted")

               Case EntityState.Modified

                   'Just get the properties that have actually changed, not al of them

                   Dim changedStorageProperties = From prop In entity.Details.Properties.All().OfType(Of Microsoft.LightSwitch.Details.IEntityStorageProperty)()

                                                   Where Not Object.Equals(prop.Value, prop.OriginalValue)

                                                   Select New PropChangeInfo With {.Name = prop.Name, .NewValue = prop.Value, .OldValue = prop.OriginalValue}

                   'Just get the properties that have actually changed, not

                   Dim changedRefrenceProperties = From prop In entity.Details.Properties.All().OfType(Of Microsoft.LightSwitch.Details.IEntityReferenceProperty)()

                                                   Where Not Object.Equals(prop.Value, prop.OriginalValue)

                                                   Let newValue As String = If(prop.Value IsNot Nothing, prop.Value.ToString, "No Value")

                                                   Let oldValue = If(prop.OriginalValue IsNot Nothing, prop.OriginalValue.ToString, "No Value")

                                                   Select New PropChangeInfo With {.Name = prop.Name, .NewValue = newValue, .OldValue = oldValue}

                   Dim changedProperties = changedRefrenceProperties.Concat(changedStorageProperties)

                   'Print some nice looking text

                   For Each prop In changedProperties

                       newValues.Append(String.Format("{1}: {2}{0}", vbCrLf, prop.Name, prop.NewValue))

                       oldValues.Append(String.Format("{1}: {2}{0}", vbCrLf, prop.Name, prop.OldValue))

                   Next

               Case Else

                   'not interested, shouldn't hapen.

           End Select

           'Set our values

           change.NewValues = newValues.ToString()

           change.OriginalValues = oldValues.ToString()

       End Sub

    End Class

    Then when it comes to using this, you need only write a simple line like this

    Private Sub WorkItems_Updating(entity As WorkItem)

               'Track the Changes

               ChangeLogger.TrackChange(Me, entity)

           End Sub

    I hope that helps, As I really needed to log when options from drop down lists are changed.

    You will notice there is no need to say if we are updating or inserting, we can get this information from: entity.Details.EntityState.ToString . We don't pass in the username because of the readonly property on the app service, so we just call that inside changelogger.

  • Hi Adam,

    I would really like to implement your code but I am falling at the first hurdle.

    Where do I create the Public ReadOnly Property CurrentUserName?

    thanks

  • Can the code be available in C# and run in VS 2010 MVC4

  • Hi

    This is really cool, and I have been working throuigh this example, but I'm getting the following error when I try to delete a record, any ideas as to where I should start looking,

    System.ArgumentException was unhandled by user code

     HResult=-2147024809

     Message=Reference properties cannot be set to deleted or discarded entities.

    Parameter name: value

     ParamName=value

  • Is it possible to do something similar but for entities exposed by the SecurityData data service?

    Thx!

  • Great article. I have followed the instructions above and all is working (c#). However, I am receiving the exception "Reference properties cannot be set to deleted or discarded entities" when attempting to delete an entity.

    Paul were you ever able to resolve this?

    System.ArgumentException was unhandled by user code

     HResult=-2147024809

     Message=Reference properties cannot be set to deleted or discarded entities.

    Parameter name: value

     Source=Microsoft.LightSwitch

     ParamName=value

     StackTrace:

          at Microsoft.LightSwitch.Details.Framework.Base.EntityDetails`2.SetReferenceValue[T](TEntity entity, Entry entry, T value)

          at Microsoft.LightSwitch.Details.Framework.Base.EntityDetails`2.SetValue[T](TEntity entity, Entry entry, T value)

          at LightSwitchApplication.AuditItem.set_Supplier(Supplier value)

          at LightSwitchApplication.ApplicationDataService.InsertAuditTrail(IEntityObject entity)

          at LightSwitchApplication.ApplicationDataService.SaveChanges_Executing()

          at LightSwitchApplication.ApplicationDataService.DetailsClass.__SaveChanges_Executing(ApplicationDataService d, Object[] args)

          at Microsoft.LightSwitch.Details.Framework.Server.OperationEntry`1.<>c__DisplayClass5.<InvokeExecuting>b__4()

          at Microsoft.LightSwitch.Utilities.Internal.UserCodeHelper.CallUserCode(Type sourceType, String methodName, String instance, String operation, ILoggingContext context, Action action, String additionalText, Func`1 getCompletedMessage, Boolean tryHandleException, Boolean swallowException, Exception& exception)

     InnerException:

    Any thoughts on how to resolve this would be greatly appreciated.

    -Ray

Page 1 of 1 (12 items)