ASP.NET Core SQL Server and MySQL database logging with Serilog

In this article we'll see how to take advantage of Serilog, a neat open-source diagnostic logging library for .NET applications, to log our ASP.NET Core web application’s data to a SQL Server, MySQL or MariaDB database.

Introduction

Any experienced developer arguably knows that logging is a must-have feature for any application, as it is necessary for detecting, investigating, and debugging issues. Luckily for us, the .NET Core framework natively supports a native logging API that can be used to implement such pivotal feature in a rather seamless way using a variety of built-in and third-party logging providers.

Here's a list of the currently available built-in logging providers:

• Console
• Debug
• EventSource
• EventLog (Windows platforms only)

These providers can be used almost without efforts from any .NET Core app: as a matter of fact, if we create our app's HostBuilder using the ConfigureWebHostDefault() method, the above logging providers will be automatically enabled by default:

As we can see, there are no native logging providers that can be used to have these logs stored within a DBMS, which would certainly be very useful as it would allow us to review our logs using a structured approach: as a matter of fact, structured logging would definitely be a great way to produce readable, filterable, indexed and exportable logs.

Here's where Serilog comes to the rescue: let's see how we can configure it to implement such task!

Install the NuGet packages

The first thing we have to do is to add the following NuGet packages to our project:

• Serilog.AspNetCore
• Serilog.Settings.Configuration
• Serilog.Sinks.MSSqlServer (for SQL Server)
• Serilog.Sinks.MariaDB (for MariaDB and/or MySQL)

These packages can be installed using the Visual Studio's GUI (Manage NuGet packages) or the Package Manager Console in the following way:

PM> Install-Package Serilog.AspNetCore
PM> Install-Package Serilog.Settings.Configuration
PM> Install-Package Serilog.Sinks.MSSqlServer
PM> Install-Package Serilog.Sinks.MariaDB

As we can easily guess, the Sinks packages are the DBMS providers that will be used to allow Serilog to communicate with each specific database engine. In this post we're going to use the MariaDB/MySQL sink, but the MS SQL Server one can be configured in the same exact way - as long as you set the autoCreateSqlTable option to true (see below for details).

Configuring Serilog

Once the required NuGet packages have been installed, we can configure the various Serilog's various options - including the Connection String to reach our DBMS - using our app's configuration files, such as:

• appsettings.json, where we can put most of the Serilog options (DBMS table name, log expiration, and so on).
• secrets.json, where we should put the options that we don't want to share and/or publish to a repository (such as our Database's Connection String).

Before proceeding, it can be useful to know that Serilog can also be configured using a code-only approach, thus avoiding to deal with the appsettings.json and secrets.json files: however, this is not recommended - at least in our own opinion - because it would force us to write the database's Connection String in plain text within our app's source code, which would pose a non-trivial security risk (see our Visual Studio's UserSecrets guide for details). That said, if you want to do that, you can check out this code-only setup guide from the Serilog's GitHub repository.

Here's a sample appsettings.json file for a typical logging scenario:

And here's the corresponding secrets.json file (for the ConnectionString only):

These settings will probably be enough for most web applications: if you want to know more about these settings, check out the Serilog's official wiki on GitHub.

Add Serilog to Program.cs file

Now that we've laid down the configuration settings, we can add Serilog to our app's Program.cs file in the following way (relevant rows are highlighted):

The above Program.cs sample file is taken from a typical ASP.NET Core 3.1 web application, but the highlighted stuff is fully compatible with .NET Core 2.0, 2.1 and 2.2 as well (and it will be most likely compatible with .NET 5 too), with some minimum differences for the IHostBuilder part.

As we can see, we had to istantiate a custom IConfiguration object so that we could retrieve the Serilog configuration file from the appsettings.json / appsettings.<environmentName>.json configuration files: while we were there, we also added the Visual Studio's User Secrets file (using the AddUserSecrets() extension method) so that we can read our database's Connection String from a secure location (the secrets.json file) instead of putting it in the configuration files during the app's development phase.  In this way not only will we be able to avoid inserting our valuable ConnectionString inside the code, but we will also be able to use an existing one (such as the Entity Framework Core's Connection String) without having to type it twice.

Testing the Logging provider

Now that everything is set, we just have to perform a quick test to confirm that our new Serilog-base Logging provider actually works. To do that, just run the app in Debug mode and then check the Database specified in the connection string: we should find a new Logs table with the following records:

We did it! Our web application's logs are now saved into our Database.

Conclusion

That's it, at least for now: we hope that our guide will help many ASP.NET Core developers who are looking for a way to log their web application's events to a DBMS.

Print Friendly & PDF Download