/// <summary>
/// Construct a sink posting to the specified database.
/// </summary>
/// <param name="connectionString">Connection string to access the database.</param>
/// <param name="tableName">Name of the table to store the data in.</param>
/// <param name="batchPostingLimit">The maximum number of events to post in a single batch.</param>
/// <param name="period">The time to wait between checking for event batches.</param>
/// <param name="formatProvider">Supplies culture-specific formatting information, or null.</param>
/// <param name="autoCreateSqlTable">Create log table with the provided name on destination sql server.</param>
/// <param name="columnOptions">Options that pertain to columns</param>
public MSSqlServerSink(
string connectionString,
string tableName,
int batchPostingLimit,
TimeSpan period,
IFormatProvider formatProvider,
bool autoCreateSqlTable = false,
ColumnOptions columnOptions = null
)
: base(batchPostingLimit, period)
{
if (string.IsNullOrWhiteSpace(connectionString))
throw new ArgumentNullException("connectionString");
if (string.IsNullOrWhiteSpace(tableName))
throw new ArgumentNullException("tableName");
_connectionString = connectionString;
_tableName = tableName;
_formatProvider = formatProvider;
_columnOptions = columnOptions ?? new ColumnOptions();
if (_columnOptions.AdditionalDataColumns != null)
_additionalDataColumnNames = new HashSet<string>(_columnOptions.AdditionalDataColumns.Select(c => c.ColumnName), StringComparer.OrdinalIgnoreCase);
if (_columnOptions.Store.Contains(StandardColumn.LogEvent))
_jsonFormatter = new JsonFormatter(formatProvider: formatProvider);
// Prepare the data table
_eventsTable = CreateDataTable();
if (autoCreateSqlTable)
{
try
{
SqlTableCreator tableCreator = new SqlTableCreator(connectionString);
tableCreator.CreateTable(_eventsTable);
}
catch (Exception ex)
{
SelfLog.WriteLine("Exception {0} caught while creating table {1} to the database specified in the Connection string.", (object)ex, (object)tableName);
}
}
}
/// <summary>
/// Adds a sink that writes log events to a table in a MSSqlServer database.
/// Create a database and execute the table creation script found here
/// https://gist.github.com/mivano/10429656
/// or use the autoCreateSqlTable option.
/// </summary>
/// <param name="loggerConfiguration">The logger configuration.</param>
/// <param name="connectionString">The connection string to the database where to store the events.</param>
/// <param name="tableName">Name of the table to store the events in.</param>
/// <param name="restrictedToMinimumLevel">The minimum log event level required in order to write an event to the sink.</param>
/// <param name="batchPostingLimit">The maximum number of events to post in a single batch.</param>
/// <param name="period">The time to wait between checking for event batches.</param>
/// <param name="formatProvider">Supplies culture-specific formatting information, or null.</param>
/// <param name="autoCreateSqlTable">Create log table with the provided name on destination sql server.</param>
/// <param name="columnOptions"></param>
/// <returns>Logger configuration, allowing configuration to continue.</returns>
/// <exception cref="ArgumentNullException">A required parameter is null.</exception>
public static LoggerConfiguration MSSqlServer(
this LoggerSinkConfiguration loggerConfiguration,
string connectionString,
string tableName,
LogEventLevel restrictedToMinimumLevel = LevelAlias.Minimum,
int batchPostingLimit = MSSqlServerSink.DefaultBatchPostingLimit,
TimeSpan? period = null,
IFormatProvider formatProvider = null,
bool autoCreateSqlTable = false,
ColumnOptions columnOptions = null
)
{
if (loggerConfiguration == null) throw new ArgumentNullException("loggerConfiguration");
var defaultedPeriod = period ?? MSSqlServerSink.DefaultPeriod;
MSSqlServerConfigurationSection serviceConfigSection =
ConfigurationManager.GetSection("MSSqlServerSettingsSection") as MSSqlServerConfigurationSection;
// If we have additional columns from config, load them as well
if (serviceConfigSection != null && serviceConfigSection.Columns.Count > 0)
{
if (columnOptions == null)
{
columnOptions = new ColumnOptions();
}
GenerateDataColumnsFromConfig(serviceConfigSection, columnOptions);
}
return loggerConfiguration.Sink(
new MSSqlServerSink(
connectionString,
tableName,
batchPostingLimit,
defaultedPeriod,
formatProvider,
autoCreateSqlTable,
columnOptions
),
restrictedToMinimumLevel);
}
/// <summary>
/// Construct a sink posting to the specified database.
/// </summary>
/// <param name="connectionString">Connection string to access the database.</param>
/// <param name="tableName">Name of the table to store the data in.</param>
/// <param name="schemaName">Name of the schema for the table to store the data in. The default is 'dbo'.</param>
/// <param name="formatProvider">Supplies culture-specific formatting information, or null.</param>
/// <param name="autoCreateSqlTable">Create log table with the provided name on destination sql server.</param>
/// <param name="columnOptions">Options that pertain to columns</param>
/// <param name="logEventFormatter">Supplies custom formatter for the LogEvent column, or null</param>
public MSSqlServerAuditSink(
string connectionString,
string tableName,
IFormatProvider formatProvider,
bool autoCreateSqlTable = false,
ColumnOptions columnOptions = null,
string schemaName = "dbo",
ITextFormatter logEventFormatter = null)
{
columnOptions.FinalizeConfigurationForSinkConstructor();
if (columnOptions != null)
{
if (columnOptions.DisableTriggers)
{
throw new NotSupportedException($"The {nameof(ColumnOptions.DisableTriggers)} option is not supported for auditing.");
}
}
_traits = new MSSqlServerSinkTraits(connectionString, tableName, schemaName, columnOptions, formatProvider, autoCreateSqlTable, logEventFormatter);
}
public MSSqlServerSinkTraits(string connectionString, string tableName, string schemaName, ColumnOptions columnOptions, IFormatProvider formatProvider, bool autoCreateSqlTable)
{
if (string.IsNullOrWhiteSpace(connectionString))
{
throw new ArgumentNullException(nameof(connectionString));
}
if (string.IsNullOrWhiteSpace(tableName))
{
throw new ArgumentNullException(nameof(tableName));
}
this.connectionString = connectionString;
this.tableName = tableName;
this.schemaName = schemaName;
this.columnOptions = columnOptions ?? new ColumnOptions();
this.formatProvider = formatProvider;
standardColumnNames = new HashSet <string>(StringComparer.OrdinalIgnoreCase);
foreach (var stdCol in this.columnOptions.Store)
{
var col = this.columnOptions.GetStandardColumnOptions(stdCol);
standardColumnNames.Add(col.ColumnName);
}
additionalColumnNames = new HashSet <string>(StringComparer.OrdinalIgnoreCase);
if (this.columnOptions.AdditionalColumns != null)
{
foreach (var col in this.columnOptions.AdditionalColumns)
{
additionalColumnNames.Add(col.ColumnName);
}
}
if (this.columnOptions.Store.Contains(StandardColumn.LogEvent))
{
jsonLogEventFormatter = new JsonLogEventFormatter(this);
}
eventTable = CreateDataTable();
if (autoCreateSqlTable)
{
try
{
SqlTableCreator tableCreator = new SqlTableCreator(this.connectionString, this.schemaName, this.tableName, eventTable, this.columnOptions);
tableCreator.CreateTable(); // return code ignored, 0 = failure?
}
catch (Exception ex)
{
SelfLog.WriteLine($"Exception creating table {tableName}:\n{ex}");
}
}
}
/// <summary>
/// Simplify the object so as to make handling the serialized
/// representation easier.
/// </summary>
/// <param name="value">The value to simplify (possibly null).</param>
/// <param name="options">Options to use during formatting</param>
/// <returns>A simplified representation.</returns>
public static string Simplify(LogEventPropertyValue value, ColumnOptions.PropertiesColumnOptions options)
{
var scalar = value as ScalarValue;
if (scalar != null)
return SimplifyScalar(scalar.Value);
var dict = value as DictionaryValue;
if (dict != null)
{
var sb = new StringBuilder();
bool isEmpty = true;
foreach (var element in dict.Elements)
{
var itemValue = Simplify(element.Value, options);
if (options.OmitElementIfEmpty && string.IsNullOrEmpty(itemValue))
{
continue;
}
if (isEmpty)
{
isEmpty = false;
if (!options.OmitDictionaryContainerElement)
{
sb.AppendFormat("<{0}>", options.DictionaryElementName);
}
}
var key = SimplifyScalar(element.Key);
if (options.UsePropertyKeyAsElementName)
{
sb.AppendFormat("<{0}>{1}</{0}>", GetValidElementName(key), itemValue);
}
else
{
sb.AppendFormat("<{0} key='{1}'>{2}</{0}>", options.ItemElementName, key, itemValue);
}
}
if (!isEmpty && !options.OmitDictionaryContainerElement)
{
sb.AppendFormat("</{0}>", options.DictionaryElementName);
}
return sb.ToString();
}
var seq = value as SequenceValue;
if (seq != null)
{
var sb = new StringBuilder();
bool isEmpty = true;
foreach (var element in seq.Elements)
{
var itemValue = Simplify(element, options);
if (options.OmitElementIfEmpty && string.IsNullOrEmpty(itemValue))
{
continue;
}
if (isEmpty)
{
isEmpty = false;
if (!options.OmitSequenceContainerElement)
{
sb.AppendFormat("<{0}>", options.SequenceElementName);
}
}
sb.AppendFormat("<{0}>{1}</{0}>", options.ItemElementName, itemValue);
}
if (!isEmpty && !options.OmitSequenceContainerElement)
{
sb.AppendFormat("</{0}>", options.SequenceElementName);
}
return sb.ToString();
}
var str = value as StructureValue;
if (str != null)
{
var props = str.Properties.ToDictionary(p => p.Name, p => Simplify(p.Value, options));
var sb = new StringBuilder();
bool isEmpty = true;
foreach (var element in props)
{
var itemValue = element.Value;
if (options.OmitElementIfEmpty && string.IsNullOrEmpty(itemValue))
{
continue;
}
if (isEmpty)
{
isEmpty = false;
if (!options.OmitStructureContainerElement)
{
if (options.UsePropertyKeyAsElementName)
{
sb.AppendFormat("<{0}>", GetValidElementName(str.TypeTag));
}
else
{
sb.AppendFormat("<{0} type='{1}'>", options.StructureElementName, str.TypeTag);
}
}
}
if (options.UsePropertyKeyAsElementName)
{
sb.AppendFormat("<{0}>{1}</{0}>", GetValidElementName(element.Key), itemValue);
}
else
{
sb.AppendFormat("<{0} key='{1}'>{2}</{0}>", options.PropertyElementName,
element.Key, itemValue);
}
}
if (!isEmpty && !options.OmitStructureContainerElement)
{
if (options.UsePropertyKeyAsElementName)
{
sb.AppendFormat("</{0}>", GetValidElementName(str.TypeTag));
}
else
{
sb.AppendFormat("</{0}>", options.StructureElementName);
}
}
return sb.ToString();
}
return null;
}
/// <summary>
/// Populate ColumnOptions properties and collections from app config
/// </summary>
internal static ColumnOptions ConfigureColumnOptions(MSSqlServerConfigurationSection config, ColumnOptions columnOptions)
{
var opts = columnOptions ?? new ColumnOptions();
AddRmoveStandardColumns();
AddAdditionalColumns();
ReadStandardColumns();
ReadMiscColumnOptions();
return(opts);
void AddRmoveStandardColumns()
{
// add standard columns
if (config.AddStandardColumns.Count > 0)
{
foreach (StandardColumnConfig col in config.AddStandardColumns)
{
if (Enum.TryParse(col.Name, ignoreCase: true, result: out StandardColumn stdcol) &&
!opts.Store.Contains(stdcol))
{
opts.Store.Add(stdcol);
}
}
}
// remove standard columns
if (config.RemoveStandardColumns.Count > 0)
{
foreach (StandardColumnConfig col in config.RemoveStandardColumns)
{
if (Enum.TryParse(col.Name, ignoreCase: true, result: out StandardColumn stdcol) &&
opts.Store.Contains(stdcol))
{
opts.Store.Remove(stdcol);
}
}
}
}
void AddAdditionalColumns()
{
if (config.Columns.Count > 0)
{
foreach (ColumnConfig c in config.Columns)
{
if (!string.IsNullOrWhiteSpace(c.ColumnName))
{
if (opts.AdditionalColumns == null)
{
opts.AdditionalColumns = new Collection <SqlColumn>();
}
opts.AdditionalColumns.Add(c.AsSqlColumn());
}
}
}
}
void ReadStandardColumns()
{
SetCommonColumnOptions(config.Exception, opts.Exception);
SetCommonColumnOptions(config.Id, opts.Id);
SetCommonColumnOptions(config.Level, opts.Level);
SetCommonColumnOptions(config.LogEvent, opts.LogEvent);
SetCommonColumnOptions(config.Message, opts.Message);
SetCommonColumnOptions(config.MessageTemplate, opts.MessageTemplate);
SetCommonColumnOptions(config.PropertiesColumn, opts.Properties);
SetCommonColumnOptions(config.TimeStamp, opts.TimeStamp);
SetProperty.IfProvided <bool>(config.Level, "StoreAsEnum", (val) => opts.Level.StoreAsEnum = val);
SetProperty.IfProvided <bool>(config.LogEvent, "ExcludeStandardColumns", (val) => opts.LogEvent.ExcludeStandardColumns = val);
SetProperty.IfProvided <bool>(config.LogEvent, "ExcludeAdditionalProperties", (val) => opts.LogEvent.ExcludeAdditionalProperties = val);
SetProperty.IfProvided <bool>(config.PropertiesColumn, "ExcludeAdditionalProperties", (val) => opts.Properties.ExcludeAdditionalProperties = val);
SetProperty.IfProvided <string>(config.PropertiesColumn, "DictionaryElementName", (val) => opts.Properties.DictionaryElementName = val);
SetProperty.IfProvided <string>(config.PropertiesColumn, "ItemElementName", (val) => opts.Properties.ItemElementName = val);
SetProperty.IfProvided <bool>(config.PropertiesColumn, "OmitDictionaryContainerElement", (val) => opts.Properties.OmitDictionaryContainerElement = val);
SetProperty.IfProvided <bool>(config.PropertiesColumn, "OmitSequenceContainerElement", (val) => opts.Properties.OmitSequenceContainerElement = val);
SetProperty.IfProvided <bool>(config.PropertiesColumn, "OmitStructureContainerElement", (val) => opts.Properties.OmitStructureContainerElement = val);
SetProperty.IfProvided <bool>(config.PropertiesColumn, "OmitElementIfEmpty", (val) => opts.Properties.OmitElementIfEmpty = val);
SetProperty.IfProvided <string>(config.PropertiesColumn, "PropertyElementName", (val) => opts.Properties.PropertyElementName = val);
SetProperty.IfProvided <string>(config.PropertiesColumn, "RootElementName", (val) => opts.Properties.RootElementName = val);
SetProperty.IfProvided <string>(config.PropertiesColumn, "SequenceElementName", (val) => opts.Properties.SequenceElementName = val);
SetProperty.IfProvided <string>(config.PropertiesColumn, "StructureElementName", (val) => opts.Properties.StructureElementName = val);
SetProperty.IfProvided <bool>(config.PropertiesColumn, "UsePropertyKeyAsElementName", (val) => opts.Properties.UsePropertyKeyAsElementName = val);
SetProperty.IfProvided <bool>(config.TimeStamp, "ConvertToUtc", (val) => opts.TimeStamp.ConvertToUtc = val);
// Standard Columns are subclasses of the SqlColumn class
void SetCommonColumnOptions(ColumnConfig source, SqlColumn target)
{
SetProperty.IfProvidedNotEmpty <string>(source, "ColumnName", (val) => target.ColumnName = val);
SetProperty.IfProvided <string>(source, "DataType", (val) => target.SetDataTypeFromConfigString(val));
SetProperty.IfProvided <bool>(source, "AllowNull", (val) => target.AllowNull = val);
SetProperty.IfProvided <int>(source, "DataLength", (val) => target.DataLength = val);
SetProperty.IfProvided <bool>(source, "NonClusteredIndex", (val) => target.NonClusteredIndex = val);
}
}
void ReadMiscColumnOptions()
{
SetProperty.IfProvided <bool>(config, "DisableTriggers", (val) => opts.DisableTriggers = val);
SetProperty.IfProvided <bool>(config, "ClusteredColumnstoreIndex", (val) => opts.ClusteredColumnstoreIndex = val);
string pkName = null;
SetProperty.IfProvidedNotEmpty <string>(config, "PrimaryKeyColumnName", (val) => pkName = val);
if (pkName != null)
{
if (opts.ClusteredColumnstoreIndex)
{
throw new ArgumentException("SQL Clustered Columnstore Indexes and primary key constraints are mutually exclusive.");
}
foreach (var standardCol in opts.Store)
{
var stdColOpts = opts.GetStandardColumnOptions(standardCol);
if (pkName.Equals(stdColOpts.ColumnName, StringComparison.InvariantCultureIgnoreCase))
{
opts.PrimaryKey = stdColOpts;
break;
}
}
if (opts.PrimaryKey == null && opts.AdditionalColumns != null)
{
foreach (var col in opts.AdditionalColumns)
{
if (pkName.Equals(col.ColumnName, StringComparison.InvariantCultureIgnoreCase))
{
opts.PrimaryKey = col;
break;
}
}
}
if (opts.PrimaryKey == null)
{
throw new ArgumentException($"Could not match the configured primary key column name \"{pkName}\" with a data column in the table.");
}
}
}
}
private ILogger CreateLogger(ISettings settings, string fileName = null, string tableName = null, ICollection<DataColumn> additionalColumns = null, bool? logToFile = null, bool? logToSql = null)
{
var logsPath = string.IsNullOrEmpty(Environment.GetEnvironmentVariable("WEBSITE_SITE_NAME"))
? HostingEnvironment.ApplicationPhysicalPath
: Path.Combine(Environment.GetEnvironmentVariable("HOME"), "LogFiles");
var logger = new LoggerConfiguration()
.Enrich.With<HttpRequestIdEnricher>()
.Enrich.With<UserNameEnricher>()
.Enrich.With<EventIdEnricher>();
if (logToFile ?? settings.LogToFile && !string.IsNullOrEmpty(fileName))
{
logger = logger.WriteTo.RollingFile(Path.Combine(logsPath, fileName), fileSizeLimitBytes: null);
}
if (logToSql ?? settings.LogToSql &&
!string.IsNullOrEmpty(settings.LoggingSqlServerConnectionString) &&
!string.IsNullOrEmpty(tableName))
{
var columnOptions = new ColumnOptions
{
AdditionalDataColumns = new Collection<DataColumn>
{
new DataColumn {DataType = typeof(string), ColumnName = "UserName"},
new DataColumn {DataType = typeof(string), ColumnName = "HttpRequestId"},
new DataColumn {DataType = typeof(int), ColumnName = "EventId"}
}
.Union(additionalColumns ?? Enumerable.Empty<DataColumn>())
.ToList()
};
logger = logger.WriteTo.MSSqlServer(settings.LoggingSqlServerConnectionString, tableName, restrictedToMinimumLevel: LogEventLevel.Information, columnOptions: columnOptions);
}
return logger.CreateLogger();
}
public ColumnOptions ConfigureColumnOptions(MSSqlServerConfigurationSection config, ColumnOptions columnOptions) => _columnOptionsProvider.ConfigureColumnOptions(config, columnOptions);
/// <summary>
/// Create or add to the ColumnOptions object and apply any configuration changes to it.
/// </summary>
/// <param name="columnOptions">An optional externally-created ColumnOptions object to be updated with additional configuration values.</param>
/// <param name="config">A configuration section typically named "columnOptionsSection" (see docs).</param>
/// <returns></returns>
internal static ColumnOptions ConfigureColumnOptions(ColumnOptions columnOptions, IConfigurationSection config)
{
// Do not use configuration binding (ie GetSection.Get<ColumnOptions>). That will create a new
// ColumnOptions object which would overwrite settings if the caller passed in a ColumnOptions
// object via the extension method's columnOptions parameter.
var opts = columnOptions ?? new ColumnOptions();
if (config == null || !config.GetChildren().Any())
{
return(opts);
}
AddRemoveStandardColumns();
AddAdditionalColumns();
ReadStandardColumns();
ReadMiscColumnOptions();
return(opts);
void AddRemoveStandardColumns()
{
// add standard columns
var addStd = config.GetSection("addStandardColumns");
if (addStd.GetChildren().Any())
{
foreach (var col in addStd.GetChildren().ToList())
{
if (Enum.TryParse(col.Value, ignoreCase: true, result: out StandardColumn stdcol) &&
!opts.Store.Contains(stdcol))
{
opts.Store.Add(stdcol);
}
}
}
// remove standard columns
var removeStd = config.GetSection("removeStandardColumns");
if (removeStd.GetChildren().Any())
{
foreach (var col in removeStd.GetChildren().ToList())
{
if (Enum.TryParse(col.Value, ignoreCase: true, result: out StandardColumn stdcol) &&
opts.Store.Contains(stdcol))
{
opts.Store.Remove(stdcol);
}
}
}
}
void AddAdditionalColumns()
{
var newcols =
config.GetSection("additionalColumns").Get <List <SqlColumn> >()
?? config.GetSection("customColumns").Get <List <SqlColumn> >(); // backwards-compatibility
if (newcols != null)
{
foreach (var c in newcols)
{
if (!string.IsNullOrWhiteSpace(c.ColumnName))
{
if (opts.AdditionalColumns == null)
{
opts.AdditionalColumns = new Collection <SqlColumn>();
}
opts.AdditionalColumns.Add(c);
}
}
}
}
void ReadStandardColumns()
{
var section = config.GetSection("id");
if (section != null)
{
SetCommonColumnOptions(opts.Id);
#pragma warning disable 618 // deprecated: BigInt property
SetProperty.IfNotNull <bool>(section["bigInt"], (val) => opts.Id.BigInt = val);
#pragma warning restore 618
}
section = config.GetSection("level");
if (section != null)
{
SetCommonColumnOptions(opts.Level);
SetProperty.IfNotNull <bool>(section["storeAsEnum"], (val) => opts.Level.StoreAsEnum = val);
}
section = config.GetSection("properties");
if (section != null)
{
SetCommonColumnOptions(opts.Properties);
SetProperty.IfNotNull <bool>(section["excludeAdditionalProperties"], (val) => opts.Properties.ExcludeAdditionalProperties = val);
SetProperty.IfNotNull <string>(section["dictionaryElementName"], (val) => opts.Properties.DictionaryElementName = val);
SetProperty.IfNotNull <string>(section["itemElementName"], (val) => opts.Properties.ItemElementName = val);
SetProperty.IfNotNull <bool>(section["omitDictionaryContainerElement"], (val) => opts.Properties.OmitDictionaryContainerElement = val);
SetProperty.IfNotNull <bool>(section["omitSequenceContainerElement"], (val) => opts.Properties.OmitSequenceContainerElement = val);
SetProperty.IfNotNull <bool>(section["omitStructureContainerElement"], (val) => opts.Properties.OmitStructureContainerElement = val);
SetProperty.IfNotNull <bool>(section["omitElementIfEmpty"], (val) => opts.Properties.OmitElementIfEmpty = val);
SetProperty.IfNotNull <string>(section["propertyElementName"], (val) => opts.Properties.PropertyElementName = val);
SetProperty.IfNotNull <string>(section["rootElementName"], (val) => opts.Properties.RootElementName = val);
SetProperty.IfNotNull <string>(section["sequenceElementName"], (val) => opts.Properties.SequenceElementName = val);
SetProperty.IfNotNull <string>(section["structureElementName"], (val) => opts.Properties.StructureElementName = val);
SetProperty.IfNotNull <bool>(section["usePropertyKeyAsElementName"], (val) => opts.Properties.UsePropertyKeyAsElementName = val);
// TODO PropertiesFilter would need a compiled Predicate<string> (high Roslyn overhead, see Serilog Config repo #106)
}
section = config.GetSection("timeStamp");
if (section != null)
{
SetCommonColumnOptions(opts.TimeStamp);
SetProperty.IfNotNull <bool>(section["convertToUtc"], (val) => opts.TimeStamp.ConvertToUtc = val);
}
section = config.GetSection("logEvent");
if (section != null)
{
SetCommonColumnOptions(opts.LogEvent);
SetProperty.IfNotNull <bool>(section["excludeAdditionalProperties"], (val) => opts.LogEvent.ExcludeAdditionalProperties = val);
SetProperty.IfNotNull <bool>(section["ExcludeStandardColumns"], (val) => opts.LogEvent.ExcludeStandardColumns = val);
}
section = config.GetSection("message");
if (section != null)
{
SetCommonColumnOptions(opts.Message);
}
section = config.GetSection("exception");
if (section != null)
{
SetCommonColumnOptions(opts.Exception);
}
section = config.GetSection("messageTemplate");
if (section != null)
{
SetCommonColumnOptions(opts.MessageTemplate);
}
// Standard Columns are subclasses of the SqlColumn class
void SetCommonColumnOptions(SqlColumn target)
{
SetProperty.IfNotNullOrEmpty <string>(section["columnName"], (val) => target.ColumnName = val);
SetProperty.IfNotNull <string>(section["dataType"], (val) => target.SetDataTypeFromConfigString(val));
SetProperty.IfNotNull <bool>(section["allowNull"], (val) => target.AllowNull = val);
SetProperty.IfNotNull <int>(section["dataLength"], (val) => target.DataLength = val);
SetProperty.IfNotNull <bool>(section["nonClusteredIndex"], (val) => target.NonClusteredIndex = val);
}
}
void ReadMiscColumnOptions()
{
SetProperty.IfNotNull <bool>(config["disableTriggers"], (val) => opts.DisableTriggers = val);
SetProperty.IfNotNull <bool>(config["clusteredColumnstoreIndex"], (val) => opts.ClusteredColumnstoreIndex = val);
string pkName = config["primaryKeyColumnName"];
if (!string.IsNullOrEmpty(pkName))
{
if (opts.ClusteredColumnstoreIndex)
{
throw new ArgumentException("SQL Clustered Columnstore Indexes and primary key constraints are mutually exclusive.");
}
foreach (var standardCol in opts.Store)
{
var stdColOpts = opts.GetStandardColumnOptions(standardCol);
if (pkName.Equals(stdColOpts.ColumnName, StringComparison.InvariantCultureIgnoreCase))
{
opts.PrimaryKey = stdColOpts;
break;
}
}
if (opts.PrimaryKey == null && opts.AdditionalColumns != null)
{
foreach (var col in opts.AdditionalColumns)
{
if (pkName.Equals(col.ColumnName, StringComparison.InvariantCultureIgnoreCase))
{
opts.PrimaryKey = col;
break;
}
}
}
if (opts.PrimaryKey == null)
{
throw new ArgumentException($"Could not match the configured primary key column name \"{pkName}\" with a data column in the table.");
}
}
}
}
public MSSqlServerSinkTraits(string connectionString, string tableName, string schemaName, ColumnOptions columnOptions, IFormatProvider formatProvider, bool autoCreateSqlTable)
{
if (string.IsNullOrWhiteSpace(connectionString))
{
throw new ArgumentNullException(nameof(connectionString));
}
if (string.IsNullOrWhiteSpace(tableName))
{
throw new ArgumentNullException(nameof(tableName));
}
ConnectionString = connectionString;
TableName = tableName;
SchemaName = schemaName;
ColumnOptions = columnOptions ?? new ColumnOptions();
FormatProvider = formatProvider;
if (ColumnOptions.AdditionalDataColumns != null)
{
AdditionalDataColumnNames = new HashSet <string>(ColumnOptions.AdditionalDataColumns.Select(c => c.ColumnName), StringComparer.OrdinalIgnoreCase);
}
if (ColumnOptions.Store.Contains(StandardColumn.LogEvent))
{
JsonFormatter = new JsonFormatter(formatProvider: formatProvider);
}
EventTable = CreateDataTable();
if (autoCreateSqlTable)
{
try
{
SqlTableCreator tableCreator = new SqlTableCreator(connectionString, SchemaName);
tableCreator.CreateTable(EventTable);
}
catch (Exception ex)
{
SelfLog.WriteLine("Exception {0} caught while creating table {1} to the database specified in the Connection string.", ex, tableName);
}
}
}
public ColumnOptions ConfigureColumnOptions(ColumnOptions columnOptions, IConfigurationSection config) => _columnOptionsProvider.ConfigureColumnOptions(columnOptions, config);
/// <summary>
/// Generate an array of DataColumns using the supplied MSSqlServerConfigurationSection,
/// which is an array of keypairs defining the SQL column name and SQL data type
/// Entries are appended to a list of DataColumns in column options
/// </summary>
/// <param name="serviceConfigSection">A previously loaded configuration section</param>
/// <param name="columnOptions">column options with existing array of columns to append our config columns to</param>
private static void GenerateDataColumnsFromConfig(MSSqlServerConfigurationSection serviceConfigSection,
ColumnOptions columnOptions)
{
foreach (ColumnConfig c in serviceConfigSection.Columns)
{
// Set the type based on the defined SQL type from config
DataColumn column = new DataColumn(c.ColumnName);
Type dataType = null;
switch (c.DataType)
{
case "bigint":
dataType = Type.GetType("System.Int64");
break;
case "bit":
dataType = Type.GetType("System.Boolean");
break;
case "char":
case "nchar":
case "ntext":
case "nvarchar":
case "text":
case "varchar":
dataType = Type.GetType("System.String");
break;
case "date":
case "datetime":
case "datetime2":
case "smalldatetime":
dataType = Type.GetType("System.DateTime");
break;
case "decimal":
case "money":
case "numeric":
case "smallmoney":
dataType = Type.GetType("System.Decimal");
break;
case "float":
dataType = Type.GetType("System.Double");
break;
case "int":
dataType = Type.GetType("System.Int32");
break;
case "real":
dataType = Type.GetType("System.Single");
break;
case "smallint":
dataType = Type.GetType("System.Int16");
break;
case "time":
dataType = Type.GetType("System.TimeSpan");
break;
case "uniqueidentifier":
dataType = Type.GetType("System.Guid");
break;
}
column.DataType = dataType;
if (columnOptions.AdditionalDataColumns == null)
{
columnOptions.AdditionalDataColumns = new Collection<DataColumn>();
}
columnOptions.AdditionalDataColumns.Add(column);
}
}
public SqlTableCreator(string connectionString, string schemaName, string tableName, DataTable dataTable, ColumnOptions columnOptions)
{
this.connectionString = connectionString;
this.schemaName = schemaName;
this.tableName = tableName;
this.dataTable = dataTable;
this.columnOptions = columnOptions;
}
