Database Connection and Transaction Management Strategies

Effective management of database connections and transactions is critical for application performance and data integrity. In .NET, connection pooling optimizes the creation cost of connections by reusing existing objects. While acquiring a connection from the pool is efficient, it is essential to release connections back to the pool immediately after use to prevent resource exhaustion.

Two primary architectural patterns are commonly used to handle database lifecycle:

1. Per-Request Connection: A connection is opened when a web request begins (e.g., in Application_BeginRequest) and closed when the request ends. While simple, this is inefficient because it holds a connection open even when the request performs no database operations, unnecessarily tying up pool resources.

2. Manual Connection Handling: A connection is opened only when a database operation is imminent and closed immediately afterward. This maximizes efficiency but results in repetitive, boilerplate code scattered throughout the application.

ABP's Approach to Connection and Transaction Management

The ABP framework (ASP.NET Boilerplate) integrates the best aspects of both strategies, offering an automated model that handles connections and transactions seamlessly without repetitive code.

Repository Classes

Repositories serve as the primary access point for data manipulation. ABP automatically initializes a database connection and begins a transaction upon entering a repository method. The transaction is committed and the connection released upon successful exit. If an exception occurs, the transaction is rolled back. Consequently, repository methods function implicitly as atomic Units of Work (UoW).

Consider the following example using NHibernate:

public class ProductRepository : NhRepositoryBase<Product>, IProductRepository
{
  public IList<Product> GetFeaturedProducts(string filterText)
  {
    var dataQuery = from item in Session.Query<Product>()
                    where item.IsFeatured && !item.IsArchived
                    select item;
 
    if (!string.IsNullOrWhiteSpace(filterText))
    {
      dataQuery = dataQuery.Where(item => item.Name.Contains(filterText));
    }
 
    return dataQuery.ToList();
  }
}

No explicit database connection or session management code is required within the repository.

When a repository method calls another repository method, they share the same connection and transaction context. The initial method manages the lifecycle, while nested methods simply participate in the existing transaction.

Application Service Classes

Methods within application services are also treated as Units of Work by default. For example, in the service below, multiple repositories operate within a single transactional scope:

public class OrderAppService : IOrderAppService
{
  private readonly IOrderRepository _orderRepo;
  private readonly IAuditLogRepository _auditLogRepo;
 
  public OrderAppService(IOrderRepository orderRepo, IAuditLogRepository auditLogRepo)
  {
    _orderRepo = orderRepo;
    _auditLogRepo = auditLogRepo;
  }
 
  public void PlaceOrder(PlaceOrderDto dto)
  {
    var newOrder = new Order { CustomerId = dto.CustomerId, TotalAmount = dto.Amount };
    _orderRepo.Add(newOrder);
    _auditLogRepo.LogAction("Order Created", newOrder.Id);
  }
}

ABP opens a connection and transaction when PlaceOrder is invoked. Both repository inserts share this context. The transaction commits only if the method completes without throwing an exception.

Manual Unit of Work Control

While ABP handles UoW automatically for repositories and application services, you can manage it explicitly using the UnitOfWork attribute or the IUnitOfWorkManager.

Using the UnitOfWork Attribute:

[UnitOfWork]
public void ProcessOrder(ProcessOrderInput input)
{
  var order = new Order { Id = input.OrderId, Status = OrderStatus.Processed };
  _orderRepo.Update(order);
  _notificationService.NotifyUser(input.UserId);
}

Using IUnitOfWorkManager:

For more granular control, you can inject IUnitOfWorkManager to define a specific scope:

public class PaymentService
{
  private readonly IUnitOfWorkManager _uowManager;
  private readonly IInvoiceRepository _invoiceRepo;
  private readonly IPaymentRepository _paymentRepo;
 
  public PaymentService(IUnitOfWorkManager uowManager, IInvoiceRepository invoiceRepo, IPaymentRepository paymentRepo)
  {
    _uowManager = uowManager;
    _invoiceRepo = invoiceRepo;
    _paymentRepo = paymentRepo;
  }
 
  public void ProcessPayment(PaymentDto dto)
  {
    var payment = new Payment { Amount = dto.Amount, InvoiceId = dto.InvoiceId };
 
    using (var uow = _uowManager.Begin())
    {
      _invoiceRepo.MarkAsPaid(dto.InvoiceId);
      _paymentRepo.Insert(payment);
 
      uow.Complete();
    }
  }
}

Note the explicit call to uow.Complete(). If this method is not called, the transaction is rolled back. The Begin method accepts options to configure the unit of work behavior.

Advanced Unit of Work Configuration

Disabling the Unit of Work

Application service methods have UoW enabled by default. To disable this behavior, use the IsDisabled property. This is useful for methods that perform no database operations or require manual transaction scoping:

[UnitOfWork(IsDisabled = true)]
public virtual void RemoveConnection(RemoveConnectionInput input)
{
  _connectionRepository.Delete(input.Id);
}

If a disabled method is called by another method that has an active UoW, it will participate in the caller's transaction regardless of the attribute.

Non-Transactional Units of Work

By default, a Unit of Work is transactional. To execute database operations without a transaction (e.g., to minimize locking during read-heavy tasks), set the transactional parameter to false:

[UnitOfWork(isTransactional: false)]
public TaskListDto GetTasks(TaskFilterInput input)
{
  var tasks = _taskRepo.GetAllWithDetails(input.AssignedUserId, input.Status);
  return new TaskListDto
      {
        Items = ObjectMapper.Map<List<TaskDto>>(tasks)
      };
}

While the database transaction is disabled, the ORM still tracks changes. Be cautious using this setting, as data integrity typically relies on transactions.

Nested Unit of Work Calls

If a method marked with [UnitOfWork] calls another method also marked with the attribute, they share the same database connection and transaction. The outermost scope manages the lifecycle. This applies to attribute-based and manual scopes as long as execution occurs on the same thread.

Automatic Change Tracking

When a Unit of Work is active, ABP automatically commits all changes at the end of the method. You do not need to call update methods explicitly for tracked entities:

[UnitOfWork]
public void UpdateProfile(UpdateProfileDto input) {
  var user = _userRepo.Get(input.UserId);
  user.FullName = input.NewName;
}

In this example, the change to user.FullName is automatically saved to the database when the method completes.

Handling IQueryable and Deferred Execution

Returning IQueryable from a repository requires an active database connection because execution is deferred. Therefore, the method consuming the IQueryable must be a Unit of Work to ensure the connection is open when the query is materialized (e.g., via ToList()):

[UnitOfWork]
public UserListDto FilterUsers(UserFilterDto filter)
{
  var baseQuery = _userRepository.GetAll();
 
  if (!string.IsNullOrEmpty(filter.Keyword))
  {
    baseQuery = baseQuery.Where(u => u.FullName.Contains(filter.Keyword));
  }
 
  if (filter.RoleId.HasValue)
  {
    baseQuery = baseQuery.Where(u => u.RoleId == filter.RoleId.Value);
  }
 
  var result = baseQuery.Skip(filter.SkipCount).Take(filter.MaxResultCount).ToList();
 
  return new UserListDto { Items = ObjectMapper.Map<List<UserDto>>(result) };
}

Method Constraints

The [UnitOfWork] attribute can only be applied to:

• Public methods of classes injected via interfaces.
• Public virtual methods of self-injected classes (like MVC Controllers).
• Protected virtual methods.

Methods must be virtual to allow ABP to create dynamic proxies. Private methods or manually instantiated classes will not support interception.

Global Configuration and Events

Default UoW settings can be modified in the module's PreInitialize method:

public override void PreInitialize()
{
  Configuration.UnitOfWork.IsolationLevel = System.Data.IsolationLevel.ReadCommitted;
  Configuration.UnitOfWork.Timeout = TimeSpan.FromMinutes(30);
}

You can also interact with the current UoW manually. For instance, to save changes mid-transaction or trigger logic upon completion:

public void CreateTask(CreateTaskDto input)
{
  var task = new Task { Description = input.Description };
 
  if (input.AssignedUserId.HasValue)
  {
    task.AssignedUserId = input.AssignedUserId.Value;
    _unitOfWorkManager.Current.Completed += (sender, args) => 
    { 
      /* Logic to send email to assigned user */ 
    };
  }
 
  _taskRepo.Insert(task);
}