Connection pooling

Topics: Data Access Application Block
Aug 18, 2010 at 8:03 PM

I want to just confirm my understanding. We want to implement connection pooling in our application. As of now the basic connection pooling provided by ADO.Net is sufficient. When using DAAB my understanding is that the DAAB takes care of connection pooling and we do not have to explicitly configure anything for connection pooling. Is this understanding correct?. Also if we run into issues with connection resource problems, where are the knobs to adjust these connection pool settings for DAAB.



Aug 19, 2010 at 2:39 AM

As far as I know connection pooling is enabled by default in DAAB. DAAB is built on top of the ADO.NET, therefore the same way on configuring connection pooling in ADO.NET should also work with DAAB.

Gino Terrado
Global Technology and Solutions
Avanade, Inc.


Aug 19, 2010 at 6:03 AM

Things may be a little confusing around this topic, because there are two different things that tend to be called "connection pooling" in the DAAB.

Gino is correct that we just sit on top of the ADO.NET connection pool. By default, pooling is on, but you can turn it off it you want in the connection string (in SQL Server at least, I don't know about other databases).

In normal operation, when you call a DAAB method like ExecuteNonQuery or ExecuteReader or whatnot, the DAAB will create a new SqlConnection (or OracleConnection or whatever), do the operation, then close and dispose the connection. This has always been the guidance from the ADO.NET team - open the connection late, close it as soon as possible, and rely on the underlying connection pool to avoid creating actual physical connections to the database each time.

However, this approach doesn't work when using transactions. So, if there's an ambient transaction running (you've done new TransactionScope() somewhere up the stack) the DAAB behavior changes - we will open the connection the first time, then keep it open and reuse it for all the DAAB operations on that thread, until the transaction completes. This has also been referred to as a connection pool at some points in the past. There's not way to tweak this behavior, it just always work this way.

If you want to change the underlying ADO.NET pooling behavior, you change it in the connection string. If you don't like the way DAAB opens & closes connections, then you'll need to drop back and manage the connection objects yourself.