How to use Dapper with Repository Pattern / Unit of Work?

.net c# dapper unit-of-work

Question

Coming up with a good solution using Dapper, Repositories, and Unit of Work has been a challenge for me. I've done a lot of research and have seen implementations where the Unit of Work class has a dictionary of repositories. That doesn't seem to be the right way to go about it. Here's what I'm trying to do.

UnitOfWork.cs

public interface IUnitOfWork
{
    SqlConnection GetConnection();

    SqlTransaction GetTransaction();

    void CommitChanges();
}

public class UnitOfWork : IUnitOfWork
{
    private SqlConnection connection;
    private SqlTransaction transaction;

    public SqlConnection GetConnection()
    {
        if (connection != null)
        {
            return connection;
        }

        connection = new SqlConnection(@"Data Source=");
        connection.Open();
        transaction = connection.BeginTransaction();
        return connection;
    }

    public SqlTransaction GetTransaction()
    {
        return this.transaction;
    }

    public void CommitChanges()
    {
        try
        {
            transaction.Commit();
        }
        catch
        {
            transaction.Rollback();
        }
        finally
        {
            transaction.Dispose();
            connection.Close();
        }
    }
}

I know this is probably a terrible implementation, but I'm just trying to start a foundation.

Here is a service implementation. The UnitOfWork instance has been injected into the service.

public class VeterinarianService : IVeterinarianService
{
    private readonly IClock clock;
    private readonly IUnitOfWork work;
    private readonly IVeterinarianRepository vetRepository;
    private readonly ITestingRepository testingRepository;


    public VeterinarianService(IClock clock, IUnitOfWork work, IVeterinarianRepository vetRepository, ITestingRepository testingRepository)
    {
        this.clock = clock;
        this.work = work;
        this.vetRepository = vetRepository;
        this.testingRepository = testingRepository;
    }

    /// <summary>
    /// Creates a veterinarian.
    /// </summary>
    /// <param name="newVetDTO">The newVetDTO containing all required parameters.</param>
    /// <returns>The newly created veterinarian.</returns>
    public async Task<VeterinarianDTO> CreateVeterinarian(NewVeterinarianDTO newVetDTO)
    {
        var now = clock.Now.ToDateTimeUtc();

        var vet = Mapper.Map<Veterinarian>(newVetDTO);
        var veterinarian = await vetRepository.Create(vet, now);
        // Call the second repository method here.

        // Commit the database changes from both repositories.
        this.work.CommitChanges();
        return Mapper.Map<VeterinarianDTO>(veterinarian);
    }
}

Here is the repository implementation. Note that the same UnitOfWork object is injected into this repository. It is the same UnitOfWork that was injected into the service class.

public class VeterinarianRepository : IVeterinarianRepository
{
    private readonly ICache cache;
    private readonly IUnitOfWork work;

    private readonly TimeSpan cacheTimeSpan = new TimeSpan(5, 0, 0);
    private const CommandType Type = CommandType.StoredProcedure;

    public VeterinarianRepository(ICache cache, IUnitOfWork work)
    {
        this.cache = cache;
        this.work = work;
    }

    public async Task<Veterinarian> Create(Veterinarian vet, DateTime date)
    {
            var connection = work.GetConnection();
            var parameters = new DynamicParameters();
            parameters.Add("@FirstName", vet.FirstName);
            parameters.Add("@LastName", vet.LastName);
            parameters.Add("@Date", date);
            parameters.Add("@User", 1);


            var identity = await connection.ExecuteScalarAsync<int?>("dbo.CreateVeterinarian", parameters, this.work.GetTransaction(), commandType: Type);

            if (identity.HasValue)
                vet.VeterinarianIdentity = identity.Value;
            else throw new Exception();
    }
}

My thought process here is that the same instance of the UnitOfWork class will be injected into the service and the underlying repositories. A service can call multiple repositories using the same connection / transaction and if all was successful, it can commit the entire transaction, or roll the whole thing back. This would roll back any change made by any of the repositories.

Two questions:

  1. Is this a ridiculous way to implement such a thing?

  2. My VeterinarianRepository implementation, in the Create method, I pass the Transaction created by the UnitOfWork instance to the ExecuteScalarAsync method. After ExecuteScalarAsync executes however, the transaction seems to be completed. I get an "This SqlTransaction has completed; it is no longer usable." exception when trying to use the UnitOfWork CommitChanges() method. I was expecting the transaction to stay open so it could be used by a few other repositories, if the service required that.

1
3
10/11/2016 5:49:01 PM

Popular Answer

Your service should facilitate a repository that contains a constructor that gets a configuration or connection service. The implementation of that repository should, in my option, be built in a way that only the persistent storage endpoint (or connection) is needed for calls to be made.

Your VeterinarianRepository should be created with a configuration that defines a path to persistence and the implementation to carry out the command using a specific implementation. There is no orchestration managed or known about to your VeterinarianRepository, outside of it's scope of work. Also, again my opinion, your VeterinarianService should be able to function regardless of how the invoked VeterinarianRepository did or did not do it's work. I don't know if this helps in relation to dapper, however, ORMs carrying their own hardened implementations are not very repository'esque friendly to generic services.

0
10/11/2016 8:59:41 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow