Connection pool with possible severed connections

Question

I have multiple threads accessing the same database (with same connection string). Each thread:

• creates it's own SqlConnection instance using the same connection string

• uses code below to open it's own connection instance whenever it needs one

      try
      {
          wasOpened = connection.State == ConnectionState.Open;
  
          if (connection.State == ConnectionState.Closed)
          {
              connection.Open();
          }
      }
      catch (Exception ex)
      {
          throw new Exception(string.Format("Connection to data source {0} can not be established! Reason: {1} - complete stack {2}",
                                            connection.Database, ex.Message, ex.StackTrace == null ? "NULL" : ex.StackTrace.ToString()));
      }
  

We have tested this code on 2 servers so far, and one server sometimes throws an exception in SqlConnection.Open method. Here is the exception message we get from catch block:

Connection to data source xyz can not be established! Reason: Invalid operation. The connection is closed. - complete stack

at System.Data.SqlClient.SqlConnection.GetOpenConnection()
at System.Data.SqlClient.SqlConnection.get_Parser()
at System.Data.SqlClient.SqlConnection.Open()

Inspecting SqlConnection.GetOpenConnection method shows that innerConnection is null:

internal SqlInternalConnection GetOpenConnection()
{
    SqlInternalConnection innerConnection = this.InnerConnection as SqlInternalConnection;
    if (innerConnection == null)
    {
        throw ADP.ClosedConnectionError();
    }
    return innerConnection;
}

It stays unclear to me: why does connection pool sometimes give me severed connection (innerConnection == null)?

Edit #1: there are no static properties in code - we ARE always closing connection when appropriate, wasOpened is used in our Close method and means: if connection was already opened when our Open is called, just leave it open on Close, otherwise close it. However, this is not related to problem described in this question (innerConnection == null).

Edit #2: Server: SQL Server 2008 R2, Windows Server 2003. Client: Windows Server 2003 (code runs within SSIS package custom component). Connection string: Data Source=server_name;Initial Catalog=db_name;Integrated Security=SSPI;Application Name=app_name

Answer 1

First, read this carefully: SQL Server Connection Pooling (ADO.NET)

I'll quote the most important part for you (i think):

A connection pool is created for each unique connection string. When a pool is created, multiple connection objects are created and added to the pool so that the minimum pool size requirement is satisfied. Connections are added to the pool as needed, up to the maximum pool size specified (100 is the default). Connections are released back into the pool when they are closed or disposed.

When a SqlConnection object is requested, it is obtained from the pool if a usable connection is available. To be usable, a connection must be unused, have a matching transaction context or be unassociated with any transaction context, and have a valid link to the server.

The connection pooler satisfies requests for connections by reallocating connections as they are released back into the pool. If the maximum pool size has been reached and no usable connection is available, the request is queued. The pooler then tries to reclaim any connections until the time-out is reached (the default is 15 seconds). If the pooler cannot satisfy the request before the connection times out, an exception is thrown.

We strongly recommend that you always close the connection when you are finished using it so that the connection will be returned to the pool. You can do this using either the Close or Dispose methods of the Connection object, or by opening all connections inside a using statement in C#, or a Using statement in Visual Basic. Connections that are not explicitly closed might not be added or returned to the pool. For more information, see using Statement (C# Reference)

In short: don't poach in the territory of the connection-pool and close connections as soon as you're finished with them(f.e. via using-statement).

Since you don't want to throw your DB-Class into the garbage can, i would suggest to either increase the maximum poolsize and/or the timeout or disable pooling and see what happens.

<add name="theConnectionString" connectionString="Data Source=(local);
     Database=AdventureWorks; Integrated Security=SSPI; 
     Max Pool Size=200; Pooling=True; Timout=60" />

You should also try to catch this specific error and clear all connection pools:

System.Data.SqlClient.SqlConnection.ClearAllPools();

Or have a look at these questions which look promising:

• SQL Server connection pool doesn't detect closed connections?
• http://forums.asp.net/t/1729521.aspx/1

Answer 2

I have multiple threads accessing the same database (with same connection string). Each thread:

1. creates it's own SqlConnection instance using the same connection string
2. uses code below to open it's own connection instance whenever it needs one

If you have a problem that appears at random, in your case, given the code you have displayed, you might have:

1. A problem with the connection pooling on the server
2. A race condition somewhere in your code

All that being said...

You should wrap your SqlConnection in a using statement. This way the connection will be closed when your code, or thread, is done with it.

using (SqlConnection connection = new SqlConnection(connectionString))
{
   //... stuff
}

This way the connection is guaranteed to call the Dispose() method (which internally calls Close() anyways). That way the connection can be returned to the pool, if it is in use (which it probably is).