How to log to SQL Server in ASP. Net core

Time:2021-7-2

Translation link:https://www.infoworld.com/art…

Logging is a basic function of an application. Logging is especially useful for finding, checking and debugging bugs,SerilogIs a third-party open source class library, it can be structured log output to the console, files and a number of other categories of data storage.

This article mainly discusses how to write structured logs to SQL server through serilog. To run the examples in this article, you need to install visual studio 2019.

Install serilog

To use serilog, you need to install it through nuget. There are two ways to choose from, or use theNuGet package managerVisual interface, either inNuGet package manager consoleType the following command in the command line window.


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

Initialize serilog

The following code shows how to add the serilog to the asp.net core. You can focus on using theUseSerilog()The extension method takes serilog as the default logging framework of asp.net core.


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

Build a test web program

In order to show the usage of serilog in the application, the following is a complete code segment. We can focus on how to configure and build webhost.


    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();
    }

In order to makeUseSerilog()Available, remember to refer to the serilog namespace.


using Serilog;

Configure database connection

When creating asp.net core project with visual studio,appsettings.jsonThe file is created by default. This file is very important. The subsequent database connection string and other configuration information will be configured here. The following is completeappsettings.jsonto configure.


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

Creating log table with SQL Server

The application will automatically generate the table. Of course, you can also manually generate the log table in the database. If you want to do so, you can use the following script.


   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 the application is running, the database will generate a log table, and the asp.net core will record the log to the table. The following shows the log information that has been recorded in the log table.

How to log to SQL Server in ASP. Net core

Log in action

You can use dependency injection (DI) to inject log instances into the controller, as shown in the following code:


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

The following code fragment shows how to use serilog to log in the action under the controller.


    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 serilog is independent of. Net core, and it is perfectly involved in the asp.net core ecosystem in the form of plug-ins, making structured logs easier and more convenient to be recorded, serilog can use dozens of receivers to send logs to different targets, including text files, databases, AWS, azure and Google court service, In this article, I’ve shown you how serilog works with SQL server, and I’ll discuss more advanced features of serilog in future articles.

More high quality dry goods: see my GitHub:dotnetfly