Correct way to use DbConnection, DbTransaction with connection pooling, transactionScope and dependency injection?

Question

I have a Oracle database and I'm using the Oracle.ManagedDataAccess.

In some cases I will need to do actions in a single transactions, but often not.

I'm not sure what the best way to handle DbConnection objects within a single TransactionScope.

I could inject a DbConnection into the repositories and then even use LifetimePerScope to ensure they all get the same DbConnection instance. But is that a smart move, is it ok to .Open() the connection once.

using (var scope = _lifetimeScope.BeginLifetimeScope())
{
    var connection = scope.Resolve<IDbConnection>();
    var personRepo = scope.Resolve<IPersonRepository>();
    var workRepo = scope.Resolve<IWorkRepository>();
    connection.Open();
    var transaction = connection.BeginTransaction()
    personRepo.DeleteById(someId);
    workRepo.DeleteByPersonId(someId);
    transaction.Commit();
}

This would force me to always use a LifetimeScope, even if not using a Transaction, and open the connection outside the repository method.

Are TransactionScopes dependent on a single connection or can I open multiple connections (how does the connectionPool handle that while a transaction is open?) within the same transaction?

I'm a total outsider to DbConnections and all that so I might be totally misunderstanding the best way to use TransactionScope and DbConnections.

MushinNoShin MushinNoShin · Accepted Answer · 2015-09-10T11:59:51

Possible duplicate of: Why always close Database connection?

Since this has a bounty, I can't flag it as a duplicate :(

Anyway, connection pooling is largely done for you. You should close connections as soon as you can, to return them to the pool.

Transactions are related to a specific open connection and should be finished when closing the connection.

Correct way to use DbConnection, DbTransaction with connection pooling, transactionScope and dependency injection?

2 Answers