Code Structure with Dapper and Dapper.Contrib

One of the major benefits of using Dapper is its speed and versatility in data access. However, it falls short in object-relational mapping. To address this limitation, the community developed Dapper.Contrib, a library that simplifies the mapping of database tables to classes using annotations.

As time passes, creating repository classes can become repetitive and mechanical. A practical solution to this is to implement a base repository class, which streamlines development and reduces code duplication. In this article, we’ll explore how to build a generic repository structure using Dapper.Contrib.

Creating the Base Repository Interface

We start by defining an interface that outlines the generic repository methods. The use of the generic type T makes the interface reusable with any class representing a database table.

internal interface IBaseRepository<T> where T : class
{
    IEnumerable<T> GetAll();

    T Get(int id);

    T Get(string id);

    long Insert(T entity);

    long Insert(IEnumerable<T> entity);

    bool Update(T entity);

    bool Update(IEnumerable<T> entity);

    bool Delete(T entity);

    bool Delete(IEnumerable<T> entity);
}

In the interface, methods Get(Id) are defined for both integer and string types, allowing flexibility in database queries. If your database uses another type of primary key, such as GUID, you can simply implement the corresponding method, and it will be available for use.

Implementing the Base Repository

Next, we implement the interface using Dapper.Contrib. In the example below, we access a SQL Server database and utilize Dapper.Contrib’s built-in methods such as Insert, Update, Delete, and Get.

internal class BaseRepository<T> : IBaseRepository<T> where T : class
{
    public bool Delete(T entity)
    {
        using (var conn = new SqlConnection(ConnectionString))
            return conn.Delete(entity);
    }

    public bool Delete(IEnumerable<T> entity)
    {
        using (var conn = new SqlConnection(ConnectionString))
            return conn.Delete(entity);
    }

    public IEnumerable<T> GetAll()
    {
        using (var conn = new SqlConnection(ConnectionString))
            return conn.GetAll<T>();
    }

    public T Get(int id)
    {
        using (var conn = new SqlConnection(ConnectionString))
            return conn.Get<T>(id);
    }

    public T Get(string id)
    {
        using (var conn = new SqlConnection(ConnectionString))
            return conn.Get<T>(id);
    }

    public long Insert(T entity)
    {
        using (var conn = new SqlConnection(ConnectionString))
            return conn.Insert(entity);
    }

    public long Insert(IEnumerable<T> entity)
    {
        using (var conn = new SqlConnection(ConnectionString))
            return conn.Insert(entity);
    }

    public bool Update(T entity)
    {
        using (var conn = new SqlConnection(ConnectionString))
            return conn.Update(entity);
    }

    public bool Update(IEnumerable<T> entity)
    {
        using (var conn = new SqlConnection(ConnectionString))
            return conn.Update(entity);
    }

    public string ConnectionString => "My Connection String";
}

Defining the Entity with Annotations

Now, let’s create an entity representing a table in the database. We use annotations provided by Dapper.Contrib, such as [Table] to map the table and [Key] to define the primary key.

[Table("dbo.TABLE_MYFILE")]
internal class MyFile
{
    [Key]
    public int Id { get; set; }

    public string Customer { get; set; }

    public string FileName { get; set; }

    public DateTime FileDate { get; set; }

    public string FilePath { get; set; }
}

Creating the Specific Repository

With the base repository implemented, creating a specific repository for the MyFile entity becomes straightforward. We just need to inherit the BaseRepository class and implement the corresponding interface.

internal class FileRepository : BaseRepository<MyFile>, IBaseRepository<MyFile>
{
    // Specific implementations, if needed
}

Using the Repository

Now, when instantiating the repository, we gain easy access to all the functionality provided by Dapper.Contrib. For example, we can retrieve, insert, update, or delete records from the table associated with the MyFile entity in a simple and efficient manner.

Here is an example of how to use the FileRepository to insert a new file record into the database:

class Program
{
    static void Main(string[] args)
    {
        // Instantiate the repository
        var fileRepository = new FileRepository();

        // Create a new file entity
        var newFile = new MyFile
        {
            Customer = "John Doe",
            FileName = "report.pdf",
            FileDate = DateTime.Now,
            FilePath = "/documents/reports/report.pdf"
        };

        // Insert the new file into the database
        var insertedId = fileRepository.Insert(newFile);

        // Output the ID of the newly inserted file
        Console.WriteLine($"File inserted with ID: {insertedId}");

        // Retrieve the file by ID
        var retrievedFile = fileRepository.Get((int)insertedId);

        // Output retrieved file information
        Console.WriteLine($"Retrieved File: {retrievedFile.FileName}, Customer: {retrievedFile.Customer}");

        // Update the file record
        retrievedFile.FileName = "updated_report.pdf";
        fileRepository.Update(retrievedFile);
        Console.WriteLine("File updated successfully.");

        // Delete the file record
        fileRepository.Delete(retrievedFile);
        Console.WriteLine("File deleted successfully.");
    }
}