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.

Answer 1

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.

Answer 2

TransactionScope related to BeginTransaction() is specific to a connection. If you want to maintain a transaction across multiple connections(multiple DBs,resources), then you need DTC aware TransactionScope. Here is a similar SO post. You need to use Oracle.ManagedDataAccessDTC.dll to facilitate that.

You might want to go through these links:

1.All about transactionscope

2.How To Configure DTC to Support Oracle Transactions

Hope this helps.