What is an ORM - Dapper Introduction

In this post we’ll talk about 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’s a micro ORM for .NET—actually, it’s the most popular micro ORM in the .NET community, and it’s created and maintained by the StackOverflow 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 only focuses on mapping between the database and objects.

2.1 - ORM vs Micro ORM

Dapper is not the only micro ORM. You’ve almost certainly heard of Entity Framework, another ORM, but in this case, not a micro ORM.

A traditional ORM (like Entity Framework) doesn’t only map between the database and objects—it also generates the SQL that will be executed in the background.

Whereas a micro ORM focuses solely on the mapping, and it’s the developer who needs to write the SQL.

Note: there isn’t a better or worse one, they are simply two valid ways to program.

2.2 - Features of a micro ORM

Lightweight: Micro ORMs are known for being very lightweight because they don’t have a designer (like Entity Framework does), nor do they require XML for configuration. They are built so developers can focus on coding.

Speed: With a micro ORM, you have to write the SQL query yourself, and in some cases, it can be much faster than one generated automatically by Entity Framework.

Easy to use: As mentioned earlier, Dapper is designed for developers, hence it’s designed to be “plug and play.” Also, the number of methods you need to learn is minimal and simple.

3 - Main features of Dapper

So far I’ve talked about features of (micro) ORMs, but what makes Dapper special?

Query and mapping: Dapper is focused on providing fast and accurate object mapping. Also, query parameters are parameterized, so SQL injection is avoided.

Performance: Dapper is the king of ORMs when it comes to performance. To achieve this, it extends the IDbConnection interface, meaning it’s a bit closer to the “core” of the language and gives us performance benefits. Dapper’s GitHub page has a list comparing its performance against other ORMs.

Very simple API: The goal of Dapper is to do a few things and do them all very well. The API offers three types of methods:

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

Any database: Another big advantage is that it works with any type of database. I personally usually work with MySQL, but PostgreSQL or SQL Server work perfectly.

4 - Using Dapper in our code

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

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

Next, we need to migrate our code that uses a "pure" connection (as seen in the post about connecting to a MySQL database) to how this is done with Dapper.

4.1 - Adding the connection to the Dependency Container

This step isn’t absolutely necessary, but it can make our life much easier when programming.

The first thing we need to do is go to our code and add to the dependency container the DbConnection class, but not referencing any interface. 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: For this example, the connection credentials are “hardcoded,” but under normal circumstances they 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, we inject it into each repository as a parameter.

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 that DBConnection accepts any type of connection. In this case, we’re using MySQL, but if we ever wanted to switch to PostgreSQL, we would only need to change our definition in the dependency injector instead of in every repository.

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

4.3 - Example of a "pure" Query.

To retrieve a user in the way shown earlier (which I called “pure”), we have 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);

As we can see, the steps include:

• 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 has to be done manually.

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 we can imagine, this requires quite a bit of work.

When we call the URL to retrieve the user, the response is correct:

Url: https://localhost:44363/api/PerfilPersonal/ivanabadRespuesta:{    "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 “difficulty” of not using any kind of ORM, let's look at the benefits of using one once we get to the code.

The final goal is the same—we want to retrieve a PersonalProfileEntity from the database.

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

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

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

If you create a constructor, you need to include the parameters in the same order as they appear in the database, and respecting case sensitivity. If your columns aren’t named the same, you should use SQL aliases in the query itself.

For example, if in the database you have id, nombre, email, the constructor should have that order. If the constructor is id, email, Nombre, it won’t work (an error will be thrown).

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 us with a set of extension methods that allow us to perform different actions against the database.

All actions (methods) have a normal and a generic version (generics) which take <T>. For example, querying the first item can be done both with QueryFirst(..) and QueryFirst<T>(...). The difference is that the generic method will do the mapping automatically.

It's the same for async and sync versions: QueryFirstAsync<T> and QueryFirst<T> are both available.

In this 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 you have a 1 - 1 relationship, you usually query a single item either by manually accessing the first result with result[0] or by doing top 1 in SQL.

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

Converting the code we saw in section 4.3 to Dapper queries is very straightforward, as you don't need to do mapping anymore 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 get more than one result, the functionality is similar to above. There’s 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—because you need to build your SQL query in the code itself.

You must specify the fields to insert as well as their values—not the direct value, but the property of the object you want to insert. For example, for the field “nombre” you’ll use @Nombre to refer to the object's property.

To perform the insert with Dapper, we 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 will return the ID of the new entry.

6.3 - Update data with dapper

To update, we'll use a similar process as above: you need to write the full query with the data you want to update.

But this time we use the ExecuteAsync method—the result is 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 with Dapper, the process is similar to updating. The difference is that you need to write an SQL delete query. Again, 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 "pure" connections.
• What Dapper is and its benefits.
• Introduction to using Dapper in our code.