次の方法で共有

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

  • [アーティクル]

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!

Comments

  • Anonymous
    June 22, 2011
    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.

  • Anonymous
    June 22, 2011
    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

  • Anonymous
    June 22, 2011
    amazing

  • Anonymous
    June 23, 2011
    Great Article as always - making ls so much easier to learnOne 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 improvedPublic 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 SubEnd ClassFrom the ing events call with appropriate values for examplePrivate Sub Employees_Deleting(entity As Employee)           Dim ob As New ChangeLogger           ob.TrackChange(Me, ChangeLogger.ChangeType.Delete, entity, Me.Application.User.FullName)       End SubPrivate Sub Employees_Inserting(entity As Employee)           Dim ob As New ChangeLogger           ob.TrackChange(Me, ChangeLogger.ChangeType.Insert, entity, Me.Application.User.FullName)End SubThis code doesn't rely on a relationship between entities.just a thought :-)

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

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

  • Anonymous
    November 24, 2011
    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 usernamePublic ReadOnly Property CurrentUserName As String           Get               Return Me.Application.User.Name           End Get       End PropertyThen Create the ChangeLogger.vb file and it looks like.Imports LightSwitchApplicationPublic 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 SubEnd ClassThen when it comes to using this, you need only write a simple line like thisPrivate Sub WorkItems_Updating(entity As WorkItem)           'Track the Changes           ChangeLogger.TrackChange(Me, entity)       End SubI 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.

  • Anonymous
    February 26, 2012
    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

  • Anonymous
    May 29, 2012
    Can the code be available in C# and run in VS 2010 MVC4

  • Anonymous
    June 20, 2012
    HiThis 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

  • Anonymous
    August 12, 2012
    Is it possible to do something similar but for entities exposed by the SecurityData data service?Thx!

  • Anonymous
    December 08, 2012
    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.EntityDetails2.SetReferenceValue[T](TEntity entity, Entry entry, T value)&nbsp; &nbsp; &nbsp; at Microsoft.LightSwitch.Details.Framework.Base.EntityDetails2.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.OperationEntry1.&lt;&gt;c__DisplayClass5.&lt;InvokeExecuting&gt;b__4()&nbsp; &nbsp; &nbsp; at Microsoft.LightSwitch.Utilities.Internal.UserCodeHelper.CallUserCode(Type sourceType, String methodName, String instance, String operation, ILoggingContext context, Action action, String additionalText, Func1 getCompletedMessage, Boolean tryHandleException, Boolean swallowException, Exception& exception) InnerException:Any thoughts on how to resolve this would be greatly appreciated.-Ray

  • Anonymous
    November 07, 2014
    Employees_Deleting method does not work for me :(  I'm using VS2013 and when deleting an employee, I get validation errors...and also see this in the output window: "A first chance exception of type 'System.ArgumentException' occurred in Microsoft.LightSwitch.dllReference properties cannot be set to deleted or discarded entities."Help!!!

  • Anonymous
    February 09, 2015
    I am getting the same ArgumentException that others are getting when attempting to delete a record. Any resolution?

  • Anonymous
    February 09, 2015
    The comment has been removed

  • Anonymous
    July 02, 2015
    Is it possible to show the original report after update, edit or delete entities ?

  • Anonymous
    September 28, 2015
    Can i get editor's username if i use "do not use authentification" option of my LS Project?

  • Anonymous
    January 21, 2016
    To avoid the 'Reference properties cannot be set to deleted or discarded entities' issue, change deleting handler to: Private Sub Employees_Deleting(entity As Employee)    Dim change = me.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

  • Anonymous
    April 21, 2017
    Very soon this site will be famous among all blog viewers, due to it's good articles or reviews

  • Anonymous
    February 19, 2018
    Good Day!Very Nice .Can you teach me how to create Audit Trail in VB.Net using Microsoft Access.Thanks in advance.