voyager-api/ScrapperAPI/Repositories/SessionRepository.cs

using Dapper;
using ScrapperAPI.Dtos;
using ScrapperAPI.Interfaces;

namespace ScrapperAPI.Repositories;

public sealed class SessionRepository : ISessionRepository
{
    private readonly IDbConnectionFactory _db;

    public SessionRepository(IDbConnectionFactory db) => _db = db;

    public async Task<int> CreateAsync(string name, CancellationToken ct)
    {
        const string sql = """
                           insert into session(name) values (@name)
                           returning id;
                           """;

        using var conn = await _db.CreateOpenConnectionAsync(ct);
        return await conn.ExecuteScalarAsync<int>(new CommandDefinition(sql, new { name }, cancellationToken: ct));
    }

    public async Task<SessionRow?> FindByNameAsync(string name, CancellationToken ct)
    {
        const string sql = """
                           select id, name
                           from session
                           where name = @name
                           limit 1;
                           """;

        using var conn = await _db.CreateOpenConnectionAsync(ct);
        return await conn.QuerySingleOrDefaultAsync<SessionRow>(
            new CommandDefinition(sql, new { name }, cancellationToken: ct));
    }

    public async Task<int?> GetIdByNameAsync(string name, CancellationToken ct)
        => (await FindByNameAsync(name, ct))?.Id;
    
    public async Task<IReadOnlyList<SessionRow>> GetAllAsync(CancellationToken ct)
    {
        const string sql = """
                           select id, name
                           from session
                           order by id;
                           """;

        using var conn = await _db.CreateOpenConnectionAsync(ct);
        return (await conn.QueryAsync<SessionRow>(
            new CommandDefinition(sql, cancellationToken: ct)
        )).ToList();
    }
}