Dapper / SQL Server persistence patterns (repositories, parameterized SQL, connection factory).
Trigger: This skill MUST be loaded whenever working on:
4-Persistence/MotorcycleRAG.Persistence/Sql/.Dapper, SqlConnection, or ISqlConnectionFactory.Do NOT use EF Core patterns. No DbContext, no LINQ-to-SQL, no DbSet<T>, no migrations via EF, no Include(), no SaveChangesAsync(). All data access uses Dapper with raw parameterized SQL.
4-Persistence/MotorcycleRAG.Persistence/Sql/
├── SqlConnectionFactory.cs # ISqlConnectionFactory implementation
├── ServiceCollectionExtensions.cs # DI registration
└── Repositories/
├── UserRepository.cs
├── AuditRepository.cs
├── UsageRepository.cs
├── PlanRepository.cs
├── WebSourceRepository.cs
├── WebScrapeRunRepository.cs
├── ToolConfigurationRepository.cs
└── ToolConfigurationAuditRepository.cs
// 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();
MCR_API_SQL_CONNECTION_STRINGvar result = await connection.QueryFirstOrDefaultAsync<T>(sql, new { Id = id });
var results = await connection.QueryAsync<T>(sql, parameters);
var id = await connection.QuerySingleAsync<int>(sql, parameters);
await connection.ExecuteAsync(sql, new { Field1 = value1, Field2 = value2 });
var results = await connection.QueryAsync<T>(
"EXEC sp_ProcedureName @Param1, @Param2",
new { Param1 = value1, Param2 = value2 });
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);
var parameters = new DynamicParameters();
parameters.Add("@RequiredParam", value);
if (optionalValue.HasValue)
parameters.Add("@OptionalParam", optionalValue.Value);
else
parameters.Add("@OptionalParam", DBNull.Value);
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;
}
transaction parameter to Dapper methods inside a transaction scope3-Domain/MotorcycleRAG.Contracts/Repositories/ (or Interfaces/)4-Persistence/MotorcycleRAG.Persistence/Sql/Repositories/ServiceCollectionExtensions.AddSqlPersistenceServices()InvalidOperationException and descriptive messages_logger.LogError(ex, "Failed to {Operation} for {Entity} {Id}", ...)DbContext, DbSet, LINQ queries, SaveChangesAsync)SqlConnection directly — always use ISqlConnectionFactorySELECT * — always list explicit columnsISqlConnectionFactory for all connection creationDynamicParameters)using statementsServiceCollectionExtensions.AddSqlPersistenceServices()NULL values explicitly with ?? DBNull.Value pattern