# Why Dapper

Dapper is a small library that keeps your SQL but writes the ADO.NET code around it for you - opening the connection, adding parameters, and turning rows into objects.

Journeyman · C# · T-SQL · Updated November 3, 2026

## 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:

```csharp
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:

```csharp
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:

```bash
dotnet add package Dapper
```

Then:

```csharp
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($"{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; }
}
```

```text
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

| Stays the same as ADO.NET | Dapper does for you |
|---|---|
| You write the SQL | Opens and closes the connection |
| You use `SqlConnection` and your connection string | Adds the parameters, from an object |
| You choose the queries, joins and indexes | Runs the command |
| SQL injection is avoided with parameters | Turns each row into an object, by column name |

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](https://www.ask-codey.com/cheat-sheets/dapper-why-dapper-cheat-sheet)

Source: https://www.ask-codey.com/lessons/dapper-why-dapper