Audit tracking

Would it be possible to add a feature that would allow for audit tracking at the row level? I have a couple of tables that have CreatedBy/CreatedDate and ModifiedBy/ModifiedDate. It would be nice to have a simple hook available to allow for the setting of a username, and then the entity could figure out how to set the CreatedBy and  Modified fields.

Additionally we could make the tables that require auditing just like enums tables are selected. As far as I'm concerned the fieldnames could be dictated just as long as the datetime fields could be configured to used UTCDates. 

Any suggestions as to where this could be done in the current build would be nice!

You can use the DataRequesting & DataRequested events:

http://community.codesmithtools.com/search/SearchResults.aspx?q=DataRequesting+AND+groupid%3a11&o=Relevance

http://community.codesmithtools.com/search/SearchResults.aspx?q=DataRequested+AND+groupid%3a11&o=Relevance
 

Thanks,

 A quick question, been trying to find the right location to register and handle the event, I was thinking it could be done in the provider class however, it seems that the event is not handled until after the fact, and I get an error since I have the CreatedBy and ModifiedBy fields not nullable in the database. If I handle the event at the UI level it seems to behave.

Any suggestions?

Hi

I created a partial class in my "Custom Code" folder and added a constructor like below and it works fine for me:

        /// <summary>
        /// Initializes a new instance of the <see cref="T:EntityProviderBase"/> class.
        /// </summary>
        protected EntityProviderBase() {
            this.DataRequesting += new DataRequestingEventHandler(DataRequestingHandler);
        }
 

why not just save that information as part of your save routine? Since its already part of your object graph its pretty simple. then if you want a central repository for saving all audits you simply wire in a trigger on the tables you are auditing. I can post the trigger we use tomorrow if you would like.

so it would look like this kinda...

 data, data, created by, created date

then the trigger would copy the values to an alternate table that would be a catchall history table.

Using the event process allows a more generic approach rather than putting the code into each save routine. Additionally triggers don't always have access to all the information you need (e.g. current user).

It would be really nice to be able to mark certain tables as needing Audit capabilities, this would then offer a stub for developers to fillin the gaps. Maybe add an option in CodeSmith to look for a certain field in the table, or allow us to select the tables similar to the enum

I guess I'm just lazy and want it all ;-)

personally I think you two are depending on a business layer to do what you should be handling by database design.

What I recommended isnt hard to implement, and its a lot easier than trying to wire up classes of objects to handle the implementation.

 CREATE TABLE [dbo].[Doctors](
 [ID] [bigint] IDENTITY(1,1) NOT NULL,
 [DisplayName] [varchar](210) COLLATE Latin1_General_CI_AI NULL,
 [FirstName] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
 [LastName] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
 [MiddleName] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
 [Credentials] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
 [GroupID] [bigint] NOT NULL CONSTRAINT [DF_Doctors_GroupID]  DEFAULT ((1)),
 [Active] [bit] NOT NULL CONSTRAINT [DF_Doctors_Active]  DEFAULT ((1)),
 [AddDate] [datetime] NULL,
 [AddUserID] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
 [ChangeDate] [datetime] NULL,
 [ChangeUserID] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
 CONSTRAINT [PK_Doctors] PRIMARY KEY CLUSTERED
(
 [ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

There you have generally what you need for auditing. In you save routine you pass in what you need from your currentprincipal and the Now() function..and you have row level audits.

then you create an onUpdate trigger that copies the original values to a dump table before they are overwritten.

Simple as boiling water.

I found this thread looking for a similar solution.  Given the lack, here's what I did. 

Here is another option (use the attached files):

1. create the journaling tables (you need to attach the journaling tables to a person id, i've included my person table in the code)
2. generate the net tiers
3. add the entity base code to your entity base
4. for the entities you want to journal, follow the template laid out in Person.cs
5. save the journal class somewhere
6. to journal an entity, send the entity and modification type (update/insert/delete) to the static journalling class

It wouldn't be hard to add this to the net tiers template.  This would of been easier to implement if there was a saving event :)

these files may help you (sorry, I just wrote a longer message and got an error)

this may help