Using Nlog with SQL Server database

Question

I want to use nlog with SQL Server database. I read the official documentation and some different articles about that. I connected nlog to my project, and the project was built succeessfully.

But when I start debug, in database writing nothing. I not see anything error in Visual Studio. When I write in just file, using nlog, all work well. I used Entity Framework Core for create nlog's table. I using the same connection string in appsettings.json, and that works well.

I don't undeestand where I am making a mistake.

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"
      xsi:schemaLocation="http://www.nlog-project.org/schemas/NLog.xsd NLog.xsd"
      autoReload="true"
      throwExceptions="false"
      internalLogLevel="Off" internalLogFile="c:\temp\nlog-internal.log">
  <extensions>
    <add assembly="NLog.Web.AspNetCore"/>
  </extensions>  
  <targets>
    <target xsi:type="Database"
            name="dblog"
            connectionString="Data Source=.\\SQLEXPRESS;Initial Catalog=AyanaDB;integrated security=True;MultipleActiveResultSets=True;"
            commandText="INSERT INTO [dbo].[NLogs] ([CallSite], [Date], [Exception], [Level], [Logger], [MachineName], [Message], [StackTrace], [Thread], [Username])
                         VALUES (@CallSite, @Date, @Exception, @Level, @Logger, @MachineName, @Message, @StackTrace, @Thread, @Username);">
      <parameter name="@CallSite" layout="${callsite:filename=true}" />
      <parameter name="@Date" layout="${longdate}" />
      <parameter name="@Exception" layout="${exception}" />
      <parameter name="@Level" layout="${level}" />
      <parameter name="@Logger" layout="${logger}" />
      <parameter name="@MachineName" layout="${machinename}" />
      <parameter name="@Message" layout="${message}" />
      <parameter name="@StackTrace" layout="${stacktrace}" />
      <parameter name="@Thread" layout="${threadid}" />
      <parameter name="@Username" layout="${windows-identity:domain=true}" />
    </target>
  </targets>
  <rules>
    <logger name="*" minlevel="Trace" writeTo="dblog" />
  </rules>
</nlog>

Program.cs:

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

        try
        {
            logger.Debug("init main");
            CreateHostBuilder(args).Build().Run();
        }
        catch (Exception exception)
        {
            logger.Error(exception, "Stopped program because of exception");
            throw;
        }
        finally
        {
            LogManager.Shutdown();
        }
    }

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

Example using Nlog in code:

public class EveningWorkService : IEveningWorkService
{
    public EveningWorkService(AyDbContext ayDbContext,
                              IRutorService rutorService,
                              IDriverService driverService,
                              ISoftService softService,
                              ILogService logService,
                              ILogger<EveningWorkService> logger)
    {
        _context = ayDbContext;
        _rutorService = rutorService;
        _driverService = driverService;
        _softService = softService;
        _logService = logService;
        _logger = logger;
        logger.LogInformation("It's work!");
    }

NLog.cs:

public class NLog
{
    public int Id { get; set; }
    public string CallSite { get; set; }
    public DateTime Date { get; set; }
    public string Message { get; set; }
    public string Exception { get; set; }
    public string Level { get; set; }
    public string Logger { get; set; }
    public string MachineName { get; set; }
    public string StackTrace { get; set; }
    public string Thread { get; set; }
    public string Username { get; set; }
}

All project's file you can see there.

Check github.com/NLog/NLog/wiki/Logging-troubleshooting . Probably a good idea to activate the NLog InternalLogger and check for warnings / errors. — Rolf Kristensen
@RolfKristensen Very thanks. It is really helped for me. Error was in "${windows-identity:domain=true}". — Paul
Maybe replace with ${environment-user}. See also github.com/NLog/NLog/wiki/Environment-User-Layout-Renderer — Rolf Kristensen
@RolfKristensen Yes, with ${environment-user} works well. Very thanks.) — Paul

Rolf Kristensen Rolf Kristensen · Accepted Answer · 2020-10-25T16:40:55

The ${windows-identity} is not available by default on .NET Core:

<parameter name="@Username" layout="${windows-identity:domain=true}" />

You have to install the nuget-package. See also: https://github.com/NLog/NLog/wiki/Windows-Identity-Layout-Renderer

Alternative you can change to ${environment-user}:

<parameter name="@Username" layout="${environment-user}" />

See also: https://github.com/NLog/NLog/wiki/Environment-User-Layout-Renderer

Using Nlog with SQL Server database

1 Answers