Write your logs into database in an ASP.NET Core application using Serilog

In most scenarios, we normally use a flat-files for writing your logs or exception messages. But what if you write that information into the table in a database instead of a file. To implement this functionality, we can make use of third-party providers such as Serilog to log information in a SQL server database. Even though it's not a recommended approach, one can easily search the logs by executing SQL queries against the table.

Installing Packages

Serilog provides the functionality to write logs to different sources such as files, trace logs, database and the providers for these are called Serilog Sinks. To write logs to a table in a SQL Server database, you will need to add the following NuGet packages

Install-Package Serilog
Install-Package Serilog.Settings.Configuration
Install-Package Serilog.Sinks.MSSqlServer

The first package contains the core runtime, the second package can read the key under the "Serilog" section from a valid IConfiguration source and the last one is responsible for making the connection to the database and writing information into the log table.

Configuring Serilog 

Modify the appsettings.json file to add a new section called "Serilog". We will set up the connection string to the database, provide the name of the table and instruct Serilog to create the table if not found in the DB

"Serilog": {
    "MinimumLevel": "Error",
    "WriteTo": [
      {
        "Name": "MSSqlServer",
        "Args": {
          "connectionString": "Server=(localdb)\\MSSQLLocalDB;Database=Employee;Trusted_Connection=True;MultipleActiveResultSets=true",
          "tableName": "Logs",
          "autoCreateSqlTable": true
        }
      }
    ]
  },

Then modify the Program.cs file to read these values from the JSON file

var configSettings = new ConfigurationBuilder()
.AddJsonFile("appsettings.json")
.Build();

.ConfigureAppConfiguration(config =>
{
    config.AddConfiguration(configSettings);
})

Now, to hook up Serilog provider, import the namespace first

using Serilog

An add the following in the CreateHostBuilder method

Log.Logger = new LoggerConfiguration()
    .ReadFrom.Configuration(configSettings)
    .CreateLogger();

.ConfigureLogging(logging =>
{
    logging.AddSerilog();
})

Here's the full code

public static IHostBuilder CreateHostBuilder(string[] args)
{
    var configSettings = new ConfigurationBuilder()
        .AddJsonFile("appsettings.json")
        .Build();

    Log.Logger = new LoggerConfiguration()
        .ReadFrom.Configuration(configSettings)
        .CreateLogger();

    return Host.CreateDefaultBuilder(args)
    .ConfigureAppConfiguration(config =>
    {
        config.AddConfiguration(configSettings);
    })
    .ConfigureLogging(logging =>
    {
        logging.AddSerilog();
    })
    .ConfigureWebHostDefaults(webBuilder =>
    {
        webBuilder.UseStartup();
    });

}

Testing it out

Create a new MVC application from Visual Studio By default, Logging is enabled in the application via the ILogger interface. In the case of a web app, you will get an ILogger from DI container and use that object for writing into the configured log providers Let's see how we can do that in HomeController

First, create a private variable

private readonly ILogger<HomeController> _logger;

Then modify the constructor as shown below

public HomeController(ILogger logger)
{
    _logger = logger;
}

Now, use the LogError method to write a message into the log

public IActionResult Index()
{
    _logger.LogError("Writing to log");
    return View();
}

If you run the application and goto the home page, you will see this message written to the table.