dotnet-api-boilerplate
A Dotnet 9.0 WebApi template project. MediatR, Swagger, Mapper, Serilog and more implemented.
- 596
- C#
- MIT
- 19 mo ago
A high-performance Serilog sink that writes log events to Microsoft SQL Server using bulk insert operations.
Repo loresoft/serilog-sinks-sqlserver, read from GitHub 7 h ago. loresoft has not joined Take My Repo.
loresoft/serilog-sinks-sqlserver is a public repo on GitHub by loresoft. A high-performance Serilog sink that writes log events to Microsoft SQL Server using bulk insert operations. It declares the MIT license on GitHub. It is written mainly in C#, has 9 stars and last changed on 2026-11-18. Nobody has reviewed it on Take My Repo yet, which says nothing about its quality. Read from GitHub on 2026-11-22.
In the maintainer's words. Shown from GitHub; Take My Repo does not keep a copy.
A high-performance Serilog sink that writes log events to Microsoft SQL Server using bulk insert operations.
SqlBulkCopy for efficient bulk insert operationsThis sink is designed to be a high-performance, lightweight alternative to Serilog.Sinks.MSSqlServer with significant improvements in speed and memory efficiency.
Based on Serilog.Sinks.SqlServer.Benchmark tests (100 log events per batch):
| Method | Mean | Rank | Gen0 | Gen1 | Allocated |
|---|---|---|---|---|---|
| SqlServerSink | 2.082 ms | 1 | 7.8125 | - | 438.31 KB |
| MSSqlServerSink | 2.666 ms | 2 | 117.1875 | 27.3438 | 5773.93 KB |
Key Performance Benefits:
ArrayBufferWriter, Span<T>, and modern .NET APIsJsonWriter using Utf8JsonWriter for zero-copy serializationSqlBulkCopy with fewer intermediate transformationsIDataReader implementation instead of DataTable, eliminating the overhead of DataTable's internal structuresSerilog, Microsoft.Data.SqlClient, and polyfills)Install the sink via NuGet:
dotnet add package Serilog.Sinks.SqlServer
Or via Package Manager Console:
Install-Package Serilog.Sinks.SqlServerCreate a table in your SQL Server database to store log events:
CREATE TABLE [dbo].[LogEvent]
(
[Id] BIGINT IDENTITY(1,1) NOT NULL,
[Timestamp] DATETIMEOFFSET NOT NULL,
[Level] NVARCHAR(50) NOT NULL,
[Message] NVARCHAR(MAX) NULL,
[TraceId] NVARCHAR(100) NULL,
[SpanId] NVARCHAR(100) NULL,
[Exception] NVARCHAR(MAX) NULL,
[Properties] NVARCHAR(MAX) NULL,
[SourceContext] NVARCHAR(1000) NULL,
CONSTRAINT [PK_LogEvent] PRIMARY KEY CLUSTERED ([Id] ASC),
INDEX [IX_LogEvent_TimeStamp] NONCLUSTERED ([Timestamp] DESC),
INDEX [IX_LogEvent_Level] NONCLUSTERED ([Level] ASC),
INDEX [IX_LogEvent_TraceId] NONCLUSTERED ([TraceId] ASC),
)
WITH (DATA_COMPRESSION = PAGE);using Serilog; Log.Logger = new LoggerConfiguration() .WriteTo.SqlServer( connectionString: "Data Source=(local);Initial Catalog=Serilog;Integrated Security=True;TrustServerCertificate=True;", tableName: "LogEvent", tableSchema: "dbo" ) .CreateLogger(); Log.Information("Hello, SQL Server!"); Log.CloseAndFlush();
using Serilog; using Serilog.Sinks.SqlServer; Log.Logger = new LoggerConfiguration() .WriteTo.SqlServer(config => { config.ConnectionString = "Data Source=(local);Initial Catalog=Serilog;Integrated Security=True;TrustServerCertificate=True;"; config.TableName = "LogEvent"; config.TableSchema = "dbo"; config.MinimumLevel = LogEventLevel.Information; config.BatchSizeLimit = 100; config.BufferingTimeLimit = TimeSpan.FromSeconds(5); }) .CreateLogger();
You can configure the sink using appsettings.json with the Serilog.Settings.Configuration package:
appsettings.json:
{
"ConnectionStrings": {
"Serilog": "Data Source=(local);Initial Catalog=Serilog;Integrated Security=True;TrustServerCertificate=True;"
},
"Serilog": {
"Using": [ "Serilog.Sinks.SqlServer" ],
"MinimumLevel": {
"Default": "Information",
"Override": {
"Microsoft": "Warning",
"System": "Warning"
}
},
"WriteTo": [
{
"Name": "SqlServer",
"Args": {
"connectionString": "Data Source=(local);Initial Catalog=Serilog;Integrated Security=True;TrustServerCertificate=True;",
"tableName": "LogEvent",
"tableSchema": "dbo"
}
}
],
"Enrich": [ "FromLogContext" ]
}
}Program.cs:
using Serilog; var builder = WebApplication.CreateBuilder(args); builder.Host .UseSerilog((context, services, configuration) => configuration .ReadFrom.Configuration(context.Configuration) ); var app = builder.Build(); app.UseSerilogRequestLogging(); app.Run();
Or for a console application:
using Serilog; using Microsoft.Extensions.Configuration; var configuration = new ConfigurationBuilder() .SetBasePath(Directory.GetCurrentDirectory()) .AddJsonFile("appsettings.json") .Build(); Log.Logger = new LoggerConfiguration() .ReadFrom.Configuration(configuration) .CreateLogger(); try { Log.Information("Application starting"); // Your application code } finally { Log.CloseAndFlush(); }
The SqlServerSinkOptions class inherits from BatchingOptions and provides the following properties:
| Property | Default | Description |
|---|---|---|
ConnectionString |
- | SQL Server connection string (required) |
TableName |
"LogEvent" |
Name of the table to write logs to |
TableSchema |
"dbo" |
Schema of the table |
MinimumLevel |
LevelAlias.Minimum |
Minimum log event level |
LevelSwitch |
null |
Dynamic level switch for runtime level adjustments |
BulkCopyOptions |
SqlBulkCopyOptions.Default |
SqlBulkCopy options for bulk insert operations |
Mappings |
MappingDefaults.StandardMappings |
Column mappings for log event properties |
BatchSizeLimit |
1000 |
Number of log events to batch before writing (inherited) |
BufferingTimeLimit |
2 seconds |
Maximum time to wait before flushing a batch (inherited) |
The sink includes the following standard column mappings:
| Column Name | Type | Description | Nullable | Size |
|---|---|---|---|---|
Timestamp |
DateTimeOffset |
UTC timestamp of the log event | No | - |
Level |
string |
Log level (e.g., "Information", "Error") | No | 50 |
Message |
string |
Rendered log message | Yes | MAX |
TraceId |
string |
Distributed tracing trace ID | Yes | 100 |
SpanId |
string |
Distributed tracing span ID | Yes | 100 |
Exception |
string |
Exception details as JSON | Yes | MAX |
Properties |
string |
Additional properties as JSON | Yes | MAX |
SourceContext |
string |
Source context (typically class name) | Yes | 1000 |
The Exception column stores exception details as a JSON object with the following structure:
{
"Message": "The error message",
"BaseMessage": "Inner exception message (if present)",
"Type": "System.InvalidOperationException",
"Text": "Full exception text including stack trace",
"HResult": -2146233079,
"ErrorCode": -2147467259,
"Source": "MyApplication",
"MethodName": "MyMethod",
"ModuleName": "MyAssembly",
"ModuleVersion": "1.0.0.0"
}Key fields:
Message - The exception's primary error messageBaseMessage - Message from the innermost exception (if there's an inner exception chain)Type - Fully qualified type name of the exceptionText - Complete exception text including stack trace (from ToString())HResult - The HRESULT error codeErrorCode - Error code for ExternalException typesSource - The application or object that caused the errorMethodName - Name of the method that threw the exceptionModuleName - Name of the assembly containing the throwing methodModuleVersion - Version of the assemblyNote: Aggregate exceptions with a single inner exception are automatically flattened to the inner exception.
The Properties column stores log event properties as a JSON object. Property values are serialized according to their type:
Scalar values:
{
"UserId": 123,
"UserName": "John Doe",
"IsActive": true,
"Amount": 99.99,
"RequestId": "550e8400-e29b-41d4-a716-446655440000",
"Timestamp": "2024-01-15T10:30:45Z"
}Structured values:
{
"User": {
"Id": 123,
"Name": "John Doe",
"Email": "john@example.com"
}
}Arrays/Sequences:
{
"Roles": ["Admin", "User", "Manager"],
"Numbers": [1, 2, 3, 4, 5]
}Supported scalar types:
string, bool, int, long, double, float, decimal, byte, short, etc.DateTime, DateTimeOffset, TimeSpan, DateOnly, TimeOnly (as ISO strings)Guid (as string), Enum (as string), BigInteger (as string), char (as string)null values are preservedNote: By default, properties enriched via
FromLogContext()orWithProperty()are included in this column. You can extract specific properties to dedicated columns using custom mappings (see below).
Add custom property mappings to log additional data:
Log.Logger = new LoggerConfiguration() .Enrich.WithProperty("ApplicationName", "MyApp") .Enrich.WithProperty("ApplicationVersion", "1.0.0") .Enrich.WithProperty("EnvironmentName", "Production") .WriteTo.SqlServer(config => { config.ConnectionString = connectionString; config.TableName = "LogExtended"; // Add custom property mappings config.AddPropertyMapping("ApplicationName", size: 500); config.AddPropertyMapping("ApplicationVersion", size: 500); config.AddPropertyMapping("EnvironmentName", size: 500); }) .CreateLogger();
Corresponding table structure:
CREATE TABLE [dbo].[LogExtended]
(
[Id] BIGINT IDENTITY(1,1) NOT NULL,
[Timestamp] DATETIMEOFFSET NOT NULL,
[Level] NVARCHAR(50) NOT NULL,
[Message] NVARCHAR(MAX) NULL,
[TraceId] NVARCHAR(100) NULL,
[SpanId] NVARCHAR(100) NULL,
[Exception] NVARCHAR(MAX) NULL,
[Properties] NVARCHAR(MAX) NULL,
[SourceContext] NVARCHAR(1000) NULL,
[ApplicationName] NVARCHAR(500) NULL,
[ApplicationVersion] NVARCHAR(500) NULL,
[EnvironmentName] NVARCHAR(500) NULL,
CONSTRAINT [PK_LogExtended] PRIMARY KEY CLUSTERED ([Id] ASC)
);For more control, define custom mappings manually:
config.Mappings.Add( new ColumnMapping<LogEvent>( ColumnName: "MachineName", ColumnType: typeof(string), GetValue: logEvent => Environment.MachineName, Nullable: true, Size: 100 ) );
Note: When you specify a
Sizefor string columns, the sink automatically truncates values that exceed the specified length to prevent SQL insert errors. For example, ifSizeis set to 100 and a value is 150 characters, it will be truncated to 100 characters before insertion. Columns without aSizespecified (or withSize: null) will not be truncated.
config.Mappings.Clear(); // Remove all default mappings config.Mappings.Add(MappingDefaults.TimestampMapping); config.Mappings.Add(MappingDefaults.LevelMapping); config.Mappings.Add(MappingDefaults.MessageMapping); // Add only the mappings you need
using Serilog; var builder = WebApplication.CreateBuilder(args); builder.Services.AddSerilog(loggerConfiguration => { loggerConfiguration .ReadFrom.Configuration(builder.Configuration) .Enrich.FromLogContext() .WriteTo.Console() .WriteTo.SqlServer(config => { config.ConnectionString = builder.Configuration.GetConnectionString("Serilog"); config.TableName = "LogEvent"; }); }); var app = builder.Build(); app.UseSerilogRequestLogging(); app.Run();
{
"ConnectionStrings": {
"Serilog": "Data Source=(local);Initial Catalog=Serilog;Integrated Security=True;TrustServerCertificate=True;"
},
"Serilog": {
"MinimumLevel": {
"Default": "Information",
"Override": {
"Microsoft": "Warning",
"System": "Warning"
}
}
}
}Adjust batch size and timeout based on your logging volume:
config.BatchSizeLimit = 1000; // Larger batches for high-volume scenarios config.BufferingTimeLimit = TimeSpan.FromSeconds(10); // Longer timeout for efficiency
Configure bulk copy behavior:
using Microsoft.Data.SqlClient; config.BulkCopyOptions = SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.FireTriggers;
Create appropriate indexes for your query patterns:
-- Optimize for time-based queries CREATE INDEX [IX_LogEvent_TimeStamp] ON [dbo].[LogEvent] ([Timestamp] DESC); -- Optimize for filtering by level CREATE INDEX [IX_LogEvent_Level] ON [dbo].[LogEvent] ([Level] ASC); -- Optimize for distributed tracing queries CREATE INDEX [IX_LogEvent_TraceId] ON [dbo].[LogEvent] ([TraceId] ASC);
This library does not automatically create database tables. You are responsible for creating the target log table before using the sink.
The sink prioritizes performance and efficiency by design. Automatic table creation would introduce several challenges:
CREATE TABLE permissions to application accountsYou have full control over your table schema. At minimum, create columns that match your configured mappings:
CREATE TABLE [dbo].[LogEvent]
(
[Id] BIGINT IDENTITY(1,1) NOT NULL,
[Timestamp] DATETIMEOFFSET NOT NULL,
[Level] NVARCHAR(50) NOT NULL,
[Message] NVARCHAR(MAX) NULL,
[TraceId] NVARCHAR(100) NULL,
[SpanId] NVARCHAR(100) NULL,
[Exception] NVARCHAR(MAX) NULL,
[Properties] NVARCHAR(MAX) NULL,
[SourceContext] NVARCHAR(1000) NULL,
CONSTRAINT [PK_LogEvent] PRIMARY KEY CLUSTERED ([Id] ASC)
)
WITH (DATA_COMPRESSION = PAGE);You can add indexes, partitioning, and other optimizations based on your specific requirements. See the Quick Start section for complete table examples.
As your application generates logs over time, the log table will grow continuously. It's important to implement a log retention strategy to manage storage costs and maintain query performance.
The following stored procedure provides an efficient way to delete old log entries in batches, preventing transaction log bloat and minimizing system impact:
CREATE PROCEDURE dbo.PurgeLogs @RetentionDays INT = 90, @BatchSize INT = 5000 AS BEGIN SET NOCOUNT ON; -- Calculate the threshold date DECLARE @Threshold DATETIMEOFFSET = DATEADD(DAY, -@RetentionDays, SYSUTCDATETIME()); DECLARE @RowsDeleted INT = 0; DECLARE @TotalDeleted INT = 0; -- Validate batch size IF @BatchSize <= 0 SET @BatchSize = 5000; -- Delete in batches to prevent transaction log bloat and reduce lock contention WHILE 1 = 1 BEGIN DELETE TOP (@BatchSize) FROM dbo.LogEvent WITH (ROWLOCK) WHERE [Timestamp] < @Threshold; SET @RowsDeleted = @@ROWCOUNT; SET @TotalDeleted = @TotalDeleted + @RowsDeleted; -- Exit if no more rows to delete IF @RowsDeleted = 0 BREAK; -- Brief delay between batches to reduce contention WAITFOR DELAY '00:00:00.100'; -- 100ms END -- Return summary SELECT @TotalDeleted AS TotalRowsDeleted; END
How the procedure works:
Batch Processing: Deletes records in configurable batches (default 10,000 rows) rather than all at once
Row-Level Locking: Uses WITH (ROWLOCK) hint to minimize lock escalation and reduce contention with concurrent operations
Threshold-Based Deletion: Removes logs older than the specified retention period (default 90 days)
Progress Tracking: Returns total deleted rows and provides feedback during execution
For very high-volume logging scenarios, consider implementing table partitioning by date:
See SQL Server table partitioning documentation for implementation details.
Enable Serilog self-logging to diagnose connection problems:
using Serilog.Debugging; SelfLog.Enable(Console.Error);
Ensure your database table schema matches your column mappings. Missing columns will cause bulk insert to fail.
BatchSizeLimit for high-volume scenariosBufferingTimeLimit to allow more batchingContributions are welcome! Please feel free to submit a Pull Request.
This project is licensed under the MIT License - see the LICENSE file for details.
loresoft/serilog-sinks-sqlserver is a public repo on GitHub by loresoft. A high-performance Serilog sink that writes log events to Microsoft SQL Server using bulk insert operations.
It declares the MIT license on GitHub.
Its maker has not tagged a system, so it is unknown here. Its README says what it needs.
Open "Take it" on its page: Take My Repo writes an install plan for your machine and hands it to your AI agent (Claude Code, Codex or Cursor), which asks you before each step. Take My Repo has not tested it on any machine.
Its last change on GitHub was on 2026-11-18. GitHub counts 1 open issue and pull requests. Take My Repo does not judge from that whether it is maintained.
A Dotnet 9.0 WebApi template project. MediatR, Swagger, Mapper, Serilog and more implemented.
Nobody has reviewed it on Take My Repo yet, which says nothing about its quality.
Log exception details and custom properties that are not output in Exception.ToString().

The Prisma ORM: write your application in TypeScript with human-readable queries instead of SQL. Host your database on Prisma Postgres and your app on Prisma Compute.

This project is an Web API Open-Source Boilerplate Template that includes NET 8, Web API standards, clean n-tier architecture, GraphQL service, Redis, Mssql, Mongo databases and User Auditing (Identity) with a lot of best practices.
The MSBuild Community Tasks Project is an open source project for MSBuild tasks.
Generate Entity Framework Core model from an existing database

Source generator that helps register attribute marked services in the dependency injection ServiceCollection
A comprehensive collection of high-quality Blazor components
A library for using FluentValidation with Blazor EditForm components. Provides seamless integration between FluentValidation and Blazor forms with support for real-time validation, nested objects, async validation, and rule sets.
Calculator.NET - Calculator that evaluates math expressions