What are the specifics and basics of connecting Serilog to a MSSQL database?

Question

What are the specifics and basics of connecting Serilog to a MSSQL database? Here is what I have so far. Can you please tell where I am going wrong?

In the app.config I have:

    <?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2" />
    </startup>
  <connectionStrings>
    <add name="LogConnectionString" connectionString="Data Source=data.dev.db.com,14330;Integrated Security=SSPI;Database=DNA_Logs;"
      providerName="System.Data.SqlClient"/>
  </connectionStrings>
</configuration>

In the C# code I have:

public Guid transactionGuid = Guid.NewGuid();

public string transactionGUIDString = string.Empty;

public StringWriter TraceLog = new StringWriter();

public void HelloLog()
{
    this.transactionGUIDString = transactionGuid.ToString("B").ToUpper();

    var logger = new LoggerConfiguration()
        .MinimumLevel.Debug()
        .Enrich.WithProperty("TransactionId", this.transactionGuid)
        .WriteTo.MSSqlServer("LogConnectionString", "Serilog_Logs")
        .WriteTo.TextWriter(TraceLog)
        .CreateLogger();

    // Output logs
    logger.Information("Hello, Serilog!");
}

And the database table is configured like this:

So I have a few questions. First of all, since the database table is technically named "dbo.Serilog_Logs" shouldn't the C# code use this table name instead of just "Serilog_Logs"?

I found some sample code for Serilog but it only writes to the console. I want to modify this sample code to write to the MSSQL Server. I wonder how the columns of the table are supposed to be configured.

Answer 1

dbo.Serilog_Logs default schema name is dbo if you didn't specify it, You can create your own schema using below SQL statement and mention it in configuration method arguments using schemaName: "LOG"

CREATE SCHEMA LOG;

Also make sure that database is exist and you have access to it throw your connection string, it database not exist create new one with below SQL command

CREATE DATABASE Serilog_DB;

And to configure table columns you can use columnOptions and define columns using ColumnOptions as below example

var connectionString = ConfigurationManager.ConnectionStrings["LogConnectionString"].ToString();
var tableName = "Serilog_Logs";
var customColumnOptions = new ColumnOptions();
customColumnOptions.Store.Remove(StandardColumn.Properties); // Remove Column
customColumnOptions.Store.Add(StandardColumn.LogEvent); // Add new column to default columns

var logger = new LoggerConfiguration()
    .MinimumLevel.Debug()
    .Enrich.WithProperty("TransactionId", transactionGuid)
    .WriteTo.MSSqlServer(connectionString: connectionString
        , tableName: tableName
        , schemaName: "LOG" // Schema should be create in database first
        , columnOptions: customColumnOptions
        , autoCreateSqlTable: true) // Will create the table if a table by that name doesn't exist
    .WriteTo.Console()
    .CreateLogger();

Result should be as below image