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!

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

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 )

you sir, are cruel....

Keith Chisarik

LOL....sorry, Keith...it was kindof funny