Writing Logs to SQL Server Using NLog

We have previously learned how to log messages to a File using NLog. In this article, we are going to learn how to use Nlog to log messages into SQL Server.
We are going to see how we can configure an ASP.NET Core Web API project to support NLog and how we can configure NLog using an XML configuration file.

So, let’s start

Creating the Database and Table for Logging

Let’s start by creating the database and a table that will store our log messages. Using SQL Server Management Studio, let’s create our database and name it Nlog. To create the table and columns, right-click on the Nlog database, select New Query and execute the SQL script:

Support Code Maze on Patreon to get rid of ads and get the best discounts on our products!

CREATE TABLE Logs(
    Id int NOT NULL PRIMARY KEY IDENTITY(1,1),
    CreatedOn datetime NOT NULL,
    Level nvarchar(10),
    Message nvarchar(max),
    StackTrace nvarchar(max),
    Exception nvarchar(max),
    Logger nvarchar(255),
    Url nvarchar(255)
);

We are creating a Logs table with 8 columns. We are giving each column a name that describes the kind of log message it will store. After executing the script, we are going to have a new table with all the columns in the database:

Creating the Project

Now let’s create a new ASP.NET Web API project.

Open VS 2019 and select Create a new project, choose ASP.NET Web API and select Next.

Let’s name our project Nlog.API and the solution as Nlog:

Finally, let’s choose .NET 5.0 as our target framework and select Create:

Installing Packages and Configuring NLog

Using the Nuget Package Manager on Visual Studio, we are going to install these packages:

• NLog 4.7.10
• NLog.Web.AspNetCore 4.13.0
• System.Data.SqlClient 4.8.2

Note:
Not installing the System.Data.SqlClient package will result in this error:

Failed to create ConnectionType from DBProvider=sqlserver
Exception: System.IO.FileNotFoundException

Updating Program.cs

Now, let’s update our Program.cs file to set up NLog:

namespace Nlog.API
{
    public class Program
    {
        public static void Main(string[] args)
        {
            var logger = NLogBuilder.ConfigureNLog("nlog.config").GetCurrentClassLogger();

            try
            {
                CreateHostBuilder(args).Build().Run();

            }
            catch (Exception exception)
            {
                logger.Error(exception, "Stopped program because of exception");
                throw;
            }
            finally
            {
                NLog.LogManager.Shutdown();
            }
        }

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


}

To start logging, we need to create a Logger instance. Before creating the Logger instance, we are configuring Nlog by passing the configuration file nlog.config which we are going to create in the next section. The GetCurrentClassLogger() method returns the Logger instance with the name of the current class (Nlog.API.Program).

The logging.ClearProviders() method clears the default logging providers added by Host.CreateDefaultBuilder and we are calling UseNLog(), an extension method for IHostBuilder, to register NLog as a logging provider for our entire application and set it up for dependency injection.

Updating appsettings.json

By default, the minimum log level is set to Information in our appsettings.json file. This setting overrides the minimum log level we specify in Program.cs when we call logging.SetMinimumLevel(LogLevel.Trace). Let’s update the Default key in the Logging section of our appsettings.json file. Remember to update any environment-specific appsettings.json file as well:

"Logging": {
    "LogLevel": {
      "Default": "Trace",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
 }

Creating NLog.config File

Now, let’s create our nlog.config file.

In the project root, let’s create a text file and name it nlog.config:

<?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="Trace"
      internalLogFile="C:\Nlog\logs\internalLog.txt">			
                
    <targets>
        <target xsi:type="Database"
      name="dbTarget"
      connectionString="Data Source=localhost;Initial Catalog=Nlog;Integrated Security=true;"
      commandText="INSERT INTO Logs(CreatedOn,Message,Level,Exception,StackTrace,Logger,Url) VALUES (@datetime,@msg,@level,@exception,@trace,@logger,@url)">
            <parameter name="@datetime" layout="${date}" />
            <parameter name="@msg" layout="${message}" />
            <parameter name="@level" layout="${level}" />
            <parameter name="@exception" layout="${exception}" />
            <parameter name="@trace" layout="${stacktrace}" />
            <parameter name="@logger" layout="${logger}" />
            <parameter name="@url" layout="${aspnet-request-url}" />
        </target>
    </targets>

    <rules>
        <logger name="*" minlevel="Trace" writeTo="dbTarget" />
    </rules>
</nlog>

Our configuration file contains a single target and a rule. We create a database target and insert the values that will be stored in our Logs table each time we log a message.

The commandText attribute contains an SQL statement to insert the values. We are not passing the values directly, instead, we are passing parameter names(e.g. @msg) that hold the values. The value associated with each parameter name is what gets returned from the layout attribute. Each layout attribute returns the value of the layout renderer that we pass to it.

${date} and ${logger} are examples of layout renderers that return the current date and time and the logger name respectively.

NLog provides us with several layout renderers. For a full list of all layout renderers, please check here.

Note:
A misspelled name of any of the layout renderers, for example, ${trace} instead of ${stacktrace} will result in this error:

Error parsing layout
Exception: System.ArgumentException: LayoutRenderer cannot be found

Ensure to change the connectionString and internalLogFile path to match yours. The internal log file is useful for debugging NLog.

Writing Logs in the Controller

Using the WeatherForecastController class that was generated for us in the Controller folder, let’s edit it to log messages:

namespace Nlog.API.Controllers
{
    [ApiController]
    [Route("[controller]")]
    public class WeatherForecastController : ControllerBase
    {

        private readonly ILogger<WeatherForecastController> _logger;

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


        [HttpGet]
        public IEnumerable<string> Get()
        {
            _logger.LogDebug("This is a debug message");
            _logger.LogInformation("This is an info message");  
            _logger.LogWarning("This is a warning message ");
            _logger.LogError(new Exception(), "This is an error message");

            return new string[] { "Cool", "Weather" };
        }
    }
}

Now let’s launch our application and navigate to https://localhost:5001/weatherforecast.
The page displays an array of two strings: Cool, Weather.

If everything is set up correctly, we should see the log messages stored in the Logs table:                                                                   

Log messages from Microsoft.Hosting.Lifetime are also stored because of the rule in our configuration file:  

<logger name="*" minlevel="Trace" writeTo="dbTarget" />

The wildcard character (*) tells NLog to include all log messages including messages from Microsoft. If we want to skip all log messages from Microsoft and log only our messages, we can change the rule:

<logger name="Microsoft.*" maxlevel="off" final="true" />
<logger name="*" minlevel="Trace" writeTo="dbTarget" />

This rule will discard log messages from Microsoft.Hosting.Lifetime. However, according to the NLog documentation, since we are using only NLog as our logging provider, discarding hosting lifetime startup messages can cause hosting environments such as Docker and Visual studio not to see our application as started.

Lastly, if any error occurs during the setup, NLog logs its errors in the path specified in internalLogFile of our config file. This file helps in debugging.

Conclusion

In this article, we have seen how to configure and use NLog to log messages into SQL Server. To log messages, we learned about creating a database target and using layout renderers provided by NLog as values for our table columns in SQL Server. We also saw how to configure an ASP.NET Core Web API project to use NLog.

Liked it? Take a second to support Code Maze on Patreon and get the ad free reading experience!