Dapper Micro-ORM Fundamentals

Level: Intermediate

ℹ️ Where This Fits

Dapper is a lightweight ORM between ADO.NET and EF Core. You write SQL (control) + Dapper handles parameter mapping and object conversion. Best for complex queries, high-performance code, and when you need SQL flexibility without full ORM overhead.

ℹ️ What You'll Learn

• Dapper: Micro-ORM (lightweight SQL-first approach, middle ground between ADO.NET and EF Core)
• When to use Dapper: Complex SQL queries, performance-critical code, large datasets, stored procedures
• Dapper vs EF Core: EF generates SQL (less control), Dapper writes SQL (full control)
• Dapper vs ADO.NET: ADO.NET requires manual SqlCommand setup, Dapper adds Query/Execute extension methods
• Query method: SELECT query returns List of typed objects automatically (connection.Query with Student type)
• QuerySingle method: SELECT returning exactly one row (student by ID) or throws exception
• QueryFirstOrDefault method: SELECT returning first row or null if not found
• QuerySingleOrDefault method: Expects one row or none, throws if multiple rows
• QueryAsync method: Async version of Query (preferred for scalability with await)
• Execute method: INSERT/UPDATE/DELETE query returns affected row count
• ExecuteAsync method: Async version of Execute (returns Task with row count)
• ExecuteScalar method: Returns single scalar value (COUNT, SUM, ID from INSERT)
• QueryMultiple method: Multiple SELECT statements in one query, returns multiple result sets
• Parameter mapping: Anonymous objects map properties to SQL @parameter placeholders automatically
• Anonymous objects for parameters: Auto-maps properties to SQL parameters (type-safe, cleaner than old-style)
• DynamicParameters: Manual parameter builder for complex scenarios, DbString for special handling
• SqlMapper extension methods: Dapper adds methods to IDbConnection (Query, Execute, etc)
• Transactions: Begin transaction and pass to Query/Execute for multi-operation consistency
• Mapping: Automatic property matching (Student.Name matches "Name" column) or custom splitting
• Stored procedures: Execute with CommandType.StoredProcedure and parameter mapping
• Buffering: buffered: false for streaming large results (memory-efficient)
• School Management queries: Get students by class, count by section, bulk insert fee payments, semester updates
• Performance: Dapper faster than EF for complex queries (near ADO.NET speed, less code)
• Common mistakes: Forgetting parameters (SQL injection risk), wrong CommandType for procs, connection not opened
• When NOT to use: Simple CRUD (use EF Core), complex relationships (use EF Include), rapid prototyping

What is Dapper?

Micro-ORM. SQL-first approach. Thin wrapper over ADO.NET with automatic parameter mapping.

ADO.NET  ← Manual SqlCommand setup, SqlDataReader
Dapper   ← Write SQL, automatic mapping to objects
EF Core  ← LINQ, generated SQL

Installation

dotnet add package Dapper

Basic Setup

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

public class StudentRepository
{
    private readonly string _connString;

    public StudentRepository(IConfiguration config)
    {
        _connString = config.GetConnectionString("DefaultConnection");
    }

    private IDbConnection GetConnection()
    {
        return new SqlConnection(_connString);
    }
}

Querying with Query<T>()

public class Student
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string RollNumber { get; set; }
    public string ClassName { get; set; }
    public StudentStatus Status { get; set; }
}

public async Task<List<Student>> GetStudentsAsync()
{
    using (IDbConnection conn = GetConnection())
    {
        string query = "SELECT Id, Name, RollNumber, ClassName, Status FROM Students";
        var students = await conn.QueryAsync<Student>(query);
        return students.ToList();
    }
}

public async Task<Student> GetStudentAsync(int id)
{
    using (IDbConnection conn = GetConnection())
    {
        string query = "SELECT Id, Name, RollNumber, ClassName FROM Students WHERE Id = @Id";
        var student = await conn.QueryFirstOrDefaultAsync<Student>(
            query,
            new { Id = id }
        );
        return student;
    }
}

Parameters (SQL Injection Prevention)

public async Task<List<Student>> FilterByClassAsync(string className, StudentStatus status)
{
    using (IDbConnection conn = GetConnection())
    {
        string query = @"
            SELECT Id, Name, RollNumber, ClassName, Status 
            FROM Students 
            WHERE ClassName = @ClassName AND Status = @Status";

        var students = await conn.QueryAsync<Student>(
            query,
            new { ClassName = className, Status = status }
        );

        return students.ToList();
    }
}

Parameters mapped automatically from anonymous object.

Insert, Update, Delete with Execute()

public async Task CreateStudentAsync(Student student)
{
    using (IDbConnection conn = GetConnection())
    {
        string query = @"
            INSERT INTO Students (Name, RollNumber, ClassName, Status)
            VALUES (@Name, @RollNumber, @ClassName, @Status)";

        var rowsAffected = await conn.ExecuteAsync(
            query,
            new
            {
                student.Name,
                student.RollNumber,
                student.ClassName,
                student.Status
            }
        );

        // rowsAffected = 1 if success
    }
}

public async Task UpdateStudentAsync(int id, Student student)
{
    using (IDbConnection conn = GetConnection())
    {
        string query = @"
            UPDATE Students 
            SET Name = @Name, ClassName = @ClassName, Status = @Status
            WHERE Id = @Id";

        await conn.ExecuteAsync(
            query,
            new
            {
                Id = id,
                student.Name,
                student.ClassName,
                student.Status
            }
        );
    }
}

public async Task DeleteStudentAsync(int id)
{
    using (IDbConnection conn = GetConnection())
    {
        string query = "DELETE FROM Students WHERE Id = @Id";
        await conn.ExecuteAsync(query, new { Id = id });
    }
}

Multiple Result Sets

public class StudentWithExams
{
    public Student Student { get; set; }
    public List<Exam> Exams { get; set; }
}

public async Task<StudentWithExams> GetStudentWithExamsAsync(int studentId)
{
    using (IDbConnection conn = GetConnection())
    {
        string query = @"
            SELECT Id, Name, RollNumber, ClassName, Status FROM Students WHERE Id = @StudentId;
            SELECT e.Id, e.Name, e.ExamDate, e.MaxMarks 
            FROM Exams e
            JOIN ExamResult er ON e.Id = er.ExamId
            WHERE er.StudentId = @StudentId";

        using (var reader = await conn.QueryMultipleAsync(query, new { StudentId = studentId }))
        {
            var student = await reader.ReadFirstOrDefaultAsync<Student>();
            var exams = (await reader.ReadAsync<Exam>()).ToList();

            return new StudentWithExams { Student = student, Exams = exams };
        }
    }
}

Transactions

public async Task TransferStudentAsync(int studentId, string newClass)
{
    using (IDbConnection conn = GetConnection())
    {
        conn.Open();

        using (IDbTransaction transaction = conn.BeginTransaction())
        {
            try
            {
                string updateQuery = "UPDATE Students SET ClassName = @NewClass WHERE Id = @StudentId";
                await conn.ExecuteAsync(
                    updateQuery,
                    new { StudentId = studentId, NewClass = newClass },
                    transaction
                );

                string auditQuery = @"
                    INSERT INTO AuditLog (Action, EntityName, EntityId, ChangedAt)
                    VALUES (@Action, 'Student', @StudentId, @ChangedAt)";
                await conn.ExecuteAsync(
                    auditQuery,
                    new { Action = "ClassTransfer", StudentId = studentId, ChangedAt = DateTime.UtcNow },
                    transaction
                );

                transaction.Commit();
            }
            catch
            {
                transaction.Rollback();
                throw;
            }
        }
    }
}

When to Use Dapper

• Complex SQL queries
• Performance-critical code
• Stored procedures
• Mix of SQL and C# logic

When NOT to use Dapper:

• Simple CRUD (use EF Core)
• Multi-tenant with dynamic filtering (use EF Core)
• Large object graphs with deep relationships (use EF Core)

Key Takeaways

• Dapper = SQL-first, lightweight
• Query<T>() = SELECT and map
• Execute() = INSERT/UPDATE/DELETE
• Parameters = safe from SQL injection
• QueryMultiple() = multiple result sets
• Transactions = ACID operations
• Stored procedures = supported

💡 Dapper Tip

Keep SQL in your code or stored procedures. Use parameters always.

🤖Use AI to Learn Faster

Use ChatGPT, Claude, or Copilot to go deeper on Dapper Micro-ORM. Try these prompts:

• "When should I use Dapper vs EF Core?"
• "How do I execute stored procedures with Dapper?"
• "What's the difference between Query and Execute?"
• "How do I handle transactions?"
• "Quiz me on Dapper"

💡 Tip: After reading this article, paste your own code into AI and ask "What could go wrong here and why?" — fastest way to find edge cases and deepen understanding.