How to log to SQL server using Serilog with Asp.net 5 (dotnet core)

Question

I managed to write to Serilog.Sinks.MSSqlServer sink if I configure in code(C#), Startup class. However if I try web.config in the wwwroot it does not work;

<add key="serilog:minimum-level" value="Information"/>
<add key="serilog:using:MSSqlServer" value="Serilog.Sinks.MSSqlServer" />
<add key="serilog:write-to:MSSqlServer.connectionString" value="Server=MVDV.."/>
<add key="serilog:write-to:MSSqlServer.tableName" value="Logs"/>

It may be that configuration is not read from there by Serilog? Is it any place to put this configuration keys to have it read out of the box? Or should I create a json file then read values and use again C# code in the Startup class something like:

.WriteTo.MSSqlServer(connectionString: myReadFromJsonConfigValue) ?

Answer 1

ASP.NET Core configuration has been re-architected and doesn't depend on Xml configurations any more, check this excellent article for an introduction about the new configuration system.

In order to use Serilog with SQL Server Sink follow the below steps:

Step1: Update the project.json to reference the Serilog and Serilog.Sinks.MSSqlServer packages by adding the following lines at the end of the dependencies section.

"Serilog": "1.5.14",
"Serilog.Sinks.MSSqlServer": "3.0.48"

Step 2: Add Serilog SQL Server Sink settings into appsettings.json Update appsettings.json file to include all the required Serilog SQL Server Sink configuration by adding the following JSON at the end of the appsettings.json file and before the last closing curly braces, make sure to update the values to your relevant values.

"Serilog": {
    "ConnectionString": "Server=(local);Database=serilogdemo;trusted_connection=true",
    "TableName": "Logs"
  }

Step 3: Update the Startup class to configure Serilog.ILogger ASP.NET Core has a new builtin Dependency Injection feature that can be used by registering the services and their implementations through the ConfigureServices method inside Startup class so add the following section add the end of the ConfigureServices method. The Dependency Injection feature provide three types of registrations Transient , Scoped and Singleton and for this question I have used Singleton just for demo purpose.

services.AddSingleton<Serilog.ILogger>(x=>
{
    return new LoggerConfiguration().WriteTo.MSSqlServer(Configuration["Serilog:ConnectionString"], Configuration["Serilog:TableName"],autoCreateSqlTable:true).CreateLogger();
});

As you can see, the nested JSON configurations inside the appsettings.json could be read used the property Configuration with configuration values can be retrieved using a : separated key.

Step 4: Get reference to the Serilog.ILogger You can get instance of the Serilog.ILogger via the building constructor injection feature by simply adding variable in the constructor of your controller to Serilog.ILogger

Hope that helps