Common connection & transaction management approaches

Connection and transaction management is one of the most important concepts in an application that uses a database. When to open a connection, when to start a transaction, how to dispose the connection and so on..

As probably you know, .NET uses connection pooling. So, creating a connection actually gets a connection from the pool since creating a new connection has a cost. If there is no free connection in the pool, a new one is created and added to the pool. When you dispose the connection, it's actually sent back to the pool. It's not disposed completely. This mechanism provided by .NET as an out of the box feature. So, we should always dispose a connection after using it and create a new connection when needed. This is the best practice.

There are two common approach to create/dispose a database connection in an application:

First approach: Creating a connection when a web request begins (Application_BeginRequest event in global.asax), using the same connection in all database operations and closing/disposing the connection at end of the request (Application_EndRequest event). This is simple but not efficient. Why?

Also, it's considered as a best practice to perform database operations in a transactional way. If one operation fails, all of them are rolled back. Since a transaction may lock some rows (even tables) in database, it must be short-lived.

Second approach: Creating a connection when needed (just before using it) and disposing it just after using it. This is the most efficient but a tedious and repating work to create/dispose connections everywhere.

Connection & Transaction management in ASP.NET Boilerplate

ASP.NET Boilerplate mixes both approach and provides a simple yet efficient model.

Repository classes

Repositories are main classes where database operations are performed. ASP.NET Boilerplate opens a database connection (it may not be opened immediately, but opened in first database usage, based on ORM provider implementation) and begins a transaction when entering a repository method. So, you can use connection safely in a repository method. At the end of the method, the transaction is commited and the connection is disposed. If the repository method throws any exception, transaction is rolled back and the connection is disposed. In this way, a repository method is atomic (a unit of work). ASP.NET Boilerplate does all of these automatically. Here, a sample repository:

public class ContentRepository : NhRepositoryBase<Content>, IContentRepository
{
    public List<Content> GetActiveContents(string searchCondition)
    {
        var query = from content in Session.Query<Content>()
                    where content.IsActive && !content.IsDeleted
                    select content;

        if (!string.IsNullOrEmpty(searchCondition))
        {
            query = query.Where(content => content.Text.Contains(searchCondition));
        }

        return query.ToList();
    }
}
			

This sample uses NHibernate as ORM. As shown above, no database connection (Session in NHibernate) open/close code is written.

If a repository method calls another repository method (in general, if a unit of work method calls another unit of work method), both uses same connection & transaction. The first entering method manages connection & transaction, others use it.

Application services

An application service method is also considered a unit of work. Assume that we have an application service method like below:

public class PersonAppService : IPersonAppService
{
    private readonly IPersonRepository _personRepository;
    private readonly IStatisticsRepository _statisticsRepository;

    public PersonAppService(IPersonRepository personRepository, IStatisticsRepository statisticsRepository)
    {
        _personRepository = personRepository;
        _statisticsRepository = statisticsRepository;
    }

    public void CreatePerson(CreatePersonInput input)
    {
        var person = new Person { Name = input.Name, EmailAddress = input.EmailAddress };
        _personRepository.Insert(person);
        _statisticsRepository.IncrementPeopleCount();
    }
}

In the CreatePerson method, we're inserting a person using person repository and incrementing total people count using statistics repository. Both of repositories shares same connection and transaction in this example since this is an application service method. ASP.NET Boilerplate opens a database connection and starts a transaction when entering CreatePerson method and commint the transaction at end of the method if no exception is thrown, rolls back if any exception occurs. In that way, all database operations in CreatePerson method becomes atomic (unit of work).

Unit Of Work

Unit of work implicitly works for repository and application service methods. You should explicitly use it if you want to control database connection and transaction somewhere else. There are two approaches for this.

UnitOfWork attribute

First and preferred approach is using UnitOfWorkAttribute. Example:

[UnitOfWork]
public void CreatePerson(CreatePersonInput input)
{
    var person = new Person { Name = input.Name, EmailAddress = input.EmailAddress };
    _personRepository.Insert(person);
    _statisticsRepository.IncrementPeopleCount();
}

Thus, CreatePerson methods becomes unit of work and manages database connection and transaction, both repositories use same unit of work. Note that no need to UnitOfWork attribute if this is an application service method. See 'unit of work method restrictions' section.

There are some options of the UnitOfWork attribute. See 'unit of work in detail' section.

IUnitOfWorkManager

Second appropaches is using the IUnitOfWorkManager.Begin(...) method as shown below:

public class MyService
{
    private readonly IUnitOfWorkManager _unitOfWorkManager;
    private readonly IPersonRepository _personRepository;
    private readonly IStatisticsRepository _statisticsRepository;

    public MyService(IUnitOfWorkManager unitOfWorkManager, IPersonRepository personRepository, IStatisticsRepository statisticsRepository)
    {
        _unitOfWorkManager = unitOfWorkManager;
        _personRepository = personRepository;
        _statisticsRepository = statisticsRepository;
    }

    public void CreatePerson(CreatePersonInput input)
    {
        var person = new Person { Name = input.Name, EmailAddress = input.EmailAddress };

        using (var unitOfWork = _unitOfWorkManager.Begin())
        {
            _personRepository.Insert(person);
            _statisticsRepository.IncrementPeopleCount();

            unitOfWork.Complete();
        }
    }
}

You can inject and use IUnitOfWorkManager as shown here (if you inherit your application service from ApplicationService class, then you can directly use CurrentUnitOfWork property. If not, you should inject IUnitOfWorkManager). Thus, you can create more limited scope unit of works. In this approach, you should call Complete method manually. If you don't call, transaction is rolled back and changes are not saved.

Begin method has overloads to set unit of work options.

It's better and shorter to use UnitOfWork attribute if you don't have a good reason.

Unit of work in detail

Disabling unit of work

You may want to disable unit of work for an application service method (since it's enabled as default). To do that, use UnitOfWorkAttribute's IsDisabled property. Example usage:

[UnitOfWork(IsDisabled = true)]
public virtual void RemoveFriendship(RemoveFriendshipInput input)
{
    _friendshipRepository.Delete(input.Id);
}
			

Normally, you don't want to do that since an application service method should be atomic and uses database in general. Some situations you may want to disable unit of work for an application service method:

Note that if a unit of work method calls this RemoveFriendship method, disabling is ignored and it uses the same unit of work with the caller method. So, use disabling by carefully. Also, the code above works well since repository methods are unit of work as default.

Non-transactional unit of work

A unit of work is transactional as default (by it's nature). Thus, ASP.NET Boilerplate starts/commits/rollbacks an explicit database-level transaction. In some special cases, transaction may cause problems since it may lock some rows or tables in the database. In this situations, you may want to disable database-level transaction. UnitOfWork attribute can get a boolean value in it's constructor to work as non-transactional. Example usage:

[UnitOfWork(isTransactional: false)]
public GetTasksOutput GetTasks(GetTasksInput input)
{
    var tasks = _taskRepository.GetAllWithPeople(input.AssignedPersonId, input.State);
    return new GetTasksOutput
            {
                Tasks = Mapper.Map<List<TaskDto>>(tasks)
            };
}

I suggest to use this attribute as [UnitOfWork(isTransactional: false)]. I think it's more readable and explicit. But you can use as [UnitOfWork(false)].

Note that ORM frameworks (like NHibernate and EntityFramework) internally saves changes in a single command. Assume that you updated a few Entities in a non-transactional UOW. Even in this situation all updates are performed at end of the unit of work with a single database command. But if you execute an SQL query directly, it's performed immediately.

There is a restriction for non-transactional UOWs. If you're already in a transactional unit of work scope, setting isTransactional to false is ignored.

Use non-transactional unit of works carefully since most of the times it should be transactional for data integrity. If your method just reads data, not changes it, it can be non-transactional of course.

A unit of work method calls another

If a unit of work method (a method declared with UnitOfWork attribute) calls another unit of work method, they share same connection and transaction. First method manages connection, others use it. This true for methods run in same Thread (or in same request for web applications). Actually, when a unit of work scope begins, all codes executing in same thread shares same connection and transaction until the unit of work scope ends. This is true both for UnitOfWork attribute and UnitOfWorkScope class.

Unit of work scope

You can create a different and isolated transaction in another transaction or can create a non-transactional scope in a transaction. .NET defines TransactionScopeOption for that. You can set Scope option of the unit of work.

Automatically saving changes

When we use unit of work for a method, ASP.NET Boilerplate saves all changes at the end of the method automatically. Assume that we need method to update name of a person:

[UnitOfWork]
public void UpdateName(UpdateNameInput input)
{
    var person = _personRepository.Get(input.PersonId);
    person.Name = input.NewName;
}

That's all, name was changed! We did not even called _personRepository.Update method. O/RM framework keep track of all changes of entities in a unit of work and reflects changes to the database.

Note that no need to declare UnitOfWork for application service methods since they are unit of work as default.

IRepository.GetAll() method

When you call GetAll() out of a repository method, there must be an open database connection since it returns IQueryable. This is needed because of deferred execution of IQueryable. It does not perform database query unless you call ToList() method or use the IQueryable in a foreach loop (or somehow access to queried items). So, when you call ToList() method, database connection must be alive.

Consider the example below:

[UnitOfWork]
public SearchPeopleOutput SearchPeople(SearchPeopleInput input)
{
    //Get IQueryable<Person>
    var query = _personRepository.GetAll();

    //Add some filters if selected
    if (!string.IsNullOrEmpty(input.SearchedName))
    {
        query = query.Where(person => person.Name.StartsWith(input.SearchedName));
    }

    if (input.IsActive.HasValue)
    {
        query = query.Where(person => person.IsActive == input.IsActive.Value);
    }

    //Get paged result list
    var people = query.Skip(input.SkipCount).Take(input.MaxResultCount).ToList();

    return new SearchPeopleOutput { People = Mapper.Map<List<PersonDto>>(people) };
}

Here, SearchPeople method must be unit of work since ToList() method of IQueryable is called in the method body, and database connection must be open when IQueryable.ToList() is executed.

Like that for GetAll() method, you must use unit of work if a database connection is needed out of the repository. Note that application service methods are unit of work by default. 

UnitOfWork attribute restrictions

You can use UnitOfWork attribute for;

It's suggested to always make the method virtual. You can not use for private methods. Because, ASP.NET Boilerplate uses dynamic proxying for that and private methods can not be seen by derived classes. UnitOfWork attribute (and any proxying) does not work if you don't use dependency injection and instantiate the class yourself.

Options

There are some options can be used to change behaviour of a unit of work.

First, we can change default values of all unit of works in the startup configuration. This is generally done in PreInitialize method of our module.

public class SimpleTaskSystemCoreModule : AbpModule
{
    public override void PreInitialize()
    {
        Configuration.UnitOfWork.IsolationLevel = IsolationLevel.ReadCommitted;
        Configuration.UnitOfWork.Timeout = TimeSpan.FromMinutes(30);
    }

    //...other module methods
}

Second, we can override defaults for a particular unit of work. For that, UnitOfWork attribute constructor and IUnitOfWorkManager.Begin method have overloads to get options.

Methods

UnitOfWork system works seamlessly and invisibly. But, in some special cases, you need to call it's methods.

SaveChanges

ASP.NET Boilerplate saves all changes at end of a unit of work, you don't have to do anything. But, sometimes, you may want to save changes to database in middle of a unit of work operation. In this case, you can inject IUnitOfWorkManager and call IUnitOfWorkManager.Current.SaveChanges() method. An example usage may be saving changes to get Id of a new inserted Entity in EntityFramework. Note that: if current unit of work is transactional, all changes in the transaction are rolled back if an exception occurs, even saved changes.

Events

A unit of work has Completed, Failed and Disposed events. You can register to these events and perform needed operations. Inject IUnitOfWorkManager and use IUnitOfWorkManager.Current property to get active unit of work and register to it's events.

You may want to run some code when current unit of work successfully completed. Example:

public void CreateTask(CreateTaskInput input)
{
    var task = new Task { Description = input.Description };

    if (input.AssignedPersonId.HasValue)
    {
        task.AssignedPersonId = input.AssignedPersonId.Value;

        _unitOfWorkManager.Current.Completed += (sender, args) => { /* TODO: Send email to assigned person */ };
    }

    _taskRepository.Insert(task);
}