Logging Errors in SQL Server using Serilog

Logging Errors/Exceptions in SQL Server using Serilog

Logging is a critical aspect of software development, aiding developers in diagnosing issues, monitoring application behavior, and understanding user interactions. Serilog, a popular logging library in the .NET ecosystem, provides flexible and extensible logging capabilities.
In this blog, we will learn how to implement Serilog to write logs to a SQL Server database in a .NET Core application.
The process is fairly simple. So lets do it step by step.

Step-1: Adding Serilog Nuget Packages

Go to Manage Nuget Packages and add the latest stable version of the following packages:

1. Serilog.AspNetCore
2. Serilog.Settings.Configuration
3. Serilog.Sinks.MSSqlServer

Step-2: Adding Serilog Settings

Go to appsettings.json file and add the following settings for Serilog (Every setting is self explanatory):

"Serilog": {
   "Using": [ "Serilog.Sinks.MSSqlServer" ], //Write logs to SQL Server
   "MinimumLevel": {
      "Default": "Warning",
      "Override": {
         "System": "Error",
         "Microsoft": "Error"
      }
   },
   "WriteTo": [
      {
         "Name": "MSSqlServer",
         "Args": {
            "connectionString": "Server=.\\<SERVERNAME>;Database=<DATABASENAME>;Trusted_Connection=true;TrustServerCertificate=True;MultipleActiveResultSets=true;",
            "sinkOptionsSection": {
               "tableName": "<TABLENAME>",
               "schemaName": "<SCHEMANAME>",
               "autoCreateSqlTable": true //default is false
            },
            "restrictedToMinimumLevel": "Error",
            "columnOptionsSection": {
               "clusteredColumnstoreIndex": false,
               "primaryKeyColumnName": "Id",
               "removeStandardColumns": [ "MessageTemplate", "Properties", "LogEvent" ],
               "id": { "nonClusteredIndex": true },
               "timeStamp": {
               "columnName": "TimestampUtc",
               "convertToUtc": true
               }
            }
         }
      }
   ]
}

Step-3: Adding and Using Serilog in Middleware Pipeline

Go to the Startup.cs or Program.cs file (depending upon which configuration you are using) and add the following code:
Note: The following code is applicable to Program.cs file as used in .Net Core 6.0 and later.

using Serilog;
.
.
//Create Logger from settings from appsettings.json
var logger = new LoggerConfiguration()
.ReadFrom.Configuration(builder.Configuration)
.CreateLogger();
//Add Logger
Log.Logger = logger;
builder.Host.UseSerilog(logger);
.
.
var app = builder.Build();
.
.
//Use Logger after any custom exception handling middleware
app.UseMiddleware<ExceptionHandlingMiddleware>();
//Use Serilog
app.UseSerilogRequestLogging();

Please Note: Configuring Serilog settings in the appsettings.json file is one (and not the only one) method. These settings can also be configured via code in Program.cs file if you prefer to do so.
I find it better to configure in appsettings.json but you can set the same in Program.cs as below.
This code gives you a hint on how to set it through code. Feel free to play around and experiment with these settings to learn more if you choose to do it this way.

Log.Logger = new LoggerConfiguration()
                 .WriteTo.MSSqlServer(
                    connectionString: "YourSqlConnectionString",
                    sinkOptions: new MSSqlServerSinkOptions
                    {
                       TableName = "Logs",
                       AutoCreateSqlTable = true,
                       BatchPostingLimit = 100,
                       Period = TimeSpan.FromSeconds(1)
                    }
                  )
                 .CreateLogger();

// Other code lines removed for brevity

Log.CloseAndFlush();

Conclusion

Integrating Serilog with SQL Server in a .NET Core application provides a robust logging solution with the ability to store logs in a centralized and structured manner. As your application evolves, consider exploring additional Serilog features and sink options to meet specific logging requirements. With Serilog, you have a powerful tool for effective logging in your .NET Core projects.

Logging Errors in SQL Server using Serilog