6. Writing a custom logger provider (file and database)

public sealed class DbLoggerProvider : ILoggerProvider
{
    private readonly Channel<LogRow> _queue = Channel.CreateBounded<LogRow>(new BoundedChannelOptions(10_000) { FullMode = BoundedChannelFullMode.DropOldest });
    private readonly Task _writer;
    private readonly string _connectionString;
    private readonly LogLevel _minLevel;

    public DbLoggerProvider(string connectionString, LogLevel minLevel)
    {
        _connectionString = connectionString;
        _minLevel = minLevel;
        _writer = Task.Run(WriteLoopAsync);
    }

    public ILogger CreateLogger(string categoryName) => new DbLogger(categoryName, this);

    internal bool IsEnabled(LogLevel level) => level >= _minLevel && level != LogLevel.None;
    internal void Enqueue(LogRow row) => _queue.Writer.TryWrite(row);

    private async Task WriteLoopAsync()
    {
        var batch = new List<LogRow>(100);
        await foreach (var row in _queue.Reader.ReadAllAsync())
        {
            batch.Add(row);
            if (batch.Count < 100 && _queue.Reader.Count > 0) continue;   // gather a batch
            try
            {
                await using var conn = new SqlConnection(_connectionString);
                await conn.ExecuteAsync("INSERT INTO dbo.AppLogs (Utc, Level, Category, Message, Exception, TraceId) " +
                                        "VALUES (@Utc, @Level, @Category, @Message, @Exception, @TraceId)", batch);   // Dapper
            }
            catch { /* never let logging crash the app; optionally fall back to a file */ }
            batch.Clear();
        }
    }

    public void Dispose() { _queue.Writer.TryComplete(); _writer.Wait(TimeSpan.FromSeconds(5)); }
}

internal sealed class DbLogger(string category, DbLoggerProvider provider) : ILogger
{
    public IDisposable? BeginScope<TState>(TState state) where TState : notnull => null;
    public bool IsEnabled(LogLevel logLevel) => provider.IsEnabled(logLevel);
    public void Log<TState>(LogLevel level, EventId id, TState state, Exception? ex, Func<TState, Exception?, string> formatter)
    {
        if (!IsEnabled(level)) return;
        provider.Enqueue(new LogRow(DateTime.UtcNow, level.ToString(), category, formatter(state, ex), ex?.ToString(), Activity.Current?.TraceId.ToString()));
    }
}

public sealed record LogRow(DateTime Utc, string Level, string Category, string Message, string? Exception, string? TraceId);

// Registration
builder.Logging.AddProvider(new DbLoggerProvider(builder.Configuration.GetConnectionString("Logs")!, LogLevel.Warning));

CREATE TABLE dbo.AppLogs (
  Id BIGINT IDENTITY PRIMARY KEY, Utc DATETIME2 NOT NULL, Level VARCHAR(20) NOT NULL, Category NVARCHAR(256) NOT NULL,
  Message NVARCHAR(MAX) NOT NULL, Exception NVARCHAR(MAX) NULL, TraceId VARCHAR(64) NULL);
CREATE INDEX IX_AppLogs_Utc ON dbo.AppLogs(Utc) INCLUDE (Level);