Dapper

Introduction

Dapper is an open-source micro Object-Relational Mapper (ORM) for .NET and .NET Core. It allows developers to execute raw SQL queries and map the results directly to C# objects in a fast and efficient way. Dapper focuses on object mapping, which makes it extremely lightweight and fast compared to full ORMs.

Why use Dapper

• High performance with minimal overhead.
• Simple to use with plain SQL.
• Automatic mapping to C# objects.
• Supports both synchronous and asynchronous queries.

When to use Dapper

• When high performance is required .
• When you prefer writing SQL manually.
• In web APIs or stateless applications .

How to use Dapper

Install via NUget:

dotnet add package Dapper

Basic example:

var sql = "SELECT * FROM Product WHERE CategoryID = @categoryID";
var products = connection.Query<Product>(sql, new { categoryID = 1 }).ToList();

• SQL is executed against the database.

• Parameters are safely injected.

• Results are automatically mapped to objects.

Mapping

Mapping results

Dapper maps database columns to object properties automatically.

Example:

class Product
{
    public int ProductID { get; set; }
    public string Name { get; set; }
}

If SQL returns:

SELECT ProductID, Name FROM Product

Then the values are mapped directly to matching properties. Property names must match column names (case-insensitive).

Mapping custom models

Mapping custom models means mapping query results to your own custom classes instead of database entities.

Example:

class ProductDTO
{
    public string Name { get; set; }
    public decimal Price { get; set; }
}
var sql = "SELECT Name, UnitPrice AS Price FROM Product";
var products = connection.Query<ProductDTO>(sql);

• Use AS to match property names.
• Only required columns need to exist.

Handling multiple rows

When a query returns multiple rows, Dapper maps them to a collection.

• Returned type: IEnumerable
• Can be iterated or converted to a list.

Query versions

Query (multiple rows)

Used when retrieving multiple rows.

var sql = "SELECT * FROM Product WHERE CategoryID = @categoryID";
var products = connection.Query<Product>(sql, new { categoryID = 1 });

Returns: IEnumerable

QueryFirst / QueryFirstAsync

Returns the first row from the result set.

Use when:

• You only need one row
• The query may return multiple rows

Example:

var sql = "SELECT * FROM Product WHERE ProductID = @productID";
var product = connection.QueryFirst<Product>(sql, new { productID = 1 });

Returns: Single object.

Behavior: Throws error if no rows are returned.

QuerySingle / QuerySingleAsync

Returns exactly one row. This version is good when only one result is expected.

Example:

var sql = "SELECT * FROM Product WHERE ProductID = @productID";
var product = connection.QuerySingle<Product>(sql, new { productID = 1 });

Returns: Single object.

Behavior: Throws error if 0 or more than 1 row is returned.

ExecuteScalar / ExecuteScalarAsync

Returns a single value (first column of the first row). Useful for aggregate queries such as :

• COUNT.
• SUM.
• MAX.

Example:

var count = connection.ExecuteScalar<int>("SELECT COUNT(*) FROM Product");

Returns: Single value (e.g., int, string).

QueryMultiple / QueryMultipleAsync

Used to execute multiple SQL queries in a single call and return multiple result sets.

Use when:

• You need data from multiple tables.
• You want to reduce the number of database calls.

Returns:

• Multiple collections (one per query result set).

Query (non-generic)

Returns dynamic objects instead of typed models.

Example:

var sql = "SELECT * FROM Product WHERE CategoryID = @categoryID";
var result = connection.Query(sql, new { categoryID = 1 });

Returns: IEnumerable

Query specific columns

Used when only part of the data is needed.

Example:

var names = connection.Query<string>("SELECT Name FROM Product");

Returns: IEnumerable

Async methods

Async methods work the same as synchronous ones but require await.