1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84using Microsoft.Data.SqlClient;

namespace VanDerHeijden.Logging.Sql;

/// <summary>
/// Writes log entries to a SQL Server table in bulk using SqlBulkCopy.
/// Expected table schema:
///   CREATE TABLE Logs (
///     Id        BIGINT IDENTITY PRIMARY KEY,
///     Timestamp DATETIME2       NOT NULL,
///     Level     NVARCHAR(20)    NOT NULL,
///     Category  NVARCHAR(256)   NOT NULL,
///     Message   NVARCHAR(MAX)   NOT NULL,
///     Exception NVARCHAR(MAX)   NULL,
///     Path      NVARCHAR(1024)  NULL,
///     Method    NVARCHAR(10)    NULL,
///     ClientIp  NVARCHAR(45)    NULL,
///     Referer   NVARCHAR(2048)  NULL,
///     UserAgent NVARCHAR(512)   NULL
///   );
/// </summary>
public sealed class SqlLogWriter(string connectionString, string tableName = "Logs") : IBatchedLogWriter<SqlLogEntry>
{
	/// <summary>
	/// Bulk-inserts all entries into the configured SQL Server table using <see cref="SqlBulkCopy"/>.
	/// </summary>
	/// <param name="entries">The log entries to insert.</param>
	/// <param name="ct">A token that can cancel the operation.</param>
	public async Task WriteBatchAsync(List<SqlLogEntry> entries, CancellationToken ct)
	{
		await using var connection = new SqlConnection(connectionString);
		await connection.OpenAsync(ct);

		using var bulkCopy = new SqlBulkCopy(connection)
		{
			DestinationTableName = tableName,
			BulkCopyTimeout = 30
		};

		bulkCopy.ColumnMappings.Add(nameof(SqlLogEntry.Timestamp), "Timestamp");
		bulkCopy.ColumnMappings.Add(nameof(SqlLogEntry.Level),     "Level");
		bulkCopy.ColumnMappings.Add(nameof(SqlLogEntry.Category),  "Category");
		bulkCopy.ColumnMappings.Add(nameof(SqlLogEntry.Message),   "Message");
		bulkCopy.ColumnMappings.Add(nameof(SqlLogEntry.Exception), "Exception");
		bulkCopy.ColumnMappings.Add(nameof(SqlLogEntry.Path),      "Path");
		bulkCopy.ColumnMappings.Add(nameof(SqlLogEntry.Method),    "Method");
		bulkCopy.ColumnMappings.Add(nameof(SqlLogEntry.ClientIp),  "ClientIp");
		bulkCopy.ColumnMappings.Add(nameof(SqlLogEntry.Referer),   "Referer");
		bulkCopy.ColumnMappings.Add(nameof(SqlLogEntry.UserAgent), "UserAgent");

		var table = ToDataTable(entries);
		await bulkCopy.WriteToServerAsync(table, ct);
	}

	/// <inheritdoc/>
	public ValueTask DisposeAsync() => ValueTask.CompletedTask;

	private static System.Data.DataTable ToDataTable(List<SqlLogEntry> entries)
	{
		var table = new System.Data.DataTable();
		table.Columns.Add("Timestamp", typeof(DateTime));
		table.Columns.Add("Level",     typeof(string));
		table.Columns.Add("Category",  typeof(string));
		table.Columns.Add("Message",   typeof(string));
		table.Columns.Add("Exception", typeof(string));
		table.Columns.Add("Path",      typeof(string));
		table.Columns.Add("Method",    typeof(string));
		table.Columns.Add("ClientIp",  typeof(string));
		table.Columns.Add("Referer",   typeof(string));
		table.Columns.Add("UserAgent", typeof(string));

		foreach (var e in entries)
			table.Rows.Add(
				e.Timestamp, e.Level, e.Category, e.Message,
				(object?)e.Exception ?? DBNull.Value,
				(object?)e.Path      ?? DBNull.Value,
				(object?)e.Method    ?? DBNull.Value,
				(object?)e.ClientIp  ?? DBNull.Value,
				(object?)e.Referer   ?? DBNull.Value,
				(object?)e.UserAgent ?? DBNull.Value);

		return table;
	}
}