Back to: ASP.NET Core Web API Tutorials

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 essential for tracking application behavior, errors, and performance. NLog is a flexible logging framework that can write log events to various targets, including a SQL Server database. Centralizing logs in a database allows for structured querying, filtering by severity, and easier analysis across different application instances.

To log messages to a SQL Server database using NLog in an ASP.NET Core application, we need to configure the database target in the NLog.config file and set up the appropriate connection strings and SQL commands. So, let us proceed and implement this step by step. We will work with the same application that we created in our previous article.

Step 1: Create the Database Table

First, we need to create a table in the SQL Server database where the log entries will be stored. Please use the following SQL script to create the LoggingDB database and LogEntries table to store log data. This table includes fields to record the log date, severity level, message, logger name, and exception details.

-- Create a new database named LoggingDB
CREATE DATABASE LoggingDB;
GO

-- Use the newly created database
USE LoggingDB;
GO

-- Create the LogEntries table with standard logging columns
CREATE TABLE LogEntries (
    Id INT IDENTITY(1,1) PRIMARY KEY,   -- Unique auto-incrementing primary key for each log entry
    Date DATETIME,                      -- Timestamp for when the log entry was created
    Level VARCHAR(50),                  -- Log level (Trace, Debug, Info, Warn, Error, Fatal)
    Message VARCHAR(MAX),               -- The log message content
    Logger VARCHAR(250),                -- Name of the logger (usually the class name)
    Exception VARCHAR(MAX)              -- Exception details, if any
);
GO

Column Explanations:

Each column is defined to ensure that logs can be traced and filtered appropriately.

• 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.
• 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 filter 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 logger’s name 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: The Exception column stores information about any exceptions that occurred. This will include the exception message, stack trace, and other details NLog captures about the exception. This column is useful for diagnosing errors.

Step 2: Install Required NuGet Packages

Next, we need to add the NLog and its SQL Server-targeted Packages. The following packages allow our application to send log messages to SQL Server and integrate NLog with ASP.NET Core. You can do this via NuGet Package Manager for Solution or using the Package Manager Console:

• NLog.Web.AspNetCore: This package provides support for integrating NLog with ASP.NET Core.
• NLog.Database: This package supports logging into a database with NLog.
• Microsoft.Data.SqlClient: This package is the data provider for SQL Server when using ADO.NET Core.

We have already installed NLog.Web.AspNetCore package into our project. Let us install the remaining packages using the Package Manager Console by executing the following commands:

• Install-Package NLog.Database
• Install-Package Microsoft.Data.SqlClient

Step 3: Configure NLog

We have already created the NLog.config file in the project root directory. So, copy and paste the following code to the NLog.config file. This file contains the configuration for NLog, including the database target. 

<?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="internal-nlog.txt"
   throwConfigExceptions="true">

 <!-- Define various log targets where logs will be written -->
 <targets>

  <!-- AsyncWrapper target for asynchronous Database logging -->
  <target xsi:type="AsyncWrapper"
                name="asyncFileWrapper">

   <!-- 
    Log to SQL Server 
    commandText: Specifies the SQL command to insert log entries into the LogEntries table
    @date: The timestamp of the log event.
    @level: The log level (e.g., Info, Error).
    @logger: The name of the logger.
    @message: The log message.
    @exception: Exception details, if any.
   -->

   <target xsi:type="Database" name="database"
     connectionString="Server=LAPTOP-6P5NK25R\SQLSERVER2022DEV;Database=LoggingDB;Integrated Security=True;TrustServerCertificate=True;"
     commandText="INSERT INTO LogEntries (Date, Level, Message, Logger, Exception) VALUES (@date, @level, @message, @logger, @exception)">

    <!-- Parameter mapping -->
    <parameter name="@date" layout="${date}" />
    <parameter name="@level" layout="${level:uppercase=true}" />
    <parameter name="@logger" layout="${logger}" />
    <parameter name="@message" layout="${message}" />
    <parameter name="@exception" layout="${exception:tostring}" />
   </target>
  </target>

  <!-- Console target: Display logs in the console window (without async wrapper, as console writes are usually fast) -->
  <target xsi:type="Console"
    name="console"
    layout="${longdate}|${level:uppercase=true}|${logger}|${message} ${exception:format=ToString}" />

  <!-- Null target: Discard logs, used to filter out logs from specific namespaces -->
  <target xsi:type="Null" name="blackhole" />
 </targets>

 <!-- Define logging rules to control which logs go to which targets -->
 <rules>
  <!-- Filter out logs from the "Microsoft" namespace by sending them to the "blackhole" target -->
  <logger name="Microsoft.*" minlevel="Trace" writeTo="blackhole" final="true" />

  <!-- Filter out logs from the "System" namespace by sending them to the "blackhole" target -->
  <logger name="System.*" minlevel="Trace" writeTo="blackhole" final="true" />

  <!-- Send all logs with level Information or higher to the database target -->
  <logger name="*" minlevel="Information" writeTo="database" />

  <!-- Send all logs with level Debug or higher to the console target -->
  <logger name="*" minlevel="Debug" writeTo="console" />
 </rules>
</nlog>

Database Configuration Explanation:

• Target Definition (Database): The <target> tag with xsi:type=”Database” defines the database target.
• Connection String: Specify the connection string in the connectionString attribute. Ensure you replace YOUR SERVER and DATABASE with your SQL Server and database name. Set the appropriate authentication method as needed.
• Command Text: The commandText contains the SQL statement to insert logs into the LogEntries table.
• Parameters: Define parameters using <parameter> tags that match the columns in the database table.

Note: Make sure that the NLog.config file is set to Copy to Output Directory:

• Right-click on NLog.config in Solution Explorer.
• Select Properties.
• Set Copy to Output Directory to Copy Always.

Step 4: Register NLog in Program.cs

Modify the Program.cs class file as follows to configure NLog as your logging provider.

using NLog.Web;
namespace LoggingDemo
{
    public class Program
    {
        public static void Main(string[] args)
        {
            var builder = WebApplication.CreateBuilder(args);

            // Add services to the container.
            builder.Services.AddControllers();
            builder.Services.AddEndpointsApiExplorer();
            builder.Services.AddSwaggerGen();

            // Configure NLog for ASP.NET Core
            // Clears default logging providers.
            builder.Logging.ClearProviders();

            // Use NLog as the logging provider.
            builder.Host.UseNLog(); 

            var app = builder.Build();

            // Configure the HTTP request pipeline.
            if (app.Environment.IsDevelopment())
            {
                app.UseSwagger();
                app.UseSwaggerUI();
            }

            app.UseHttpsRedirection();

            app.UseAuthorization();

            app.MapControllers();

            app.Run();
        }
    }
}

Step 5: Use Logging in Controller

Now, we can inject ILogger<T> in controllers or services to log messages to the SQL Server database. For a better understanding, create an API Empty Controller named Test Controller and then copy and paste the following code:

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

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

        [HttpGet("all-logs")]
        public IActionResult LogAllLevels()
        {
            _logger.LogTrace("LogTrace: Entering the LogAllLevels endpoint with Trace-level logging.");

            // Simulate a variable and log it at Trace level
            int calculation = 5 * 10;
            _logger.LogTrace("LogTrace: Calculation value is {calculation}", calculation);

            _logger.LogDebug("LogDebug: Initializing debug-level logs for debugging purposes.");

            // Log some debug information
            var debugInfo = new { Action = "LogAllLevels", Status = "Debugging" };
            _logger.LogDebug("LogDebug: Debug information: {@debugInfo}", debugInfo);

            _logger.LogInformation("LogInformation: The LogAllLevels endpoint was reached successfully.");

            // Simulate a condition that might cause an issue
            bool resourceLimitApproaching = true;
            if (resourceLimitApproaching)
            {
                _logger.LogWarning("LogWarning: Resource usage is nearing the limit. Action may be required soon.");
            }

            try
            {
                // Simulate an error scenario
                int x = 0;
                int result = 10 / x;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "LogError: An error occurred while processing the request.");
            }

            // Log a critical error scenario
            bool criticalFailure = true;
            if (criticalFailure)
            {
                _logger.LogCritical("LogCritical: A critical system failure has been detected. Immediate attention is required.");
            }

            return Ok("All logging levels demonstrated in this endpoint.");
        }
    }
}

Testing and Verification

Finally, run your application and perform actions (access the above API endpoint) that trigger logging. Then, check the LogEntries table (or whatever table you have configured) to ensure that the logs are being correctly recorded, as shown in the below image:

How to Customize the NLog table in SQL Server

Let’s see how we can customize the NLog table in SQL Server. We want to add some additional columns, such as ServerIP and UniqueId.

• UniqueId: Stores a UniqueId per request; it will mostly be a GUID.
• ServerIP: Stores the server’s IP address generating the log (hardcode).
• ApplicationName: Helps identify which application instance (or service) generated the log.

Alter the SQL Table:

Add new columns to the LogEntries table:

-- Add columns for UniqueId, ServerIP, and ApplicationName
ALTER TABLE LogEntries ADD UniqueId VARCHAR(250), ServerIP VARCHAR(100), ApplicationName VARCHAR(100);
GO

Update NLog.config:

Modify the <target> configuration to include ServerIP and ApplicationName parameters with hardcoded values. The layouts for @serverIp and @appName use constant strings. This means every log entry will carry these fixed values unless overridden. In the following configuration parameter mapping, the ${event-properties:item=UniqueId} retrieves a UniqueId from the log event’s properties that we need to specify in our application code. So, modify the NLog.config file as follows.

<?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="internal-nlog.txt"
   throwConfigExceptions="true">

 <!-- Define various log targets where logs will be written -->
 <targets>

  <!-- AsyncWrapper target for asynchronous Database logging -->
  <target xsi:type="AsyncWrapper"
                name="asyncFileWrapper">

   <!-- Log to SQL Server -->
   <target xsi:type="Database" name="database"
     connectionString="Server=LAPTOP-6P5NK25R\SQLSERVER2022DEV;Database=LoggingDB;Integrated Security=True;TrustServerCertificate=True;"
     commandText="INSERT INTO LogEntries (Date, Level, Message, Logger, Exception, UniqueId, ServerIP, ApplicationName) 
     VALUES (@date, @level, @message, @logger, @exception, @uniqueId, @serverIP, @appName)">

    <!-- Parameter mapping -->
    <parameter name="@date" layout="${date}" />
    <parameter name="@level" layout="${level:uppercase=true}" />
    <parameter name="@logger" layout="${logger}" />
    <parameter name="@message" layout="${message}" />
    <parameter name="@exception" layout="${exception:tostring}" />
    <parameter name="@uniqueId" layout="${event-properties:item=UniqueId}" />
    <!-- Hardcoded ServerIP; replace with your actual server IP if needed -->
    <parameter name="@serverIP" layout="192.168.1.100" />
    <!-- Hardcoded Application Name -->
    <parameter name="@appName" layout="MyWebAPI" />
   </target>
  </target>

  <!-- Console target: Display logs in the console window (without async wrapper, as console writes are usually fast) -->
  <target xsi:type="Console"
    name="console"
    layout="${longdate}|${level:uppercase=true}|${logger}|${message} ${exception:format=ToString}" />

  <!-- Null target: Discard logs, used to filter out logs from specific namespaces -->
  <target xsi:type="Null" name="blackhole" />
 </targets>

 <!-- Define logging rules to control which logs go to which targets -->
 <rules>
  <!-- Filter out logs from the "Microsoft" namespace by sending them to the "blackhole" target -->
  <logger name="Microsoft.*" minlevel="Trace" writeTo="blackhole" final="true" />

  <!-- Filter out logs from the "System" namespace by sending them to the "blackhole" target -->
  <logger name="System.*" minlevel="Trace" writeTo="blackhole" final="true" />

  <!-- Send all logs with level Information or higher to the database target -->
  <logger name="*" minlevel="Information" writeTo="database" />

  <!-- Send all logs with level Debug or higher to the console target -->
  <logger name="*" minlevel="Debug" writeTo="console" />
 </rules>
</nlog>

Logging with a GUID

When logging an event where we want to include a GUID, we need to specify the UniqueId placeholder and supply a value for the placeholder. For a better understanding, please modify the Test Controller as follows:

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

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

        [HttpGet("all-logs")]
        public IActionResult LogAllLevels()
        {
            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!");
        }
    }
}

Now, run the application, and it should work as expected.

Benefits of Logging to SQL Server Database using NLog:

The following are the benefits of using NLog to Log the data into SQL Server Database.

• Centralized Log Storage: By logging into a SQL Server database, all log entries from different application instances or services are stored in a single, centralized location, simplifying log management and analysis.
• Structured Data: SQL Server stores logs in structured tables, enabling SQL queries to filter, sort, and analyze log data efficiently. For faster debugging and reporting, developers and administrators can filter logs by levels (e.g., Error, Warning, Information), dates, or custom properties.
• Performance and Scalability: SQL Server can handle many log entries, making it suitable for enterprise applications with high traffic and extensive logging needs. Supports the use of indexing and partitioning to improve log search performance.
• Enhanced Security: SQL Server offers robust security features, such as authentication, authorization, and encryption, ensuring that logs are stored securely. Logs in an SQL server database are less susceptible to unauthorized access than flat file logs.

Following these steps, we can effectively set up and customize NLog for SQL Server logging in ASP.NET Core Web API Applications.

In the next article, I will discuss Serilog vs. NLog in the 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 an ASP.NET Core Web API Application.”