Repository Pattern (C#)
There are some really good articles out there about the repository pattern but I haven’t seen one that strongly demonstrates different data providers, sample code, or some of the tricks I’ve learned. I primarily build internal business applications so I’ve used this pattern at almost every .NET job I’ve ever had. It’s very common and I’ve learned a few tricks over the years.
GitHub
I’ve set up a sample Repository Pattern Project on GitHub that will allow you to clone it. This project is an MVC application built on Domain-Driven Design (DDD) principles. It’s built with Docker and creates an MSSQL Server, MongoDB, and a Postgres server. There are 4 repositories that interface each of those databases and a fake repository for unit testing. I even threw in some Dapper.
What you will learn:
- Repository Pattern
- Dapper with (MSSQL Stored Procedures, Postgres)
- MongoDB
- Decoupling repositories for unit testing
- Unit Testing with Moq
- Dependency Injection
- How to change data providers with ease
Getting Started
You will need to clone this repository from GitHub and run Docker Compose. This will set up Docker containers for MSSQL, MongoDB, and PostgreSQL. The MSSQL container auto seeds the database with stored procedures and everything to start with.
git clone https://github.com/mrjamiebowman-blog/C-Sharp-Repository-Pattern.git
Then you will need to cd into that directory to run Docker Compose.
docker-compose up
Repository Pattern
The repository pattern is fairly simple. It’s very easy to decouple code and replace a data provider in the future by encapsulating that logic within this pattern. It’s also very easy to unit test because a fake repository can be created and used as a substitute for the real one. On a build server, making requests to the real database could cause unit tests to fail due to latency or other issues.
Repository Interface
The repository interface defines what methods are expected from any repository that derives from this interface.
The name of the interface should always be generic.
ICustomersRepository is more generic than ICustomersSqlRepository.
Tips: Typically, the repository pattern is used for breaking apart dependencies for unit testing. However, if you truly needed to interface multiple databases then use a dynamic/object instead of integers for a more anonymous parameter value. Normally, I would use a normal integer for an MSSQL database.
Certain data sources may use different types of identifiers. While MSSQL commonly uses integers, it could use a composite key or MongoDB for example uses alphanumeric keys while Dynamics CRM/D365 uses Guids to identify records.
int id = 4; // mssql
string id = “5f0a888694e331568c891831”; // mongodb
string id = “D1822037-16D0-46B3-89D1-9B666A796059”; // dynamics crm
|
1 2 3 4 5 6 7 8 9 |
public interface ICustomersRepository { Task<Customer> GetByIdAsync(dynamic id); Task<List<Customer>> GetCustomersAsync(); Task DeleteByIdAsync(dynamic id); Task<Customer> CreateAsync(Customer model); Task<Customer> SaveAsync(Customer model, bool upsert = true); Task InitDb(); } |
Dependency Injection
During the Startup.cs process the ConfigureServices method is called. This is where dependency injection must be configured.
Manual Configuration
|
1 2 3 4 5 6 7 |
public void ConfigureServices(IServiceCollection services) { services.AddControllersWithViews(); services.AddTransient<IDataService, DataService>(); services.AddTransient<ICustomersRepository, SqlCustomersRepository>(); } |
One way to do this is to manually configure this in the Startup.cs like above. Each entity will have a repository so this could become cumbersome.
Alternative Configuration
An alternative would be to create a static method on the DataService class that handles the injection mapping. This would leave all of the dependency injection logic in the RepositoryPattern.Data library which could be beneficial, especially if this were a NuGet library or a Git submodule that was being reused across multiple projects.
|
1 2 3 4 5 |
public void ConfigureServices(IServiceCollection services) { services.AddControllersWithViews(); DataService.ConfigureServices(services); } |
DataService.cs
|
1 2 3 4 5 |
public static void ConfigureServices(IServiceCollection services) { services.AddTransient<IDataService, DataService>(); services.AddTransient<ICustomersRepository, SqlCustomersRepository>(); } |
Facade Service (DataService.cs)
Repository code should do one thing, interface the data provider. If there is explicit logic happening in the repository code then you’re doing it wrong. I typically wrap all of my logic in a facade Data Service class. If I had any validation, or modification to the data before it is saved, it would happen in the Data Service. That way, the same logic could be applied regardless of the database that is being used.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
public class DataService : IDataService { private readonly ICustomersRepository _customersRepository; public DataService(ICustomersRepository customersRepository) { _customersRepository = customersRepository; } public static void ConfigureServices(IServiceCollection services) { services.AddTransient<IDataService, DataService>(); services.AddTransient<ICustomersRepository, SqlCustomersRepository>(); //services.AddTransient<ICustomersRepository, MongoCustomersRepository>(); } public DataService(ICustomersRepository customersRepository) : this() { _customersRepository = customersRepository; } #region Customers public async Task<Customer> CreateCustomerAsync(Customer model) { // validation logic return await _customersRepository.CreateAsync(model); } public async Task DeleteCustomerByIdAsync(dynamic id) { await _customersRepository.DeleteByIdAsync(id); } public async Task<Customer> GetCustomerByIdAsync(dynamic id) { return await _customersRepository.GetByIdAsync(id); } public async Task<List<Customer>> GetCustomersAsync() { return await _customersRepository.GetCustomersAsync(); } public async Task<Customer> SaveCustomerAsync(Customer model, bool upsert = true) { // validation logic return await _customersRepository.SaveAsync(model, upsert); } #endregion } |
SQL Customer Repository (Dapper)
Dapper is the absolute most powerful Object Relational Mapper (ORM) I’ve come across. The reason I feel so strongly about this statement is that Dapper can do things that most of the other ORMs can’t. Dapper is completely capable of splitting and managing multiple datasets being returned from a stored procedure. It’s easy and effective to write custom mappers for Dapper. However, those specific features don’t work well for cross-database implementations.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
public class SqlCustomersRepository : BaseSqlRepository, ICustomersRepository { public async Task<Customer> CreateAsync(Customer model) { try { using (IDbConnection conn = GetConnection()) { conn.Open(); var parameters = new DynamicParameters(); parameters.Add("@FirstName", model.FirstName); parameters.Add("@LastName", model.LastName); parameters.Add("@Email", model.Email); if (model.BillingAddress.AddressId.HasValue) parameters.Add("@BillingAddressId", model.BillingAddress.AddressId); if (model.ShippingAddress.AddressId.HasValue) parameters.Add("@ShippingAddressId", model.ShippingAddress.AddressId); var data = (await conn.QueryAsync<Customer>(StoredProcedures.CreateCustomer, parameters, commandType: CommandType.StoredProcedure)).SingleOrDefault(); return data; } } catch (Exception ex) { throw ex; } } |
Base Repository
Creating an abstract base repository is a good choice when derived repositories need to share common logic like SQL connections, stored procedure names, or any other common logic.
Use a static class to reference stored procedure names
I also like to create a sub-class with static properties to reference stored procedures. This is very useful because you can see how many references there are to those stored procedures. This helps identify duplicate code and makes refactoring easier because it will be easier to identify which functions are using these stored procedures.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
public abstract class BaseSqlRepository : BaseRepository { private IConfigurationSection _databaseSettings; private IConfigurationSection _settings; protected bool? _debug { get; set; } private static string dbSchema = "dbo"; public BaseSqlRepository() { _databaseSettings = GetConfigurationSection("SqlDatabase"); } public IDbConnection GetConnection() { var conn = new SqlConnection(_databaseSettings["ConnectionString"]); return conn; } public static class StoredProcedures { public static string CreateCustomer = $"{dbSchema}.uspCustomerCreate"; public static string GetCustomer = $"{dbSchema}.uspCustomerGet"; public static string DeleteCustomer = $"{dbSchema}.uspCustomerDelete"; public static string SaveCustomer = $"{dbSchema}.uspCustomerSave"; public static string GetCustomers = $"{dbSchema}.uspCustomersGet"; } } |
Switching data providers with ease
Switching data providers is easier because the repository pattern encapsulates the data logic. If we were to decide to switch to another data provider like MongoDB I could easily create a MongoCustomersRepository that interfaces ICustomerRepository and set up my logic there. Switching data providers at this point are as easy as configuring the dependency resolver to map ICustomersRepository to MongoCustomersRepository. This is a great design pattern if there are plans to change to another data source in the future. A popular open-source alternative to MSSQL is Postgres. This may reduce costs since licensing isn’t required.
|
1 2 3 4 5 6 |
public static void ConfigureServices(IServiceCollection services) { services.AddTransient<IDataService, DataService>(); //services.AddTransient<ICustomersRepository, SqlCustomersRepository>(); services.AddTransient<ICustomersRepository, MongoCustomersRepository>(); } |
Unit Testing
Generic Repository Pattern
I highly recommend reading this article. It covers more about the repository pattern and how to write a generic repository.
https://codewithshadman.com/repository-pattern-csharp/
Links
https://codewithshadman.com/repository-pattern-csharp/
I really liked this article. He covers a lot of other points like Entity Framework, and UnitOfWork.
4 Common Mistakes with the Repository Pattern
This was also mentioned on Shadman’s blog. I highly recommend looking over this as well.
