CODEWITHSUNDEEP
c#sqlasp.net-mvcsql-server-2008
yakya
yakya

Reputation: 5210

Send an optional value with connection string to database server

I need to add two columns in all of my tables which are LastUpdateDate and LastUpdateUserId. I don't use Entity Framework and I do all of my CRUD operations in Stored Procedures. My problem is that I have over than 150 Stored Procedures and changing 150 Stored Procedures plus references of these Stored Procedures in my project would take a lot of time.

If there were no LastUpdateUserId column, I would use Trigger to insert LastUpdateDate value when there is an update in a table. But LastUpdateUserId mixes the situation.

So what do you suggest? Is there any way that I can send an optional value with connection string and then maybe I can get the value in Stored Procedures?

Upvotes: 0

Views: 256

Answers (2)

Simon Halsey
Simon Halsey

Reputation: 5480

We abused the workstationId field for just this purpose. It was for a specific use case though.

If you change the connection string for each user though, you lose all the benefits connection pooling gives you.

if you are using the same login for all users, then I'm afraid the only way to really do it is to change your procs.

If each user has their own login, there is SQL to get that value, as others have indicated.

Upvotes: 1

Richard Deeming
Richard Deeming

Reputation: 31228

Assuming you're using Windows authentication, you should be able to get the current user's login name using either SUSER_SNAME() or ORIGINAL_LOGIN(). This will typically include the domain name.

Alternatively, you could abuse the "Application Name" property of the connection string, and retrieve it using the APP_NAME() function.

Upvotes: 1

Related Questions