Opening and closing a database connection inside a transaction

Question

I designed the data access portion of our framework so that every time a business object (BO) needs to interact with the database, it would have to open a connection, invoke the data access layer (to execute the query), and then close the connection. Then if it needed to run in a transaction, it would open the connection, begin the transaction, invoke the data access layer (to execute the query) and then commit the transaction, close the transaction, and finally close the connection.

I did it this way in the mindset of "open late, close early"… but what if I needed to call other BOs to submit data in a single transaction? Is there a better way to handle opening and closing connections as well as working with transactions?

I'm a rookie in designing application architecture, so I hope I'm not doing this wrongly… any help is appreciated.

Answer 1

As mentioned by others, TransactionScope is the way to go.

If you are using SQL Server 2008 and .NET 3.5, I would modify the design to have the business object control the transaction and leave the opening and closing of the connection to the data layer.

With connection pooling on, you will not actually be incurring the overhead of opening a physical database connection and your connections will only be open when performing actual work. Since (I assumed) you have SQL Server 2008 with .NET 3.5 your transaction will not escalate to a distributed transaction (unless you open multiple connections at the same time) so you get the best of both worlds.

Then you could write your business object like this:

using (TransactionScope transactionScope = new TransactionScope())
{
    DataObject dataObject = new DataObject();
    dataObject.UpdateQuantity(...);

    ShippingManager shippingManager = new ShippingManager();
    shippingManager.ShipOrder(...);

    transactionScope.Complete()
}

This avoids having to pass connection strings around to all business objects and makes coordinating transactions easy.

Update

The beauty of System.Transactions is that all of the transactions are managed for you irrespective of the connection that you are using. You just declare a TransactionScope and all database access within that TransactionScope will occur withing a single transaction (unless you request otherwise with different TransactionScope settings).

In the past (SQL Server 2005 .NET 2.0), if you opened and closed a connection and then opened and closed another connection (even with the same connection string) then the transaction was promoted from a Lightweight Transaction to a Distributed Transaction. This was undesirable because performance suffers (communication to MSDTC is out of process and the two phase commit protocol) and MSDTC can be a pain to configure in many production environments (firewalls and security).

With SQL Server 2008 and .NET 3.5 they have added the ability to avoid this promotion when opening and closing multiple connections with the same connection string within a single transaction. For a really good explanation of what they did see Extending Lightweight Transactions in SqlClient.

Update 2

Transactions with Oracle 10g will function properly with TransactionScope. And it looks like ODP.NET supports Lightweight Transactions (which is nice). Unfortunately, I think that the promotion to a distributed transaction will occur with the closing and opening of connections.

If you wish to avoid a distributed transaction you could pass the connection to every method call/Business Object. If you don't want to pass a connection around, you could use a ConnectionScope class which keeps the connection open on the thread. An alternative to that would be to use Enterprise Library 3.0 (and above) Data Access Application Block. The Data Access Block can detect that a transaction is in progress and use the same connection to avoid a distributed transaction.