Sql – Diagnosing SqlConnection leaks

From MSDN (DataContext Constructor (IDbConnection)):

If you provide an open connection, the DataContext will not close it. Therefore, do not instantiate a DataContext with an open connection unless you have a good reason to do this.

So basically, it looks like your connections are waiting for GC to finalize them before they will be released. If you have lots of code that does this, one approach might be to overide Dispose() in the data-context's partial class, and close the connection - just be sure to document that the data-context assumes ownership of the connection!

    protected override void Dispose(bool disposing)
    {
        if(disposing && this.Connection != null && this.Connection.State == ConnectionState.Open)
        {
            this.Connection.Close();
            this.Connection.Dispose();
        }
        base.Dispose(disposing);
    }

Personally, I would happily give it (regular data-context, w/o the hack above) an open connection as long as I was "using" the connection (allowing me to perform multiple operations) - i.e.

using(var conn = GetConnection())
{
   // snip: some stuff involving conn

   using(var ctx = new FooContext(conn))
   {
       // snip: some stuff involving ctx
   }

   // snip: some more stuff involving conn
}

@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.