As I've mentioned in earlier posts, the project my team and I are working on has an extremely normalized database. This is both annoying and useful; annoying because many things that do not need to be SQL tables are, and useful because everything is easily found and categorized.
Because we are not allowed to change the SQL architecture, we are attempting to make our C# architecture both match the SQL one where possible, and improve upon it. We decided early on that Dapper was going to be our data-access tech, and that we wanted to use the Repository-Service pattern that I've written about before.
So, in the interest of better code readability, we tried to see if it was possible to create a "layer" of our architecture below the repository layer. Said layer would be a "base" class that all other repositories would inherit from, and the methods would need to be generic.
We did find a way, and we think that way will be useful to more than just our team; hence, this post. Let's see how to use a Dapper Base Repository to improve our code's readability!
Anatomy of a Query Method
Here's an example of a simple Query<T> base method.
using Dapper;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
namespace DapperBaseRepositoryDemo.Repositories
{
public class DapperBaseRepository
{
public List<T> Query<T>(string query, object parameters = null)
{
try
{
using (SqlConnection conn
= new SqlConnection("Your Connection String"))
{
return conn.Query<T>(query, parameters).ToList();
}
}
catch (Exception ex)
{
//Handle the exception
return new List<T>();
}
}
}
}
The goal of these kinds of methods is to be as generic as possible, so that they are useful in as many situations as possible.
In the real world, though, we need several of these methods to make a useful base repository:
public class DapperBaseRepository
{
public T QueryFirst<T>(string query, object parameters = null)
{
try
{
using (SqlConnection conn
= new SqlConnection("Your Connection String"))
{
return conn.QueryFirst<T>(query, parameters);
}
}
catch (Exception ex)
{
//Handle the exception
return default; //Or however you want to handle the return
}
}
public T QueryFirstOrDefault<T>(string query, object parameters = null)
{
try
{
using (SqlConnection conn
= new SqlConnection("Your Connection String"))
{
return conn.QueryFirstOrDefault<T>(query, parameters);
}
}
catch (Exception ex)
{
//Handle the exception
return default; //Or however you want to handle the return
}
}
public T QuerySingle<T>(string query, object parameters = null)
{
try
{
using (SqlConnection conn
= new SqlConnection("Your Connection String"))
{
return conn.QuerySingle<T>(query, parameters);
}
}
catch (Exception ex)
{
//Handle the exception
return default; //Or however you want to handle the return
}
}
public T QuerySingleOrDefault<T>(string query, object parameters = null)
{
try
{
using (SqlConnection conn
= new SqlConnection("Your Connection String"))
{
return conn.QuerySingleOrDefault<T>(query, parameters);
}
}
catch (Exception ex)
{
//Handle the exception
return default; //Or however you want to handle the return
}
}
}In other situations, we might need asynchronous versions of these methods; they are omitted here for brevity.
Executing a Command
We might also want to allow our base repository to execute SQL commands, and we can do so with this method:
public class DapperBaseRepository
{
public void Execute(string query, object parameters = null)
{
try
{
using (SqlConnection conn
= new SqlConnection("Your Connection String"))
{
conn.Execute(query, parameters);
}
}
catch (Exception ex)
{
//Handle the exception
}
}
}Using the Base Repository
We can use the base repository by creating a "higher" repository class and inheriting from the base. For example, say we need a UserRepository with the following methods:
- GetByID()
- GetAll()
- SearchByFirstName()
We can implement each of these methods as a single line:
public class UserRepository : DapperBaseRepository
{
public User GetByID(int id)
{
return QuerySingle<User>("SELECT * FROM user WHERE ID = @userID",
new { userID = id });
}
public List<User> GetAll()
{
return Query<User>("SELECT * FROM user");
}
public List<User> SearchByFirstName(string firstName)
{
return Query<User>("SELECT * FROM user WHERE FirstName LIKE @firstName + '%'",
new { firstName = firstName });
}
}Other Considerations
The idea behind using this architecture is to simplify the code needed to write the repository layer. That improved readability matters, especially when dealing with large-scale projects such as ours.
Our project has over forty "primary" SQL tables and their corresponding C# classes. By sticking to the Repository-Service pattern we have at least forty Repository classes. While we can't do anything about the number of classes, we can reduce the amount of code in each of them, saving other developers who work on this project as well as our future selves precious minutes and seconds in reading and understanding the code.
Another benefit of this pattern is that it introduces a single point of failure when querying the database. If all queries (and potentially all SQL) go through this Dapper Base Repository class, then finding and tracking bug in that part of the system becomes much easier. Admittedly, this relies on a cohesive and well-defined error handling strategy, and that's a whole separate blog post.
Summary
Large-scale, complex projects benefit the most from readability improvments, and when using Dapper and C# the Dapper Base Repository pattern allows for better code readability and a single point of failure by creating a base class with generic methods that allow for querying and execution against a SQL database.
We are currently using this pattern in a production application. Do you see anything we could improve, or a way to make our code more readable? Share in the comments!
If you enjoyed this post, would you please consider buying me a coffee? Your support enables me to keep traditional advertising off my site, and is very much appreciated!
Happy Coding!
