Why Dapper

After this lesson you can

• Explain what Dapper does for you, and what stays the same as ADO.NET

Key takeaways

• Dapper keeps your SQL and writes the ADO.NET code around it: it opens the connection, adds the parameters and turns each row into an object.
• Dapper builds each object by matching a column name to a property name.
• dotnet add package Dapper adds the library, and using Dapper; adds methods such as Query to every SqlConnection.
• Dapper is a micro-ORM: it maps rows to objects and stops there. Unlike Entity Framework Core, it leaves the SQL to you.

In Connecting Apps to SQL Server, every DAO method followed the same pattern: open a connection, create a command, add each parameter, run it, read each row, and copy each column into a property. The SQL was the interesting part; the rest was the same in every method.

Dapper is a small library that writes that repeated part for you. You still write the SQL. Dapper runs it, and turns the rows into your objects.

The same method, twice

Here's GetById from the ADO.NET Product DAO:

public Product? GetById(int productId)
{
    string sql = """
        SELECT ProductId, ProductName, CategoryId, UnitPrice, UnitsInStock
        FROM Products
        WHERE ProductId = @ProductId;
        """;

    using SqlConnection connection = new SqlConnection(connectionString);
    connection.Open();
    using SqlCommand command = new SqlCommand(sql, connection);
    command.Parameters.AddWithValue("@ProductId", productId);

    using SqlDataReader reader = command.ExecuteReader();
    if (reader.Read())
    {
        return new Product
        {
            ProductId = (int)reader["ProductId"],
            ProductName = (string)reader["ProductName"],
            CategoryId = reader["CategoryId"] as int?,
            UnitPrice = reader["UnitPrice"] as decimal?,
            UnitsInStock = reader["UnitsInStock"] as short?
        };
    }
    return null;
}

And here it is with Dapper:

public Product? GetById(int productId)
{
    string sql = """
        SELECT ProductId, ProductName, CategoryId, UnitPrice, UnitsInStock
        FROM Products
        WHERE ProductId = @ProductId;
        """;

    using SqlConnection connection = new SqlConnection(connectionString);
    return connection.QuerySingleOrDefault<Product>(sql, new { ProductId = productId });
}

The SQL is identical. What's gone is the plumbing: Dapper opens the connection, adds @ProductId from the object you pass, runs the query, and builds the Product by matching each column name to a property name.

A whole program

Add the package to a project that already has SqlClient:

dotnet add package Dapper

Then:

using Dapper;
using Microsoft.Data.SqlClient;

string connectionString = "Server=localhost;Database=Northwind;Integrated Security=true;TrustServerCertificate=true;";

using SqlConnection connection = new SqlConnection(connectionString);
List<Product> products = connection.Query<Product>(
    "SELECT TOP (3) ProductId, ProductName, UnitPrice FROM Products ORDER BY UnitPrice DESC;").ToList();

foreach (Product product in products)
{
    Console.WriteLine(quot;{product.ProductId}: {product.ProductName}, {product.UnitPrice:0.00}");
}

public class Product
{
    public int ProductId { get; set; }
    public string ProductName { get; set; } = "";
    public int? CategoryId { get; set; }
    public decimal? UnitPrice { get; set; }
    public short? UnitsInStock { get; set; }
}

38: Côte de Blaye, 263.50
29: Thüringer Rostbratwurst, 123.79
9: Mishi Kobe Niku, 97.00

using Dapper; adds new methods, such as Query, to every SqlConnection. (They're extension methods: methods a library adds to a class it didn't write.)

What Dapper is, and isn't

Dapper is a micro-ORM: it maps rows to objects and stops there. Big ORMs, such as Entity Framework Core (which you'll meet in HTTP and Web APIs), also write the SQL for you. Dapper's choice is to keep the SQL in your hands, so what runs is the SQL you wrote, apart from expanding IN lists. It's fast, small, and widely used in .NET; this site's own services use it.

Check yourself

1 question to try on the lesson page.

So what

Same SQL, a tenth of the code. Dapper removes the repetitive part of data access, so each DAO method is the query, and a line to run it.

Key terms

• Dapper
• Micro-ORM
• ORM

Cheat sheets

• Why Dapper