What is an ORM - Dapper Introduction

In this post we will discuss Dapper, a micro ORM that allows us to map from our database to C#.

1 - What is Dapper?

First, we need to understand what Dapper is. It is a micro ORM for .NET, in fact, it is the most popular micro ORM in the .NET community and is created and maintained by the Stack Overflow team.

2 - What is a micro ORM?

"Micro ORM" comes from the acronym "object relational mapper"; this means that an ORM is responsible for mapping from the database to our objects in C#.

Depending on the ORM you are using, it will perform more or fewer actions. A micro ORM focuses solely on mapping between the database and the objects.

2.1 - ORM vs Micro ORM

Dapper is not the only micro ORM. You have probably heard of Entity Framework, which is another ORM, but in this case, it is not a micro ORM.

A traditional ORM (like Entity Framework) doesn't only perform mapping between the database and objects, but also generates the SQL that will be executed behind the scenes.

Whereas a micro ORM focuses only on mapping, and it's the developer who must write the SQL.

Note: Neither one is better or worse, they're simply two different, fully valid programming approaches.

2.2 - Features of a micro ORM

Lightweight: Micro ORMs are very lightweight because they don't include a designer (like Entity Framework) or require an XML for configuration. They're designed for developers to focus only on coding.

Speed: Using a micro ORM, the SQL query needs to be written manually, and it can often be much faster than an auto-generated query from Entity Framework.

Easy to use: As previously mentioned, Dapper is designed for developers, so it's meant to be plug-and-play. Additionally, there are very few methods to learn, and they are straightforward.

3 - Main features of Dapper

Up to now, I've talked about the characteristics of (micro) ORMs, but what makes Dapper special?

Querying and mapping: Dapper focuses specifically on fast and accurate mapping of our objects. Additionally, query parameters are parameterized, helping to prevent SQL injection.

Performance: Dapper is the king of ORMs in terms of performance. To achieve this, it extends the IDbConnection interface, which makes it a bit closer to the "core" of the language, giving us performance benefits. On its GitHub page, Dapper provides a list comparing its performance to other ORMs.

Very simple API: Dapper aims to do a couple of things and do them very well. The API provides us with three kinds of methods:

• Methods that map to concrete types.
• Methods that map to dynamic types.
• Methods to execute commands, such as insert or delete.

Works with any database: Another big advantage is that it works with any type of database. Personally, I often work with MySQL, but PostgreSQL or SQL Server work perfectly too.

4 - Using Dapper in our code

For this example, we’ll use code from the official C# website, which is available in the blog section.

The first thing we have to do is go to NuGet and download the latest version of Dapper:

Afterwards, we need to migrate our existing code that uses a raw connection, as shown in the post about connecting to a MySQL database, to the approach of performing this action with Dapper.

4.1 - Adding the connection to the Dependency Container

This step is not strictly necessary, but it makes development much easier.

First, go to your code and add to the dependency container the DbConnection class. It will not reference any interface; instead, we'll build the class right there.

services
    .AddScoped<DbConnection>(x => new MySqlConnection("Server=127.0.0.1;Port=3306;Database=webpersonal;Uid=webpersonaluser;password=webpersonalpass;"))

Note: the whole connection string is "hardcoded" here for demonstration. In normal cases, it should be in secrets/vault, or at worst, in configuration files.

services
     .AddScoped<DbConnection>(x => new MySqlConnection(configuration.GetValue<string>("conexionMysql")))

Once we have DbConnection in our DI (Dependency Injection), we inject it as a parameter to each repository.

public abstract class BaseRepository<T>
    where T : class
{

    protected readonly DbConnection _conexion;

    public BaseRepository(DbConnection conexion)
    {
        _conexion = conexion;
    }
}

4.2 - Why use DBConnection?

The main reason is because DBConnection can accept any connection type. In this case, we use MySQL but if we wanted to switch to PostgreSQL we would only need to update our definition in the dependency injector instead of changing all repositories.

Of course, we must change where we use MySQLConnection to our injected connection for our current project.

4.3 - Raw query example

To retrieve a user in the way seen above (which I called "raw") we’d do something like the following:

public async Task<T?> GetByUserId(int userId)
{
    using (MySqlConnection conexion = new MySqlConnection(ConnectionString))
    {
        conexion.Open();
        MySqlCommand cmd = new MySqlCommand();
        cmd.Connection = conexion;
        cmd.CommandText = $"select * from {TableName} where UserId = ?userId";
        cmd.Parameters.Add("?userId", MySqlDbType.Int32).Value = userId;
        T? result = null;
        DbDataReader reader = await cmd.ExecuteReaderAsync(CommandBehavior.CloseConnection);

        while (await reader.ReadAsync())
        {
            result = Create(reader);
        }
        return result;
    }
}

public abstract T? Create(DbDataReader reader);

Here we have the following points:

• Connection
• Query
• Parameters / query
• Mapping to our object,

In this case, we return <T?> where T could be PersonalProfileEntity or the user itself.

This mapping is manual.

public override PersonalProfileEntity? Create(DbDataReader reader)
{
    return PersonalProfileEntity.Create(
        Convert.ToInt32(reader[nameof(PersonalProfileEntity.UserId)]),
        Convert.ToInt32(reader[nameof(PersonalProfileEntity.Id)]),
        reader[nameof(PersonalProfileEntity.FirstName)].ToString() ?? "",
        reader[nameof(PersonalProfileEntity.LastName)].ToString() ?? "",
        reader[nameof(PersonalProfileEntity.Description)].ToString() ?? "",
        reader[nameof(PersonalProfileEntity.Phone)].ToString() ?? "",
        reader[nameof(PersonalProfileEntity.Email)].ToString() ?? "",
        reader[nameof(PersonalProfileEntity.Website)].ToString() ?? "",
        reader[nameof(PersonalProfileEntity.GitHub)].ToString() ?? ""
        );
}

As you can imagine, this involves quite a bit of work.

When you call the URL to get the user, it returns the correct value:

Url: https://localhost:44363/api/PerfilPersonal/ivanabad
Respuesta:
{
    "valor": {
        "userId": 1,
        "id": 1,
        "userName": "ivanabad",
        "firstName": "ivan",
        "lastName": "abad",
        "description": "test desdcp",
        "phone": "elf",
        "email": "[email protected]",
        "website": "https://www.netmentor.es",
        "gitHub": "/ElectNEwt",
        "interests": [],
        "skills": []
    },
    "errores": [],
    "success": true
}

5 - Preparing the code for dapper

Now that we've seen the "challenge" of not using any kind of ORM, let's see what benefits using one brings when coding.

The final goal is the same: to retrieve a PersonalProfileEntity from the database.

To replicate the same query using Dapper, all we need to do is use our connection inside a using block.

And in our entities, create a constructor or make the setters public so Dapper can access them.

Personally, I recommend a constructor with a protected access modifier.

If you create a constructor, make sure the input parameters are in the same order as in the database and preserve casing. If your column names are different, use SQL aliases in the query itself.

For example, if the database has id, nombre, email, the constructor must use that order. If, for example, the constructor is id, email, Nombre, this will not work (it will throw an error).

public class PersonalProfileEntity
{
    public readonly int UserId;
    public readonly int? Id;
    public readonly string FirstName;
    public readonly string LastName;
    public readonly string Description;
    public readonly string Phone;
    public readonly string Email;
    public readonly string Website;
    public readonly string GitHub;

    protected PersonalProfileEntity(int? id, int userid, string firstname, string description, string phone, string email,
        string lastname, string website, string github)
    {
        UserId = userid;
        Id = id;
        FirstName = firstname;
        LastName = lastname;
        Description = description;
        Phone = phone;
        Email = email;
        Website = website;
        GitHub = github;
    }

    public static PersonalProfileEntity Create(int userId, int? id, string firstName, string lastName, string description,
        string phone, string email, string website, string gitHub)
        => new PersonalProfileEntity(id, userId, firstName, description, phone, email, lastName, website, gitHub);
}

6 - Possible actions with Dapper

Dapper provides a series of extension methods that allow us to perform different actions on the database.

All actions (methods) have their regular version and a version with generics which accepts <T>. For example, to get the first item, you have both QueryFirst(..) and QueryFirst<T>(...); the generic version will map the result automatically.

The same goes for their asynchronous and synchronous versions: there is QueryFirstAsync<T> and QueryFirst<T>.

For our example, we'll look at the generic and asynchronous versions.

6.1 - Queries with dapper

To perform queries, we have two main methods:

A - Query a single item with dapper

When we have a 1-1 relationship, we usually query a single item, either by manually accessing the first result result[0] or using top 1 in SQL.

Dapper gives us a solution for this scenario and provides the method QueryFirstAsync() or QueryFirstAsync<T>.

To convert the code from section 4.3 to a dapper query is very simple, there's no more need for manual mapping, and it will look like this:

public async Task<T?> GetByUserId(int userId)
{
    using(var con = _conexion)
    {
        con.Open();

        return await con.QueryFirstAsync<T?>($"select * from {TableName} where UserId = @userId", new
        {
            userId = userId
        });
    }
}

Note: you can also use QuerySingleAsync<T>.

B - Query lists with dapper

To query lists, or more than one result, the process is very similar to before. We use a method called QueryAsync<T>, which returns IEnumerable<T>.

public async Task<List<T>> GetListByUserId(int userId)
{
    using (var con = _conexion)
    {
        con.Open();
        return (await con.QueryAsync<T>($"select * from {TableName} where UserId = @userId", new
        {
            userId = userId
        })).ToList();
    }
}

6.2 - Insert data with dapper

To insert data, the code is a bit more complex, but not much more, mainly because we have to construct our SQL query in code.

You must specify the fields to insert as well as their values, not the value itself, but the property of the object you want to insert. For example, for the "nombre" field, you'd use @Nombre to refer to the property.

To execute our insert with Dapper, we will use the QueryAsync<T> method.

public async Task<PersonalProfileEntity> Insertar(PersonalProfileEntity perfilPersonal)
{
    string sql = $"insert into {TableName} ({nameof(PersonalProfileEntity.UserId)}, {nameof(PersonalProfileEntity.FirstName)}, " +
            $"{nameof(PersonalProfileEntity.LastName)}, {nameof(PersonalProfileEntity.Description)}, {nameof(PersonalProfileEntity.Phone)}," +
            $"{nameof(PersonalProfileEntity.Email)}, {nameof(PersonalProfileEntity.Website)}, {nameof(PersonalProfileEntity.GitHub)}) " +
            $"values (@{nameof(PersonalProfileEntity.UserId)}, @{nameof(PersonalProfileEntity.FirstName)}, @{nameof(PersonalProfileEntity.LastName)}," +
            $"@{nameof(PersonalProfileEntity.Description)}, @{nameof(PersonalProfileEntity.Phone)}, @{nameof(PersonalProfileEntity.Email)}," +
            $"@{nameof(PersonalProfileEntity.Website)}, @{nameof(PersonalProfileEntity.GitHub)} );" +
            $"Select CAST(SCOPE_IDENTITY() as int)";
    using(var con = _conexion)
    {
        con.Open();
        var newId = (await con.QueryAsync<int>(sql, perfilPersonal)).First();
        return PersonalProfileEntity.UpdateId(perfilPersonal, newId);
    }
}

The Select CAST(SCOPE_IDENTITY() as int) part is so it returns the ID of the newly inserted row.

6.3 - Update data with dapper

To update, we will use a similar process as before, since we also need to write the full query with the data we want to update.

But this time, we will use the ExecuteAsync method, which returns the number of affected rows.

public async Task<PersonalProfileEntity> Update(PersonalProfileEntity perfilPersonal)
{
    string sql = $"Update {TableName} " +
        $"set {nameof(PersonalProfileEntity.FirstName)} = @{nameof(PersonalProfileEntity.FirstName)}, " +
            $"{nameof(PersonalProfileEntity.LastName)} = @{nameof(PersonalProfileEntity.LastName)}, " +
            $"{nameof(PersonalProfileEntity.Description)} = @{nameof(PersonalProfileEntity.Description)}, " +
            $"{nameof(PersonalProfileEntity.Phone)} = @{nameof(PersonalProfileEntity.Phone)}," +
            $"{nameof(PersonalProfileEntity.Email)} = @{nameof(PersonalProfileEntity.Email)}, " +
            $"{nameof(PersonalProfileEntity.Website)} = @{nameof(PersonalProfileEntity.Website)}, " +
            $"{nameof(PersonalProfileEntity.GitHub)} = @{nameof(PersonalProfileEntity.GitHub)}" +
            $"Where {nameof(PersonalProfileEntity.Id)} = @{nameof(PersonalProfileEntity.UserId)}";
            
    using (var con = _conexion)
    {
        con.Open();
        int filasAfectadas = await con.ExecuteAsync(sql, perfilPersonal);
        return perfilPersonal;
    }
}

6.4 - Delete data with dapper

To delete data using dapper, the process is similar to updating: we have to write a SQL statement for deletion. We also use the ExecuteAsync method.

public async Task<int> Delete(int id)
{
    string sql = $"delete from {TableName} Where {nameof(PersonalProfileEntity.Id)} = @id";

    using (var con = _conexion)
    {
        con.Open();
        await con.ExecuteAsync(sql, new { id = id });
        return id;
    }
}

Conclusion

• In this post, we've seen what an ORM and a Micro ORM are.
• The differences between an ORM and a Micro ORM.
• The benefits of using a (Micro) ORM versus "raw" connections.
• What Dapper is and its benefits.
• Introduction to using Dapper in our code.