.NET 4.7中使用NLog记录日志到数据库表

1. 首先安装必要的NuGet包

在项目中安装以下NuGet包：

• NLog

• NLog.Config (可选，用于自动生成配置文件)

• 相应的数据库提供程序（如System.Data.SqlClient for SQL Server）

Install-Package NLog
Install-Package NLog.Config
Install-Package System.Data.SqlClient

2. 配置NLog.config文件

在项目中添加或修改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="Info"
      internalLogFile="c:\temp\nlog-internal.log">

  <extensions>
    <add assembly="NLog" />
  </extensions>

  <targets>
    <!-- 数据库目标 -->
    <target name="database" 
            xsi:type="Database"
            connectionString="YourConnectionStringHere"
            commandText="INSERT INTO LoginLogs(ID, LoginName, Message, CreateTime) VALUES(@ID, @LoginName, @Message, @CreateTime)">
      
      <parameter name="@ID" layout="${guid}" />
      <parameter name="@LoginName" layout="${event-properties:item=LoginName}" />
      <parameter name="@Message" layout="${message}" />
      <parameter name="@CreateTime" layout="${date:format=yyyy-MM-dd HH\:mm\:ss}" />
    </target>
  </targets>

  <rules>
    <logger name="*" minlevel="Info" writeTo="database" />
  </rules>
</nlog>

 如果需要使用异步记录日志的话，配置如下：

<?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\nlog-internal.log">

  <extensions>
    <add assembly="NLog" />
  </extensions>

  <targets async="true"> <!-- 启用全局异步 -->
    <!-- 异步数据库目标 -->
    <target name="asyncDatabase" xsi:type="AsyncWrapper" 
            queueLimit="10000" 
            overflowAction="Discard">
      <target xsi:type="Database"
              connectionString="YourConnectionStringHere"
              commandText="INSERT INTO LoginLogs(ID, LoginName, Message, CreateTime) VALUES(@ID, @LoginName, @Message, @CreateTime)">
        
        <parameter name="@ID" layout="${guid}" />
        <parameter name="@LoginName" layout="${event-properties:item=LoginName}" />
        <parameter name="@Message" layout="${message}" />
        <parameter name="@CreateTime" layout="${date:format=yyyy-MM-dd HH\:mm\:ss}" />
      </target>
    </target>
  </targets>

  <rules>
    <logger name="*" minlevel="Info" writeTo="asyncDatabase" />
  </rules>
</nlog>

3. 创建数据库表

确保你的数据库中有对应的表结构：

CREATE TABLE LoginLogs (
    ID UNIQUEIDENTIFIER PRIMARY KEY,
    LoginName NVARCHAR(100),
    Message NVARCHAR(MAX),
    CreateTime DATETIME
)

4. 在代码中使用NLog记录登录日志 

using NLog;

public class LoginService
{
    private static readonly Logger Logger = LogManager.GetCurrentClassLogger();

    public void LogLoginAttempt(string loginName, string message, bool isSuccess)
    {
        var logEvent = new LogEventInfo
        {
            Level = isSuccess ? LogLevel.Info : LogLevel.Warn,
            Message = message
        };
        
        logEvent.Properties["LoginName"] = loginName;
        
        Logger.Log(logEvent);
    }
}

5. 使用示例 

var loginService = new LoginService();

// 成功登录
loginService.LogLoginAttempt("john.doe", "User logged in successfully", true);

// 失败登录
loginService.LogLoginAttempt("john.doe", "Invalid password", false);