Logging to Database using NLog in ASP.NET Core Web API

Logging to SQL Server Database using NLog in ASP.NET Core Web API

In this article, I will discuss Logging into a SQL Server Database with NLog in ASP.NET Core Web API Application with Examples. Please read our previous article discussing Logging using NLog in the ASP.NET Core Web API Application.

Logging to SQL Server Database using NLog in ASP.NET Core Web API

Logging is an essential aspect of any application for tracking its behavior, errors, and performance metrics. NLog is a flexible and popular logging framework for .NET applications, and integrating it with an ASP.NET Core Web API for logging to an SQL Server database involves several steps. Let us first create a new ASP.NET Core Web API Project named NLogDemo.

Step 1: Create the Database Table

First, you’ll need a table in your SQL Server database to store the log entries. Please use the following SQL script to create the LogEntries table to store log data. We created the LogEntries table with columns for storing the log date, level, message, logger, and exception details.

CREATE DATABASE LoggingDB;
GO

Use LoggingDB;
GO

CREATE TABLE LogEntries (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Date DATETIME,
    Level VARCHAR(50),
    Message VARCHAR(MAX),
    Logger VARCHAR(250),
    Exception VARCHAR(MAX)
);
GO

Here’s a breakdown of each column and its purpose from the perspective of logging with NLog:

  • Id: This is an auto-incrementing integer that serves as the primary key for the LogEntries table. Each log entry gets a unique ID, starting from 1 and incrementing by 1 for each new entry. This column ensures that each log entry can be uniquely identified and referenced.
  • Date: The Date column records the date and time the log entry was created. This is important for understanding when specific events occurred within the application and is useful for tracing events and issues.
  • Level: The Level column represents the severity or importance of the log entry. NLog supports various logging levels such as Trace, Debug, Info, Warn, Error, and Fatal. This categorization helps in filtering logs based on their severity, enabling developers or system administrators to focus on the most important logs, like errors and warnings, during troubleshooting.
  • Message: The Message column stores the actual log message. 
  • Logger: The Logger column records the name of the logger that generated the log entry. In NLog, a logger is typically associated with a specific area of an application, such as the fully qualified class name. This helps identify which part of the application the log entry originated from, facilitating easier debugging and monitoring.
  • Exception: Finally, the Exception column stores information about any exceptions that occurred. This will include the exception message, stack trace, and any other details NLog captures about the exception. This column is particularly useful for diagnosing errors and understanding the circumstances under which they occurred.
Step 2: Install Required NuGet Packages

You need to add the NLog and its SQL Server-targeted Packages to your project. You can do this via NuGet Package Manager or the Package Manager Console. The packages you need are

  1. NLog: This is the core package for NLog. It allows developers to log messages to various targets (like files, consoles, databases, etc.) and control the logging level (e.g., Debug, Information, Error). 
  2. NLog.Web.AspNetCore: This package extends NLog with specific features for ASP.NET Core applications. It provides additional logging capabilities to web applications, such as logging web request details, integrating with ASP.NET Core’s dependency injection system, etc.
  3. NLog.Database: This package is designed to enable logging into a database with NLog. It includes the necessary configuration options to log messages directly to a database table.
  4. Microsoft.Data.SqlClient: This package is the data provider for SQL Server. In the context of logging with NLog (especially when using the NLog.Database package), Microsoft.Data.SqlClient is required to establish connections to SQL Server databases, execute commands, and write log entries to the database. 

You can install these via the NuGet Package Manager Console with the following commands:

Install-Package NLog
Install-Package NLog.Web.AspNetCore
Install-Package NLog.Database
Install-Package Microsoft.Data.SqlClient

Step 3: Configure NLog

If you haven’t already, create a file named nlog.config in your ASP.NET Core project root directory and then copy and paste the following code. This file contains the configuration for NLog, including the database target and the layout of your log messages. Ensure that this file is set to copy to the output directory on the build. 

<?xml version="1.0" ?>
<nlog xmlns="http://www.nlog-project.org/schemas/NLog.xsd"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      autoReload="true"
      internalLogLevel="Warn"
      internalLogFile="internal-nlog.txt">

  <extensions>
    <add assembly="NLog.Web.AspNetCore"/>
  </extensions>

  <targets async="true">
    <target name="database" xsi:type="Database" connectionString="Server=LAPTOP-6P5NK25R\SQLSERVER2022DEV;Database=LoggingDB;Integrated Security=True;TrustServerCertificate=True;App=EntityFramework">
      <commandText>
        insert into LogEntries (Date, Level, Message, Logger, Exception) values (@time_stamp, @level, @message, @logger, @exception);
      </commandText>

      <parameter name="@time_stamp" layout="${date}" />
      <parameter name="@level" layout="${level}" />
      <parameter name="@message" layout="${message}" />
      <parameter name="@logger" layout="${logger}" />
      <parameter name="@exception" layout="${exception:tostring}" />
    </target>

    <!-- Ignore Microsoft and System namespaces -->
    <target xsi:type="Null" name="blackMicrosoftSystem" />

  </targets>

  <rules>
    <!--Ignore Microsoft -->
    <logger name="Microsoft.*" minlevel="Trace" writeTo="blackMicrosoftSystem" final="true" />
    <!--Ignore System -->
    <logger name="System.*" minlevel="Trace" writeTo="blackMicrosoftSystem" final="true" />
    
    <!--Catch All -->
    <logger name="*" minlevel="Trace" writeTo="database"/>
  </rules>
</nlog>

Here is a breakdown of the above configuration file:

Global Configuration
  • autoReload: When set to true, NLog will automatically reload the configuration if the config file changes. This is useful during development or in environments where configuration changes must be applied without restarting the application.
  • internalLogLevel: Set to Warn specifies the minimum log level of internal NLog messages that should be written in the internal log file (internal-nlog.txt). This is useful for diagnosing issues with NLog itself.
  • internalLogFile: Specifies the file where internal NLog messages (of the configured level or higher) will be logged.
Extensions
  • NLog.Web.AspNetCore: This extension enables logging capabilities for ASP.NET Core applications, providing additional functionality and ease of integration with ASP.NET Core features.
Targets

Targets define where the logs should be written. In this configuration, there are two targets defined:

  • database: This target is configured to log messages to a database table named LogEntries. It uses the SQL Server database connection string and specifies an SQL INSERT statement to add log entries to the table. Each log attribute (like timestamp, level, message, logger, and exception) is mapped to a corresponding column in the database table. The target is set to async=”true”, meaning log writing will be performed asynchronously to improve application performance.
  • Null (blackMicrosoftSystem): This target discards the log messages it receives. It’s used here to ignore logs from specific namespaces without storing them anywhere.
Rules

Rules define which log messages should be written to which targets based on the logger name and the minimum log level:

  • Ignoring Microsoft and System namespaces: The first two rules route logs from Microsoft.* and System.* namespaces to the blackMicrosoftSystem target, effectively discarding them. This is commonly done to reduce noise from framework-level logs that are not relevant to the application’s operational logging.
  • Catch All: The final rule captures logs from all sources (name=”*”) at all levels (minlevel=”Trace”) and writes them to the database target. This ensures that all other logs not explicitly ignored are stored in the database for future reference or analysis.

Note: Ensure you replace YOUR SERVER and DATABASE with your actual SQL Server and database name. Set the appropriate authentication method as needed. In the Properties of nlog.config, set Copy to Output Directory to Copy if newer. This ensures the configuration file is available in the output directory after the application has been built.

Step 4: Register NLog in Program.cs

In your Program.cs file, register NLog by adding UseNLog() to the host builder. Here’s how it might look:

var builder = WebApplication.CreateBuilder(args);

// Configure NLog for ASP.NET Core
builder.Logging.ClearProviders(); // Clears default logging providers.
builder.Host.UseNLog(); // Use NLog as the logging provider.
Step 5: Use Logging in Your Application

Now, you can inject ILogger<T> into your controllers or services and use it for logging. Here’s an example of logging from a controller. This will send your logs to the SQL Server database according to the configuration you’ve defined in the nlog.config file. Create an API Empty Controller named TestController and then copy and paste the following code:

using Microsoft.AspNetCore.Mvc;
namespace NLogDemo.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class TestController : ControllerBase
    {
        private readonly ILogger<TestController> _logger;

        public TestController(ILogger<TestController> logger)
        {
            _logger = logger;
        }

        [HttpGet]
        public IActionResult Get()
        {
            try
            {
                _logger.LogTrace("This is a Trace log, the most detailed information.");
                _logger.LogDebug("This is a Debug log, useful for debugging.");
                _logger.LogInformation("This is an Information log, general info about app flow.");
                _logger.LogWarning("This is a Warning log, indicating a potential issue.");
                _logger.LogCritical("This is a Critical log, indicating a serious failure in the application.");

                //Simulating an error situation
                int x = 10, y = 0;
                int z = x / y;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"This is an Error log, indicating a failure in the current operation.");
            }

            return Ok("Check your logs to see the different logging levels in action!");
        }
    }
}
Step 6: InvariantGlobalization set to False

Open the project properties file and then set the InvariantGlobalization to false as follows:

<InvariantGlobalization>false</InvariantGlobalization>

The InvariantGlobalization setting in an ASP.NET Core project file relates to the handling of globalization and localization features within the application.

When set to false, this setting indicates that the application opts out of using the invariant culture. The invariant culture is a culture that is culture-insensitive; it is associated with the English language but not with any country/region. When applications target the invariant culture, they can have more predictable behavior regarding string comparison, case changes, and date/time formatting, independent of the locale in which the application is running.

However, opting out of the invariant culture (<InvariantGlobalization>false</InvariantGlobalization>) means the application will use the globalization features specific to the culture settings of the operating system on which it is running. This allows for more localized behavior, such as displaying dates, times, numbers, and strings according to the local culture’s formats. This setting is crucial for applications that need to support multiple languages and regional settings to provide a more personalized experience for users worldwide.

So, setting <InvariantGlobalization> to false enables ASP.NET Core applications to utilize culture-specific settings, which is essential for applications targeting a global audience with needs for localization and internationalization.

Testing and Verification

Finally, run your application and perform actions (access the above API endpoint) that trigger logging. Then, check your SQL Server database’s LogEntries table (or whatever table you have configured) to ensure that the logs are being correctly recorded:

How to Customize the NLog Table in SQL Server

Let us see how we can customize the NLog table in the SQL Server. We want to add additional columns to the NLog table, such as UserId, UniqueId, etc. In this demo, we are going to add the UniqueId column to the NLog table, which is going to be a GUID. Add the UniqueId column to your SQL Server table by executing the following SQL Statement:

ALTER TABLE LogEntries ADD UniqueId UNIQUEIDENTIFIER;

Update your NLog configuration to include the new column. So, modify the nlog.config file as follows. In the following setup, the ${event-properties:item=UniqueId} layout retrieves a UniqueId from the log event’s properties you specify in your application code.

<?xml version="1.0" ?>
<nlog xmlns="http://www.nlog-project.org/schemas/NLog.xsd"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      autoReload="true"
      throwExceptions="false"
      internalLogLevel="Warn"
      internalLogFile="internal-nlog.txt">

  <extensions>
    <add assembly="NLog.Web.AspNetCore"/>
  </extensions>

  <targets async="true">
    <target name="database" xsi:type="Database" connectionString="Server=LAPTOP-6P5NK25R\SQLSERVER2022DEV;Database=LoggingDB;Integrated Security=True;TrustServerCertificate=True;App=EntityFramework">
      <commandText>
        insert into LogEntries (Date, Level, Message, Logger, Exception, UniqueId) values (@time_stamp, @level, @message, @logger, @exception, @uniqueId);
      </commandText>

      <parameter name="@time_stamp" layout="${date}" />
      <parameter name="@level" layout="${level}" />
      <parameter name="@message" layout="${message}" />
      <parameter name="@logger" layout="${logger}" />
      <parameter name="@exception" layout="${exception:tostring}" />

      <!-- Ensure the GUID passed to NLog is inserted into UniqueId -->
      <parameter name="@uniqueId" layout="${event-properties:item=UniqueId}" />
    </target>

    <!-- Ignore Microsoft and System namespaces -->
    <target xsi:type="Null" name="blackMicrosoftSystem" />

  </targets>

  <rules>
    <!--Ignore Microsoft -->
    <logger name="Microsoft.*" minlevel="Trace" writeTo="blackMicrosoftSystem" final="true" />
    <!--Ignore System -->
    <logger name="System.*" minlevel="Trace" writeTo="blackMicrosoftSystem" final="true" />
    
    <!--Catch All -->
    <logger name="*" minlevel="Trace" writeTo="database"/>
  </rules>
</nlog>
Logging with a GUID

When logging an event where you want to include a GUID, you’d do something like the following in your application. So, modify the TestController as follows:

using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Logging;
using NLog;
using System;
namespace NLogDemo.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class TestController : ControllerBase
    {
        private readonly ILogger<TestController> _logger;

        public TestController(ILogger<TestController> logger)
        {
            _logger = logger;
        }

        [HttpGet]
        public IActionResult Get()
        {
            var UniqueId = Guid.NewGuid();

            try
            {
                _logger.LogTrace("{UniqueId} This is a Trace log, the most detailed information.", UniqueId);
                _logger.LogDebug("{UniqueId} This is a Debug log, useful for debugging.", UniqueId);
                _logger.LogInformation("{UniqueId} This is an Information log, general info about app flow.", UniqueId);
                _logger.LogWarning("This is a Warning log, indicating a potential issue.{UniqueId}", UniqueId);
                _logger.LogCritical("This is a Critical log, indicating a serious failure in the application.");

                //Simulating an error situation
                int x = 10, y = 0;
                int z = x / y;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "{UniqueId} This is an Error log, indicating a failure in the current operation.", UniqueId);
            }

            return Ok("Check your logs to see the different logging levels in action!");
        }
    }
}

Your logging requirements might require you to add more columns or change existing ones. Always update your NLog configuration and the SQL Server table schema to match.

By following these steps, you can customize the NLog logs table in SQL Server to suit your specific needs. Remember, the key to customization lies in the NLog configuration file and the schema of the SQL Server table. Always ensure they are in sync for seamless logging.

In the next article, I will discuss Serilog vs NLog in ASP.NET Core Web API Application. In this article, I explain how to log into an SQL Server Database with NLog in an ASP.NET Core Web API Application with examples. I hope you enjoy this article, Logging to a SQL Server Database with NLog in ASP.NET Core Web API Application.

Leave a Reply

Your email address will not be published. Required fields are marked *