Dapper Queries — QueryAsync, QueryFirstOrDefaultAsync, and QuerySingleAsync

QueryAsync              → returns IEnumerable, zero or more rows
QueryFirstOrDefaultAsync → first row or null/default if none
QuerySingleAsync        → exactly one row; throws if zero or multiple rows
QuerySingleOrDefaultAsync → one row or null; throws if multiple rows

ExecuteAsync               → INSERT/UPDATE/DELETE, returns rows affected
ExecuteScalarAsync      → returns single scalar value (COUNT, MAX, etc.)

// Repository method: get all active prescriptions for a patient
public async Task<IReadOnlyList<PrescriptionReadDto>> GetActivePrescriptionsAsync(
    Guid patientId, CancellationToken ct)
{
    const string sql = @"
        SELECT
            p.id          AS Id,
            p.medication  AS MedicationName,
            p.dose_amount AS DoseAmount,
            p.dose_unit   AS DoseUnit,
            p.prescribed_at AS PrescribedAt,
            c.full_name   AS PrescriberName
        FROM prescriptions p
        INNER JOIN clinicians c ON c.id = p.prescriber_id
        WHERE p.patient_id = @PatientId
          AND p.is_active  = 1
          AND p.is_deleted = 0
        ORDER BY p.prescribed_at DESC";

    using var connection = _connectionFactory.CreateConnection();
    var result = await connection.QueryAsync<PrescriptionReadDto>(
        sql,
        new { PatientId = patientId },
        commandTimeout: 30);

    return result.AsList();
}

public async Task<PatientSummaryDto?> GetPatientByMrnAsync(string mrn, CancellationToken ct)
{
    const string sql = @"
        SELECT
            id          AS Id,
            mrn         AS Mrn,
            first_name  AS FirstName,
            last_name   AS LastName,
            date_of_birth AS DateOfBirth,
            ward_id     AS WardId
        FROM patients
        WHERE mrn = @Mrn
          AND is_deleted = 0";

    using var connection = _connectionFactory.CreateConnection();
    return await connection.QueryFirstOrDefaultAsync<PatientSummaryDto>(
        sql,
        new { Mrn = mrn });
}

// Returns null if not found — caller checks for null.
// Use for: lookups where zero results is a valid scenario.

public async Task<InrResultDto> GetLatestInrAsync(Guid patientId, CancellationToken ct)
{
    const string sql = @"
        SELECT TOP 1
            id         AS Id,
            inr_value  AS InrValue,
            recorded_at AS RecordedAt,
            lab_id     AS LabId
        FROM lab_results
        WHERE patient_id  = @PatientId
          AND result_type = 'INR'
          AND is_deleted  = 0
        ORDER BY recorded_at DESC";

    using var connection = _connectionFactory.CreateConnection();
    return await connection.QuerySingleAsync<InrResultDto>(
        sql,
        new { PatientId = patientId });
    // Throws InvalidOperationException if no rows — use when absence is an error
    // Throws if more than one row — use QueryFirstOrDefaultAsync if duplicates are possible
}

// Infrastructure/Database/SqlConnectionFactory.cs
public sealed class SqlConnectionFactory : ISqlConnectionFactory
{
    private readonly string _connectionString;

    public SqlConnectionFactory(IConfiguration config)
        => _connectionString = config.GetConnectionString("Default")!;

    public IDbConnection CreateConnection()
    {
        var connection = new SqlConnection(_connectionString);
        connection.Open();
        return connection;
    }
}

// Register in DI
builder.Services.AddSingleton<ISqlConnectionFactory, SqlConnectionFactory>();

// Usage in repository: using var ensures connection is returned to the pool
using var connection = _connectionFactory.CreateConnection();
var result = await connection.QueryAsync<PatientDto>(sql, parameters);

// Dapper's async methods accept CancellationToken via CommandDefinition
public async Task<IReadOnlyList<PatientDto>> SearchPatientsAsync(
    string searchTerm, CancellationToken ct)
{
    const string sql = @"
        SELECT id AS Id, mrn AS Mrn, first_name AS FirstName, last_name AS LastName
        FROM patients
        WHERE (first_name LIKE @Search OR last_name LIKE @Search OR mrn LIKE @Search)
          AND is_deleted = 0
        ORDER BY last_name, first_name
        OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY";

    using var connection = _connectionFactory.CreateConnection();

    var command = new CommandDefinition(
        sql,
        parameters:     new { Search = $"%{searchTerm}%" },
        commandTimeout: 15,
        cancellationToken: ct);

    var result = await connection.QueryAsync<PatientDto>(command);
    return result.AsList();
}

public async Task<int> DeactivatePrescriptionAsync(Guid id, CancellationToken ct)
{
    const string sql = @"
        UPDATE prescriptions
        SET is_active  = 0,
            updated_at = GETUTCDATE()
        WHERE id = @Id AND is_deleted = 0";

    using var connection = _connectionFactory.CreateConnection();
    return await connection.ExecuteAsync(
        sql,
        new { Id = id });
    // Returns number of rows affected — check for 0 to detect "not found"
}

// ExecuteScalarAsync for returning a value
public async Task<int> GetActivePatientCountAsync(Guid wardId, CancellationToken ct)
{
    const string sql = @"
        SELECT COUNT(*) FROM patients
        WHERE ward_id = @WardId AND is_deleted = 0";

    using var connection = _connectionFactory.CreateConnection();
    return await connection.ExecuteScalarAsync<int>(
        sql,
        new { WardId = wardId });
}

// Dapper maps SQL column names to C# property names (case-insensitive).
// Use column aliases to match property names when they differ.

// SQL column: "patient_name" → C# property: "PatientName" (works — Dapper handles snake_case)
// SQL column: "prescribed_at" → C# property: "PrescribedAt" (works)
// SQL column: "inr" → C# property: "InrValue" → FAILS without alias

// Fix: alias in SQL
SELECT inr AS InrValue FROM lab_results

// Or use a custom mapper (SqlMapper.SetTypeMap)
SqlMapper.SetTypeMap(typeof(PatientDto), new CustomPropertyTypeMap(
    typeof(PatientDto),
    (type, columnName) => type.GetProperties()
        .FirstOrDefault(p =>
            p.GetCustomAttributes<ColumnAttribute>()
                .Any(a => a.Name == columnName))));