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

@Ed Guiness,

(I know this is an old thread but for the benefit of those who might need this in future)

;tldr answer: Not without some code changes ( mostly Non-Breaking and reasonable changes IMHO ).

Its very Strange that Microsoft does not expose the SPID once the connection object has been opened. I can see for example the SQL Version name and other SQL Server specific properties that I can get from the SqlConnection Object but not the SPID.

Details:

Every SQLConnection is assigned one SPID. This is obtained using the T-SQL Command @@SPID but this executes on the SQL Server side. Trick is to pass that along with the main work that is being done on SQL Server side and read it on the C# side.

Four possible scenarios for which SPID is needed.

1. You Are executing a Stored Procedure that Returns a resultset (minus the spid)
2. You are doing INS/UPD/DEL a Stored Procedure.
3. You are running Prepared SQL stms On the fly (inline) from ADO.Net (yikes!!) to do CRUD operations ( i.e Scenarios 1 and 2 without Stored Procedures )
4. You are inserting Data directly to a Table using SqlBulkCopy

1. Stored Procedure that Returns a resultset

Let say you have a SP (USP_GetDBDetails) that returns Rows from master db. We need to add a line of code to the Existing SQL Stmt to return the SPID and Get it on the C# Side using Paramter type for retrieving ReturnValue. The main Resultsets can also be read.

Stored Procedures are a beautiful thing. They can simultaneously return a return value AND a resultset AND a OutPut Parameter. In this case on the SP Side we only need to add the additional return value at the end of the SP that is being executed by ADO.Net using SqlConnection.We do this as shown in the T-SQL code below:

CREATE Procedure [dbo].[USP_GetDBDetails] 
AS
BEGIN

    SELECT 
            database_id,
            name,
            create_date         
      FROM  [sys].[databases]

      Return @@SPID -- Line of Code that needs to be added to return the SPID

END

Now to capture the SPID on the C# side (modify connection string as needed) :

        using (SqlConnection conn = new SqlConnection(@"Data Source=(local);Initial Catalog=master;Persist Security Info=True;Integrated Security =SSPI;"))
        {

            string strSql = "USP_GetDBDetails";

            SqlCommand sqlcomm = new SqlCommand();
            sqlcomm.CommandText = strSql;
            sqlcomm.CommandType = CommandType.StoredProcedure;
            sqlcomm.Connection = conn;

            SqlParameter returnValueParam = sqlcomm.Parameters.Add("@ReturnValue", SqlDbType.Int);
            returnValueParam.Direction = ParameterDirection.ReturnValue;

            conn.Open();

**// Reader Section**
            SqlDataReader rdr = sqlcomm.ExecuteReader();
            DataTable dt = new DataTable();
            dt.Load(rdr); // Get the Reultset into a DataTable so we can use it !
            rdr.Close();  // Important to close the reader object before reading the return value.

// Lets get the return value which in this case will be the SPID for this connection.
           string spid_str = returnValueParam.Value.ToString();
           int spid = (int)sqlcomm.Parameters["@ReturnValue"].Value; // Another Way to get the return value.

           Console.WriteLine("SPID For this Conn = {0} ", spid);

// To use the Reult Sets that was returned by the SP:

        foreach (DataRow dr in dt.Rows)
        {
            string dbName = dr["Name"].ToString();
            // Code to use the Other Columns goes here

        }
      }

Output :

SPID For this Conn = 66

---

2. If the Connection object is executing A SP that handles INS/UPS/DEL

Add the RETURN @@SPID at the end of the SP that is responsible for INS/UPD/DEL just as we did for Scenario 1.

And on the C# Side to get the SPID .. everything remains same as in Scenario 1 except the reader section. Delete the 4 lines under the Reader Section and replace with this line below. (and obviously the foreach loop to iterate the DataTable dt wont be necessary)

sqlcomm.ExecuteNonQuery();

---

3. INS/UPD/DEL Using Inline SQL

Move these stmts into a Stored Procedure and follow Steps for Scenario 2 . There might be ways to do some T-SQL acrobatics to inject the @@SPID and return it by perhaps utilizing MultipleActiveResultSets option but not very Elegant IMO.

---

4. SqlBulkCopy.

This will require querying the table to get the spid. Since there is no Stored procedure to return the SPID from SqlServer that can be captured.

We need to add an extra column of type INT to hold the SPID value like so:

ALTER TABLE dbo.TBL_NAME ADD
    SPID int NOT NULL Default( @@SPID )
GO

By doing this SQL Server will automatically insert the SPID value into the newly added column. No Code change will be necessary on C# ADO side that handles the BulkCopy. A typical Bulkcopy ADO code looks like below and it should continue to work after the ALTER TABLE Stmt above.

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
            {

            DataTable dt = new DataTable();
            dt.Columns.Add("Col1");
            dt.Columns.Add("Col2");

            string[] row = { "Col1Value", "Col2Value" };

            dt.Rows.Add(row);
                bulkCopy.DestinationTableName = "TBL_NAME_GOES_HERE"; //TBL_NAME

                try
                {
                    // Write from the source to the destination.
                    bulkCopy.WriteToServer(dt);
                }
                catch (SqlException ex)
                {

                  // Handle Exception

                }
            }
     }

So to check the output do a Select distinct SPID from dbo.TBL_NAME

Thats it . Hope that helps somebody.

Answer 2

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

Answer 3

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 4

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 5

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 6

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.