Serilog: Logging to Microsoft SQL Server custom tables

Serilog provides diagnostic logging to files, the console, and elsewhere. It is easy to set up, has a clean API, and is portable between recent .NET platforms. In this article, you can find how to use Serilog to log the events into tables with custom columns.

Installation:

Find the package Serilog.Sinks.MSSqlServer in NuGet Package Manager and install.

Custom Table Schema:

Let the columns of the table where we are going to log the events be

• LogId - Primary Key with Identity
• Message - Custom message
• CreatedBy - Owner of the log
• CreatedDate - Log date

Implementation:

Create a static class and add the following constructor code

   static CustomLogger()
        {
            var sinkOptions = new MSSqlServerSinkOptions()
            {
                TableName = "Your table name",
                AutoCreateSqlTable = true
            };
            var connectionString = "Your connection string";
            
            logger = new LoggerConfiguration()
           .MinimumLevel.Information()
           .WriteTo.MSSqlServer(connectionString, sinkOptions,null, null,
            Serilog.Events.LogEventLevel.Information, columnOptions:     GetColumnOptions())
           .CreateLogger();
        }

In the above code, we are configuring sink options with the table name and other required properties. Using this we are creating a logger which stores events.

Now we will set the table's custom columns and remove Serilog's default columns.

  public static ColumnOptions GetColumnOptions()
        {
            var columnOptions = new ColumnOptions();


            // Override the default Primary Column of Serilog by custom column name
            columnOptions.Id.ColumnName = "LogId";


            // Removing all the default column
            columnOptions.Store.Remove(StandardColumn.TimeStamp);
            columnOptions.Store.Remove(StandardColumn.Message);
            columnOptions.Store.Remove(StandardColumn.Level);
            columnOptions.Store.Remove(StandardColumn.Exception);
            columnOptions.Store.Remove(StandardColumn.MessageTemplate);
            columnOptions.Store.Remove(StandardColumn.Properties);


            // Adding all the custom columns
            columnOptions.AdditionalColumns = new List<SqlColumn>
            {
                new SqlColumn { DataType = SqlDbType.VarChar, ColumnName = "Message", DataLength = 250, AllowNull = false},
                new SqlColumn { DataType = SqlDbType.VarChar, ColumnName = "CreatedBy",DataLength = 50, AllowNull = false },
                new SqlColumn { DataType = SqlDbType.DateTime2, ColumnName = "CreatedDate", DataLength = 7, AllowNull = false },
            };
            return columnOtions;
         
        }

In the above code, we are overriding the default Primary Key with the custom table's Primary Key, removing the default columns and adding the required Custom columns.

Next, we will write a method where the logger we have created in the constructor is used to store logs in the custom table.

 public static void Information(string message, string createdBy)
        {
            logger.Information("{Message}{CreatedBy}{CreatedDate}", message, createdBy, DateTime.Now);
        }
        

The Custom Logger class is ready. In the same way, we add methods for different Logging Levels. We can use this class as follows wherever we need to log

CustomLogger.Information("My custom log message", "Amrutha Chenna");