ASP.NET Core 7中NLOG Database Target无法连接问题求助

错误信息 #

首次尝试错误 #

Error DatabaseTarget(Name=DatabaseSiteLog): Error when writing to database. Exception: Microsoft.Data.SqlClient.SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
 ---> System.ComponentModel.Win32Exception (2): The system cannot find the file specified.
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open()
   at NLog.Targets.DatabaseTarget.OpenConnection(String connectionString, LogEventInfo logEventInfo)
   at NLog.Targets.DatabaseTarget.EnsureConnectionOpen(String connectionString, LogEventInfo logEventInfo)
   at NLog.Targets.DatabaseTarget.WriteLogEventSuppressTransactionScope(LogEventInfo logEvent, String connectionString)
ClientConnectionId:00000000-0000-0000-0000-000000000000
Error Number:2,State:0,Class:20

近期尝试错误 #

Error DatabaseTarget(Name=DatabaseSiteLog): Error when writing to database. Exception: System.InvalidOperationException: The ConnectionString property has not been initialized.
   at Microsoft.Data.SqlClient.SqlConnection.PermissionDemand()
   at Microsoft.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)
   at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open()
   at NLog.Targets.DatabaseTarget.OpenConnection(String connectionString, LogEventInfo logEventInfo)
   at NLog.Targets.DatabaseTarget.EnsureConnectionOpen(String connectionString, LogEventInfo logEventInfo)
   at NLog.Targets.DatabaseTarget.WriteLogEventSuppressTransactionScope(LogEventInfo logEvent, String connectionString)

相关配置文件 #

appsettings.json #

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning",
      "Microsoft.EntityFrameworkCore.Database.Comman": "Information"
    }
  },
  "ConnectionStrings": {
    "ApiContext": "Data Source = (localdb)\\MSSQLLocalDB; Initial Catalog = SiteDb"
  }
}

NLog.config目标配置片段 #

<target xsi:type="Database"
        name="DatabaseSiteLog"
        connectionString="${configsetting:name=ConnectionStrings.ApiContext}">
    <commandText>
        INSERT INTO dbo.Log columns Values values
    </commandText>
    <parameter name="@columns" layout="${values}" />
</target>

故障排查与解决方案 #

错误原因拆解 #

1. 首次错误：SQL Server连接失败
   • 核心问题：LocalDB实例未启动、实例名称错误，或SiteDb数据库不存在/无访问权限
2. 近期错误：连接字符串未初始化
   • 核心问题：NLog未正确集成ASP.NET Core配置系统，无法读取appsettings.json中的连接字符串；或configsetting布局渲染器未启用

分步修复 #

1. 确保LocalDB实例正常运行

• 打开命令提示符，执行以下命令：

  # 查看已存在的LocalDB实例
  sqllocaldb i
  # 启动MSSQLLocalDB实例
  sqllocaldb start MSSQLLocalDB
  

• 打开SQL Server Management Studio，连接到(localdb)\MSSQLLocalDB，确认SiteDb数据库已创建，且dbo.Log表结构正确（需与NLog的commandText匹配）

2. 修复NLog与ASP.NET Core配置的集成

• 安装必要NuGet包：确保已安装NLog.Web.AspNetCore（适配ASP.NET Core 7的版本）
• 修正Program.cs初始化逻辑：确保先加载配置，再初始化NLog

  var builder = WebApplication.CreateBuilder(args);
  
  // 加载配置文件（含appsettings.json）
  builder.Configuration
      .AddJsonFile("appsettings.json", optional: false, reloadOnChange: true)
      .AddJsonFile($"appsettings.{builder.Environment.EnvironmentName}.json", optional: true);
  
  // 清除默认日志提供器，启用NLog
  builder.Logging.ClearProviders();
  builder.Host.UseNLog();
  
  // 后续服务配置...
  var app = builder.Build();
  // ...
  

• 修正NLog.config：
  • 添加extensions节点，启用configsetting布局渲染器
  • 修复无效的SQL commandText，替换为合法的插入语句

  <nlog xmlns="http://www.nlog-project.org/schemas/NLog.xsd"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        autoReload="true"
        internalLogLevel="Info"
        internalLogFile="internal-nlog.txt">
  
    <!-- 启用NLog.Web.AspNetCore扩展 -->
    <extensions>
      <add assembly="NLog.Web.AspNetCore"/>
    </extensions>
  
    <targets>
      <target xsi:type="Database"
              name="DatabaseSiteLog"
              connectionString="${configsetting:name=ConnectionStrings.ApiContext}">
        <!-- 替换为实际的表列和参数 -->
        <commandText>
          INSERT INTO dbo.Log (LogLevel, Message, CreatedTime)
          VALUES (@LogLevel, @Message, @CreatedTime);
        </commandText>
        <!-- 配置与列对应的参数 -->
        <parameter name="@LogLevel" layout="${level}"/>
        <parameter name="@Message" layout="${message}"/>
        <parameter name="@CreatedTime" layout="${date:format=yyyy-MM-dd HH:mm:ss}"/>
      </target>
    </targets>
  
    <!-- 添加日志规则，指定哪些日志写入数据库目标 -->
    <rules>
      <logger name="*" minlevel="Info" writeTo="DatabaseSiteLog"/>
    </rules>
  </nlog>
  

3. 验证连接字符串有效性

在代码中直接测试连接，排除NLog之外的问题：

using Microsoft.Data.SqlClient;

// 在Program.cs或某个接口中添加测试代码
var connectionString = builder.Configuration.GetConnectionString("ApiContext");
using var connection = new SqlConnection(connectionString);
try
{
    connection.Open();
    Console.WriteLine("数据库连接成功");
}
catch (Exception ex)
{
    Console.WriteLine($"连接失败：{ex.Message}");
}

验证 #

启动API后，触发日志记录（例如在接口中注入ILogger并调用logger.LogInformation("测试日志")），然后：

1. 查看internal-nlog.txt是否有新的错误信息
2. 检查SiteDb数据库的dbo.Log表是否新增日志数据

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