7. Repository, unit of work and connection management in ASP.NET Core

public interface IDbConnectionFactory { DbConnection Create(); }
public sealed class SqlConnectionFactory(IConfiguration cfg) : IDbConnectionFactory
{
    public DbConnection Create() => new SqlConnection(cfg.GetConnectionString("Shop"));
}

public interface IProductRepository
{
    Task<IReadOnlyList<ProductDto>> SearchAsync(string? q, int page, int size, CancellationToken ct);
    Task<ProductDto?> GetAsync(int id, CancellationToken ct);
    Task<int> CreateAsync(CreateProduct cmd, CancellationToken ct);
}

public sealed class ProductRepository(IDbConnectionFactory db) : IProductRepository
{
    public async Task<IReadOnlyList<ProductDto>> SearchAsync(string? q, int page, int size, CancellationToken ct)
    {
        const string sql = @"SELECT Id, Name, Category, Price FROM dbo.Products
                             WHERE (@q IS NULL OR Name LIKE @q + '%')
                             ORDER BY Name OFFSET @skip ROWS FETCH NEXT @size ROWS ONLY";
        await using var conn = db.Create();
        var rows = await conn.QueryAsync<ProductDto>(new CommandDefinition(sql, new { q, skip = (page - 1) * size, size }, cancellationToken: ct));
        return rows.AsList();
    }
    public async Task<ProductDto?> GetAsync(int id, CancellationToken ct)
    {
        await using var conn = db.Create();
        return await conn.QuerySingleOrDefaultAsync<ProductDto>(new CommandDefinition(
            "SELECT Id, Name, Category, Price FROM dbo.Products WHERE Id = @id", new { id }, cancellationToken: ct));
    }
    public async Task<int> CreateAsync(CreateProduct c, CancellationToken ct)
    {
        await using var conn = db.Create();
        return await conn.QuerySingleAsync<int>(new CommandDefinition(
            "INSERT INTO dbo.Products (Name, Category, Price) OUTPUT INSERTED.Id VALUES (@Name, @Category, @Price)", c, cancellationToken: ct));
    }
}

// Program.cs
builder.Services.AddSingleton<IDbConnectionFactory, SqlConnectionFactory>();
builder.Services.AddScoped<IProductRepository, ProductRepository>();