Mastering Dapper with the Generic Repository Pattern
Welcome, fellow developer! Are you tired of writing the same CRUD operations over and over again for every new project? Are you fed up with the tedious task of mapping database entities to objects in your code? If you’re nodding your head in agreement, then you’re in luck! In this blog, we’re going to explore how to implement a generic repository using Dapper — a simple, yet powerful micro ORM that will make your life a whole lot easier. Before reading further Follow Me for such tech savvy content.
Why do we need Generic Repository?
A generic repository allows us to abstract away the details of data access, so we can focus on writing our business logic without worrying about the underlying data source. This means that we can reuse the same code across multiple entities and projects, making our code more modular and easier to maintain.
So without further ado lets get into the real deal.
Implementation
In this project, we have created basic CRUD functionality for a Product object using Generic Repository Pattern. Within generic repository, Dapper ORM is used for database connection, query execution and object mapping.
Project Structure
In this project we have created separate folders for Service, Repository and Model for separation of concerns.
Model
Product.cs
using System.ComponentModel.DataAnnotations.Schema;
using System.ComponentModel.DataAnnotations;
namespace DapperGenericRepository.Model
{
[Table("tbl_Product")]
public class Product
{
[Key]
[Column("Id")]
public int Id { get; set; }
[Column("product_name")]
public string Name { get; set; }
[Column("product_description")]
public string Description { get; set; }
[Column("product_price")]
public decimal Price { get; set; }
}
}This code represents a C# class that defines the properties and attributes of a Product entity in a database.
The [Table("tbl_Product")] attribute specifies the table name in the database that corresponds to this entity. The [Key] attribute indicates that the Id property is the primary key of this entity, while the [Column("Id")] attribute specifies the corresponding column name in the database.
The class also defines three additional properties for the Name, Description, and Price fields of the Product entity, each of which is mapped to a corresponding column in the database using the [Column] attribute. By using these attributes, we can easily map the properties of this class to the appropriate columns in the database and vice versa.
Repository
IGenericRepository.cs
namespace DapperGenericRepository.Repository
{
interface IGenericRepository<T>
{
T GetById(int id);
IEnumerable<T> GetAll();
bool Add(T entity);
bool Update(T entity);
bool Delete(T entity);
}
}This code defines an interface IGenericRepository<T> that provides basic CRUD methods for an entity of type T which is a C# generic type.
GenericRepository.cs
using System.ComponentModel.DataAnnotations.Schema;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using static Dapper.SqlMapper;
namespace DapperGenericRepository.Repository
{
public class GenericRepository<T> : IGenericRepository<T> where T : class
{
IDbConnection _connection;
readonly string connectionString = " "; //YOUR_SQL_CONNECTION_STRING
public GenericRepository()
{
_connection = new SqlConnection(connectionString);
}
public bool Add(T entity)
{
int rowsEffected = 0;
try
{
string tableName = GetTableName();
string columns = GetColumns(excludeKey: true);
string properties = GetPropertyNames(excludeKey: true);
string query = $"INSERT INTO {tableName} ({columns}) VALUES ({properties})";
rowsEffected = _connection.Execute(query, entity);
}
catch (Exception ex) { }
return rowsEffected > 0 ? true : false;
}
public bool Delete(T entity)
{
int rowsEffected = 0;
try
{
string tableName = GetTableName();
string keyColumn = GetKeyColumnName();
string keyProperty = GetKeyPropertyName();
string query = $"DELETE FROM {tableName} WHERE {keyColumn} = @{keyProperty}";
rowsEffected = _connection.Execute(query, entity);
}
catch(Exception ex) { }
return rowsEffected > 0 ? true : false;
}
public IEnumerable<T> GetAll()
{
IEnumerable<T> result = null;
try
{
string tableName = GetTableName();
string query = $"SELECT * FROM {tableName}";
result = _connection.Query<T>(query);
}
catch(Exception ex) { }
return result;
}
public T GetById(int Id)
{
IEnumerable<T> result = null;
try
{
string tableName = GetTableName();
string keyColumn = GetKeyColumnName();
string query = $"SELECT * FROM {tableName} WHERE {keyColumn} = '{Id}'";
result = _connection.Query<T>(query);
}
catch(Exception ex) { }
return result.FirstOrDefault();
}
public bool Update(T entity)
{
int rowsEffected = 0;
try
{
string tableName = GetTableName();
string keyColumn = GetKeyColumnName();
string keyProperty = GetKeyPropertyName();
StringBuilder query = new StringBuilder();
query.Append($"UPDATE {tableName} SET ");
foreach (var property in GetProperties(true))
{
var columnAttr = property.GetCustomAttribute<ColumnAttribute>();
string propertyName = property.Name;
string columnName = columnAttr.Name;
query.Append($"{columnName} = @{propertyName},");
}
query.Remove(query.Length - 1, 1);
query.Append($" WHERE {keyColumn} = @{keyProperty}");
rowsEffected = _connection.Execute(query.ToString(), entity);
}
catch (Exception ex) { }
return rowsEffected > 0 ? true : false;
}
private string GetTableName()
{
string tableName = "";
var type = typeof(T);
var tableAttr = type.GetCustomAttribute<TableAttribute>();
if (tableAttr != null)
{
tableName = tableAttr.Name;
return tableName;
}
return type.Name + "s";
}
public static string GetKeyColumnName()
{
PropertyInfo[] properties = typeof(T).GetProperties();
foreach (PropertyInfo property in properties)
{
object[] keyAttributes = property.GetCustomAttributes(typeof(KeyAttribute), true);
if (keyAttributes != null && keyAttributes.Length > 0)
{
object[] columnAttributes = property.GetCustomAttributes(typeof(ColumnAttribute), true);
if (columnAttributes != null && columnAttributes.Length > 0)
{
ColumnAttribute columnAttribute = (ColumnAttribute)columnAttributes[0];
return columnAttribute.Name;
}
else
{
return property.Name;
}
}
}
return null;
}
private string GetColumns(bool excludeKey = false)
{
var type = typeof(T);
var columns = string.Join(", ", type.GetProperties()
.Where(p => !excludeKey || !p.IsDefined(typeof(KeyAttribute)))
.Select(p =>
{
var columnAttr = p.GetCustomAttribute<ColumnAttribute>();
return columnAttr != null ? columnAttr.Name : p.Name;
}));
return columns;
}
protected string GetPropertyNames(bool excludeKey = false)
{
var properties = typeof(T).GetProperties()
.Where(p => !excludeKey || p.GetCustomAttribute<KeyAttribute>() == null);
var values = string.Join(", ", properties.Select(p =>
{
return $"@{p.Name}";
}));
return values;
}
protected IEnumerable<PropertyInfo> GetProperties(bool excludeKey = false)
{
var properties = typeof(T).GetProperties()
.Where(p => !excludeKey || p.GetCustomAttribute<KeyAttribute>() == null);
return properties;
}
protected string GetKeyPropertyName()
{
var properties = typeof(T).GetProperties()
.Where(p => p.GetCustomAttribute<KeyAttribute>() != null);
if (properties.Any())
{
return properties.FirstOrDefault().Name;
}
return null;
}
}
}This code defines a generic repository class called GenericRepository which implements the IGenericRepository interface. The GenericRepository class is used for performing CRUD (Create, Read, Update, and Delete) operations on a database table.
The class makes use of the Dapper ORM (Object-Relational Mapping) library to execute SQL queries and map the results to objects of the specified type T. The class has methods for adding, updating, deleting, and retrieving entities from the database.
The class uses reflection to get the table and column names from the properties of the entity class. The Table attribute is used to get the table name, and the Column attribute is used to get the column name.
The class uses the IDbConnection interface to interact with the database. The connection string is specified in the constructor of the class, and a new instance of the SqlConnection class is created to establish a connection to the database.
ProductRepository.cs
using DapperGenericRepository.Model;
namespace DapperGenericRepository.Repository
{
public class ProductRepository : GenericRepository<Product>
{
}
}This code defines a class called ProductRepository which inherits from a generic class GenericRepository<T>. The ProductRepository class specifies the type parameter Product to GenericRepository<T>, which means that it inherits all the methods defined in GenericRepository<T> but with the Product entity type.
Therefore, ProductRepository is a specific implementation of a generic repository for the Product entity. It can be used to perform CRUD (Create, Read, Update, Delete) operations on the Product table in a database using Dapper ORM (Object-Relational Mapping).
Service
ProductService.cs
using DapperGenericRepository.Model;
using DapperGenericRepository.Repository;
namespace DapperGenericRepository.Service
{
public class ProductService
{
public bool Add(Product product)
{
bool isAdded = false;
try
{
ProductRepository productRepository = new ProductRepository();
isAdded = productRepository.Add(product);
}
catch(Exception ex)
{
}
return isAdded;
}
public List<Product> GetAll()
{
List<Product> products = new List<Product>();
try
{
ProductRepository productRepository = new ProductRepository();
products = productRepository.GetAll().ToList();
}
catch (Exception ex)
{
}
return products;
}
public Product Get(int Id)
{
Product product = new Product();
try
{
ProductRepository productRepository = new ProductRepository();
product = productRepository.GetById(Id);
}
catch (Exception ex)
{
}
return product;
}
public bool Update(Product product)
{
bool isUpdated = false;
try
{
ProductRepository productRepository = new ProductRepository();
isUpdated = productRepository.Update(product);
}
catch (Exception ex)
{
}
return isUpdated;
}
public bool Delete(Product product)
{
bool isDeleted = false;
try
{
ProductRepository productRepository = new ProductRepository();
isDeleted = productRepository.Delete(product);
}
catch (Exception ex)
{
}
return isDeleted;
}
}
}This code defines a ProductService class that provides methods for performing CRUD (Create, Read, Update, Delete) operations on a Product entity. The class depends on the ProductRepository class, which in turn depends on the GenericRepository class.
This class is an example of a simple CRUD service using a generic repository pattern.
Final Thoughts
Congratulations, you made it to the end of the blog! I hope you found this blog useful and insightful. Take a deep breath, sit back, and let all the knowledge you’ve gained sink in. You’ve learned something new today, and that’s always worth celebrating. So go ahead and give yourself a pat on the back. Job well done!
Fortunately this project is available on Github: https://github.com/ZuraizAhmedShehzad/DapperGenericRepository
HAPPY CODING FOLKS !!!
