How to get the "Created/Updated By" User Info

dagware posted on Wednesday, August 01, 2007

All my BOs have "CreatedBy" and "UpdatedBy" fields in them. How do people usually handle setting these fields? It probably shouldn't be up to the consumer (application) to remember to set them. Should the BOs themselves set these values somehow, based perhaps on some helper object that contains information about who the current user is?

I'm just looking for some ideas on how other people have handled this. Any help will be appreciated!

Dan

JonStonecash replied on Wednesday, August 01, 2007

The application that I am working on right now pulls the "UserID" from a shared/static method on the custom identity class that we use to represent the user login. We set the values in the DoUpdateInsert logic within each class. If the shared method cannot determine the "UerID", it returns a "-1" value. In our case there is no referential integrity on these values; the security values are stored in a different database that is shared by several other applications.

I should also note that we are making fairly extensive use of code generation. Once we decided on this approach (after maybe 30 seconds of thought), we set up the templates and generated all of the code. Setting the IDs takes maybe four lines of code. Not a particularly big deal.

Jon Stonecash

triplea replied on Thursday, August 02, 2007

I also do it in the DoUpdateInsert but here is how I would really like to do it:

All my tables have .ModificationUser and .ModificationTimeStamp fields which are readonly within my objects.
Use Windows Authentication to log to the SQL Server and use stored procs to perform database interaction.
Each table has a trigger which is fired on Insert/Update and enters the timestamps and determines the user id (or username) of the logged on user and updates that as well.

There are 2 issues/questions here:

Can you determine within a trigger the username of the person who caused the trigger to fire in the first place?
Are there any performance issues if the above is possible?

If its possible and no real performance issues arise, this is ideal since your objects don't have to do anything and also 1 object might update more than 1 database table, in which case you'd have to manually update these fields.

tmg4340 replied on Thursday, August 02, 2007

You can determine the username of the session using the SUSER_NAME() function in SQL Server. If you're using Windows authentication, this should return the fully-qualified ID of the user who initiated the session. I say "fully qualified" because any domain information would be included as well.

As for performance implications, I've done similar setups before and haven't noticed any performance hit. I wasn't working in really high-volume systems, but I don't see how the trigger would be any more of a performance hit than marshalling parameters back and forth. If your trigger was fairly data-intensive, you probably would notice a performance hit - but then you'd probably be doing a lot more than updating the user ID field.

(By the way - if you have a field that is of a "timestamp" type, you don't have to update it - SQL Server does that for you. If you have a binary field that you're treating as a timestamp, then you're on your own. And if your timestamp field really is a timestamp, and you're bringing it into your object, make sure you read the sections in Rocky's book about how to work with them on the client. Just an FYI.)

- Scott

stanc replied on Thursday, August 02, 2007

The problem I see with this approach is that you will need to return these values back to your object, otherwise you object won't know the values. And if you do in a trigger, you won't be be able to return them from your SP call.

We're setting the user and date in the BO before calling the SP to update. The only draw back to this is if you are using just the simple dataportal, you have to deal with differences in each client's clock. Of course if you let the server do it and you have users in multiple timezones you would have to deal with that as well.

Just my two cents, probably only worth one!

dagware replied on Thursday, August 02, 2007

stanc:
The problem I see with this approach is that you will need to return these values back to your object, otherwise you object won't know the values. And if you do in a trigger, you won't be be able to return them from your SP call.

I would think the way to do this is to re-read the record you just inserted or updated, and return the values from that, which will contain what was added by the trigger. If I'm not mistaken, Rocky does this somewhere in at least one of his examples (he doesn't use a trigger, but he returns the date/time values by re-reading the row he just inserted/updated). I actually never saw the advantage of re-reading the record until this thread.

Thanks to everyone for your thoughts on this. I can't use the SQL Server method because I'm logging in to SQL Server using an application ID. I will undoubtedly use some variation of Jon's logic, because that's what I was thinking of anyway. It's good to know I wasn't totally out in left field.

Thanks, everyone!

-Dan

JustinJones replied on Thursday, August 02, 2007

I generally get the ID value from Csla.ApplicationContext.User in the object itself during insert/update operations. This way the caller is not required to set them, and the caller can't set them incorrectly. We can't really do it at the SQL level because the DB connection uses a service account. My understanding is that you can't use connection pooling when you use user accounts to connect to SQL, which can be a heavy load on SQL in a high volume environment.