If you're working with databases in .NET and need a fast ORM (Object Relational Mapper), Dapper is an excellent choice. Dapper is a simple object mapper for .NET that allows you to execute SQL queries and map results to .NET objects efficiently. Let me explain more about how Dapper works and how it compares to Entity Framework.

We just released a completely FREE 7-day trial on my online learning platform, Dometrain. Sign up for a free Dometrain Pro trial and get access to 10 of our courses for 7 days.

What is an ORM?

Before we get into Dapper, let me quickly explain what an ORM is.
An Object-Relational Mapper (ORM) is a tool that helps work with databases without needing to write a lot of database code. Think of it like a translator between two languages: the language of code (like C#) and the language of databases (like SQL or MongoDB). Normally, when we want to save or retrieve information from a database, we would need to write a lot of code to map the database results into our traditional objects. ORMs automate this so we don't have to. Dapper fits more into the mapping of results from a database than creating queries behind the scenes as Entity Framework does.

Why Dapper?

Dapper sits in between direct SQL access and more complex ORMs like Entity Framework. Here’s why you might want to consider using Dapper over other ORMs:

  • Performance: Dapper is extremely fast, often outperforming Entity Framework, especially when dealing with raw SQL queries.
  • Control: You write SQL queries directly, giving you complete control over how data is fetched and updated.
  • Lightweight: It’s just an extension library for IDbConnection, meaning it doesn’t add overhead or require learning new concepts.
  • Easy to use: With a few lines of code, you can map database records to C# objects.
  • Support for Multiple Databases: Dapper can be used with a variety of relational databases, including SQL Server, MySQL, PostgreSQL, and SQLite, among others.

Examples

Let's create a new project to see how we can make it work.
First, you need to add the Dapper library to your project. You can install it through your IDE or through NuGet by running this command: Install-Package Dapper

Dapper doesn’t require much setup. All you need is an existing database connection that implements IDbConnection. You can typically work with SqlConnection from System.Data.SqlClient if you're using SQL Server.

In this example, we will use MS SQL Server, so create the demo table using this script:

CREATE DATABASE DapperDemo;

USE DapperDemo;

CREATE TABLE Users (
    Id INT PRIMARY KEY IDENTITY(1,1),
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL
);

-- Let's insert some sample data as well for our queries to work
INSERT INTO Users (FirstName, LastName) VALUES ('Cick', 'Nhapsas');
INSERT INTO Users (FirstName, LastName) VALUES ('Dot', 'Net');

Now, to map this table to a C# class, we'll create the User class:

public class User
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

Here’s the C# code that connects to the DapperDemo database we just created and interacts with the Users table using Dapper. Make sure to update the connection string with your actual database server information if you named the DB or the table differently.

string connectionString = "Server=.;Database=DapperDemo;Trusted_Connection=True;";

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();

    string sql = "SELECT Id, FirstName, LastName FROM Users";
    var users = (await connection.QueryAsync<User>(sql)).ToList();

    Console.WriteLine("Users in the database:");
    foreach (var user in users)
    {
        Console.WriteLine($"{user.FirstName} {user.LastName}");
    }

    // INSERT
    var newUser = new User { FirstName = "New", LastName = "User" };
    string insertSql = "INSERT INTO Users (FirstName, LastName) VALUES (@FirstName, @LastName)";
    var affectedRows = await connection.ExecuteAsync(insertSql, newUser);
    Console.WriteLine($"Inserted {affectedRows} new user(s).");

    // UPDATE
    var userToUpdate = new User { Id = 2, FirstName = "DomePlane" }; // Assuming Id=2 exists
    string updateSql = "UPDATE Users SET FirstName = @FirstName WHERE Id = @Id";
    affectedRows = await connection.ExecuteAsync(updateSql, userToUpdate);
    Console.WriteLine($"Updated {affectedRows} user(s).");

    // DELETE
    string deleteSql = "DELETE FROM Users WHERE Id = @Id"; // Assuming Id=1 exists
    affectedRows = await connection.ExecuteAsync(deleteSql, new { Id = 1 });
    Console.WriteLine($"Deleted {affectedRows} user(s).");
}

public class User
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

In the very long example above, we connect to the database we created locally. First, we query all the users to print them. Then, we insert a user and finally update and delete a user. This is the simplest thing you can do using Dapper and, most likely, the most common thing that is needed for the vast majority of applications.

Now, we can move on to some more advanced examples. Let's create another table called Orders and add some sample data again.

CREATE TABLE Orders (
    Id INT PRIMARY KEY IDENTITY(1,1),
    UserId INT NOT NULL,
    OrderDate DATETIME NOT NULL,
    Amount DECIMAL(18, 2) NOT NULL,
    FOREIGN KEY (UserId) REFERENCES Users(Id)
);

INSERT INTO Orders (UserId, OrderDate, Amount) VALUES (3, GETDATE(), 100.50);
INSERT INTO Orders (UserId, OrderDate, Amount) VALUES (3, GETDATE(), 250.75);
INSERT INTO Orders (UserId, OrderDate, Amount) VALUES (2, GETDATE(), 50.25);

Let's also create a stored procedure so we can understand how to call stored procedures using Dapper.

CREATE PROCEDURE sp_GetUserAndOrders
    @UserId INT
AS
BEGIN
    SELECT Id, FirstName, LastName 
    FROM Users
    WHERE Id = @UserId;

    SELECT Id, UserId, OrderDate, Amount 
    FROM Orders
    WHERE UserId = @UserId;
END;

Dapper can handle queries that return multiple result sets. Let's see an example where we retrieve data for both User and Order tables and also call our stored procedure that created:

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();

    using (var multi = await connection.QueryMultipleAsync("sp_GetUserAndOrders", new { UserId = 3 }, commandType: CommandType.StoredProcedure))
    {
        var user = (await multi.ReadAsync<User>()).FirstOrDefault();

        var orders = (await multi.ReadAsync<Order>()).ToList();

        Console.WriteLine($"User: {user.FirstName} {user.LastName}");

        Console.WriteLine("Orders:");
        foreach (var order in orders)
        {
            Console.WriteLine($"Order Date: {order.OrderDate}, Amount: {order.Amount}");
        }
    }
}

As we saw, Dapper’s QueryMultiple method allows you to handle multiple result sets in one go. In the example above, we read the User with Id = 3 using our stored procedure and retrieve all the User Orders just like we selected in our stored procedure.

Avoiding SQL Injection

To avoid SQL injection when using Dapper, you should always use parameterized queries. Parameterized queries ensure that user input is treated as data, not as executable code, preventing malicious SQL commands from being injected into your queries.

So avoid using string interpolation and use parameterized queries like this:

 var newUser = new User { FirstName = "New", LastName = "User" };
    string insertSql = "INSERT INTO Users (FirstName, LastName) VALUES (@FirstName, @LastName)";
    var affectedRows = await connection.ExecuteAsync(insertSql, newUser);

As we saw in our example above.

Conclusion

Dapper is an excellent choice for developers who need fast, simple, and flexible data access in .NET without the overhead of a full-fledged ORM. Now I want to hear from you. Are you using Dapper? Do you prefer Entity Framework? I hope you enjoyed it, and as always, keep coding.

Next steps

Dometrain's 7-day trial is now live, so try it for free and get access to 10 Dometrain courses for 7 days.

squaread_ad-trial.jpg