How Should I Manage Connections and Transactions Effectively with Dapper?

Managing Connections with Dapper

In Dapper, managing connections is handled in two primary ways:

Fully Managing Connections

In this approach, the developer is responsible for opening and closing the connection explicitly. Similar to handling connections in ADO.NET.

Allowing Dapper to Manage Connections

Dapper can automatically open and close connections if not already managed by the developer. This is like using the DataAdapter.Fill() method.

However, it's recommended that developers manage their own connections, especially for scenarios involving multiple queries within a wider granularity (e.g., per request).

Connection Handling Best Practices

To avoid resource leaks and improve performance, connections should be closed after use. This can be achieved by:

• Calling Close(), Dispose(), or enclosing the connection in a using block.

To further enhance resource management, consider implementing a Unit of Work to manage transactions.

Sample Code for Unit of Work in C#

The following example provides a sample implementation of a UnitOfWork that handles transaction management:

public sealed class UnitOfWork : IUnitOfWork
{
    internal UnitOfWork(IDbConnection connection)
    {
        _id = Guid.NewGuid();
        _connection = connection;
    }

    IDbConnection _connection = null;
    IDbTransaction _transaction = null;
    Guid _id = Guid.Empty;

    IDbConnection IUnitOfWork.Connection
    {
        get { return _connection; }
    }
    IDbTransaction IUnitOfWork.Transaction
    {
        get { return _transaction; }
    }
    Guid IUnitOfWork.Id
    {
        get { return _id; }
    }

    public void Begin()
    {
        _transaction = _connection.BeginTransaction();
    }

    public void Commit()
    {
        _transaction.Commit();
        Dispose();
    }

    public void Rollback()
    {
        _transaction.Rollback();
        Dispose();
    }

    public void Dispose()
    {
        if(_transaction != null)
            _transaction.Dispose();
        _transaction = null;
    }
}

Repository Pattern with Unit of Work

Repositories in this code rely on dependency injection with constructor to receive the UnitOfWork, providing a central location for controlling access to the database.

Sample Code for Repository with Unit of Work

public sealed class MyRepository
{
    public MyRepository(IUnitOfWork unitOfWork) 
    {
        this.unitOfWork = unitOfWork;
    }
    
    IUnitOfWork unitOfWork = null;

    public MyPoco Get()
    {
        return unitOfWork.Connection.Query(sql, param, unitOfWork.Transaction, .......);
    }

    public void Insert(MyPoco poco)
    {
        return unitOfWork.Connection.Execute(sql, param, unitOfWork.Transaction, .........);
    }
}

Usage of Unit of Work

Example usage of the UnitOfWork with a transaction:

using(DalSession dalSession = new DalSession())
{
    UnitOfWork unitOfWork = dalSession.UnitOfWork;
    unitOfWork.Begin();
    try
    {
        //Database code
        MyRepository myRepository = new MyRepository(unitOfWork);
        myRepository.Insert(myPoco);
        unitOfWork.Commit();
    }
    catch
    {
        unitOfWork.Rollback();
        throw;
    }
}

Example usage of without a transaction:

using(DalSession dalSession = new DalSession())
{
    //Database code
    MyRepository myRepository = new MyRepository(dalSession.UnitOfWork);
    myRepository.Insert(myPoco);
}

By controlling connections and transactions centrally with a Unit of Work, developers can improve resource management and ensure proper data integrity.

The above is the detailed content of How Should I Manage Connections and Transactions Effectively with Dapper?. For more information, please follow other related articles on the PHP Chinese website!