NLog Database Logging in ASP.NET Core

Today in this article, we will see how to perform NLog Database Logging in ASP.NET Core with an example in the ASP.NET Core application.

NLog is a highly efficient logging framework that helps us enable logging to almost all logging source formats including Console, File, Database logging, etc.

Today in this article, we will cover below aspects,

High-end logging requirements like Database or File or Rolling File logging providers 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 in logging to different logging providers like Console, Event, EventSource, etc.

Microsoft recommends using a third-party logger framework like a Serilog or NLog or Log4Net for other high-end logging requirements like Database or File/Rollingfile logging.

We shall be targeting SQL Database as a logging source.

NLog supports the 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 a 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 install the packages from Nuget Package Manager,

NLog Database Configuration

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

DDL – Create SQL Table schema for NLog logging table

I have used the below script to create the NlogDBLog table.

Sample DDL file can be found on GitHub,

https://github.com/thecodebuzz/nlog-database-logging-config-file-example/blob/master/DDL

 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 the 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 fields as below,

• name – Our custom target name for the given provider identification. It should be the same as writeTo field in the 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 in the Application

Configuration can be loaded using dependency injection or manually was not possible using custom approaches.

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

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

Below is the complete code base,

The above technique is declarative Nlog logging instead of DI in the application pipeline.

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 the 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 be captured in the NLog SQL table,

Adding Database provider

You can also use any other provider like MySQL or SQLite providers. Please use the 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"

XML or Custom JSON Loading

One can also define configuration in XML or JSON or INI and loads them as required. Please refer article on Loading Configuration from XML.JSON, INI file in ASP.NET Core.

One can also use Typesafe configuration in code to map config key-value pairs to the complex type.

That’s All! Happy Coding!

Do you have any comments or ideas or any better suggestions to share?

Please sound off your comments below.

Happy Coding !!

Summary

NLog simplifies and helps us enable logging in a few simple steps and address the Database logging requirements easily.

Currently, high-end logging requirements like Databases 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.

Please bookmark this page and share it with your friends. Please Subscribe to the blog to receive notifications on freshly published(2024) best practices and guidelines for software design and development.