Replace Manual ADO.NET Mapping with Dapper in C#

by

Summary

The developer is struggling with manual data mapping and infrastructure coupling. They are currently using raw ADO.NET to manually iterate through a MySqlDataReader, mapping every single column to a class constructor by index or string name. While this works for a single query, it is highly unscalable, error-prone, and creates a massive amount of boilerplate code that must be rewritten every time the database schema changes.

Root Cause

The fundamental issue is the lack of an Object-Relational Mapper (ORM) or a Micro-ORM.

  • Manual Mapping Fatigue: The developer is manually bridging the gap between relational rows and C# objects.
  • Tight Coupling: The data access logic is hardcoded with specific column names and connection strings, making it difficult to move from a Console app to a WPF application without duplicating logic.
  • Schema Fragility: If a single column name changes in the SQL database (e.g., patient_name becomes full_name), the entire while(reader.Read()) loop breaks.

Why This Happens in Real Systems

In production environments, this pattern leads to Technical Debt Accrual. As a system grows from 5 tables to 500 tables:

  • Maintenance Overhead: Engineers spend more time writing “glue code” (mapping) than building actual business features.
  • Bug Density: Manual index-based reading (reader.GetInt64(0)) is a primary source of runtime exceptions when schema migrations occur.
  • Performance Bottlenecks: While raw ADO.NET is technically the fastest, the human cost of maintaining it outweighs the millisecond gains in 99% of enterprise applications.

Real-World Impact

  • Increased Time-to-Market: Developing new features takes longer because of repetitive boilerplate.
  • Fragile Deployments: A simple SQL migration can cause a cascade of failures across the C# codebase if mapping is handled manually.
  • Difficult Testing: Because the logic is tightly bound to a MySqlConnection, it is nearly impossible to perform Unit Testing without a live database running.

Example or Code

To solve this, we use Dapper, a high-performance Micro-ORM that automates the mapping process.

using Dapper;
using MySqlConnector;
using System.Collections.Generic;
using System.Linq;

public class Sql
{
    private readonly string _connectionString = "Server=localhost;Port=3306;Database=clinicqueue;User ID=root;Password=;";

    public IEnumerable GetAllAppointments()
    {
        using (var connection = new MySqlConnection(_connectionString))
        {
            const string sql = @"
                SELECT 
                    a.id, 
                    a.patient_name AS PatientName, 
                    d.name AS DoctorName, 
                    d.specialty AS DoctorSpecialty, 
                    a.start_time AS StartTime, 
                    a.status 
                FROM appointments a 
                JOIN doctors d ON a.doctor_id = d.id";

            return connection.Query(sql).ToList();
        }
    }
}

How Senior Engineers Fix It

A senior engineer moves away from “writing code that works” to “designing systems that evolve.”

  • Implement a Micro-ORM: Use Dapper for performance-critical paths or Entity Framework Core for complex domain modeling.
  • Abstraction via Repository Pattern: Instead of calling Sql.GetAllAppointments() directly in the WPF UI, they create an IAppointmentRepository. This allows the UI to remain agnostic of whether the data comes from MySQL, an API, or a Mock for testing.
  • Dependency Injection (DI): They inject the connection string and the database context, rather than hardcoding it inside the method.
  • Separation of Concerns: They ensure the Data Access Layer (DAL) is a separate project/namespace from the Presentation Layer (WPF).

Why Juniors Miss It

  • Focus on “The Happy Path”: Juniors often focus on making the data appear on the screen as quickly as possible, ignoring the long-term cost of the implementation.
  • Lack of Pattern Awareness: They see code as a series of instructions to be executed rather than a structure to be maintained.
  • Underestimating Refactoring: They assume the database schema is “permanent” and do not account for the inevitable changes that happen in a professional lifecycle.

Leave a Comment