Setting up Serilog to write logs to SQL Server db table

Question

I'm trying to set up Serilog to write logs to my db table.

Here's the breakdown of what I did to try and set up logging to SQL Server.

1) Install the Nuget Packages, Serilog and Serilog.Sinks.MSSqlServer

2) I set up a class called Logger.cs that houses all the configuration for Serilog:

public static class Logger
{
    private static readonly ILogger _Logger;

    static Logger()
    {

        var connStr = "data source=DESKTOP-BLAH;Initial Catalog=DBNAME;Trusted_Connection=True;";

        _Logger = new LoggerConfiguration()
            .WriteTo.MSSqlServer(connStr, "ApplicationLogs", 
            columnOptions: GetSqlColumnOptions(), restrictedToMinimumLevel: LogEventLevel.Debug, batchPostingLimit: 1)
            .CreateLogger();

        Serilog.Debugging.SelfLog.Enable(msg => Debug.WriteLine(msg));

    }

    public static ColumnOptions GetSqlColumnOptions()
    {
        var colOptions = new ColumnOptions();

        colOptions.Store.Remove(StandardColumn.Properties);
        colOptions.Store.Remove(StandardColumn.MessageTemplate);
        colOptions.Store.Remove(StandardColumn.Message);
        colOptions.Store.Remove(StandardColumn.Exception);
        colOptions.Store.Remove(StandardColumn.TimeStamp);
        colOptions.Store.Remove(StandardColumn.Level);

        colOptions.AdditionalDataColumns = new Collection<DataColumn>
        {
            new DataColumn{DataType = typeof(DateTime), ColumnName = "LogTimeStamp"},
            new DataColumn{DataType = typeof(Int32), ColumnName = "RecordNum"},
            new DataColumn{DataType = typeof(string), ColumnName = "ComputerName"},
            new DataColumn{DataType = typeof(DateTime), ColumnName = "ProcessTimeStamp"},
            new DataColumn{DataType = typeof(string), ColumnName = "LogGroup"},
            new DataColumn{DataType = typeof(string), ColumnName = "Type"},
            new DataColumn{DataType = typeof(Int32), ColumnName = "EventId"},
            new DataColumn{DataType = typeof(string), ColumnName = "UserId"},
            new DataColumn{DataType = typeof(Int32), ColumnName = "Line"},
            new DataColumn{DataType = typeof(string), ColumnName = "Description"},
            new DataColumn{DataType = typeof(string), ColumnName = "Source"},
            new DataColumn{DataType = typeof(string), ColumnName = "Data"},
            new DataColumn{DataType = typeof(DateTime), ColumnName = "AddTimestamp"},
            new DataColumn{DataType = typeof(string), ColumnName = "DeviceID"}

        };
        return colOptions;
    }


    public static void WriteError(ApplicationLog infoToLog)
    {
        try
        {        
            _Logger.Error(LogEventLevel.Error,
                    "{LogTimestamp}{RecordNum}{ComputerName}{ProcessTimeStamp}{LogGroup}" +
                    "{Type}{EventId}{UserId}{Line}" +
                    "{Description}{Source}{Data}{AddTimestamp}{DeviceID}",
                    infoToLog.LogTimestamp, infoToLog.RecordNum,
                    infoToLog.ComputerName, infoToLog.ProcessTimestamp,
                    infoToLog.LogGroup, infoToLog.Type,
                    infoToLog.EventId, infoToLog.UserId,
                    infoToLog.Line, infoToLog.Description,
                    infoToLog.Source, infoToLog.Data,
                    infoToLog.AddTimestamp, infoToLog.DeviceId);
        }
        catch(Exception ex)
        {

        }
    }
}

3) Then I believe I should be good to go and I set it up like this:

ApplicationLog appLog = new ApplicationLog()
{
    LogTimestamp = DateTime.Now,
    RecordNum = 1,
    ComputerName = Environment.MachineName,
    ProcessTimestamp = DateTime.Now,
    LogGroup = "10",
    Type = "T",
    EventId = 23,
    UserId = Environment.UserName,
    Line = 0,
    Description = "Duplicate Failed to Upload",
    Source = "FooController.cs",
    Data = "DBNAME",
    AddTimestamp = DateTime.Now,
    DeviceId = "15"

};

Logger.WriteError(appLog);

4) Created table in my DB already and just waiting for the data from logging to populate.

However, it seems like it still doesn't get saved in my SQL Server db table.

I'm wondering if I missed any steps or what I did wrong for the setup here?

Solution

Can you try using the self logging of serilog to see what is happening:

If you are using .net core, put following in Program.main()

Serilog.Debugging.SelfLog.Enable(msg =>
{
Debug.Print(msg);
Debugger.Break();
});