C# | Dapper using Generic Repository
Introduction
Dapper is a simple, lightweight, and high-performance Object-Relational Mapping (ORM) library for .NET. It is widely used for database access in C# applications due to its speed and simplicity. In this guide, we will explore how to use Dapper in combination with a Generic Repository pattern to streamline database interactions.
Setting Up Dapper
Before using Dapper, make sure to install the Dapper NuGet package in your C# project. You can do this using the following command in the Package Manager Console:
1
Install-Package Dapper
Generic Repository
A Generic Repository is a design pattern that provides a generic interface to interact with various types of entities in a consistent way. It allows you to perform common database operations (CRUD) without writing repetitive code for each entity.
Let’s create a simple Generic Repository interface:
1
2
3
4
5
6
7
8
public interface IRepository<T>
{
IEnumerable<T> GetAll();
T GetById(int id);
void Insert(T entity);
void Update(T entity);
void Delete(int id);
}
Implementing the Generic Repository with Dapper
Now, let’s implement the Generic Repository using Dapper for a hypothetical Product
entity.
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
using Dapper;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
public class DapperRepository<T> : IRepository<T>
{
private readonly IDbConnection _dbConnection;
public DapperRepository(string connectionString)
{
_dbConnection = new SqlConnection(connectionString);
}
public IEnumerable<T> GetAll()
{
return _dbConnection.Query<T>("SELECT * FROM " + typeof(T).Name);
}
public T GetById(int id)
{
return _dbConnection.QueryFirstOrDefault<T>("SELECT * FROM " + typeof(T).Name + " WHERE Id = @Id", new { Id = id });
}
public void Insert(T entity)
{
_dbConnection.Execute($"INSERT INTO {typeof(T).Name} VALUES (@Property1, @Property2, ...)", entity);
}
public void Update(T entity)
{
_dbConnection.Execute($"UPDATE {typeof(T).Name} SET Property1 = @Property1, Property2 = @Property2, ... WHERE Id = @Id", entity);
}
public void Delete(int id)
{
_dbConnection.Execute($"DELETE FROM {typeof(T).Name} WHERE Id = @Id", new { Id = id });
}
}
Example Usage
Now, let’s see how to use the DapperRepository
with a Product
entity:
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
public class Product
{
public int Id { get; set; }
public string Name { get; set; }
public decimal Price { get; set; }
}
class Program
{
static void Main()
{
var connectionString = "your_database_connection_string";
var productRepository = new DapperRepository<Product>(connectionString);
// Retrieve all products
var allProducts = productRepository.GetAll();
// Retrieve a product by Id
var productId = 1;
var product = productRepository.GetById(productId);
// Insert a new product
var newProduct = new Product { Name = "New Product", Price = 19.99 };
productRepository.Insert(newProduct);
// Update an existing product
product.Name = "Updated Product";
productRepository.Update(product);
// Delete a product
productRepository.Delete(productId);
}
}
This example demonstrates how to use Dapper with a Generic Repository for a simple Product
entity. Adjust the code according to your specific entity and database schema. Make sure to replace "your_database_connection_string"
with the actual connection string for your database.