DEV Community

Hassan BOLAJRAF
Hassan BOLAJRAF

Posted on

C# | Dapper using Generic Repository

Note
You can check other posts on my personal website: https://hbolajraf.net

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:

Install-Package Dapper
Enter fullscreen mode Exit fullscreen mode

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:

public interface IRepository<T>
{
    IEnumerable<T> GetAll();
    T GetById(int id);
    void Insert(T entity);
    void Update(T entity);
    void Delete(int id);
}
Enter fullscreen mode Exit fullscreen mode

Implementing the Generic Repository with Dapper

Now, let's implement the Generic Repository using Dapper for a hypothetical Product entity.

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 });
    }
}
Enter fullscreen mode Exit fullscreen mode

Example Usage

Now, let's see how to use the DapperRepository with a Product entity:

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);
    }
}
Enter fullscreen mode Exit fullscreen mode

What Next ?

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.

Image of Datadog

Create and maintain end-to-end frontend tests

Learn best practices on creating frontend tests, testing on-premise apps, integrating tests into your CI/CD pipeline, and using Datadog’s testing tunnel.

Download The Guide

Top comments (1)

Collapse
 
luizeario profile image
Luiz Dario

Great article! I wrote one about the Generic Repository using Dapper.Contrib as well. Congrats!

Sentry image

See why 4M developers consider Sentry, “not bad.”

Fixing code doesn’t have to be the worst part of your day. Learn how Sentry can help.

Learn more