NLOG with SQL Server and PostgreSQL in .Net Core

Here I am providing the steps to configure the Nlog for logging into the database in .net core.

• Install NLog.Web.AspNetCore nuget package
• Now, add a nlog.config file manually in the project and put all the configurations like file log path, database settings, etc. in this file. I have explained each in detail below.

We are going to use NLog with the database, so let’s create a table first in dB. The table structure is shown in the below screenshot.

Now, update your nlog.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"
    throwConfigExceptions="true"
    internalLogLevel="info"
    internalLogFile="c:\temp\internal-nlog.txt">

<extensions>
  <add assembly="NLog.Web.AspNetCore"/>
</extensions>
<targets>
  <target name="database" xsi:type="Database" connectionString="" >
    <commandText>
      INSERT INTO "NLogs" (
      "Application", "Level", "Message", "Logger", "Callsite", "Exception", "Logged")
      VALUES ( @Application, @Level, @Message,
      @Logger, @Callsite, @Exception,@Logged);

    </commandText>

    <parameter name="@application" layout="AspNetCoreNlog" />
    <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}" />
    <parameter name="@logged" layout="${date}" />
  </target>
</targets>

<rules>
  <logger name="*"  minlevel="Debug" maxlevel="Error" final="true" writeTo="database" />
</rules>
</nlog>

Let’s discuss the nlog.config file layout.

Top-level elements

The root element is nlog in nlog.config file. The use of an XML namespace is optional but enables Intellisense in Visual Studio. We can set the internal log file path to internalLogFile. We can check the working log status in this file. So, if nlog is not working, you can check this file to know the issue.

NLog config is case-insensitive when not using a namespace and is case-sensitive when using a namespace.

You can use the following elements as children to nlog. Targets and rules are required in any configuration and others are optional and can be useful in advanced scenarios.

targets — defines log targets/outputs

rules — defines log routing rules

extensions — loads NLog extensions from the *.dll file

include– includes external configuration file

variable — sets the value of a configuration variable

The simplest configuration consists of one target and one rule (logger) that routes messages to the target.

Targets

The targets section defines log Targets. Each target is represented by a target element. There are two attributes required for each target:

name — target name

type — target type — such as “File”, “Database”, “Mail”, “Cloud”. When using namespaces this attribute is named xsi:type.

To know more about targets click on the below link

Here our log target is the database. So, inside the targets, we have defined our target as a database.

<target name=”database” xsi:type=”Database” connectionString=”” >

In the target tag, we’ll define the database connection string.

Note:- The default db provider will be SQL server if you are using any other database you will need to set the dbProvider.

<commandText>
INSERT INTO “NLogs” (“Application”, “Level”, “Message”, “Logger”, “Callsite”, “Exception”, “Logged”) VALUES (@Application, @Level, @Message, @Logger, @Callsite, @Exception, @Logged);
</commandText>

The statement inside the commandText is the insert SQL statement for inserting the logs in NLog table.

Now, declare all the parameters that are going to be inserted in the NLog table. The parameter node will contain the property name and Layout.

<parameter name=”@application” layout=”AspNetCoreNlog” />
 <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}” />
 <parameter name=”@logged” layout=”${date}” />

To know about the layout, please follow the below link

https://github.com/nlog/nlog/wiki/Configuration-file#layouts-and-layout-renderers

Rules

The rules section maps loggers to targets and log levels.

https://github.com/nlog/nlog/wiki/Configuration-file#rules

Now, in .net core application update the below line in Program.cs .

public static IWebHostBuilder CreateWebHostBuilder(string[] args) =>
           WebHost.CreateDefaultBuilder(args).UseNLog()
               .UseStartup<Startup>();

The Logging configuration specified in appsettings.json overrides any call to SetMinimumLevel. So either remove “Default”: or adjust it correctly to your needs.

{
“Logging”: {
“IncludeScopes”: false,
“LogLevel”: {
“Default”: “Trace”,
“Microsoft”: “Warning”,
“Microsoft.Hosting.Lifetime”: “Information”
}
},
“AllowedHosts”: “*”
}

Remember to also update any environment-specific configuration to avoid any surprises. Ex appsettings.Development.json

Write logs

Inject the ILogger in you controller:

using Microsoft.Extensions.Logging;
public class HomeController : Controller {
private readonly ILogger<HomeController> _logger;
public HomeController(ILogger<HomeController> logger)
{
_logger = logger;
_logger.LogDebug(1, “Log Debug”);
}
public IActionResult Index() {
_logger.LogInformation(“Hello, world!”);
return View();
}

Note: If you don’t see any logging in db, please check the file internallogfile (path set in the nlog.config file). You can check the issue with logging in this file.

To configure the NLog with Postgres, we just need to make a few changes.

Change the db configuration settings for the postgres in nlog.config file.

<target name=”database” xsi:type=”Database” dbProvider=”Npgsql.NpgsqlConnection, Npgsql” connectionString=”User ID=;Password=;Host=;port=;Database=;” >
<commandText>
INSERT INTO public.”NLogs” (“Application”, “Level”, “Message”, “Logger”, “Callsite”, “Exception”, “Logged”) VALUES (@Application, @Level, @Message, @Logger, @Callsite, @Exception,@Logged );
</commandText>

That’s it, rest things will be the same as the above (NLog with SQL server).