Repository Design Pattern with Dapper

Question

This is maybe more a question for code review rather than stack overflow.

I am using Dapper for a MicroORM to retrieve and Save Data to SQL Server 2014. I have got DTO classes in a DTO Proj that represent the Data retrieved from the DB or saved to the DB.

I am using the Repository Pattern so at my Service layer if a repository is required I am using constructor DI to inject that dependency and then call the method on the Repository to do the work.

so let say I have 2 services called CustomerService and CarService.

I then have 2 Repositories a CustomerRepository and a CarRepository.

I have an interface which defines all the methods in each Repository and then the concrete implementations.

An example method is shown below (calling a Stored Proc to do the DB INSERT (note the actual string variable for the stored proc is defined as a private string at the top of the class):

    public void SaveCustomer(CustomerDTO custDTO)
    {
        using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["myDB"].ConnectionString))
        {
            db.Execute(saveCustSp, custDTO, commandType: CommandType.StoredProcedure);
        }
    }

This all works fine but I am finding myself repeating the using block in every method in every repository. I have two real questions outlined below.

Is there a better approach which I could be using perhaps somehow using a BaseRepository class which every other Repository inherits from and the Base would implement the instantiation of the DB connection?

Would that still work ok for multiple concurrent Users on the system?

****UPDATE****

Based on Silas answer I have created the following

public interface IBaseRepository
{
    void Execute(Action<IDbConnection> query);
}

public class BaseRepository: IBaseRepository
{
        public void Execute(Action<IDbConnection> query)
        {
            using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["myDB"].ConnectionString))
            {
                query.Invoke(db);
            }
        }
}

However, in my repositories, I have other methods such as the below:

    public bool IsOnlyCarInStock(int carId, int year)
    {
        using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["myDB"].ConnectionString))
        {
            var car = db.ExecuteScalar<int>(anotherStoredSp, new { CarID = carId, Year = year },
                                commandType: CommandType.StoredProcedure);

            return car > 0 ? true : false;
        }
    }

and

    public IEnumerable<EmployeeDTO> GetEmployeeDetails(int employeeId)
    {
        using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["myDB"].ConnectionString))
        {
            return db.Query<EmployeeDTO>(anotherSp, new { EmployeeID = employeeId },
                                commandType: CommandType.StoredProcedure);
        }
    }

What is the correct way to add these to my Base repository using Generic Type T so I could return any type of DTO or any C# Native type

Solution 1

Sure, a function to create and dispose your Connection will work great.

protected void Execute(Action<IDbConnection> query)
{
    using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["myDB"].ConnectionString))
    {
        query.Invoke(db);
    }
}

And your simplified call site:

public void SaveCustomer(CustomerDTO custDTO)
{
    Execute(db => db.Execute(saveCustSp, custDTO, CommandType.StoredProcedure));
}

---

With Return Values:

public T Get<T>(Func<IDbConnection, T> query)
{
    using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["myDB"].ConnectionString))
    {
        return query.Invoke(db); 
    }
}

In your call site, just write the logic you wish to use.

public IEnumerable<EmployeeDTO> GetEmployeeDetails(int employeeId)
{
    return Get<IEnumerable<EmployeeDTO>(db => 
        db.Query<EmployeeDTO>(anotherSp, new { EmployeeID = employeeId }, CommandType.StoredProcedure));
}

Solution 2

This is not directly relevant to your question. But I suggest you consider using DapperExtensions.

Initially, I did implemented Repository pattern using Dapper. The drawback was that, I have to write queries all over; it was very stringy. Due to hard coded queries it was near to impossible to write generic repository.

Recently, I upgraded my code to use DapperExtensions. This fixes lot many issues.

Following is the generic repository:

public abstract class BaseRepository<T> where T : BasePoco
{
    internal BaseRepository(IUnitOfWork unitOfWork)
    {
        dapperExtensionsProxy = new DapperExtensionsProxy(unitOfWork);
    }

    DapperExtensionsProxy dapperExtensionsProxy = null;

    protected bool Exists()
    {
        return (GetCount() == 0) ? false : true;
    }

    protected int GetCount()
    {
        var result = dapperExtensionsProxy.Count<T>(null);
        return result;
    }

    protected T GetById(Guid id)
    {
        var result = dapperExtensionsProxy.Get<T>(id);
        return result;
    }
    protected T GetById(string id)
    {
        var result = dapperExtensionsProxy.Get<T>(id);
        return result;
    }

    protected List<T> GetList()
    {
        var result = dapperExtensionsProxy.GetList<T>(null);
        return result.ToList();
    }

    protected void Insert(T poco)
    {
        var result = dapperExtensionsProxy.Insert(poco);
    }

    protected void Update(T poco)
    {
        var result = dapperExtensionsProxy.Update(poco);
    }

    protected void Delete(T poco)
    {
        var result = dapperExtensionsProxy.Delete(poco);
    }

    protected void DeleteById(Guid id)
    {
        T poco = (T)Activator.CreateInstance(typeof(T));
        poco.SetDbId(id);
        var result = dapperExtensionsProxy.Delete(poco);
    }
    protected void DeleteById(string id)
    {
        T poco = (T)Activator.CreateInstance(typeof(T));
        poco.SetDbId(id);
        var result = dapperExtensionsProxy.Delete(poco);
    }

    protected void DeleteAll()
    {
        var predicateGroup = new PredicateGroup { Operator = GroupOperator.And, Predicates = new List<IPredicate>() };
        var result = dapperExtensionsProxy.Delete<T>(predicateGroup);//Send empty predicateGroup to delete all records.
    }

As you can see in above code, most of the methods are just wrapper over underlying DapperExtensionsProxy class. DapperExtensionsProxy internally also manages UnitOfWork which you can see below. These two classes can be combined without any issue. I personally prefer to keep them separate.

You can also notice that additional methods Exists, DeleteById, and DeleteAll are implemented those are not part of DapperExtensionsProxy.

Method poco.SetDbId is defined in each POCO class to set its Identifier property. In my case, identifiers of POCOs may have different datatypes and names.

Following is DapperExtensionsProxy:

internal sealed class DapperExtensionsProxy
{
    internal DapperExtensionsProxy(IUnitOfWork unitOfWork)
    {
        this.unitOfWork = unitOfWork;
    }

    IUnitOfWork unitOfWork = null;

    internal int Count<T>(object predicate) where T : BasePoco
    {
        var result = unitOfWork.Connection.Count<T>(predicate, unitOfWork.Transaction);
        return result;
    }

    internal T Get<T>(object id) where T : BasePoco
    {
        var result = unitOfWork.Connection.Get<T>(id, unitOfWork.Transaction);
        return result;
    }

    internal IEnumerable<T> GetList<T>(object predicate, IList<ISort> sort = null, bool buffered = false) where T : BasePoco
    {
        var result = unitOfWork.Connection.GetList<T>(predicate, sort, unitOfWork.Transaction, null, buffered);
        return result;
    }

    internal IEnumerable<T> GetPage<T>(object predicate, int page, int resultsPerPage, IList<ISort> sort = null, bool buffered = false) where T : BasePoco
    {
        var result = unitOfWork.Connection.GetPage<T>(predicate, sort, page, resultsPerPage, unitOfWork.Transaction, null, buffered);
        return result;
    }

    internal dynamic Insert<T>(T poco) where T : BasePoco
    {
        var result = unitOfWork.Connection.Insert<T>(poco, unitOfWork.Transaction);
        return result;
    }

    internal void Insert<T>(IEnumerable<T> listPoco) where T : BasePoco
    {
        unitOfWork.Connection.Insert<T>(listPoco, unitOfWork.Transaction);
    }

    internal bool Update<T>(T poco) where T : BasePoco
    {
        var result = unitOfWork.Connection.Update<T>(poco, unitOfWork.Transaction);
        return result;
    }

    internal bool Delete<T>(T poco) where T : BasePoco
    {
        var result = unitOfWork.Connection.Delete<T>(poco, unitOfWork.Transaction);
        return result;
    }

    internal bool Delete<T>(object predicate) where T : BasePoco
    {
        var result = unitOfWork.Connection.Delete<T>(predicate, unitOfWork.Transaction);
        return result;
    }
}

Following is the BasePoco used above:

public abstract class BasePoco
{
    Guid pocoId = Guid.NewGuid();

    public Guid PocoId { get { return pocoId; } }

    public virtual void SetDbId(object id)
    {//Each POCO should override this method for specific implementation.
        throw new NotImplementedException("This method is not implemented by Poco.");
    }

    public override string ToString()
    {
        return PocoId + Environment.NewLine + base.ToString();
    }
}

This also uses UnitOfWork which is explained here.

Solution 3

I know this is a very old question, but I still wanted to make a suggestion.

Dapper.SimpleRepository is a NuGet package that has already done all of the work for you of creating a Repository built on top of Dapper. It gives you basic CRUD methods as well as the ability to use filters, full queries, stored procs, etc. It supports both Async and Non-Async. And it will work with Framework, Standard, and Core.

It gives you two options. Assuming Foo is a C# class that mirrors a database table...

Option 1: Create your repository by injecting the connection string and defining the type.

Dapper.SimpleRepository.Repository<Foo> fooRepo = new Dapper.SimpleRepository.Repository<Foo>("your connection string");

Then, basic CRUD is as simple as:

fooRepo.Insert(foo);    // Add a record to the database
fooRepo.Get(55);             // Get a sinlge item from the database by Id
fooRepo.Update(foo);    // Update a record in the database
fooRepo.Delete(55);          // Delete a single object from the database by Id

Option 2: Create your repository by injecting the connection string but DO NOT define the type.

Dapper.SimpleRepository.Repository repo = new Dapper.SimpleRepository.Repository("your connection string");

Then your CRUD methods look like this:

repo.Insert<Foo>(foo);    // Add a record to the database
repo.Get<Foo>(55);        // Get a sinlge item from the database by Id
repo.Update<Foo>(foo);    // Update a record in the database
repo.Delete<Foo>(55);     // Delete a single object from the database by Id

For all of the methods that go beyond basic crud (and there are many), see the GitHub page.

(Full disclosure... I created the NuGet package.)

Comments

[OrcusZ]

This is the way to achieve it, you need to make your BaseRepository Disposable to dispose your IDbConnection. You can have a look about working with repository pattern and unit of work pattern in microsoft documentation docs.microsoft.com/en-us/aspnet/mvc/overview/older-versions/‌​…

[Callum Linington]

the using block is a necessary evil because you're opening connections to the database that need to be closed. So the repetition is necessary. I would only suggest don't get caught up in the whole repository design pattern stuff....

[Ctrl_Alt_Defeat]

@Callum - what other pattern would you suggest or could you illustrate with an example. I had looked at using CQRS but I felt repository as above worked for me based on KISS

[David Klempfner]

Off topic but CustomerDTO should be CustomerDto. Classes need to use PascalCase (as recommended by Microsoft). Since the acronym DTO is greater than 2 characters, you need to make it Dto.

[Ctrl_Alt_Defeat]

Thanks Silas. Ill have a look at that. Would you recommend puttinh the Execute method in a Base repository and any other repositories inherit from that? Also will query.Invoke work for any type of Dapper db.function?

[Silas Reinagel]

If you expect to have many classes that need this functionality, then a base repository is a great idea.

[Silas Reinagel]

Invoking the query/action will work for anything you wish to do with an IDbConnection, including both Dapper methods, and non-Dapper methods.

[Ctrl_Alt_Defeat]

Hey - Silas - I have updated the question again - I added a few more examples of the type of methods I am calling in the different repositories - not sure how they should look in the Base Repository and then how I would call them from the Repository that inherits from Base

[Silas Reinagel]

I added code that should handle any query scenarios.

[Ctrl_Alt_Defeat]

Hi @Silas - I had a follow up question wonder could you help with - stackoverflow.com/questions/43192696/…

[Viacheslav Yankov]

@SilasReinagel, is it good to open new connection for every query? I am new to Dapper and don't know how to implement my DAL with it. Can there be performance problems?

[Silas Reinagel]

@syler It depends on your use case. If you are concerned with performance and have had a problem, then run a Profiler to see whether managing the connection manually is more performant. In general, I defer the decision to Dapper.

[Ajt]

Nice @Amit Joshi...if I have multiple database connection string will it work? Yes the pls suggest wat minimal changes need to be done?pls suggest

[Amit Joshi]

@LajithKumar: No change will be needed to make this work with multiple database instances. Note that UnitOfWork is injected in Repository like this BaseRepository(IUnitOfWork unitOfWork). You may create new UnitOfWork for each database instance and inject it in same class (new instance of Repository) without any change.

[Neutrino]

Isn't one of the key aspects of the repository pattern that repositories should not map directly to individual database tables, but instead to domain level aggregate root objects? I don't see how SimpleDapper achieves that.

[Neutrino]

Isn't another key aspect of the Repository Pattern that clients of domain objects are insulated from the details of specific data access implementations? But in this case clients use raw SQL strings to retrieve filtered domain objects. Maybe I'm wrong but this doesn't seem like a genuine implementation of the Repository Pattern at all to me, it's just a simple data access class.

[Bjørn]

What if the ID's in the database is GUID's?

[Casey Crookston]

@Bjørn ooo good question! I may have to test that out. But for now, you can get records using a select with a WHERE clause.

[Casey Crookston]

@Neutrino, not sure how I missed your comment before. You are correct that this isn't a true repository in the classic sense. But then, Dapper wasn't meant to operate in that realm. This is just a quick, light tool that comes close to operating like a repository without a complex usage of Unit of work.

[Bjørn]

In one of my recent endeavors I have made a repository that can handle all types of id's too. I'm not sure if I'm happy about it yet, but it uses a general identity object that uses whatever the implementation of the data layer uses, which is quite neat until now. I have not come to any problems with it yet, but I have to work with the identity object in the application code, not knowing if the id's are ints, long, guids or any other object.