title: .net SqlConnection not being closed even when within a using { }
body: <p>Please help!</p>
  
  <p><em>Background info</em></p>
  
  <p>I have a WPF application which accesses a SQL Server 2005 database. The database is running locally on the machine the application is running on.</p>
  
  <p>Everywhere I use the Linq DataContext I use a using { } statement, and pass in a result of a function which returns a SqlConnection object which has been opened and had an SqlCommand executed using it before returning to the DataContext constructor.. I.e.</p>
  
  <pre><code>// In the application code
  using (DataContext db = new DataContext(GetConnection()))
  {
      ... Code 
  }
  </code></pre>
  
  <p>where getConnection looks like this (I've stripped out the 'fluff' from the function to make it more readable, but there is no additional functionality that is missing).</p>
  
  <pre><code>// Function which gets an opened connection which is given back to the DataContext constructor
  public static System.Data.SqlClient.SqlConnection GetConnection()
  {
     System.Data.SqlClient.SqlConnection Conn = new System.Data.SqlClient.SqlConnection(/* The connection string */);
  
      if ( Conn != null )
      {
          try
          {
              Conn.Open();
          }
          catch (System.Data.SqlClient.SqlException SDSCSEx)
          {
               /* Error Handling */
          }
  
          using (System.Data.SqlClient.SqlCommand SetCmd = new System.Data.SqlClient.SqlCommand())
          {
              SetCmd.Connection = Conn;
              SetCmd.CommandType = System.Data.CommandType.Text;
  
              string CurrentUserID = System.String.Empty;
              SetCmd.CommandText = "DECLARE @B VARBINARY(36); SET @B = CAST('" + CurrentUserID + "' AS VARBINARY(36)); SET CONTEXT_INFO @B";
  
              try
              {
                  SetCmd.ExecuteNonQuery();
              }
              catch (System.Exception)
              {
                  /* Error Handling */
              }
          }
  
          return Conn;
      }
  </code></pre>
  
  <p><strong>I do not think that the application being a WPF one has any bearing on the issue I am having.</strong></p>
  
  <p><em>The issue I am having</em> </p>
  
  <p>Despite the SqlConnection being disposed along with the DataContext in Sql Server Management studio I can still see loads of open connections with :</p>
  
  <pre><code>status : 'Sleeping' 
  command : 'AWAITING COMMAND' 
  last SQL Transact Command Batch : DECLARE @B VARBINARY(36); SET @B = CAST('GUID' AS VARBINARY(36)); SET CONTEXT_INFO @B
  </code></pre>
  
  <p>Eventually the connection pool gets used up and the application can't continue. </p>
  
  <p>So I can only conclude that somehow running the SQLCommand to set the Context_Info is meaning that the connection doesn't get disposed of when the DataContext gets disposed. </p>
  
  <p>Can anyone spot anything obvious that would be stopping the connections from being closed and disposed of when the DataContext they are used by are disposed?</p>