Unit of Work Sample Implementation for ADO.NET

This article demonstrates how you can create a Unit Of Work implementation for ADO.NET.

I’ve got a couple of questions in my “Repository pattern” article about a unit of work implementation. Here is a sample.

This is the actual UoW factory. Notice that it has no abstraction more than returning IUnitOfWork from the method. No need to create an abstract factory as the actual creation as it is trivial.

public class UnitOfWorkFactory
{
	public static IUnitOfWork Create()
	{
		var connection = new SqlConnection
         (ConfigurationManager.ConnectionStrings("MyDb").ConnectionString);
		connection.Open();
		
		return new AdoNetUnitOfWork(connection, true);
	}
}

The unit of work itself uses the interfaces defined in System.Data to be completely driver independent. That makes it quite easy to switch DB as long as you don’t use anything but the SQL92 standard in your SQL statements.

public class AdoNetUnitOfWork : IUnitOfWork
{
	public AdoNetUnitOfWork(IDbConnection connection, bool ownsConnection)
	{
		_connection = connection;
		_ownsConnection=ownsConnection;
		_transaction = connection.BeginTransaction();
	}
	
	public IDbCommand CreateCommand()
	{
		var command = _connection.CreateCommand();
		command.Transaction = _transaction;
		return command;
	}
	
	public void SaveChanges()
	{
		if (_transaction == null)
			throw new InvalidOperationException
             ("Transaction have already been committed. Check your transaction handling.");
			
		_transaction.Commit();
		_transaction = null;
	}
	
	public void Dispose()
	{
		if (_transaction != null)
		{
			_transaction.Rollback();
			_transaction = null;
		}
		
		if (_connection != null && _ownsConnection)
		{
			_connection.Close();
			_connection = null;
		}
	}
}

Usage:

using (var uow = UnitOfWorkFactory.Create())
{
	var repos = new UserRepository(uow);
	
	uow.SaveChanges();
}

The above code requires that the repositories break the Liskov's Substitution Principle in theory. But in reality, that will never be a problem unless you switch data layer entirely. But in that case, the simplest thing is to adjust the UnitOfWorkFactory so that it returns the new implementation.

Hence, do something like this in the repository:

public class UserRepository
{
	private AdoNetUnitOfWork _unitOfWork;
	
	public UserRepository(IUnitOfWork uow)
	{
		if (uow == null)
			throw new ArgumentNullException("uow");
			
		_unitOfWork = uow as AdoNetUnitOfWork;
		if (_unitOfWork == null)
			throw new NotSupportedException("Ohh my, change that UnitOfWorkFactory, will you?");
	}
	
	public User Get(Guid id)
	{
		using (var cmd = _unitOfWork.CreateCommand())
		{
			cmd.CommandText = "SELECT * FROM Users WHERE Id = @id");
			cmd.AddParameter("id", id);
			
			// uses an extension method which I will demonstrate in a 
			// blog post in a couple of days
			return cmd.FirstOrDefault<User>();
		}
	}
}

My motivation to now abstract away the implementation in the repositories is that unit tests can never guarantee that the repositories work. Mocking/stubbing/faking will never work as the read database. Hence, you need to use integration tests (= use a DB) to be sure that your repositories work as expected.

That’s it!