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
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
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
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<DbLogEntry> _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.
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<DbLogEntry> _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
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
public static class DbLoggerExtensions
{
public static ILoggingBuilder AddDbLogger(
this ILoggingBuilder builder,
Action<DbLogOptions> configure)
{
builder.Services.Configure(configure);
builder.Services.AddSingleton<ILoggerProvider, DbLoggerProvider>();
return builder;
}
}
8️⃣ Register in Program.cs
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<Program> 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:
- Non blocking producers
- Backpressure with bounded capacity
- Single reader, many writers
- Async, allocation friendly
- Graceful shutdown
Source code:
https://github.com/stevsharp/DbLoggerSample