.NET Core日志配置：控制台输出全量日志+SQL存Warning及以上日志

注意：你的MyClass构造函数中，ILogger的泛型参数错误，应该是ILogger<MyClass>而非ILogger<UserIdFilterAttribute>，否则注入的日志实例与当前类不匹配，修正后的代码见下文示例。

方案一：使用Serilog（快速实现，推荐） #

Serilog是.NET生态中成熟的日志库，内置多种输出接收器，配置简单易维护。

步骤1：安装NuGet包 #

在项目中安装以下包：

• Serilog.AspNetCore
• Serilog.Sinks.MSSqlServer

步骤2：配置Serilog与日志输出 #

修改Program.cs：

using Serilog;

var builder = WebApplication.CreateBuilder(args);

// 配置Serilog日志规则
Log.Logger = new LoggerConfiguration()
    // 控制台输出所有级别日志
    .WriteTo.Console()
    // SQL Server仅输出Warning及以上级别日志，自动创建日志表
    .WriteTo.MSSqlServer(
        connectionString: builder.Configuration.GetConnectionString("SqlServer"),
        sinkOptions: new Serilog.Sinks.MSSqlServer.MSSqlServerSinkOptions
        {
            TableName = "Logs",
            AutoCreateSqlTable = true
        },
        restrictedToMinimumLevel: Serilog.Events.LogEventLevel.Warning)
    .CreateLogger();

// 替换默认日志系统为Serilog
builder.Host.UseSerilog();

// 注册其他服务...

var app = builder.Build();

// 配置中间件...

app.Run();

步骤3：添加数据库连接字符串 #

在appsettings.json中添加SQL Server连接字符串：

{
  "ConnectionStrings": {
    "SqlServer": "Server=.;Database=YourDatabaseName;Trusted_Connection=True;TrustServerCertificate=True;"
  }
}

修正后的MyClass代码 #

public class MyClass
{
    private readonly IConfiguration _config;
    private readonly ILogger<MyClass> _logger;

    // 修正泛型参数为当前类
    public MyClass(IConfiguration config, ILogger<MyClass> logger)
    {
        _config = config;
        _logger = logger;
    }

    public void TestLogs()
    {
        _logger.LogInformation("这是Info级日志（仅控制台输出）");
        _logger.LogWarning("This is a test（控制台+SQL Server输出）");
        _logger.LogError("This exception（控制台+SQL Server输出）");
    }
}

方案二：自定义日志提供程序（无需第三方库） #

如果不想依赖第三方库，可以自行实现SQL Server日志提供程序。

步骤1：实现自定义日志类与提供程序 #

using Microsoft.Extensions.Logging;
using System.Data.SqlClient;

// 日志提供程序
public class SqlServerLoggerProvider : ILoggerProvider
{
    private readonly string _connString;
    private readonly LogLevel _minLevel;

    public SqlServerLoggerProvider(string connString, LogLevel minLevel)
    {
        _connString = connString;
        _minLevel = minLevel;
    }

    public ILogger CreateLogger(string categoryName)
    {
        return new SqlServerLogger(categoryName, _connString, _minLevel);
    }

    public void Dispose() { }
}

// 具体日志实现
public class SqlServerLogger : ILogger
{
    private readonly string _category;
    private readonly string _connString;
    private readonly LogLevel _minLevel;

    public SqlServerLogger(string category, string connString, LogLevel minLevel)
    {
        _category = category;
        _connString = connString;
        _minLevel = minLevel;
    }

    public IDisposable BeginScope<TState>(TState state) => null;

    public bool IsEnabled(LogLevel logLevel)
    {
        // 仅处理Warning及以上级别日志
        return logLevel >= _minLevel;
    }

    public void Log<TState>(LogLevel logLevel, EventId eventId, TState state, Exception exception, Func<TState, Exception, string> formatter)
    {
        if (!IsEnabled(logLevel)) return;

        var message = formatter(state, exception);
        var logTime = DateTime.UtcNow;
        var level = logLevel.ToString();
        var exMsg = exception?.ToString() ?? string.Empty;

        // 写入SQL Server
        using var conn = new SqlConnection(_connString);
        conn.Open();
        var cmd = new SqlCommand(@"INSERT INTO Logs (LogTime, Level, Category, Message, Exception)
                                    VALUES (@LogTime, @Level, @Category, @Message, @Exception)", conn);
        cmd.Parameters.AddWithValue("@LogTime", logTime);
        cmd.Parameters.AddWithValue("@Level", level);
        cmd.Parameters.AddWithValue("@Category", _category);
        cmd.Parameters.AddWithValue("@Message", message);
        cmd.Parameters.AddWithValue("@Exception", exMsg);
        cmd.ExecuteNonQuery();
    }
}

步骤2：添加扩展方法用于注册 #

using Microsoft.Extensions.Logging;

public static class SqlServerLoggerExtensions
{
    public static ILoggingBuilder AddSqlServerLogger(this ILoggingBuilder builder, string connectionString, LogLevel minLevel = LogLevel.Warning)
    {
        builder.AddProvider(new SqlServerLoggerProvider(connectionString, minLevel));
        return builder;
    }
}

步骤3：在Program.cs中配置日志 #

var builder = WebApplication.CreateBuilder(args);

// 清除默认日志提供器，添加控制台（所有级别）和自定义SQL日志（Warning及以上）
builder.Logging.ClearProviders();
builder.Logging.AddConsole();
builder.Logging.AddSqlServerLogger(
    connectionString: builder.Configuration.GetConnectionString("SqlServer"),
    minLevel: LogLevel.Warning);

// 注册其他服务...

var app = builder.Build();

// 配置中间件...

app.Run();

步骤4：手动创建日志表 #

在SQL Server中执行以下脚本创建Logs表：

CREATE TABLE Logs (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    LogTime DATETIME NOT NULL,
    Level NVARCHAR(50) NOT NULL,
    Category NVARCHAR(255) NOT NULL,
    Message NVARCHAR(MAX) NOT NULL,
    Exception NVARCHAR(MAX) NULL
);

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