How to Perform Database Logging Using NLog in ASP.NET Core

NLog is a highly efficient logging framework which helps us enabling logging to almost all logging source format including Console, File, Database logging, etc.

High-end logging requirements like Database or File or Rolling File logging provider are still not available through the .NET Core framework.

Currently, the available ASP.NET Core version logging framework is already very rich and gives us a lot of flexibility of logging to different logging providers like Console, Event, EventSource, etc.

We shall be targeting SQL Databaseas logging source.

NLog supports below database provider as well,

• System.Data.OracleClient
• Oracle.DataAccess.Client
• System.Data.SQLite
• Npgsql
• MySql.Data.MySqlClient

Getting started

Create ASP.NET Core API

NLog is available as NuGet package. Please use the latest available version available for the ASP.NET Core version.

PM>Install-Package NLog.Web.AspNetCore -Version 4.9.1

Additionally please install below NuGet package for SQL database provider support

PM>Install-Package System.Data.SqlClient -Version 4.81

OR

You can also also install the packages from Nuget Package Manager,

NLog Database Configuration

NLog support multiple ways of managing the configuration like you can use code or combination of code and config file etc.

DDL – Create SQL Table schema

I have used below script to create the NlogDBLog table.

Sample DDL file can be found on GitHub,

SET ANSI_NULLS ON
 SET QUOTED_IDENTIFIER ON
 CREATE TABLE [dbo].[NlogDBLog] (
     [Id] [int] IDENTITY(1,1) NOT NULL,
     [Application] [nvarchar](50) NOT NULL,
     [Logged] [datetime] NOT NULL,
     [Level] [nvarchar](50) NOT NULL,
     [Message] [nvarchar](max) NOT NULL,
     [Logger] [nvarchar](250) NULL,
     [Callsite] [nvarchar](max) NULL,
     [Exception] [nvarchar](max) NULL,
   CONSTRAINT [PK_dbo.NlogDBLog] PRIMARY KEY CLUSTERED ([Id] ASC)
     WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
 ) ON [PRIMARY]

Please update the above script for any customization if needed.

After running the above script our database schema looks as below,

Create NLog.Config

Below is sample config file,

<?xml version="1.0" encoding="utf-8" ?>
<nlog xmlns="http://www.nlog-project.org/schemas/NLog.xsd"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      autoReload="true"
      internalLogLevel="Warn"
      internalLogFile="C:\temp\Logs\internal-nlog4txt">
  <extensions>
    <add assembly="NLog.Web.AspNetCore"/>
  </extensions>
 
  <targets>
 
    <target name="database" xsi:type="Database">
      <connectionString>
        Server=localhost\SQL;Database=master;Trusted_Connection=True;
      </connectionString>
      <commandText>
        insert into dbo.NlogDBLog (
        Application, Logged, Level, Message,
        Logger, CallSite, Exception
        ) values (
        @Application, @Logged, @Level, @Message,
        @Logger, @Callsite, @Exception
        );
      </commandText>
      <parameter name="@application" layout="AspNetCoreNlog" />
      <parameter name="@logged" layout="${date}" />
      <parameter name="@level" layout="${level}" />
      <parameter name="@message" layout="${message}" />
      <parameter name="@logger" layout="${logger}" />
      <parameter name="@callSite" layout="${callsite:filename=true}" />
      <parameter name="@exception" layout="${exception:tostring}" />
    </target>
  </targets>
 
  <rules>
    <logger name="*" minlevel="Trace" writeTo="database" />
  </rules>
</nlog>

In the above Configuration we have defined below,

• name – Our custom target name for the given provider identification. It should be the same as writeTo field in Rules tag.
• type – Target type Database or File
• connectionString -Define connection string
• commandText -Please mention the DML command here targetting NlogDBLog created using DDL commands

Loading Configuration

Please update the Main method for adding the Database logging as shown in below-highlighted code in Program.cs

var logger = NLog.Web.NLogBuilder.ConfigureNLog("nlog.config").GetCurrentClassLogger();

Below is the complete code base,

Please update HostBuilder as below using UseNLog,

public static IHostBuilder CreateHostBuilder(string[] args) =>
          Host.CreateDefaultBuilder(args)
              .ConfigureWebHostDefaults(webBuilder =>
              {
                  webBuilder.UseStartup<Startup>();
              })
             .ConfigureLogging(logging =>
             {
                 logging.ClearProviders();
                 logging.SetMinimumLevel(LogLevel.Trace);
             })
             .UseNLog();

Let’s do the logging using ILogger instance in any part of the code,

[HttpGet("{id}")]
public ActionResult<IEnumerable<string>> Get(int id)
{
    _logger.LogInformation("Start : Getting item details for {ID}", id);
 
    List<string> list = new List<string>();
 
    list.Add("A");
    list.Add("B");
 
    _logger.LogInformation($"Completed : Item details for  {{{string.Join(", ", list)}}}");
 
    return list;
}

Below is how the logs will captured in the NLog SQL table,

Adding Database provider

If not using SQL provider then please use fully qualified name of the provider connection type.

MySQL Client

dbProvider="MySql.Data.MySqlClient.MySqlConnection, MySqlConnector"

SQL Client

dbProvider="Microsoft.Data.SqlClient.SqlConnection, Microsoft.Data.SqlClient"

SQLite client

dbProvider="Microsoft.Data.Sqlite.SqliteConnection, Microsoft.Data.Sqlite"

Summary

NLog simplifies and helps us enabling logging in a few simple steps and address the Database logging requirements easily. Currently, high-end logging requirements like Database or Files are not yet available through the .NET Core framework and certainly, we need to depend on custom or existing external solutions and NLog suffices that requirement easily.