TheCodeBuzz

Today in this article, we will see how to perform Serilog SQL Logging with an example in ASP.NET Core

We will cover how to log the logs to an SQL database however the same approach can be easily used for other Database providers like MySQL, DB2, etc.

Currently, the available ASP.NET Core version logging framework is already very rich and gives us a lot of flexibility in logging to different logging providers like Console, Event, EventSource, etc.

However, high-end logging requirements like database logging or File or Rolling File logging provider

are still not available through the framework and we need to rely on external solutions.

Serilog is a highly efficient logging framework that helps us enable logging to almost all logging source formats including Console, File, Database logging, etc.

In this post, we will understand, how to enable database logging in using Serilog and do customization to existing behavior using an example.

We shall be targeting SQL Database as a logging source.

Getting started

We will cover below aspects of logging,

Create ASP.NET Core API

Please add below NuGet Packages. Please use the latest available version available for the ASP.NET Core version.

PM>Install-Package Serilog.AspNetCore -Version 3.2.0

Note: please use the latest available version.

Additionally please install Serilog.Sinks.MSSqlServer NuGet package for SQL database provider support,

PM>Install-Package Serilog.Sinks.MSSqlServer -Version 5.2.0

OR

Please install packages from Nuget Package Manager,

The above package will help to target Serilog as a Database logging provider

Serilog Database Sink Configuration

The Serilog sink can be configured by any of the below methods,

• Using only Code Or

• Using Configuration files (or Custom XML or ini or JSON configuration providers),

• Using a combination of Code and Configuration

• By Serilog configuration packages. 

To simplify this article, today we shall see the Code + Configuration option which is easy and simple to configure.

Loading Configuration

Please update the Main method for adding the Database logging as shown in the below-highlighted code in the Program.cs

 var configuration = new ConfigurationBuilder()
            .SetBasePath(Directory.GetCurrentDirectory())
            .AddJsonFile("appsettings.json")
            .Build();

The above code helps in loading the configuration from the Application setting configuration file.

Define Serilog Configuration

  "Serilog": {

    "ColumnOptions": {
      "addStandardColumns": [ "LogEvent" ],
      "removeStandardColumns": [ "MessageTemplate", "Properties" ],
      "timeStamp": {
        "columnName": "Timestamp",
        "convertToUtc": false
      }
    },

    "ConnectionStrings": {
      "LogDatabase": "Your Connection string here"
    },
    "SchemaName": "dbo",
    "TableName": "TheCodeBuzzDB"

  }

In the above configuration, we have defined below,

• TableName – Database name as TheCodeBuzzDB

• SchemaName – Schema as dbo

• ColumnOptions -Column option to be used for logging
  • addStandardColumns – This option if used will add all below column ids to SQL table
    • Id
    • Message
    • MessageTemplate
    • Level
    • TimeStamp
    • Exception
    • Properties

Register sink for SQL database

Let’s now add a sink that will write log events to a table in the MSSqlServer database.

 Log.Logger = new LoggerConfiguration()
                .WriteTo.MSSqlServer(
                    connectionString: "Database Connection string",
                    tableName: "TableName",
                    appConfiguration: configuration,
                    autoCreateSqlTable: true,
                    columnOptionsSection: "Serilog ColumnOptions",
                    schemaName: "Serilog SchemaName"
                .CreateLogger();

Once the above is executed sink adds the required table with a required column to the SQL table.

Auto Creating SQL Table

A property called “autoCreateSqlTable: true” indicates the SQL Table needs to be created if it doesn’t exist already.

Once the code is run below the schema gets created in SQL Server which is equal to addStandardColumns.

Here below is the complete setup,

Let’s do the logging using the ILogger instance in any part of the code,

        [HttpGet("{id}")]
        public ActionResult<IEnumerable<string>> Get(int id)
        {
            _logger.LogInformation("Start : Getting item details for {ID}", id);

            List<string> list = new List<string>();

            list.Add("A");
            list.Add("B");

            _logger.LogInformation($"Completed : Item details for  {{{string.Join(", ", list)}}}");

            return list;
        }

Below is how the logs will be captured in the SQL table,

Configuration-Driven sink

• Serilog.Settings.AppSettings

To improvise further above configuration usage projects are compatible with Microsoft.Extensions.Configuration can apply a configuration-driven sink setup to the Serilog.Settings.Configuration package.

• XML or Custom JSON Loading

One can also define configuration in XML JSON or INI and load them as required.

Please refer article on

• Loading Configuration from XML.JSON, INI file in ASP.NET Core.

One can also use Typesafe configuration in code to map config key-value pair to complex type.

That’s All! Happy Coding!

Please sound off your comments below.

Summary

High-end logging requirements like Database or File are currently not yet available through the .NET Core framework and certainly, we need to depend on custom or any existing external solutions.

Serilog helps us enable logging in a few simple steps and addresses the Database logging requirements easily.

Please bookmark this page and share it with your friends. Please Subscribe to the blog to receive notifications on freshly published(2024) best practices and guidelines for software design and development.

Share on Facebook

Post on X

Follow us

Save