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!

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?

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:

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.

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?

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

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.

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

Thanks Trent for the clarification. I'll give this a try. It should be exactly what I want.

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.

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 )