Dapper Sql

Connection Management

Pattern: ISqlConnectionFactory

// ALWAYS inject ISqlConnectionFactory, never create SqlConnection directly
private readonly ISqlConnectionFactory _connectionFactory;

public MyRepository(ISqlConnectionFactory connectionFactory)
{
    _connectionFactory = connectionFactory;
}

// ALWAYS use 'using' with CreateOpenConnectionAsync
using var connection = await _connectionFactory.CreateOpenConnectionAsync();

Connection String Rules

• Retrieved from environment variable MCR_API_SQL_CONNECTION_STRING
• NEVER hardcode connection strings
• NEVER embed credentials — Azure AD / Managed Identity authentication is enforced
• Factory validates authentication method before creating connections

Query Patterns

Single Record Retrieval

var result = await connection.QueryFirstOrDefaultAsync<T>(sql, new { Id = id });

Multiple Record Retrieval

var results = await connection.QueryAsync<T>(sql, parameters);

Single Record with Identity

var id = await connection.QuerySingleAsync<int>(sql, parameters);

Insert / Update / Delete

await connection.ExecuteAsync(sql, new { Field1 = value1, Field2 = value2 });

Stored Procedures

var results = await connection.QueryAsync<T>(
    "EXEC sp_ProcedureName @Param1, @Param2",
    new { Param1 = value1, Param2 = value2 });

Upsert (MERGE)

const string sql = @"
    MERGE INTO TableName AS target
    USING (SELECT @Id AS Id) AS source
    ON target.Id = source.Id
    WHEN MATCHED THEN UPDATE SET ...
    WHEN NOT MATCHED THEN INSERT ...;";
await connection.ExecuteAsync(sql, parameters);

Dynamic Parameters (for optional/nullable fields)

var parameters = new DynamicParameters();
parameters.Add("@RequiredParam", value);
if (optionalValue.HasValue)
    parameters.Add("@OptionalParam", optionalValue.Value);
else
    parameters.Add("@OptionalParam", DBNull.Value);

Transaction Management

using var connection = await _connectionFactory.CreateOpenConnectionAsync();
using var transaction = connection.BeginTransaction();
try
{
    await connection.ExecuteAsync(sql1, param1, transaction);
    await connection.ExecuteAsync(sql2, param2, transaction);
    transaction.Commit();
}
catch
{
    transaction.Rollback();
    throw;
}

When to Use Transactions

• Multi-statement operations that must be atomic
• Audit logging paired with data changes
• Any operation where partial completion would leave inconsistent state

Transaction Rules

• ALWAYS pass transaction parameter to Dapper methods inside a transaction scope
• ALWAYS wrap in try/catch with explicit Rollback
• NEVER nest transactions

Repository Pattern

Interface Location

• Interfaces defined in 3-Domain/MotorcycleRAG.Contracts/Repositories/ (or Interfaces/)
• Implementations in 4-Persistence/MotorcycleRAG.Persistence/Sql/Repositories/

DI Registration

• Registered in ServiceCollectionExtensions.AddSqlPersistenceServices()
• Connection factory: Singleton
• Repositories: Scoped (default) or as specified

Error Handling

• Wrap database exceptions with InvalidOperationException and descriptive messages
• Use structured logging with operation context: _logger.LogError(ex, "Failed to {Operation} for {Entity} {Id}", ...)

MUST NOT

• Use Entity Framework Core (DbContext, DbSet, LINQ queries, SaveChangesAsync)
• Create SqlConnection directly — always use ISqlConnectionFactory
• Use string concatenation for SQL queries — always parameterized
• Store connection strings in appsettings, code, or any file
• Use SELECT * — always list explicit columns
• Catch and swallow database exceptions silently

MUST DO

• Use ISqlConnectionFactory for all connection creation
• Use parameterized queries (anonymous objects or DynamicParameters)
• Dispose connections with using statements
• Use transactions for multi-statement atomic operations
• Follow existing repository pattern (interface in Contracts, implementation in Persistence)
• Register new repositories in ServiceCollectionExtensions.AddSqlPersistenceServices()
• Include structured logging for all database operations
• Handle NULL values explicitly with ?? DBNull.Value pattern