SQL

Serilog Table Configurations for MSSQLSERVER SINK

#region Error
    var sinkOptionsError = new MSSqlServerSinkOptions
    {
        AutoCreateSqlTable = true,
        EagerlyEmitFirstEvent = true,
        SchemaName = "dbo",
        TableName = "LogError",
    };
    var columnErrorOptions = new ColumnOptions
    {
        AdditionalColumns = new Collection<SqlColumn>
        {
            new SqlColumn { ColumnName = "Application", PropertyName = "Application", DataType = System.Data.SqlDbType.VarChar, DataLength = 100, AllowNull = true},
            new SqlColumn { ColumnName = "SourceContext", PropertyName = "SourceContext", DataType = System.Data.SqlDbType.VarChar, DataLength = 100, AllowNull = true}
        },
    };
    columnErrorOptions.Store.Remove(StandardColumn.MessageTemplate);
    columnErrorOptions.Store.Remove(StandardColumn.Properties);
    columnErrorOptions.Store.Add(StandardColumn.LogEvent);
    columnErrorOptions.Level.DataLength = 12;
    columnErrorOptions.TimeStamp.ConvertToUtc = true;
    #endregion

    #region Info
    var sinkOptionsInfo = new MSSqlServerSinkOptions
    {
        AutoCreateSqlTable = true,
        EagerlyEmitFirstEvent = true,
        SchemaName = "dbo",
        TableName = "LogInfo"
    };
    var columnInfoOptions = new ColumnOptions
    {
        AdditionalColumns = new Collection<SqlColumn>
        {
            new SqlColumn { ColumnName = "Application", PropertyName = "Application", DataType = System.Data.SqlDbType.VarChar, DataLength = 100, AllowNull = true},
            new SqlColumn { ColumnName = "SourceContext", PropertyName = "SourceContext", DataType = System.Data.SqlDbType.VarChar, DataLength = 100, AllowNull = true}
        },
    };
    columnInfoOptions.Store.Remove(StandardColumn.MessageTemplate);
    columnInfoOptions.Store.Remove(StandardColumn.Properties);
    columnInfoOptions.Store.Remove(StandardColumn.Exception);
    columnInfoOptions.Store.Add(StandardColumn.LogEvent);
    columnInfoOptions.Level.DataLength = 12;
    columnInfoOptions.TimeStamp.ConvertToUtc = true;
    #endregion
    #region Request
    var sinkOptionsRequest = new MSSqlServerSinkOptions
    {
        AutoCreateSqlTable = true,
        EagerlyEmitFirstEvent = true,
        SchemaName = "dbo",
        TableName = "LogRequest",
    };
    var columnRequestOptions = new ColumnOptions
    {
        AdditionalColumns = new Collection<SqlColumn>
        {
            new SqlColumn { ColumnName = "RequestMethod", PropertyName = "RequestMethod", DataType = System.Data.SqlDbType.VarChar, DataLength = 50, AllowNull = true},
            new SqlColumn { ColumnName = "RequestPath", PropertyName = "RequestPath", DataType = System.Data.SqlDbType.VarChar, DataLength = 500, AllowNull = true},
            new SqlColumn { ColumnName = "StatusCode", PropertyName = "StatusCode", DataType = System.Data.SqlDbType.VarChar, DataLength = 50, AllowNull = true},
            new SqlColumn { ColumnName = "Elapsed", PropertyName = "Elapsed", DataType = System.Data.SqlDbType.Float, AllowNull = true},
            //activating these columns will break saving to database ... so don't!!!
            //new SqlColumn { ColumnName = "ClientIp", PropertyName = "ClientIp", DataType = System.Data.SqlDbType.VarChar, DataLength = 50, AllowNull = true },
            //new SqlColumn { ColumnName = "ClientAgent", PropertyName = "ClientAgent", DataType = System.Data.SqlDbType.VarChar, DataLength = 100, AllowNull = true },
            new SqlColumn { ColumnName = "Application", PropertyName = "Application", DataType = System.Data.SqlDbType.VarChar, DataLength = 100, AllowNull = true},
        },
    };
    columnRequestOptions.Store.Remove(StandardColumn.MessageTemplate);
    columnRequestOptions.Store.Remove(StandardColumn.Exception);
    columnRequestOptions.Store.Remove(StandardColumn.Level);
    columnRequestOptions.Store.Remove(StandardColumn.Properties);
    columnRequestOptions.Store.Add(StandardColumn.LogEvent);
    columnRequestOptions.Level.DataLength = 12;
    columnRequestOptions.TimeStamp.ConvertToUtc = true;
    #endregion