The concept behind the Repository Pattern revolves around the notion that it’s beneficial to keep data access code distinct from business logic code. By employing the Repository Pattern, one can effectively centralise data access code, thereby minimising redundancy and enhancing the maintainability of the overall codebase.
The Repository Pattern consists of three main components:
- The Repository Interface
- The Repository Implementation
- The Entity Model
Generic Repository
A generic repository is a pattern used for abstracting data access logic from the rest of the application. The repository pattern is a popular design pattern that provides an abstraction layer on top of the data access layer, allowing the application to interact with data without needing to know the details of how the data is being accessed or manipulated.
Dapper is a lightweight Object-Relational Mapping (ORM) library for .NET. It provides a simple way to map database queries to .NET objects. When employed alongside the repository pattern, Dapper can simplify data access and manipulation by furnishing a set of generic methods for executing common CRUD (Create, Read, Update, Delete) operations on data entities.
A generic repository typically defines generic methods for CRUD operations (such as Get, Insert, Update, Delete) that can work with any entity type. This allows developers to write reusable data access logic without having to duplicate code for each entity type in the application.
Need of Generic Repository
With a generic repository, we’re able to abstract the intricacies of data access, freeing us to concentrate on crafting our business logic without being bogged down by the specifics of the underlying data store. This flexibility enables us to utilize the same code across various entities and projects, thereby enhancing the modularity of our codebase and simplifying maintenance tasks.
Implementation using Dapper
Model
Product.cs
using System.ComponentModel.DataAnnotations.Schema; using System.ComponentModel.DataAnnotations; namespace GenericRepositoryDemo.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; } } }
Repository
IGenericRepository.cs
namespace GenericRepositoryDemo.Repository { interface IGenericRepository<T> { T GetById(int id); IEnumerable<T> GetAll(); bool Add(T entity); bool Update(T entity); bool Delete(T entity); } }
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 GenericRepositoryDemo.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)
{
Console.WriteLine(ex.Message);
throw;
}
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)
{
Console.WriteLine(ex.Message);
throw;
}
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)
{
Console.WriteLine(ex.Message);
throw;
}
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)
{
Console.WriteLine(ex.Message);
throw;
}
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)
{
Console.WriteLine(ex.Message);
throw;
}
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 snippet presents a generic repository class named GenericRepository, which adheres to the IGenericRepository interface. The role of the GenericRepository class is to execute CRUD (Create, Read, Update, and Delete) operations on a database table.
Within this class, reflection is employed to extract table and column names from the properties of the entity class. The Table attribute facilitates retrieval of the table name, while the Column attribute facilitates retrieval of the column name.
The IDbConnection interface facilitates interaction with the database. Moreover, the class constructor provides the connection string. Subsequently, it instantiates a new instance of the SqlConnection class to establish a connection with the database.
ProductRepository.cs
using DapperGenericRepository.Model; namespace DapperGenericRepository.Repository { public class ProductRepository : GenericRepository<Product> { } }
This code establishes a class named ProductRepository, which extends a generic class called GenericRepository<T>. By specifying the type parameter Product to GenericRepository<T>, ProductRepository inherits all the methods outlined in GenericRepository<T>, tailored for the Product entity type.
In essence, ProductRepository functions as a specialized implementation of a generic repository for the Product entity. Its design leverages the Dapper ORM (Object-Relational Mapping) to execute CRUD (Create, Read, Update, Delete) operations on the Product table within a database.
Conclusion
In summary, a Generic Repository abstracts data access logic. It promotes code reusability across different entity types and projects, thereby enhancing code modularity and easing maintenance efforts. Leveraging Dapper as an ORM library simplifies data access by mapping database queries to .NET objects. Especially when combined with the Generic Repository pattern, it offers a concise way to execute common CRUD operations. This approach not only enhances flexibility but also fosters cleaner, more maintainable codebases. This is illustrated in the provided implementation using Dapper.