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

Question

I have an Oracle database and I am using Oracle.ManagedDataAccess .

In some cases, I will need to do actions in one transaction, but often not.

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

I could add DbConnection to the repository, and then even use LifetimePerScope to make sure that they all get the same DbConnection instance. But this is a smart move, is this .Open() connection .Open() .

 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 will force me to always use LifetimeScope , even if I don’t use a transaction, and open a connection outside the repository method.

Are TransactionScopes dependent on a single connection, or can I open multiple connections (how does connectionPool handle when a transaction is open?) Within a single transaction?

I am a complete outsider for DbConnections and all this, so I could completely misunderstand the best way to use TransactionScope and DbConnections.

Answer 1

Possible duplicate: Why does the database connection always close?

Since this one has generosity, I cannot designate it as a duplicate :(

In any case, joining connections is mostly done for you. You should close the connections as soon as you can, return them to the pool.

Transactions are associated with a specific open connection and must be completed when the connection is closed.

Answer 2

The TransactionScope associated with BeginTransaction () is connection specific. If you want to support a transaction across multiple connections (multiple databases, resources), then you need a DTC TransactionScope . Here is a similar SO post . For this you need to use Oracle.ManagedDataAccessDTC.dll .

You can go through these links:

1. All about the transaction

2. How to configure DTC to support Oracle transactions

Hope this helps.