Published / updated29 Aug 2026

Dapper Fundamentals

Before you start

You need: all of Articles 01–06. Dapper removes the repetition you have just written by hand — which is why it comes second.

In Visual Studio: add the Dapper package from NuGet.

Time: about 45 minutes, plus the practice.

Learning objective

Replace hand-written ADO.NET mapping with Dapper, and choose the correct query method for how many rows you expect.

Topics

What a micro-ORM is
Installing Dapper
Query<T> and how mapping works
QueryFirst, QuerySingle, and their OrDefault forms
Execute and ExecuteScalar
Column-to-property matching
Dapper versus ADO.NET versus EF Core
What Dapper does not do

What Dapper is

Dapper is a set of extension methods on IDbConnection. It takes a SQL string and a parameter object, runs the command through ADO.NET, and maps the result rows to your types.

It does not generate SQL, track changes, manage a schema, or maintain a model. You write the SQL; Dapper removes the mapping code.

	ADO.NET	Dapper	EF Core
Who writes the SQL	You	You	The ORM (usually)
Mapping	By hand	Automatic	Automatic
Change tracking	No	No	Yes
Migrations	No	No	Yes
Speed	Baseline	~Baseline	Slower
Learning curve	Moderate	Small	Large
Control over SQL	Total	Total	Limited

Dapper is roughly as fast as hand-written ADO.NET because it generates and caches IL for each mapping rather than using reflection per row.

The position on this track: SQL first, then ADO.NET so you understand the mechanism, then Dapper for productivity. Dapper is the primary data-access approach; EF Core is an optional separate track.

Installing

Right-click the project → Manage NuGet Packages → Browse, search for the package, and click Install.

The Package Manager Console (Tools → NuGet Package Manager → Package Manager Console) does the same thing typed:

Install-Package Dapper
Install-Package Microsoft.Data.SqlClient

using Dapper;
using Microsoft.Data.SqlClient;
using System.Data;

using Dapper; is what brings the extension methods into scope. Without it, connection.Query<Student> does not compile — and the error ("no definition for Query") sends people looking for a missing package that is already installed.

Query<T>

public List<Student> GetStudentsByClass(int schoolId, string className)
{
    const string sql = @"
        SELECT   s.Id, s.PublicId, s.SchoolId, s.Name, s.RollNumber, s.ClassName,
                 s.Section, s.DateOfBirth, s.ParentName, s.ParentPhone, s.Address, s.Status
        FROM     dbo.Student AS s
        WHERE    s.SchoolId = @SchoolId
          AND    s.ClassName = @ClassName
          AND    s.Status <> 1
        ORDER BY s.Section, s.Name;";

    using (IDbConnection connection = new SqlConnection(_connectionString))
    {
        return connection.Query<Student>(sql, new { SchoolId = schoolId, ClassName = className })
                         .ToList();
    }
}

That replaces roughly forty lines of ADO.NET. Compare it with the reader version from the mapping article — same SQL, same result, no GetOrdinal, no IsDBNull, no MapStudent.

Three things to notice:

No connection.Open(). Dapper opens the connection if it is closed, and closes it again afterwards. If it was already open, Dapper leaves it open — so a connection you opened is a connection you must close.

Parameters come from an anonymous object. new { SchoolId = schoolId } supplies @SchoolId. Property names must match parameter names, case-insensitively. These are real ADO.NET parameters — Dapper is not concatenating anything.

.ToList() matters. Query<T> is buffered by default, so the rows are already materialised; .ToList() just converts the IEnumerable<T>. With buffered: false it becomes lazy, and then the connection must stay open while you enumerate.

Column-to-property matching

Dapper matches result columns to properties by name, case-insensitively. A property with no matching column keeps its default; a column with no matching property is ignored silently.

SELECT s.Id, s.Name, s.RollNumber FROM dbo.Student AS s

public class Student
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public string RollNumber { get; set; } = string.Empty;
    public string? Address { get; set; }      // not selected — stays null
}

Silent non-matching is the main Dapper gotcha. A misspelled alias, a column left out of the SELECT, or a renamed property produces a default value rather than an error. When a property is unexpectedly null or 0, check the column name in the SQL first.

Aliases fix a mismatch:

SELECT s.Id           AS StudentId,
       s.Name         AS StudentName,
       COUNT(r.Id)    AS ResultCount
FROM   dbo.Student AS s
LEFT JOIN dbo.ExamResult AS r ON r.StudentId = s.Id
GROUP BY s.Id, s.Name

An aggregate or expression must be aliased — an unnamed column cannot match anything.

For a codebase using snake_case columns, map underscores once at startup:

Dapper.DefaultTypeMap.MatchNamesWithUnderscores = true;

The query methods

Method	Rows expected	0 rows	2+ rows
`Query<T>`	Any	Empty sequence	Fine
`QueryFirst<T>`	1 or more	Throws	Returns the first
`QueryFirstOrDefault<T>`	0 or more	`default`	Returns the first
`QuerySingle<T>`	Exactly 1	Throws	Throws
`QuerySingleOrDefault<T>`	0 or 1	`default`	Throws

// A list — zero rows is fine
List<Student> students = connection.Query<Student>(sql, parameters).ToList();

// A lookup by unique key — zero is legitimate, two would be a data defect
Student? student = connection.QuerySingleOrDefault<Student>(
    "SELECT ... FROM dbo.Student WHERE SchoolId = @SchoolId AND PublicId = @PublicId",
    new { SchoolId = schoolId, PublicId = publicId });

// Top scorer — several rows exist, take the first
Student topper = connection.QueryFirst<Student>(
    "SELECT TOP 1 ... ORDER BY MarksObtained DESC", parameters);

Choose the method that states your expectation. QuerySingleOrDefault on a lookup by primary key means a duplicate throws immediately, at the query, instead of being silently ignored and surfacing as a strange bug later.

QueryFirstOrDefault where QuerySingleOrDefault was meant hides real data corruption. The Single forms are a runtime assertion about your data — use them.

For a value type, default is 0, not null:

int? count = connection.QuerySingleOrDefault<int?>(sql, parameters);

Without the int?, "no rows" and "the value is zero" become indistinguishable.

Execute

For INSERT, UPDATE, DELETE and procedure calls with no result set. Returns rows affected.

public void Deactivate(int schoolId, Guid publicId)
{
    const string sql = @"
        UPDATE dbo.Student
        SET    Status = 1
        WHERE  SchoolId = @SchoolId AND PublicId = @PublicId;";

    using (IDbConnection connection = new SqlConnection(_connectionString))
    {
        int affected = connection.Execute(sql, new { SchoolId = schoolId, PublicId = publicId });

        if (affected == 0)
        {
            throw new InvalidOperationException("Student not found.");
        }
    }
}

Check the return value for the same reason as in ADO.NET: an UPDATE matching nothing is not an error.

Execute with a list

Passing a collection runs the statement once per item:

List<object> rows = new List<object>();

foreach (ExamResult result in results)
{
    rows.Add(new
    {
        SchoolId = schoolId,
        ExamId = examId,
        StudentId = result.StudentId,
        MarksObtained = result.MarksObtained,
        IsAbsent = result.IsAbsent
    });
}

connection.Execute(
    @"INSERT INTO dbo.ExamResult (PublicId, SchoolId, ExamId, StudentId, MarksObtained, IsAbsent)
      VALUES (NEWID(), @SchoolId, @ExamId, @StudentId, @MarksObtained, @IsAbsent);",
    rows);

Convenient, and it is not a bulk insert — it is 40 separate round trips. For a handful of rows that is fine; for thousands use a table-valued parameter or SqlBulkCopy.

ExecuteScalar

public decimal GetOutstandingBalance(int schoolId, int feeAccountId)
{
    const string sql = @"
        SELECT fa.TotalFees - fa.DiscountAmount - fa.PaidAmount
        FROM   dbo.FeeAccount AS fa
        WHERE  fa.Id = @FeeAccountId AND fa.SchoolId = @SchoolId;";

    using (IDbConnection connection = new SqlConnection(_connectionString))
    {
        return connection.ExecuteScalar<decimal>(
            sql, new { SchoolId = schoolId, FeeAccountId = feeAccountId });
    }
}

The generic form converts for you, which removes the object / DBNull.Value handling that plain ADO.NET needs. ExecuteScalar<decimal> on no rows returns 0; ExecuteScalar<decimal?> returns null, which is the version that distinguishes "no account" from "balance is zero".

Inserting and returning the key:

public int Create(Student student)
{
    const string sql = @"
        INSERT INTO dbo.Student (PublicId, SchoolId, Name, RollNumber, ClassName, Section,
                                 DateOfBirth, ParentName, ParentPhone, Address, Status)
        VALUES (@PublicId, @SchoolId, @Name, @RollNumber, @ClassName, @Section,
                @DateOfBirth, @ParentName, @ParentPhone, @Address, @Status);

        SELECT CAST(SCOPE_IDENTITY() AS INT);";

    student.PublicId = Guid.NewGuid();

    using (IDbConnection connection = new SqlConnection(_connectionString))
    {
        return connection.ExecuteScalar<int>(sql, student);
    }
}

Note the parameter object here is student itself — Dapper reads @Name, @RollNumber and the rest straight off the entity's properties. Any property not referenced in the SQL is ignored.

Nulls

Dapper handles NULL in both directions with no ceremony:

// null property → DBNull.Value on the way in
connection.Execute(sql, new { Address = (string?)null });

// NULL column → null property on the way out
public string? Address { get; set; }

This is one of the largest practical wins over ADO.NET. Every IsDBNull check and every ?? DBNull.Value disappears, and with them an entire class of bug.

The one requirement: the property must be nullable. A NULL column mapping to a non-nullable decimal throws, which is correct — it is telling you the model does not match the data.

public decimal? MarksObtained { get; set; }   // absent students have NULL

What Dapper does not do

Not provided	Consequence
SQL generation	You write every statement
Change tracking	`student.Name = "x"` saves nothing until you call `Execute`
Migrations	Schema is managed separately
Lazy loading	Related data needs another query, or a join
A caching layer	Each call hits the database
Identity map	Two queries for the same row return two objects

None of these is a defect. They are the trade: no abstraction to fight, and the SQL that runs is the SQL you wrote.

The practical consequence is that SELECT * remains a bad idea. Dapper will happily map it, and the new PasswordHash column will silently start being fetched.

Errors you will hit

Message	Cause	Fix
`Sequence contains no elements`	`QuerySingle`/`QueryFirst` matched nothing	Use the `OrDefault` form and check
`Sequence contains more than one element`	`QuerySingle` matched several	Filter further, or use `QueryFirst`
A property is `null` after a query	Column name does not match the property	Alias the column
`The member X of type Y cannot be used as a parameter value`	Passed a complex object where a scalar was expected	Pass an anonymous object of scalars
Everything is `null` on a mapped type	The type has no parameterless constructor or no setters	Add them

Query returns an empty list; QuerySingle throws. Choose from what "no rows" means in that case — nothing found is often normal, and should not be an exception.

Common mistakes

Missing using Dapper;, then hunting for a missing package
A column name not matching a property, giving a silent default
Not aliasing a computed or aggregate column
QueryFirstOrDefault where QuerySingleOrDefault was meant, hiding duplicates
ExecuteScalar<decimal> where decimal? was needed
Ignoring the Execute return value
Expecting Execute with a list to be a bulk insert
A non-nullable property for a nullable column
buffered: false with a connection that closes first
Opening a connection manually and expecting Dapper to close it
SELECT * because the mapping is automatic

Practice

The course exercise is convert ADO.NET code to Dapper.

Take the StudentRepository you wrote with SqlDataReader and convert every method to Dapper. Record the before and after line counts.
Confirm both versions return identical results for the same inputs.
Misspell one column alias in a SELECT. Confirm the property is silently null with no exception. This is the failure mode to recognise on sight.
Remove a column from a SELECT list and confirm the property keeps its default.
Query a unique key with QuerySingleOrDefault. Insert a duplicate directly in SSMS and re-run it. Confirm the throw, and explain why that is better than QueryFirstOrDefault.
Call QueryFirst on a query returning no rows. Record the exception message.
Use ExecuteScalar<decimal> on a non-existent account, then ExecuteScalar<decimal?>. Compare.
Insert a student passing the entity itself as the parameter object. Confirm extra properties are ignored.
Map an absent exam result into decimal? and confirm it is null, not 0. Change the property to decimal and record the exception.
Insert 1,000 rows with Execute and a list. Time it, then compare with a table-valued parameter.

Then run the AI drill from the course — ask an assistant to explain a Dapper mapping, giving it a query whose column names do not match the class. Check whether it spots the silent-default behaviour or claims an exception would be thrown. Verify its answer against exercise 3.

You can now

Replace hand-written mapping with Dapper
Choose between Query, QueryFirst, QuerySingle and their OrDefault forms
Pass parameters as an anonymous object
Alias columns so they map to properties
Say what Dapper does and does not do for you

Review questions

What happens when a result column has no matching property, and what happens the other way round?
When would you use QuerySingleOrDefault rather than QueryFirstOrDefault?
Why does ExecuteScalar<decimal> hide something that ExecuteScalar<decimal?> reveals?
Is Execute with a list of parameter objects a bulk insert?

Next: Dapper parameters and procedures

Before you start​

Learning objective​

Topics​

What Dapper is​

Installing​

Query<T>​

Column-to-property matching​

The query methods​

Execute​

Execute with a list​

ExecuteScalar​

Nulls​

What Dapper does not do​

Errors you will hit​

Common mistakes​

Practice​

You can now​

Review questions​