What Is Dapper? The Thin Layer on Top of ADO.NET and Your First Query
We meet Dapper as a set of extension methods on top of ADO.NET: what micro-ORM means, when to pick it over EF Core, setting up .NET 10 + Dapper 2.1.89 + SQLite, the first Query and a short history from 1.x to Dapper.AOT.
Version note: The code in this post targets Dapper 2.1 and .NET 10.
Guess First
In the code below we create a connection but never call Open(). Right after that we run a query on the same connection:
using var connection = new SqliteConnection(BookstoreDb.ConnectionString);
Console.WriteLine($"Before: {connection.State}");
var books = connection.Query<Book>("SELECT id, title, price FROM books ORDER BY price");
Console.WriteLine($"After: {connection.State}");There are three possibilities. One: the Query line throws an exception saying the connection is closed. Two: the query runs and the last line prints Open. Three: the query runs and the last line still prints Closed. Which one is it? We will see in our first real output in a moment.
A Query with ADO.NET: The Manual Work
The lowest layer for talking to a database in .NET is ADO.NET. It does not matter whether you are facing SQL Server, PostgreSQL or SQLite; every provider implements the same abstractions: a connection (DbConnection), a command (DbCommand), parameters (DbParameter) and a reader that walks the results row by row (DbDataReader). Reading the books below a certain price with plain ADO.NET looks like this:
static List<Book> GetCheapBooksAdoNet(decimal maxPrice)
{
using var connection = new SqliteConnection(BookstoreDb.ConnectionString);
connection.Open();
using var command = connection.CreateCommand();
command.CommandText = "SELECT id, title, price FROM books WHERE price < @MaxPrice";
command.Parameters.AddWithValue("@MaxPrice", maxPrice);
var books = new List<Book>();
using var reader = command.ExecuteReader();
while (reader.Read())
{
// Read the columns by position and convert each one by hand
books.Add(new Book
{
Id = reader.GetInt32(0),
Title = reader.GetString(1),
Price = reader.GetDecimal(2)
});
}
return books;
}Look at what we just did: open the connection, create a command, give it the SQL and the parameter, get a reader, read each column in order on every row, convert it to the right type, put it on an object, add the object to a list. Every one of these lines is necessary, and none of them is business logic. Change the column order in the SELECT and GetString(1) reads a different column; add a column to the table and you update this loop by hand.
What Does Dapper Do?
The same query with Dapper:
static List<Book> GetCheapBooksDapper(decimal maxPrice)
{
using var connection = new SqliteConnection(BookstoreDb.ConnectionString);
return connection.Query<Book>(
"SELECT id, title, price FROM books WHERE price < @MaxPrice",
new { MaxPrice = maxPrice }).ToList();
}Calling both methods with 14m in the same program gives exactly the same result:
ADO.NET: A Wizard of Earthsea, Solaris, The Hitchhiker's Guide to the Galaxy, Exhalation
Dapper: A Wizard of Earthsea, Solaris, The Hitchhiker's Guide to the Galaxy, ExhalationDapper does not replace ADO.NET; it is a handful of extension methods on top of it. Methods like Query, Execute and QuerySingle are added to the IDbConnection interface, so they work with a connection from any provider. Under the hood a command is still created, parameters are still added, a reader is still opened and the rows are still read. The only difference is that Dapper does this repetitive work instead of you.
As for how it turns rows into Book objects: the first time Dapper runs a query, it looks at the columns of the result set, matches them to the properties of the Book class by name and generates IL code for that mapping at runtime. It caches that small method together with the SQL text and the types, and later executions of the same query use the ready-made code directly. That is the secret behind a speed very close to the while (reader.Read()) loop you would write by hand.
This is why Dapper is called a micro-ORM rather than an ORM. Most of what you expect from an ORM is not here. There is no change tracking; changing a property on an object changes nothing in the database. There is no SaveChanges, no layer that translates LINQ to SQL, no migrations, and no navigation properties that load related data for you. Dapper does two things well: it runs the SQL you wrote, safely, together with its parameters, and it maps the result to objects.
Here is an analogy. ADO.NET is a toolbox; you turn every screw yourself. Dapper adds a cordless screwdriver to that toolbox, but you still decide where each screw goes, which means you still write the SQL. An ORM like EF Core is a contractor who builds the house from the plans for you: you describe what you want, and it generates the SQL.
Setup: .NET 10, Dapper 2.1.89 and SQLite
Throughout the series we use a database that needs no installation: SQLite. There is no separate server; the database is a single file in the project folder. As the provider we use Microsoft's Microsoft.Data.Sqlite package. In a terminal:
dotnet new console -n Bookstore -f net10.0
cd Bookstore
dotnet add package Dapper --version 2.1.89
dotnet add package Microsoft.Data.Sqlite --version 10.0.14Your csproj should look like this:
<Project Sdk="Microsoft.NET.Sdk">
<PropertyGroup>
<OutputType>Exe</OutputType>
<TargetFramework>net10.0</TargetFramework>
<ImplicitUsings>enable</ImplicitUsings>
<Nullable>enable</Nullable>
</PropertyGroup>
<ItemGroup>
<PackageReference Include="Dapper" Version="2.1.89" />
<PackageReference Include="Microsoft.Data.Sqlite" Version="10.0.14" />
</ItemGroup>
</Project>The Dapper package brings no database provider with it; you add the ADO.NET package for whatever database you talk to. Microsoft.Data.SqlClient for SQL Server, Npgsql for PostgreSQL, and in our case Microsoft.Data.Sqlite. The Dapper 2.1.89 NuGet package has a dedicated net10.0 target, so a .NET 10 project uses that build directly.
Our sample domain is a small bookstore: authors (authors), books (books), orders (orders) and order lines (order_items). Add the helper class that creates the schema and the sample data to the project as BookstoreDb.cs:
using Dapper;
using Microsoft.Data.Sqlite;
public static class BookstoreDb
{
public const string ConnectionString = "Data Source=bookstore.db";
// Drops the tables and rebuilds them with the same sample data on every run
public static void Reset()
{
using var connection = new SqliteConnection(ConnectionString);
connection.Execute("""
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS books;
DROP TABLE IF EXISTS authors;
CREATE TABLE authors (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
country TEXT
);
CREATE TABLE books (
id INTEGER PRIMARY KEY,
author_id INTEGER NOT NULL REFERENCES authors(id),
title TEXT NOT NULL,
price REAL NOT NULL,
published_year INTEGER,
stock INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_email TEXT NOT NULL,
created_at TEXT NOT NULL
);
CREATE TABLE order_items (
id INTEGER PRIMARY KEY,
order_id INTEGER NOT NULL REFERENCES orders(id),
book_id INTEGER NOT NULL REFERENCES books(id),
quantity INTEGER NOT NULL,
unit_price REAL NOT NULL
);
INSERT INTO authors (id, name, country) VALUES
(1, 'Ursula K. Le Guin', 'USA'),
(2, 'Frank Herbert', 'USA'),
(3, 'Stanislaw Lem', 'Poland'),
(4, 'Douglas Adams', 'UK'),
(5, 'Ted Chiang', 'USA');
INSERT INTO books (id, author_id, title, price, published_year, stock) VALUES
(1, 1, 'A Wizard of Earthsea', 12.50, 1968, 8),
(2, 1, 'The Left Hand of Darkness', 14.00, 1969, 3),
(3, 1, 'The Dispossessed', 15.25, 1974, 0),
(4, 2, 'Dune', 18.90, 1965, 12),
(5, 3, 'Solaris', 11.40, 1961, 5),
(6, 4, 'The Hitchhiker''s Guide to the Galaxy', 9.99, 1979, 20),
(7, 5, 'Exhalation', 13.20, NULL, 4);
""");
}
}Even while setting up the database we used Dapper's Execute method; the SQLite provider can run several semicolon-separated statements in a single command. Notice that the column names are snake_case: author_id, published_year. That is deliberate. How they meet the PascalCase properties on the C# side is the topic of the next post.
The First Query
The class that carries the results, Book.cs:
public class Book
{
public int Id { get; set; }
public string Title { get; set; } = "";
public decimal Price { get; set; }
}And Program.cs:
using Dapper;
using Microsoft.Data.Sqlite;
BookstoreDb.Reset();
using var connection = new SqliteConnection(BookstoreDb.ConnectionString);
Console.WriteLine($"Before: {connection.State}");
var books = connection.Query<Book>("SELECT id, title, price FROM books ORDER BY price");
Console.WriteLine($"After: {connection.State}");
foreach (var book in books)
{
Console.WriteLine($"{book.Id} {book.Title,-38} {book.Price,6:0.00}");
}Run it with dotnet run:
Before: Closed
After: Closed
6 The Hitchhiker's Guide to the Galaxy 9.99
5 Solaris 11.40
1 A Wizard of Earthsea 12.50
7 Exhalation 13.20
2 The Left Hand of Darkness 14.00
3 The Dispossessed 15.25
4 Dune 18.90Here is the answer to the opening question: no exception, and the connection is Closed both before and after the query. When Dapper sees that it was handed a closed connection, it opens it, runs the query and closes it when done. If you opened the connection yourself, Dapper leaves it alone and open. And this close is not a physical disconnect; ADO.NET's connection pooling keeps the connection in a pool, so the next open is cheap. For individual queries this is a very convenient behavior. But when you need several commands in the same session, inside a transaction for example, you open the connection yourself. We cover connection management in 06 and transactions in 07.
Query<Book> returns an IEnumerable<Book>, but by default all rows are read into a list before the method returns. This is called a buffered query, and it is why the data is already in memory by the time the connection is closed. We will discuss the alternative for large result sets in the advanced part of the series.
Look at the mapping as well. The id, title and price columns were matched to the Id, Title and Price properties of Book regardless of letter case. Even though SQLite returns id as a 64-bit integer and price as a double, Dapper converted them to the int and decimal properties.
Dapper or EF Core?
This question is often framed wrongly, because the answer is frequently "both". As we saw in the EF Core series, EF Core is very strong at change tracking, LINQ, migrations and relationship management. In a write-heavy application with a rich domain model and a schema managed from code, it saves you a lot of time.
Dapper shines where you want the SQL fully under your control: reports, dashboard queries, complex JOINs and CTEs, database-specific features such as window functions, ready-made SQL written and tuned by the database team, and read paths where every millisecond counts.
It has a price. The compiler does not check SQL strings; if you misspell a column name, you find out at runtime. When the schema changes you update the SQL yourself, and you write the insert and update code yourself. In practice many teams use EF Core on the write side and Dapper for heavy read queries. The two live happily in the same project; you can run Dapper on the connection EF Core gives you through context.Database.GetDbConnection().
A Short History: From Stack Overflow to Dapper.AOT
Dapper was developed for and by Stack Overflow; it has been open source since 2011 and today lives under the DapperLib organization on GitHub. Version 1.50 in 2016 brought .NET Core support; almost all of the core API you use today already existed back then.
Version 2.0.4 in August 2019 was mostly a packaging change: Dapper's dependency on System.Data.SqlClient was removed. From then on you add the provider package yourself. The 2.0 line also brought additions such as QueryUnbufferedAsync (2.0.138), which returns an IAsyncEnumerable.
The 2.1 line, which started in 2023, added nullable reference type annotations to the whole API and support for the $ parameter prefix. Version 2.1.11 brought a small behavior change that needs attention: command text without whitespace is treated as a stored procedure. Version 2.1.66 in 2025 trimmed the target frameworks, 2.1.72 in 2026 added a dedicated net10.0 target, and 2.1.86 brought back DateOnly/TimeOnly support, fixed, after it had been pulled for causing problems. Version 2.1.89, which this post is based on, was released in September 2026.
The genuinely new part of the story is Dapper.AOT. Classic Dapper generates its mapping code at runtime, while Dapper.AOT does it at build time with a source generator and interceptors, which makes it usable in apps published with Native AOT. The last post of the series is dedicated entirely to it.
In short, Dapper's API has had no breaking change since 2.0.4. That is why we teach the series on a single current baseline instead of version by version; when a feature arrived in a specific version, the relevant post will say so.
Common Pitfalls
The first pitfall happens to almost everyone: forgetting the using Dapper; line. Dapper's methods are extension methods, so without the namespace there is no Query on the connection, and the compiler tells you:
Program.cs(4,24): error CS1061: 'SqliteConnection' does not contain a definition for 'Query' and no accessible extension method 'Query' accepting a first argument of type 'SqliteConnection' could be found (are you missing a using directive or an assembly reference?)The implicit usings of .NET 10 do not include the Dapper namespace.
The second is the choice of provider. In older blog posts and forum answers you will see System.Data.SqlClient. That package is marked as deprecated on NuGet and points you to Microsoft.Data.SqlClient. In a new project, always use Microsoft.Data.SqlClient for SQL Server.
The third is expecting ORM behavior from Dapper. If you change the price of a book you read with Query and expect it to be saved somewhere, nothing happens; that object is now an ordinary C# object with no link to the database. To write the change you run your own UPDATE statement with Execute, which we cover in 05.
The fourth is the SELECT * habit. With Dapper, select what you need. Which column goes to which property is then obvious to everyone reading the code. We will see what happens when a column does not match in the next post; spoiler: most of the time nothing happens, silently.
How This Series Will Unfold
The series has sixteen posts, all based on Dapper 2.1 and .NET 10. The first nine are the core: Query<T> and mapping, single-row queries, parameters and SQL injection, changing data with Execute, async usage and connection management, transactions, multi-mapping and QueryMultiple. The next seven are advanced: buffered and unbuffered queries, type handlers, stored procedures, SQL Server specifics, dynamic SQL and the ecosystem, performance, and finally Dapper.AOT.
Almost all examples run on this SQLite database; for topics SQLite does not support, such as stored procedures, we will spin up SQL Server and PostgreSQL with docker. All outputs in the posts come from real runs of the code.
A Small Experiment
★☆☆ Set up the project, add BookstoreDb.cs, Book.cs and Program.cs, and get the output on your own machine. Then flip the order with ORDER BY price DESC.
★★☆ Call connection.Open(); in Program.cs before the query. What do the "Before" and "After" lines print this time? Explain in your own words why Dapper does not close a connection that is already open.
★★★ In both GetCheapBooksAdoNet and GetCheapBooksDapper, change the column order in the SELECT to title, id, price and run without touching anything else. Which version's result broke? Did you get an exception or wrong data? Why?
In the next post we go inside Query<T>: how exactly columns are matched to properties, what happens to snake_case columns, when dynamic results are useful, and why records sometimes complain that no matching constructor was found. And the series does not end there; as new Dapper versions come out, we will keep going with new posts.