Building a CRUD API with Dapper

Dapper is the best-known micro-ORM today thanks to its excellent performance. Check out how to create a .NET application and use Dapper to persist data.

ORMs (Object Relational Mappers) greatly facilitate the development of applications that require connections and persistence in databases. After all, ORMs have several resources that, in addition to mapping objects, can generate the entire database structure with just a few commands.

However, a downside is that traditional ORMs are not very performant. To solve this problem, there are micro-ORMs that are focused on performance as they have few resources compared to conventional ORMs. One of the most well-known micro-ORMs is Dapper.

In this post, we’ll learn more details about Dapper and how to implement a full CRUD in a .NET application with this high-performance Micro-ORM.

What is a Micro-ORM?

An ORM (again, that’s Object-Relational Mapper) is used to creating client code objects that reflect a relational database and provide several powerful features such as object caching and queries.

A micro-ORM has the essence of an ORM but with fewer features than a full ORM.

What is Dapper and Why is It Popular?

Dapper is an open-source micro-ORM for .NET developed by Marc Gravel and Sam Saffron in 2011 while trying to solve Stack Overflow performance issues. You can check out the Dapper story written by Sam himself: How I learned to stop worrying and write my own ORM.

The performance results achieved by Dapper made it popular among its competitors—after all, it solved performance problems on one of the most accessed sites in the world: Stack Overflow. This success is due to the way it was built and the way it removes complexity when building queries, making it extremely simple to execute, parameterize and map objects through SQL queries.

Dapper vs. EF Core

Entity Framework Core (EF Core) is a complete ORM developed and maintained by Microsoft. While there are a wide variety of options available in EF Core regarding Dapper, that doesn’t mean it’s better than Dapper. Both EF Core and Dapper have advantages and disadvantages—the important thing is to use each in scenarios where they are most appropriate.

It is common to find cases where Dapper is used in conjunction with EF Core. As expected, this union requires a large number of application resources, but the advantage of this union of resources is it allows the creation of excellent applications, composed of the best that modern technology has to offer.

When to Use Dapper

Undoubtedly the biggest advantage of using Dapper is the performance gain. In scenarios where high performance is required, Dapper may be the best choice. However, it must be considered that when using Dapper, a longer development period will be necessary, as it is necessary to write the SQL queries that will be executed by Dapper. In addition, it doesn’t have, at least natively, the option of automatic migrations as there is in EF Core—for example, requiring creations/changes of databases and tables to be done via SQL scripts.

Persisting Data with Dapper

To demonstrate the use of Dapper in a .NET application, let’s create a Minimal API that will execute CRUD operations (Create, Read, Update and Delete) in a MySQL database.

You can access the project’s source code here.

Creating the Project

So, to build the project in Visual Studio:

• Create a new project
• Choose ASP.NET Core Web API
• Name (FreelanceProjectControl is my suggestion)
• Choose .NET 6 LTS
• Uncheck “Use controllers”
• Click Create

Project Dependencies

The project will have the following dependencies that can be added to the .csproj file:

<PackageReference Include="MySql.Data" Version="8.0.31" />
<PackageReference Include="Dapper" Version="2.0.123" />

Or via NugetPackage:

• MySQL Data
• Dapper

Creating the Model Classes

First, let’s create the model classes that will contain the database entities. One class will be used to store the value of the connection string, and the other to represent the entity “Project,” which in the context of the article is a freelance project.

So, create a new folder called “Models” and inside it, create the classes below:

• ConnectionString

namespace FreelanceProjectControl.Models;
public class ConnectionString
{
    public string? ProjectConnection { get; set; }
}

• Project

namespace FreelanceProjectControl.Models;
public class Project
{
    public string? Id { get; set; }
    public string? Customer { get; set; }  
    public string? Name { get; set; }
    public int WorkedHours { get; set; }
    public decimal FlatRateAmount { get; set; }
    public decimal HourlyRateAmount { get; set; }
    public DateTime StartDate { get; set; }
    public DateTime EndDate { get; set; }
    public bool Active { get; set; }
}

Creating the Repository Class

The repository class will contain the connection to the database and also the methods responsible for performing the CRUD (Create, Read, Update and Delete).

So, create a new folder called “Data” and inside it create the class below:

• ProjectRepository

using Dapper;
using FreelanceProjectControl.Models;
using Microsoft.Extensions.Options;
using MySql.Data.MySqlClient;
using System.Data;

namespace FreelanceProjectControl.Data;
public class ProjectRepository
{
    private readonly IDbConnection _dbConnection;

    public ProjectRepository(IOptions<ConnectionString> connectionString)
    {
        _dbConnection = new MySqlConnection(connectionString.Value.ProjectConnection);
    }

    public async Task<List<Project>> GetAll()
    {
        try
        {
            _dbConnection?.Open();

            string query = @"select id, name, customer, workedHours, flatRateAmount, hourlyRateAmount, startDate, endDate, active from project";
            
            var projects = await _dbConnection.QueryAsync<Project>(query);
            return projects.ToList();
        }
        catch (Exception)
        {
            return new List<Project>();
        }
        finally
        {
            _dbConnection?.Close();
        }
    }

    public async Task<Project?> GetById(string id)
    {
        try
        {
            _dbConnection?.Open();

            string query = $@"select id, name, customer, workedHours, flatRateAmount, hourlyRateAmount, startDate, endDate, active from project where id = '{id}'";
            
            var customer = await _dbConnection.QueryAsync<Project>(query, id);
            return customer.FirstOrDefault();
        }
        catch (Exception)
        {
            return null;
        }
        finally
        {
            _dbConnection?.Close();
        }
    }

    public async Task<bool> Create(Project project)
    {
        try
        {
            _dbConnection?.Open();

            string query = @"insert into project(id, name, customer, workedHours, flatRateAmount, hourlyRateAmount, startDate, endDate, active) 
                             values(@Id, @Customer, @Name, @WorkedHours, @FlatRateAmount, @HourlyRateAmount, @StartDate, @EndDate, @Active)";

            await _dbConnection.ExecuteAsync(query, project);
            return true;
        }
        catch (Exception)
        {
            return false;
        }
        finally
        {
            _dbConnection?.Close();
        }
    }

    public async Task<bool> Update(Project project)
    {
        try
        {
            _dbConnection?.Open();

            string selectQuery = $@"select * from project where id = '{project.Id}'";

            var entity = await _dbConnection.QueryAsync<Project>(selectQuery, project.Id);

            if (entity is null)
                return false;

            string updateQuery = @"update project set name = @Name, customer = @Customer, workedHours = @WorkedHours, flatRateAmount = @FlatRateAmount, 
                                   hourlyRateAmount = @HourlyRateAmount, startDate = @StartDate, endDate = @EndDate, active = @Active 
                                   where id = @Id";

            await _dbConnection.ExecuteAsync(updateQuery, project);
            return true;
        }
        catch (Exception)
        {
            return false;
        }
        finally
        {
            _dbConnection?.Close();
        }
    }

    public async Task<bool> Delete(string id)
    {
        try
        {
            _dbConnection?.Open();

            string selectQuery = $@"select * from project where id = '{id}'";

            var entity = await _dbConnection.QueryAsync<Project>(selectQuery, id);

            if (entity is null)
                return false;

            string query = $@"delete from project where id = '{id}'";

            await _dbConnection.ExecuteAsync(query);
            return true;
        }
        catch (Exception)
        {
            return false;
        }
        finally
        {
            _dbConnection?.Close();
        }
    }
}

Note that in the above code, we are creating an instance of a MySQL connection and assigning its value to the _dbConnection variable in the class’s constructor. This variable allows us to execute all the persistence methods available in MySQL. Each method is composed of a string where the SQL code is embedded and passed as a parameter to the Dapper namespace methods.

We also need to add the repository class configuration for dependency injection to occur, so in the Program.cs file add the following code:

builder.Services.AddSingleton<ProjectRepository>();

Creating the Service Class

The service class will contain the call to the repository methods we created earlier. It will also have an interface to ensure that all methods are implemented.

So, create a new folder called “Services” and inside it create the interface and class below:

• IProjectService (interface)

using FreelanceProjectControl.Models;

namespace FreelanceProjectControl.Services
{
    public interface IProjectService
    {
        Task<List<Project>> GetAllProjects();
        Task<Project> GetProjectById(string id);
        Task<bool> CreateProject(Project project);
        Task<bool> UpdateProject(Project project);
        Task<bool> DeleteProject(string id);
    }
}

• ProjectService (class)

using FreelanceProjectControl.Data;
using FreelanceProjectControl.Models;

namespace FreelanceProjectControl.Services;
public class ProjectService : IProjectService
{
    private readonly ProjectRepository _repository;

    public ProjectService(ProjectRepository repository)
    {
        _repository = repository;
    }

    public async Task<List<Project>> GetAllProjects() =>
    await _repository.GetAll();

    public async Task<Project> GetProjectById(string id) =>
        await _repository.GetById(id);

    public async Task<bool> CreateProject(Project project) =>
        await _repository.Create(project);

    public async Task<bool> UpdateProject(Project project) =>
        await _repository.Update(project);

    public async Task<bool> DeleteProject(string id) =>
        await _repository.Delete(id);
}

To add service class dependency injection, in the Program.cs file add the following code:

builder.Services.AddTransient<IProjectService, ProjectService>();

Configuring the Database

The database used in this example will be MySQL, so for the application to work, it is necessary to have MySQL installed locally. You can use another database, but you will need to change some settings that are not covered in this article.

To create the database that will be used in the application, you can use the SQL command below:

start transaction;
create database dbprojects;

And to create the table, use the following command:

use dbprojects;
create table `project` (
  `id` varchar(36) NOT NULL DEFAULT 'uuid()',
  `name` varchar(50) NOT NULL,
  `customer` varchar(250) NOT NULL DEFAULT 'uuid()',
  `workedHours` integer NOT NULL,
  `flatRateAmount` decimal NULL,
  `hourlyRateAmount` decimal NULL,
  `startDate` datetime NOT NULL,
  `endDate` datetime NULL,
  `active` bool NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Another configuration is to create the connection string. Then in the appsettings.json file add the following code:

  "ConnectionStrings": {
     "ProjectConnection": "host=localhost; port=YOUR MYSQL PORT; database=dbprojects; user=YOUR MYSQL USER; password=YOUR MYSQL PASSWORD;"
   },

And in the Program.cs file add the code below. It will load the value of the connection string when the application starts:

builder.Services.Configure<ConnectionString>(builder.Configuration.GetSection("ConnectionStrings"));

Creating the API Endpoints

Finally, all that remains is to create the API endpoints that will be used to execute the CRUD actions. So, in the Program.cs file, below the code app.UseHttpsRedirection(); add the following code:

#region Project API

app.MapGet("/Project/Get", async (IProjectService service) =>
{
    var result = await service.GetAllProjects();
    return result.Any() ? Results.Ok(result) : Results.NotFound("No records found");
})
.WithName("GetAllProjects");

app.MapGet("/Project/GetById", async (IProjectService service, string id) =>
{
    var result = await service.GetProjectById(id);
    return result is not null ? Results.Ok(result) : Results.NotFound($"No record found - id: {id}");
})
.WithName("GetProjectById");

app.MapPost("/Project/Create", async (IProjectService service, Project project) =>
{
    bool result = await service.CreateProject(project);
    return result ? Results.Ok() : Results.BadRequest("Error creating new Project Entity");
})
.WithName("CreateProject");

app.MapPut("/Project/Update", async (IProjectService service, Project project) =>
{
    bool result = await service.UpdateProject(project);
    return result ? Results.Ok() : Results.NotFound($"No record found - id: {project.Id}");
})
.WithName("UpdateProject");

app.MapDelete("/Project/Delete", async (IProjectService service, string id) =>
{
    bool result = await service.DeleteProject(id);
    return result ? Results.Ok() : Results.NotFound($"No record found - id: {id}");
})
.WithName("DeleteProject");

#endregion

Note that in the code above, we are creating the endpoints that call the service class. As we are using the Minimal APIs template, all the code is simple and unceremonious.

Testing the Application

To test the application, just run the project and, if everything is correct, a window with the Swagger interface will open in the browser. The GIF below shows the creation and data search function, just to confirm that the application is functional.

Conclusion

Indeed, Dapper is an excellent tool for data persistence in .NET and has an outstanding performance compared to other micro-ORMS. In this article, we saw how to create an application with Dapper using MySQL as a database, as the purpose of the article was to demonstrate the use of Dapper some details were not covered such as adding logs and fields validations. Feel free to create these resources and improve the project.