Managing database connectivity with ADO.NET

Question

We have an application that is built upon ADO.NET. We follow some simple best practices that allow us to make use of the connection pool. For example, a block of code that is using a database might look something like this:

using( DbConnection dbConnection = GetDatabaseConnection() ) {   
    doWork();
}

FWIW, there's nothing special about GetDatabaseConnection. It establishes a connection with the database which is running MSSQL Server 2000. In fact, it reads as follows:

DbConnection GetDatabaseConnection() {
    return GetConnection(MyConnectionString);
}

DbConnection GetConnection(String connectionString)
{
  try {
      SqlConnection dbConnection = new SqlConnection(connectionString);
      dbConnection.Open();
      return dbConnection;
  } catch( InvalidOperationException ex ) {
      handleError(ex);
      throw;
  } catch( DbException ex ) {
      handleError(ex);
  }
}

As such, our connections are disposed of at the end of the block scope. However, we ran into a little glitch as we began to test the application. We found that our application is very bursty meaning there are times when it becomes very chatty and then goes silent for a while. The result is that we can have multiple threads simultaneously all coming up to obtain a connection.

So imagine you have 10 threads. A batch of work (please don't try to rephrase the batch of work) arrives and is segmented across a few threads. Each thread then attempts to obtain a connection and boom, I'm hit with an InvalidOperationException. I've adjusted the ConnectTimeout and all that does is stretch out the time until I get a burst of exceptions. Once I get past the "wind-up" phase, the application is fine. Then it quiesces again, the connections "go away" and the process begins again.

I've also tried tweaking the LoadBalanceTimeout but the exceptions continue to roll in. Have any of you seen this issue before? Any thoughts ... I'll throw out a couple of my own.

• Continually keep some connection "hot"
• Attempt to open the connection again up to some # of attempts
• Implement my own connection pooling (bleah, not interested in reinventing the wheel)

Edit:

Most forums I've read have discouraged increasing the connection pool size. By default the connection pool has an upper bound of 50 connections (that's more than enough -- if I have to increase it, something's fundamentally wrong elsewhere). What I notice is that when the ConnectTimeout is low, the InvalidOperationException occurs. It's as if the spin-up of the connection is too long and the pending connections all timeout.

MARS is certainly an option... The text of the InvalidOperationException.Message is:

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

Answer 1

You can try setting "Max Pool Size" higher. Also you may want to try explicitly calling "Close" on the connection.

Answer 2

Question 1: Does your GetDatabaseConnection() method spin off a new thread to generate the database connection and return it to the main thread... or are multiple threads trying to access this one isntance of the method... or is this method a shared/static method that just grabs a connection from the pool?

Question 2: What make and model is your DB server? SQL Server? Oracle? PostgreSQL?

Question 3: Do you require all the work completed on different connections, or if it could all be done on one, would that suffice? If SQL Server, then perhaps MARS allowing multiple recordsets on a single connection might help, but may not be feasible for your app.

Question 4: What is the detail in the returned exception?

Just trying to get a clear picture of how your application architecture is working...

(P.S. Does anyone know how I flag an "answer" as "not an answer, but a request for further clarification"... i.e. for this "answer")

Answer 3

According to MSDN, InvalidOperationException is thrown by SqlConnection.Open if you haven't specified a data source or server, or if the connection is already open.

Are you sure you don't have any calls to SqlConnection.Open in your "doWork" method?

Also your code in the GetConnection method swallows DbException.