Optimization & Best Practices

Level: Intermediate

ℹ️ Where This Fits

Dapper is fast by default, but understanding caching, repository patterns, and common pitfalls ensures optimal performance and clean code structure.

ℹ️ What You'll Learn

• Dapper caching: Query plans cached automatically
• Repository pattern: Abstract data access
• Connection pooling: Handled automatically
• Buffering: Default buffered: true, use false for streaming
• Chunking: Process large datasets in batches
• School Management patterns: StudentRepository, ExamRepository patterns
• Common mistakes: Creating new connections per query, not using parameters, no error handling

Repository Pattern - Clean Data Access

public interface IStudentRepository
{
    Task<List<Student>> GetByClassAsync(string className);
    Task<Student> GetByIdAsync(int id);
    Task<int> CreateAsync(Student student);
    Task UpdateAsync(Student student);
    Task DeleteAsync(int id);
}

public class StudentRepository : IStudentRepository
{
    private readonly string _connString;

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

    private IDbConnection GetConnection() => new SqlConnection(_connString);

    public async Task<List<Student>> GetByClassAsync(string className)
    {
        using (var conn = GetConnection())
        {
            return (await conn.QueryAsync<Student>(
                "SELECT Id, Name, ClassName FROM Students WHERE ClassName = @className",
                new { className = className }
            )).ToList();
        }
    }

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

    public async Task<int> CreateAsync(Student student)
    {
        using (var conn = GetConnection())
        {
            return await conn.QuerySingleAsync<int>(
                @"INSERT INTO Students (Name, ClassName, Status) 
                  VALUES (@name, @className, @status);
                  SELECT SCOPE_IDENTITY();",
                student
            );
        }
    }

    public async Task UpdateAsync(Student student)
    {
        using (var conn = GetConnection())
        {
            await conn.ExecuteAsync(
                "UPDATE Students SET Name = @name, ClassName = @className WHERE Id = @id",
                student
            );
        }
    }

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

Register Repository in DI

// Program.cs
builder.Services.AddScoped<IStudentRepository, StudentRepository>();

Use in Controller

[ApiController]
[Route("api/students")]
public class StudentsController : ControllerBase
{
    private readonly IStudentRepository _repository;

    public StudentsController(IStudentRepository repository)
    {
        _repository = repository;
    }

    [HttpGet]
    public async Task<ActionResult<List<Student>>> GetByClass(string className)
    {
        var students = await _repository.GetByClassAsync(className);
        return Ok(students);
    }

    [HttpGet("{id}")]
    public async Task<ActionResult<Student>> GetStudent(int id)
    {
        var student = await _repository.GetByIdAsync(id);
        if (student == null)
            return NotFound();
        return Ok(student);
    }
}

Batch Processing for Large Datasets

public async Task BatchImportStudentsAsync(List<Student> students)
{
    const int batchSize = 1000;
    
    for (int i = 0; i < students.Count; i += batchSize)
    {
        var batch = students.Skip(i).Take(batchSize).ToList();
        
        using (var conn = GetConnection())
        {
            await conn.OpenAsync();
            using (var trans = conn.BeginTransaction())
            {
                try
                {
                    foreach (var student in batch)
                    {
                        await conn.ExecuteAsync(
                            @"INSERT INTO Students (Name, ClassName, Status)
                              VALUES (@name, @className, @status)",
                            student,
                            transaction: trans
                        );
                    }
                    trans.Commit();
                }
                catch
                {
                    trans.Rollback();
                    throw;
                }
            }
        }
    }
}

Connection Pool Tuning

Dapper uses automatic connection pooling. Configure in connection string:

"Server=localhost;Database=SmsDb;Min Pool Size=5;Max Pool Size=100;Connection Lifetime=3600;"

Error Handling Pattern

public async Task<bool> UpdateStudentAsync(Student student)
{
    try
    {
        using (var conn = GetConnection())
        {
            var result = await conn.ExecuteAsync(
                "UPDATE Students SET Name = @name WHERE Id = @id",
                student
            );
            return result > 0;
        }
    }
    catch (SqlException ex)
    {
        // Log error
        _logger.LogError(ex, "Failed to update student {StudentId}", student.Id);
        throw;
    }
}

Best Practices Summary

✓ Use repository pattern for abstraction ✓ Always use async methods ✓ Use parameters (never concatenate) ✓ Handle errors gracefully ✓ Batch large operations ✓ Use connection pooling (automatic) ✓ Dispose connections properly (using statement)

💡 Dapper Advantages

• Fast: Minimal overhead, direct SQL execution
• Simple: No complex ORM configuration
• Flexible: Write exact SQL you need
• Testable: Easy to mock repositories

🤖Use AI to Learn Faster

Use ChatGPT, Claude, or Copilot to go deeper on Dapper Optimization & Best Practices. Try these prompts:

• "What's the repository pattern and why use it?"
• "How do you handle large dataset imports efficiently?"
• "What error handling patterns work well with Dapper?"
• "How does Dapper's performance compare to EF Core?"

💡 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.

Complete Learning Path

You've learned:

• ADO.NET: Manual, low-level control
• EF Core: High-level ORM with automatic SQL generation
• Dapper: Lightweight SQL-first approach

Choose based on needs:

• ADO.NET: Complex queries, stored procedures, raw performance
• EF Core: Rapid development, complex relationships, migrations
• Dapper: Balance of control and simplicity, performance-critical code