Dapper — Micro ORM & Raw SQL

Dapper কী?

StackOverflow-এর তৈরি micro-ORM — raw SQL লিখে object-এ map করে। EF Core-এর চেয়ে দ্রুত কিন্তু feature কম।

Setup

dotnet add package Dapper
dotnet add package Microsoft.Data.SqlClient

Basic Query

using Dapper;
using Microsoft.Data.SqlClient;

public class ProductRepository {
    private readonly string _conn;
    public ProductRepository(IConfiguration cfg) =>
        _conn = cfg.GetConnectionString("Default");

    // SELECT all
    public async Task<IEnumerable<Product>> GetAllAsync() {
        using var db = new SqlConnection(_conn);
        return await db.QueryAsync<Product>("SELECT * FROM Products");
    }

    // SELECT single
    public async Task<Product?> GetByIdAsync(int id) {
        using var db = new SqlConnection(_conn);
        return await db.QuerySingleOrDefaultAsync<Product>(
            "SELECT * FROM Products WHERE Id = @Id",
            new { Id = id });
    }

    // SELECT with parameters
    public async Task<IEnumerable<Product>> SearchAsync(string q, decimal min) {
        using var db = new SqlConnection(_conn);
        return await db.QueryAsync<Product>(@"
            SELECT * FROM Products
            WHERE Name LIKE @Pattern AND Price >= @Min",
            new { Pattern = $"%{q}%", Min = min });
    }
}

INSERT, UPDATE, DELETE

// INSERT
public async Task<int> CreateAsync(Product p) {
    using var db = new SqlConnection(_conn);
    var sql = @"
        INSERT INTO Products (Name, Price, Stock)
        VALUES (@Name, @Price, @Stock);
        SELECT CAST(SCOPE_IDENTITY() AS INT);";
    return await db.ExecuteScalarAsync<int>(sql, p);
}

// UPDATE
public async Task<int> UpdateAsync(Product p) {
    using var db = new SqlConnection(_conn);
    return await db.ExecuteAsync(
        "UPDATE Products SET Name=@Name, Price=@Price WHERE Id=@Id", p);
}

// DELETE
public async Task<int> DeleteAsync(int id) {
    using var db = new SqlConnection(_conn);
    return await db.ExecuteAsync(
        "DELETE FROM Products WHERE Id=@Id", new { Id = id });
}

Multi-mapping (JOIN)

public async Task<IEnumerable<Product>> GetWithCategoryAsync() {
    using var db = new SqlConnection(_conn);
    var sql = @"
        SELECT p.*, c.*
        FROM Products p
        INNER JOIN Categories c ON p.CategoryId = c.Id";

    return await db.QueryAsync<Product, Category, Product>(
        sql,
        (p, c) => { p.Category = c; return p; },
        splitOn: "Id");
}

Multiple Result Sets

using var db = new SqlConnection(_conn);
using var multi = await db.QueryMultipleAsync(@"
    SELECT * FROM Products WHERE Id = @Id;
    SELECT * FROM Reviews WHERE ProductId = @Id;",
    new { Id = id });

var product = await multi.ReadSingleAsync<Product>();
var reviews = (await multi.ReadAsync<Review>()).ToList();

Stored Procedure

using var db = new SqlConnection(_conn);
var products = await db.QueryAsync<Product>(
    "sp_GetProductsByCategory",
    new { CategoryId = 5 },
    commandType: CommandType.StoredProcedure);

Transaction

using var db = new SqlConnection(_conn);
await db.OpenAsync();
using var tx = db.BeginTransaction();
try {
    await db.ExecuteAsync(sql1, args, tx);
    await db.ExecuteAsync(sql2, args, tx);
    tx.Commit();
} catch {
    tx.Rollback();
    throw;
}

Dapper vs EF Core

	Dapper	EF Core
Speed	Faster ⚡	Slower
SQL	Raw, full control	LINQ → SQL
Migration	Manual	Built-in ⭐
Change tracking	❌	✅
Use case	Read-heavy, reports	CRUD, complex models