Introduction to Dapper in CSharp

Introduction to Dapper - A Lightweight ORM for C#

From ADO.NET to Object Mapping

You should already be familiar with using ADO.NET for accessing a database. If not, have a look at Introduction to Databases in CSharp.

We saw that while ADO.NET gives full control, it also requires a lot of repetitive code:

• Opening and closing connections
• Creating commands and parameters
• Reading rows from DataReader
• Manually converting rows into C# objects

This process is known as object mapping, and doing it manually can be time-consuming and error-prone.

---

What Is an ORM?

ORM stands for Object-Relational Mapper.

It’s a programming technique (and a class of libraries) that automatically maps database tables to C# classes and SQL results to objects.

An ORM handles:

• Converting objects → database rows (inserts,updates)
• Converting database rows → objects (queries)
• Managing SQL parameters automatically

With an ORM, developers can focus more on business logic instead of the mechanics of SQL data handling.

---

Dapper - A Lightweight ORM

Dapper is a micro ORM - meaning it adds just enough abstraction to simplify data access, but still allows you to write your own SQL.

• It’s developed by the Stack Overflow team.
• It builds on top of ADO.NET.
• It provides extension methods for IDbConnection such as Query, QueryFirst, Execute, etc.

Advantages

• Very fast and lightweight.
• Minimal setup and dependencies.
• Keeps SQL control (no “magic” query generation).

Disadvantages

• No automatic schema migrations or relationship tracking.
• You still write SQL manually.

---

Setting Up Dapper

Install Dapper from NuGet:

dotnet add package Dapper
dotnet add package MySql.Data

Import the namespaces:

using Dapper;
using MySql.Data.MySqlClient;

---

Using Dapper for CRUD Operations

Let’s assume we have a Student class and a MySQL table named Students:

CREATE TABLE Students (
    Id int PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(100),
    Email VARCHAR(100)
);

public class Student
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
}

---

1. Create (Insert)

using var connection = new MySqlConnection(_connectionString);
 
string sql = "INSERT INTO Students (Id, Name, Email) VALUES (@Id, @Name, @Email)";
var student = new Student { Name = "Alice", Email = "alice@example.com" };
 
connection.Execute(sql, student);
Console.WriteLine("Student inserted successfully.");

🧩 What happens here:
Dapper looks at the student object, reads its property values, and automatically binds them to the SQL parameters.

---

2. Read (Select by Id)

string sql = "SELECT * FROM Students WHERE Id = @Id";
var student = connection.QueryFirstOrDefault<Student>(sql, new { Id = 42 });
 
if (student != null)
    Console.WriteLine($"{student.Name} - {student.Email}");
else
    Console.WriteLine("Student not found.");

🧩 Explanation:
QueryFirstOrDefault<T> executes the SQL and automatically maps the result row to a Student object.

---

3. Read All

string sql = "SELECT * FROM Students";
var students = connection.Query<Student>(sql).ToList();
 
foreach (var s in students)
    Console.WriteLine($"{s.Id}: {s.Name} ({s.Email})");

🧩 Explanation:
Query<T> automatically reads all rows and returns an IEnumerable<T> - each row is converted into a Student object.

---

4. Update

string sql = "UPDATE Students SET Name = @Name, Email = @Email WHERE Id = @Id";
 
var student = new Student
{
    Id = 42,
    Name = "Alice Updated",
    Email = "alice.updated@example.com"
};
 
connection.Execute(sql, student);
Console.WriteLine("Student updated successfully.");

🧩 Explanation:
Execute runs a SQL command that does not return results (INSERT, UPDATE, DELETE).
The student object automatically provides the parameter values.

---

5. Delete

string sql = "DELETE FROM Students WHERE Id = @Id";
connection.Execute(sql, new { Id = "some-id" });
Console.WriteLine("Student deleted.");

🧩 Explanation:
Here we pass an anonymous object (new { Id = ... }) instead of a full entity.
Dapper maps this parameter directly to the SQL placeholder.

---

Key Takeaways

• Dapper is a middle ground between raw ADO.NET and full ORM frameworks.
• You still write SQL, but Dapper handles object mapping and parameter binding.
• It’s ideal for applications that want simplicity and performance without giving up control over queries.