.NET 6.0 日志处理之Serilog 写入到sql server（一）

.NET 6.0里使用Serilog 写入日志到sql server数据库表。

1、安装依赖包

Serilog:

Serilog.Sinks.MSSqlServer:

Serilog.AspNetCore:

2、添加静态类SerilogConfig

using Serilog;
using Serilog.Sinks.MSSqlServer;
using System.Collections.ObjectModel;
using System.Net;

namespace Demo_Serilog.Function;

public static class SerilogConfig
{
    public static void CreateLogger()
    {
        try
        {
            var filePath = Path.Combine(AppContext.BaseDirectory, "Logs/Serilog/log.txt");
            var logDB = @"Server=192.168.0.0;Database=DB;User ID=Admin;Password=123456;TrustServerCertificate=true;";
            var sinkOpts = new MSSqlServerSinkOptions();
            sinkOpts.TableName = "Logs";
            sinkOpts.AutoCreateSqlTable = false;
            sinkOpts.BatchPostingLimit = 100;
            sinkOpts.BatchPeriod = TimeSpan.FromMilliseconds(1);
            var columnOpts = new ColumnOptions();
            columnOpts.Store.Remove(StandardColumn.MessageTemplate);
            columnOpts.Store.Remove(StandardColumn.Properties);
            //columnOpts.Properties.ExcludeAdditionalProperties = true;
            columnOpts.AdditionalColumns = new Collection<SqlColumn>
            {
                new SqlColumn{DataType = System.Data.SqlDbType.VarChar, DataLength = -1, ColumnName = "SourceContext"},
                new SqlColumn{DataType = System.Data.SqlDbType.VarChar, DataLength = 50, ColumnName = "LineId"}
            };

            Log.Logger = new LoggerConfiguration()
                .MinimumLevel.Information()
                .Enrich.FromLogContext()
                .Enrich.WithProperty("LineId", "Web")
                .WriteTo.MSSqlServer(
                    connectionString: logDB,
                    sinkOptions: sinkOpts,
                    columnOptions: columnOpts
                    ).CreateLogger();
        }
        catch (Exception ex)
        { }     
    }

    public static void RefreshLogger()
    {
        if (Log.Logger != null)
        {
            Log.CloseAndFlush();
        }
        CreateLogger();
    }

    private static string GetIpAddress()
    {
        string ipAddress = "127.0.0.1";
        IPAddress[] ips = Dns.GetHostAddresses(Dns.GetHostName());
        foreach (IPAddress ip in ips)
        {
            if (ip.AddressFamily.ToString().ToLower().Equals("internetwork"))
            {
                ipAddress = ip.ToString();
                return ipAddress;
            }
        }

        return ipAddress;
    }
}

3、Program.cs中配

using Demo_Serilog;
using Demo_Serilog.Function;
using Serilog;
using Serilog.Settings.Configuration;

SerilogConfig.CreateLogger();
Log.Information("Starting web application");

try
{
    var builder = WebApplication.CreateBuilder(args); //创建一个Web应用程序构建器。
    LineConsts.InitConfig(builder.Configuration);
    builder.Host.UseSerilog();//配置Serilog。

    builder.Services.AddControllers();//向依赖注入容器添加控制器服务。
    // Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
    builder.Services.AddEndpointsApiExplorer(); //用于API文档生成。
    builder.Services.AddSwaggerGen();//添加Swagger生成器，生成API文档。

    var app = builder.Build(); //构建Web应用程序。

    // Configure the HTTP request pipeline.
    if (app.Environment.IsDevelopment())  //检查当前环境是否是开发环境。
    {
        app.UseSwagger(); //启用Swagger中间件，提供API文档UI。
        app.UseSwaggerUI();
    }

    app.UseHttpsRedirection(); //强制使用HTTPS。

    app.UseAuthorization(); //启用授权中间件。

    app.MapControllers(); //将控制器映射到路由

    app.Run(); //运行Web应用程序。
}
catch (Exception ex)
{
    Log.Fatal(ex, "Web服务异常退出");
}

4、日志表：

5、写入日志测试样例：

using Microsoft.AspNetCore.Mvc;
using Serilog;

namespace Demo_Serilog.Controllers
{    
    [ApiController]
    [Route("[controller]")]
    public class WeatherForecastController : ControllerBase
    {
        private static readonly string[] Summaries = new[]
        {
        "Freezing", "Bracing", "Chilly", "Cool", "Mild", "Warm", "Balmy", "Hot", "Sweltering", "Scorching"
        };

        public WeatherForecastController()
        {
        }


        [HttpGet(Name = "GetWeatherForecast")]
        public IEnumerable<WeatherForecast> Get()
        {
            try
            {
                Log.Information("Get");
                if (Summaries.Length > 0)
                {
                    throw new Exception("Error Created By SomeOne.");
                }
                return Enumerable.Range(1, 5).Select(index => new WeatherForecast
                {
                    Date = DateTime.Now.AddDays(index),
                    TemperatureC = Random.Shared.Next(-20, 55),
                    Summary = Summaries[Random.Shared.Next(Summaries.Length)]
                })
                .ToArray();
            }
            catch (Exception ex)
            {
                Log.Error("Error:" + ex.Message);
                return Enumerable.Empty<WeatherForecast>();
            }           
        }
    }
}

6、运行结果：