By Greg McGuffey - 10/23/2007
I'm attempting to implement auditing. I've got a bunch of sprocs that do a nice job of handling the database side of things, now I need to be able to instigate it from the app.
To that end I've subclassed the BusinessLayer object and overridden the Save and Delete methods. It now handles saving away an audit history item related to change, with user name and an audit comment and saves a record of the fields that were changed changed and what the old and new values were (using the sprocs mentioned above).
However, when I went to test this with a form that was using a MaintenanceFormToolstrip, it didn't work. After some looking I realized that is because the MFT is calling the BaseForm's Save/Delete methods, which call a the BO's Save/Delete, but it is called through a cast to BusinessLayer....so my code never gets called.
So, my question is, what would be the best way to optimize the use of the framework without having to reimplement everything? I could just do a custom toolstrip that calls the methods of the bo directly and handles editing status changes, but would loose all the other cool MTS goodness. On the other hand, messing with the BaseForm seems even worse...my brain is a bit fried, so I'm not seeing clearly.
Any suggestions?
Thanks!
|
By Greg McGuffey - 10/24/2007
It occurred to me that I could probably just handle the BeforeSave/AfterSave and BeforeDelete/AfterDelete events in my subclassed business layer (obvious now).
However, I'm not sure how to handle the issue that the BeforeDelete event can be canceled. I.e. if I use the MaintenanceFormToolstrip, it calls the form's delete, which asks for a confirmation of the delete (handled by BaseForm reacting to the BeforeDelete event). Now I could turn off AutoDeleteConfirmation for the form, but a month from now, after I've forgotten what I've done  , I could end up handling the BeforeDelete and mess up my auditing.
Is there a sure fire way of executing some code right before the delete is sent to the server?
|
By Trent L. Taylor - 10/24/2007
Since you are subclassing the BusinessLayer, just override the OnBeforeSave method and OnBeforeDelete method rather than handling the event so you can test on the cancellation of the event args. For example:Public Class MyBusinessLayer
Inherits MicroFour.StrataFrame.Business.BusinessLayer
Protected Overrides Sub OnBeforeDelete(ByVal e As MicroFour.StrataFrame.Business.BeforeDeleteEventArgs)
MyBase.OnBeforeDelete(e)
If Not e.Cancel Then
'-- Add your logic
End If
End Sub
End Class This way you are not relying on event execution order depending on when the handler was added and you can reliably test on the cancel arguments. Let me know if this is not what you were looking for. 
|
By Greg McGuffey - 10/24/2007
Maybe this is what I want...I'm kinda tired, so not all the neurons are firing...
So, if I override these methods, and the BaseForm is casting its BOs to BusinesLayer and calling save/delete, these methods are fired?
|
By Peter Jones - 10/24/2007
Hi Greg,For what it's worth - have you considered firing your audit procs from normal database triggers rather than from with the app. Cheers, Peter
|
By Trent L. Taylor - 10/25/2007
These are the methods that fire the events. The general purpose and structure of an On method is to fire the respective event. So the MyBase call will be where the events are actually raised and then your code below the MyBase call will be executed after all handlers have responded to the raised event.
|
By Greg McGuffey - 10/25/2007
have you considered firing your audit procs from normal database triggers rather than from with the app.
Yep. In fact that is what I'm replacing. The scope of the auditing is to know both some information about the change to data (who, when, type of change, comment about the change) and to store the old and new values related to the change. There are a few problems with triggers.
1. since I'm going to use a shared app user, the trigger won't know who the user is. I could add a dummy column to the table but there are other problems...
2. Because the trigger does a bunch of other updates to the audit tables, the SCOPE_IDENTITY() doesn't return the ID of newly added fields, thus breaking SF BOs.
3. I can't use sprocs for my CRUD because as far as I can tell, they don't like TEXT variables and I have lots of fields with TEXT fields. SQL Server 2005 would solve this (varchar(max)) but I can't migrate to it just yet 
So, as near as I can tell, I'm left with doing my CRUD using SQL, then having the app wrap audit code around it. I still want the db to do the actual work, as I don't want to increase the amount of data I'm sending. I.e. the app could figure out easily what the old/new values are and just directly update the audit tables, but that could potentially increase the data on the network by a factor of three.
Just in case anyone else is going down this road...hope this helps 
|
By Greg McGuffey - 10/25/2007
Trent L. Taylor (10/25/2007)
These are the methods that fire the events. The general purpose and structure of an On method is to fire the respective event. So the MyBase call will be where the events are actuallyraised and then your code below the MyBase call will be executed after all handlers have responded to the raised event.
Thanks Trent for the clarification. I'll give this a try. It should be exactly what I want. 
|
By StrataFrame Team - 10/25/2007
Don't forget, Greg, there are always managed stored procedures. You would be able to do everything you needed to in VB.So, you have an update sproc, or an insert sproc, do everything in VB and the business objects just think they're regular stored procedures, but it all runs on the database.
|
By Greg McGuffey - 10/25/2007
If I was using SQL Server 2005...
Life would be much easier with 2005, for a bunch of reasons.
But, on the brighter side, at least I'm not using DB2... 
(sorry Keith, could'nt help myself  )
|
By Keith Chisarik - 10/25/2007
But, on the brighter side, at least I'm not using DB2... (sorry Keith, could'nt help myself )
you sir, are cruel....
|
By Trent L. Taylor - 10/25/2007
LOL....sorry, Keith...it was kindof funny
|
By Andria Jensen - 10/31/2007
Is there auditing capability like what he wants in the Role-Based Security that you guys offer? I see mention of it, but I forget exactly how it works.
|
By Greg McGuffey - 10/31/2007
They mentioned this in class whenever it was that I was there  and as I recall, it isn't within the framework yet and it has a different purpose than I need. I'm pretty sure their are auditing simply tracks login/logout and data access, but no changes to data. I'm needing to track who and what the actual changes are to the data. They seem to be complimentary to each other. Steve did promise that their auditing would be cool though!
|
By Andria Jensen - 10/31/2007
Yeah, I need to keep track of the same things. Was/Is values of a field when it changes and who changed it. I thought that's what their auditing did, but (like you) it's been a while since I saw any of it in the class I took. And when I did see it, it wasn't even finished. Any of the SF guys want to comment on what exactly is available in the way of auditing??
|
By StrataFrame Team - 11/1/2007
The hooks are currently in place for the auditing, but there is no official word on when the module will be released. So, if you need auditing on just one or two tables, you'll want to program it in for now, but if you need auditing like that for your whole database, then you'll want to look at a 3rd party tool for SQL Server.http://sqlserver2000.databases.aspfaq.com/are-there-tools-available-for-auditing-changes-to-sql-server-data.html I've heard good things about the first one listed, ApexSQL, but I haven't heard of the others much.
|
|