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

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);
}

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 });
    }
}

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);
    }
}

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.