NLog 5.3.4无法向SQL Server表写入日志问题求助

环境信息 #

• ASP.NET Core 8 Web API
• SQL Server 2019 (RTM-GDR) (KB5046859) - 15.0.2130.3 (X64)
• 已安装NLog、NLog.Web.AspNetCore NuGet包

问题现象 #

NLog可正常写入文本文件，但SQL Server的Log表无任何日志记录。

相关代码及配置 #

Program.cs #

using NLog;
using NLog.Web;

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

            // Add services to the container.
            var logger = NLog.LogManager.Setup()
                .LoadConfigurationFromXml("nlog.config")
                .GetCurrentClassLogger();
            try
            {
                builder.Services.AddControllers();
                builder.Services.AddEndpointsApiExplorer();
                builder.Services.AddSwaggerGen();

                builder.Logging.ClearProviders();
                builder.Logging.SetMinimumLevel(Microsoft.Extensions.Logging.LogLevel.Information);
                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();
            }
            catch (Exception exception)
            {
                // NLog: catch setup errors
                logger.Error(exception, "Stopped program because of exception");
                throw;
            }
            finally
            {
                // Ensure to flush and stop internal timers/threads before application-exit (Avoid segmentation fault on Linux)
                NLog.LogManager.Shutdown();
            }
        }
    }
}

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">
    <extensions>
        <!--enable NLog.Web for ASP.NET Core-->
        <add assembly="NLog.Web.AspNetCore"/>
    </extensions>
    <targets>
        <target xsi:type="Database"
            name="dbTarget"
            dbProvider="System.Data.SqlClient"
            commandType="TableDirect"
            connectionString="Data Source=localhost\sqlexpress;Initial Catalog=myDatabase;Integrated Security=True;Encrypt=False"
             <!-- Note I removed MachineName from the insert statement and commented out the parameter below. -->
            commandText="insert into dbo.Log (Logged, Level, Message, Logger, Exception)
                                      values (@Logged,@Level,@Message,@Logger,@Exception);">

            <!--<parameter name="@MachineName" layout="${machinename}" />-->
            <parameter name="@Logged" layout="${date}" />
            <parameter name="@Level" layout="${level}" />
            <parameter name="@Message" layout="${message}" />
            <parameter name="@Logger" layout="${logger}" />
            <parameter name="@Exception" layout="${exception:tostring}" />
        </target>
        <target name="file" xsi:type="File"
           layout="${longdate} ${level} ${logger} ${message} ${aspnet-request-url} ${exception:format=ToString}"
           fileName="C:/temp/logs/logfile.txt"
           keepFileOpen="true"
           encoding="utf-8" />
    </targets>

    <rules>
        <!--Skip non-critical Microsoft logs and so log only own logs (BlackHole) -->
        <logger name="Microsoft.*" maxlevel="Info" final="true" />
        <logger name="System.Net.Http.*" maxlevel="Info" final="true" />
        
        <logger name="*" minlevel="Trace" writeTo="dbTarget" />
        <logger name="*" minlevel="Trace" writeTo="file" />
    </rules>
</nlog>

HomeController.cs #

using Microsoft.AspNetCore.Mvc;

namespace Api.Controllers
{
    [ApiController]
    [Route("[controller]")]
    public class HomeController : ControllerBase
    {
        private readonly ILogger<HomeController> _logger;
        
        public HomeController(ILogger<HomeController> logger) 
        {
            _logger = logger;
        } 

        [HttpGet(Name = "GetHome")]
        public string Get()
        {
            _logger.LogInformation("GetHome was called");
            return "Success";
        }
    }
}

SQL Server日志表创建语句 #

-- Note I allowed nulls in all columns except the identity 
-- to ensure that the non null constraint was not causing the issue.

CREATE TABLE [dbo].[Log]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [MachineName] [nvarchar](50) NULL,
    [Logged] [datetime] NULL,
    [Level] [nvarchar](50) NULL,
    [Message] [nvarchar](max) NULL,
    [Logger] [nvarchar](250) 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, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

内容的提问来源于stack exchange，提问作者Craig Gers