Use Serilog ASP.NET Core to SQL Server Database

Logging is an essential feature for any application, as it is necessary for detecting, investigating, and debugging issues. Serilog is a third-party, open source library that allows .NET developers to log structured data to the console, to files, and to several other kinds of data stores. 

This article discusses how we can use Serilog to log structured data to a SQL Server database. To work with the code examples provided in this article, you should have Visual Studio 2019 installed in your system.

Create Your First ASP.NET Core 3.0 Project

Make sure you create your ASP.NET Core project in Visual Studio. We assume that you have installed Visual Studio on your computer.

Install the NuGet packages for Serilog

To work with Serilog, you should install the Serilog packages from NuGet. You can do this either via the NuGet package manager inside the Visual Studio 2019 IDE, or by executing the following commands at the NuGet package manager console:

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

Initialize Serilog in Program.cs in ASP.NET Core

The following code snippet illustrates how you can plug Serilog into ASP.NET Core. Note how the UseSerilog() extension method has been used to set Serilog as the logging provider.

public static IWebHost BuildWebHost(string[] args) =>
            WebHost.CreateDefaultBuilder(args)
                   .UseStartup<Startup>()
                   .UseSerilog()
                   .Build();

Build an example web host in ASP.NET Core

Naturally, we’ll need an application to illustrate the use of Serilog. Here is the complete source code of the Program class for our example app. Note how we’ve configured and built the web host.

    public class Program
    {
        public static void Main(string[] args)
        {
            IConfigurationRoot configuration = new
            ConfigurationBuilder().AddJsonFile("appsettings.json",
            optional: false, reloadOnChange: true).Build();
            Log.Logger = new LoggerConfiguration().ReadFrom.Configuration
            (configuration).CreateLogger();
            BuildWebHost(args).Run();
        }
        public static IWebHost BuildWebHost(string[] args) =>
            WebHost.CreateDefaultBuilder(args)
                .UseStartup<Startup>()
                .UseSerilog()
                .Build();
    }

Configure database connection settings in ASP.NET Core

When you create a new ASP.NET Core project in Visual Studio, the appsettings.json file is created by default. Here is where you can specify the database connection string and other configuration information. Open the appsettings.json file from the project we created earlier and enter the following information:

{
  "Serilog": {
    "MinimumLevel": "Information",
    "WriteTo": [
      {
        "Name": "MSSqlServer",
        "Args": {
          "connectionString": "Data Source=LAPTOP-ULJMOJQ5;Initial
           Catalog=Research;    
     User Id=joydip; Password=sa123#;",
          "tableName": "Log",
          "autoCreateSqlTable": true
        }
      }
    ]
  }
}

Create a database table to log data in SQL Server

You might want to create the log table yourself as well. Below is the script you can use to create a log table in the SQL Server database.

CREATE TABLE [Log] (
   [Id] int IDENTITY(1,1) NOT NULL,
   [Message] nvarchar(max) NULL,
   [MessageTemplate] nvarchar(max) NULL,
   [Level] nvarchar(max) NULL,
   [TimeStamp] datetimeoffset(7) NOT NULL,
   [Exception] nvarchar(max) NULL,
   [Properties] nvarchar(max) NULL
   CONSTRAINT [PK_Log]
     PRIMARY KEY CLUSTERED ([Id] ASC)
)

When you run the application, a new table named Log will be created and the ASP.NET Core startup events will be logged there. Figure 1 below shows the data that has been logged inside the Log table.

Log data in action methods in ASP.NET Core

You can leverage dependency injection to inject a logger instance in your controller as shown in the code snippet below:

public class DefaultController : Controller
{
   private readonly ILogger<DefaultController> _logger;
   public DefaultController(ILogger<DefaultController> logger)
   {
      _logger = logger;
   }
}

The following code snippet illustrates how you can take advantage of Serilog in your controller’s action methods to log data.

public class DefaultController : Controller
    {
        private readonly ILogger<DefaultController> _logger;
        public DefaultController(ILogger<DefaultController> logger)
        {
            _logger = logger;
        }
        public IActionResult Index()
        {
            _logger.LogInformation("Hello World");
            return View();
        }
    }

Although independent of .NET Core, Serilog plugs into the ASP.NET Core ecosystem nicely, making structured logging easy and convenient. Serilog also takes advantage of dozens of sinks to send the logs to many different logging targets ranging from text files to databases to hosting providers. I will post other interesting post in next post.