So yesterday I was coding a User Story about reporting audit information for the project I am working on. This project basically extends the ERP application called Macola (don’t get me started). What we are building is an extended properties application and we are auditing each change to an extended property for SOX compliance.
So we are using a class called (funny enough) ExtendedProperty. We have a collection of these objects to fill our grid using a List(of ExtendedProperty). We were auditing each save in the Save method of each individual ExtendProperty. This created a ton of audit entries in the database. We decided to combine these into a batch audit at the collection level to mitigate the number of entries and use the xml data type in SQL Server.
So our Audit objects began to take shape and now look like this:
An AuditEntry has a one to many relationship to an AuditChange. And an AuditChange has a many to many relationship to AuditColumn as well as AuditKey. Each of the objects overrides the .ToString function to return xml to be stored in the database.
The metadata in AuditEntry is in separate columns in the database and AuditChange, AuditColumn and AuditKey are all stored in a single xml column. This gives us great flexibility in what we can audit and how much space it takes up.
How this relates to an Extended Property is that each property is responsible for its own audit unless it is in a collection of changes. So we had to change how we accessed the List(of ExtendedProperty) in the containing class. We could not have a List anymore because it became difficult to act on that list as a whole. This caused us to create an ExtendedProperties collection class that inherits from List(of ExtendedProperty). This class gives us the ability to act on the collection as a whole but still allows us the functionality of a List(of T).
The results of this audit can be returned flattened using xquery in SQL Server. It can be a little complicated when trying to compare values (sql:parameter(“parametername”) is your friend in xquery). But it makes life easier when batching audit entries.
Cool Visual Studio 2010 feature
As an aside, while I was coding the audit classes above I discovered a GREAT feature in VS2010. First I created a file for the class, then I started coding the public properties of the class. I did not create the constructor of the class yet. I went to a calling class and instantiated this new class and put the arguments in the call. There appeared a blue squiggly line below the code with the little red block in the bottom right.
When I click on the little red block it tells me it can create the constructor in the class.
So I tell it to go ahead and do that by clicking on the link. Now keep in mind I had already created the public properties for this class that will expose the arguments in the constructor. So what did Visual Studio do? It knew which arguments to assign to the private class variables automatically and wrote the constructor for me and assigned the arguments to the variables.
Sub New(ByVal userName As String, ByVal propertyName As String, ByVal operation As String, ByVal originalValue As String, ByVal newValue As String, ByVal timeStamp As Date)
' TODO: Complete member initialization
_userName = userName
_propertyName = propertyName
_operation = operation
_originalValue = originalValue
_newValue = newValue
_timeStamp = timeStamp
End Sub
Cool!
till next time…
Technorati Tags: Visual Studio,User Stories,Audit
