UnitOfWork vs Database connection

Question

I currently have made a UnitOfWork implementation which wraps both the database connection and the transaction.

using (var uow = UnitOfWorkFactory.Create())
{
    // do db operations here through repositories

   uow.SaveChanges();
}

Rollback will be called if SaveChanges haven't been called before the uow gets disposed.

Is it a bad design choice to let the uow handle both the connection and the transaction?

Let's say that I got a ASP.Net MVC website where most of the actions are just fetching information from the database. Are there a performance penalty for creating/committing transactions that aren't actually not doing anything in the database?

Did you find out whether there's any problem with always creating a transaction, even for just `SELECT` statements? I'm new to back-end programming and this is what I read in a SQL book _"Executing a `SELECT` statement within a transaction can create locks on the referenced tables, which can in turn block other users or sessions from performing work or reading data"_ — Axel, Oct 19 '16 at 15:05

score 4 · Accepted Answer · answered Apr 19 '11 at 08:59

4

If you want to implement UoW then SaveChanges should be the only place where a connection and a transaction will be used. UoW will just collect all commands that must be executed and execute them in a transaction when SaveChanges is invoked.

answered Apr 19 '11 at 08:59

Ladislav Mrnka

360,892
59
660
670

1

What about the transaction that is always started? I'm new to back-end programming and this is what I read in a SQL book _"Executing a `SELECT` statement within a transaction can create locks on the referenced tables, which can in turn block other users or sessions from performing work or reading data"_ – Axel Oct 19 '16 at 15:01

score 0 · Answer 2 · answered Apr 19 '11 at 09:10

UoW is domain specific. I had more-or-less the same implementation you use until someone pointed this out since database access should not really influence your domain.

So I ended up splitting the responsibilities. Since my UoW does make use of repositories that require a connection to a database I still require a connection.

So for instances where I do not require a UoW I will have this:

using (DatabaseConnectionFactory.Create()) { ... }

For transaction:

using (var connection = DatabaseConnectionFactory.Create().BeginTransaction()) 
{
    // do stuff

    connection.CommitTransaction();
}

If I require a UoW (typically with transaction):

using (var connection = DatabaseConnectionFactory.Create().BeginTransaction())
using (var uow = UnitOfWorkFactory.Create())
{
    // do stuff

    connection.CommitTransaction();
}

HTH

score 0 · Answer 3 · answered Apr 20 '11 at 15:27

Did you implement the Unit of Work yourself (I'm assuming you did). Look into using ADO.NET EF with the Repository pattern.

There is a big camp that feels any database operation should be wrapped in a transaction. You should for sure wrap any Inserts / Updates / Deletes in a transaction.

That being said, wrap any of the database objects that implement IDisposable in a Using Block.

UnitOfWork vs Database connection

3 Answers3