Identifying underlying sql connection of SqlConnection object

Question

I can use GetHashCode() to identify an object but is there any way to identify the actual sql connection obtained by a SqlConnection object?

I'm (still) trying to debug a problem involving pooled connections and application roles and if I could reliably identify the underlying sql connection it could help a lot.

Here's some code that might illustrate the question

SqlConnection c = new SqlConnection(myConnString);

c.Open();  // GetHashCode == "X"

c.Close(); // returns connection to pool

c.Open;  // GetHashCode() == "X" but possibly different pooled connection?

As I write this question it occurs to me that what I probably want is the SPID of the connection. Sadly, SPID isn't available when the connection is dropped by SQL due to the bug I'm trying to resolve (so at the point I'm most interested in I can't run a command on that connection to obtain the SPID).

Any other bright ideas?

Answer 1

Not a direct answer, but something you should note. The hash code of an object shouldn't change throughout its lifetime. If it did, then you could put an object into a hashed collection, change it's hash code then then be unable to retrieve it from the collection again.

If you attach a debugger and view the private fields, can't you turn some kind of internal identifier up? If so, you could access that via reflection during your debugging if you had to.

Answer 2

You could set the application name in the connection string, this is then visible in SQL Server. Normally it is defaulted the the SQL Client, but you can override:

"Integrated Security=true;Initial Catalog=Northwind;server=(local);Application Name=MyKeyword"

This property can be read back by the ConnectionString property of the SqlConnection instance.

Edit: As noted by edg the connection string defines which connection pool, so this probably wouldn't work.

Answer 3

Richard's answer will not help you, if I am understanding your issue properly, as you are looking for the actual object in the underlying .NET connection pool. I am also not convinced that the hash will help, as you are looking at the underlying pool.

I do not have an answer, per se, but a suggestion. Get a copy of Reflector (now a RedGate product) and go through System.Data.DLL and look at how things are stored in the underlying pool. I am not sure it will give you a quick and easy answer, but if there is any thing you can reflect on to get an answer to help debug your problem, it is going to be there.

BTW, what is the bug you are trying to solve?

Answer 4

One thing you could try is

SqlConnection.ClearPool();

or

SqlConnection.ClearAllPools();

to see if you can isolate any of the issues you are facing.

Answer 5

Not to say that this is impossible, but I've not yet found any way to do it.