Why is my CONTEXT_INFO() empty?

Question

I have a method that sets up my linq data context. Before it returns the DC it calls a stored proc that sets up the CONTEXT_INFO value to identify the current user.

A trigger picks up any changes made and using this context data writes an audit record.

I noticed that my context data was in the audit table blank so I wrote a simple unit test to step through this process and I still get nothing. However if I paste all the Linq-To-SQL statements into a query window the context data is there.

Looking at a profiler trace it makes quite a few sp_reset_connection calls in this process. I had understood that these should not have an affect on the CONTEXT_INFO value though.

So what's going on here?

Answer 1

A Linq to SQL DataContext does not actually hold the connection open when you execute queries, either using query comprehension or ExecuteQuery/ExecuteMethod call, and CONTEXT_INFO only lives in the context of a single connection.

In order to get this to work, you need to manually open the connection on the DataContext using context.Connection.Open() before setting the context_info. Once the connection is already open, successive queries won't auto-close the connection when they're finished.

Note - the technical reason for this is that it invokes ExecuteReader on the IDbCommand with CommandBehavior.CloseConnection set, unless the connection was already open. You can see the same behaviour yourself if you use SqlCommand/IDbCommand objects with the same flag set.

Edit - I guess I should also point out that if the connection is pooled, technically the physical connection is "open" the whole time, but the IDbConnection is still getting closed, which is what causes the connection resets.

Answer 2

sp_reset_connection does reset context_info. sp_reset_connection is the procedure called by the client app pools when recycling a connection, so it appears that you're seeting the context on one connection, closing the connection and expecting the context to be set on a new connection, whcih is obviously erroneous.