ASP.NET Core Custom Logger — Save Logs to Database (SQL)

#aspnet #csharp #database #programming

ASP.NET Core already gives you solid logging out of the box, and if you want richer sinks and structured logs, Serilog is a common choice.

But sometimes you cannot use a ready made sink, because the company already has a legacy logging table with a fixed schema. In that case, you build a custom database logger that writes into that table.

This tutorial includes:

Database table
Logger options
Custom logger
Logger provider
Background queue (Channel)
Dapper SQL insert
Registration in Program.cs
Usage example

1️⃣ Create the SQL table

CREATE TABLE dbo.AppLogs
(
    LogId     BIGINT IDENTITY PRIMARY KEY,
    Ts        DATETIME2(3) NOT NULL,
    Level     NVARCHAR(20) NOT NULL,
    Category  NVARCHAR(200) NOT NULL,
    TraceId   NVARCHAR(64) NULL,
    Message   NVARCHAR(MAX) NOT NULL,
    Exception NVARCHAR(MAX) NULL,
    Scopes    NVARCHAR(MAX) NULL
);

CREATE INDEX IX_AppLogs_Ts    ON dbo.AppLogs(Ts);
CREATE INDEX IX_AppLogs_Level ON dbo.AppLogs(Level);

2️⃣ Add NuGet packages

bash dotnet add package Dapper dotnet add package Microsoft.Data.SqlClient

3️⃣ Logger options

`csharp
public sealed class DbLogOptions
{
public string ConnectionString { get; set; } = string.Empty;
public LogLevel MinLevel { get; set; } = LogLevel.Information;

// Max number of log entries waiting in memory
public int MaxQueue { get; set; } = 5000;

}
`

4️⃣ Log entry model

csharp public sealed class DbLogEntry { public DateTime Ts { get; set; } public string Level { get; set; } = ""; public string Category { get; set; } = ""; public string? TraceId { get; set; } public string Message { get; set; } = ""; public string? Exception { get; set; } public string? Scopes { get; set; } }

5️⃣ Custom database logger

`csharp
using System.Diagnostics;
using System.Text.Json;
using System.Threading.Channels;
using Microsoft.Extensions.Logging;

public sealed class DbLogger : ILogger
{
private readonly string _category;
private readonly DbLogOptions _options;
private readonly Channel _channel;
private readonly IExternalScopeProvider? _scopeProvider;

public DbLogger(
    string category,
    DbLogOptions options,
    Channel<DbLogEntry> channel,
    IExternalScopeProvider? scopeProvider)
{
    _category = category;
    _options = options;
    _channel = channel;
    _scopeProvider = scopeProvider;
}

public IDisposable BeginScope<TState>(TState state) =>
    _scopeProvider?.Push(state) ?? NullScope.Instance;

public bool IsEnabled(LogLevel logLevel) => logLevel >= _options.MinLevel;

public void Log<TState>(
    LogLevel logLevel,
    EventId eventId,
    TState state,
    Exception? exception,
    Func<TState, Exception?, string> formatter)
{
    if (!IsEnabled(logLevel)) return;
    if (formatter is null) return;

    var traceId = Activity.Current?.TraceId.ToString();

    string? scopesJson = null;
    if (_scopeProvider is not null)
    {
        var scopes = new List<object?>();
        _scopeProvider.ForEachScope((s, list) => list.Add(s), scopes);

        if (scopes.Count > 0)
            scopesJson = JsonSerializer.Serialize(scopes);
    }

    var entry = new DbLogEntry
    {
        Ts = DateTime.UtcNow,
        Level = logLevel.ToString(),
        Category = _category,
        TraceId = traceId,
        Message = formatter(state, exception),
        Exception = exception?.ToString(),
        Scopes = scopesJson
    };

    // If queue is full, this returns false and the log is dropped
    _ = _channel.Writer.TryWrite(entry);
}

private sealed class NullScope : IDisposable
{
    public static readonly NullScope Instance = new();
    public void Dispose() { }
}

}
`

6️⃣ Logger provider (writes to SQL using Dapper)

This provider uses a bounded channel and a single background reader so request threads never block on SQL.

`csharp
using System.Threading.Channels;
using Dapper;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Options;

public sealed class DbLoggerProvider : ILoggerProvider, ISupportExternalScope
{
private readonly DbLogOptions _options;
private readonly Channel _channel;
private readonly CancellationTokenSource _cts = new();
private IExternalScopeProvider? _scopeProvider;

public DbLoggerProvider(IOptions<DbLogOptions> options)
{
    _options = options.Value;

    var channelOptions = new BoundedChannelOptions(_options.MaxQueue)
    {
        SingleReader = true,
        SingleWriter = false,
        FullMode = BoundedChannelFullMode.DropOldest
    };

    _channel = Channel.CreateBounded<DbLogEntry>(channelOptions);

    _ = Task.Run(ProcessQueueAsync);
}

public ILogger CreateLogger(string categoryName) =>
    new DbLogger(categoryName, _options, _channel, _scopeProvider);

public void SetScopeProvider(IExternalScopeProvider scopeProvider) =>
    _scopeProvider = scopeProvider;

public void Dispose()
{
    _cts.Cancel();
    _channel.Writer.TryComplete();
    _cts.Dispose();
}

private async Task ProcessQueueAsync()
{
    const string sql = @"

INSERT INTO dbo.AppLogs (Ts, Level, Category, TraceId, Message, Exception, Scopes)
VALUES (@ts, @Level, @category, @TraceId, @message, @exception, @Scopes);";

    try
    {
        while (await _channel.Reader.WaitToReadAsync(_cts.Token))
        {
            while (_channel.Reader.TryRead(out var entry))
            {
                await using var conn = new SqlConnection(_options.ConnectionString);
                await conn.ExecuteAsync(sql, entry);
            }
        }
    }
    catch (OperationCanceledException)
    {
        // graceful shutdown
    }
}

}
`

7️⃣ Add registration extension

`csharp
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;

public static class DbLoggerExtensions
{
public static ILoggingBuilder AddDbLogger(
this ILoggingBuilder builder,
Action configure)
{
builder.Services.Configure(configure);
builder.Services.AddSingleton();
return builder;
}
}
`

8️⃣ Register in Program.cs

`csharp
var builder = WebApplication.CreateBuilder(args);

builder.Logging.ClearProviders();
builder.Logging.AddConsole();

builder.Logging.AddDbLogger(options =>
{
options.ConnectionString = builder.Configuration.GetConnectionString("DefaultConnection")!;
options.MinLevel = LogLevel.Information;
options.MaxQueue = 5000;
});

var app = builder.Build();

app.MapGet("/test", (ILogger logger) =>
{
logger.LogInformation("Hello from DB Logger");
logger.LogError(new Exception("Test"), "Something failed");
return Results.Ok("OK");
});

app.Run();
`

Why we used Channels in the DB logger

Your logger needs to accept logs from many producers (web requests, background jobs, services) and write them to the DB via one efficient writer without blocking request threads or losing control when traffic spikes. Channels are a great fit: