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?
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With