Database Logging Using NLog in .Net 6

In this post I will show you how to write logs into database table in your .Net 6 application using NLog. NLog is a flexible and free logging platform for various .NET platforms, including .NET standard. It makes it easy to write log to several targets. (database, file, console) and change the logging configuration on-the-fly. It gives high performance, easy-to-use, easy to extend and flexible to configure (as you shall also see from this post).

I will be using .Net 6 minimal API for this demonstration, however, you can apply the same concept in any other .Net project. I’ll also be using Microsoft SQL Server for the database.

Libraries

<PackageReference Include="Microsoft.Data.SqlClient" Version="5.0.0" />
<PackageReference Include="NLog" Version="5.0.4" />
<PackageReference Include="NLog.Database" Version="5.0.4" />
<PackageReference Include="NLog.Web.AspNetCore" Version="5.1.4" />

Log Database Table

Create a simple log table as below:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[Log] (
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [LogTime] [datetime] NOT NULL,
  [Logger] [nvarchar](250) NULL,
  [Level] [nvarchar](50) NOT NULL,
  [Message] [nvarchar](max) NOT NULL,
  [Exception] [nvarchar](max) NULL,
  CONSTRAINT [PK_dbo.Log] 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]

NLog Config

Create nlog.config (lowercase all) file in the root of your project.

<?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="Info"
      internalLogFile="c:\temp\internal-nlog-AspNetCore.txt">

	<!-- enable asp.net core layout renderers -->
	<extensions>
		<add assembly="NLog.Web.AspNetCore"/>
	</extensions>

	<targets>
		<target name="database" xsi:type="Database">
		
			<connectionString>server=localhost;Database=Test;Initial Catalog=Test;Integrated Security=True;TrustServerCertificate=True; </connectionString>

			<commandText>
				INSERT INTO [dbo].[Log]([LogTime],[Logger] ,[Level] ,[Message] ,[Exception])
				VALUES(@LogTime,@Logger, @Level,@Message,@Exception );
			</commandText>

			<parameter name="@LogTime" layout="${date}" />
			<parameter name="@Logger" layout="${logger}" />
			<parameter name="@Level" layout="${level}" />
			<parameter name="@Message" layout="${message}" />
			<parameter name="@Exception" layout="${exception:tostring}" />
		</target>
	</targets>

	<rules>
		<logger name="Microsoft.*" finalMinLevel="Warn" writeTo="database" />
		<logger name="Microsoft.AspNetCore.Hosting.*" finalMinLevel="Error" writeTo="database" />
		<logger name="*" minlevel="Info" writeTo="database" />
	</rules>
</nlog>

For more detail about the config, check this official documentation page.

The Microsoft Logging filters specified in appsettings.json are ignored by default when using NLog 5.0. Just make sure that NLog configuration rules are configured correctly. If one specify RemoveLoggerFactoryFilter = false for the NLog Logging Provider options, then it will use the filters specified in appsettings.json. See here on how to specify the NLog configuration in the appsettings.json.

Program.cs

var logger = LogManager.Setup().LoadConfigurationFromAppSettings().GetCurrentClassLogger();
try
{
    var builder = WebApplication.CreateBuilder(args);

    builder.Services.AddEndpointsApiExplorer();
    builder.Services.AddSwaggerGen();

    builder.Logging.ClearProviders();
    builder.Host.UseNLog();

    var app = builder.Build();

    if (app.Environment.IsDevelopment())
    {
        app.UseSwagger();
        app.UseSwaggerUI();
    }

    app.UseHttpsRedirection();

    app.Run();

}
catch (Exception ex)
{
    logger.Error(ex, "An error occurred.program because of exception");
    throw;
}
finally
{
    // Ensure to flush and stop internal timers/threads before application-exit (Avoid segmentation fault on Linux)
    LogManager.Shutdown();
}

Having put all the necessary configurations in place, we can now write log within our application.

var summaries = new[]
{
"Freezing", "Bracing", "Chilly", "Cool", "Mild", "Warm", "Balmy", "Hot", "Sweltering", "Scorching"
};

app.MapGet("/weatherforecast", ([FromServices] ILogger<Program> logger) =>
{
	logger.LogInformation("Test log from {p}", "weatherforecast");
        logger.LogError(new Exception("test exception log"), "Sample exception message");
	var forecast = Enumerable.Range(1, 5).Select(index =>
		new WeatherForecast
		(
			DateTime.Now.AddDays(index),
			Random.Shared.Next(-20, 55),
			summaries[Random.Shared.Next(summaries.Length)]
		))
		.ToArray();
	return forecast;
}).WithName("GetWeatherForecast");

Let’s run the application and see the log in the table

I hope you find this helpful. Do share your thoughts in the comment section below. If you have any further request for your use, please drop your request in comment below or use the contact form to reach out to me for faster response. Happy coding.