This is my first attempt at using T4 templates and I thought I generate the NLog.xml with it by getting the tables and the columns from the database to automatically create all targets.
SqlServer reflection
To get the information about the log tables I use a couple of helper methods and classes that support the database reflection via the GetSchema method. I then translate the resulting DataTables into custom classes that I can easier use inside the template.
The main two are:
public static class SqlConnectionExtensions
{
public static List<TableInfo> GetTables(this SqlConnection conn, string database, string tableSchema)
{
using (var columns = conn.GetSchema(SchemaCollectionNames.Tables, new SchemaRestriction
{
Catalog = database,
Owner = tableSchema
}))
{
return columns.AsEnumerable().Select(x => new TableInfo(x)).ToList();
}
}
public static List<ColumnInfo> GetColumns(this SqlConnection conn, string tableSchema, string tableName)
{
using (var columns = conn.GetSchema(SchemaCollectionNames.Columns, new SchemaRestriction
{
Owner = tableSchema,
Table = tableName
}))
{
return columns.AsEnumerable().Select(x => new ColumnInfo(x)).ToList();
}
}
}
public class SqlServer
{
public List<TableInfo> GetTables(string connectionString, string database, string tableSchema)
{
using(var conn = new SqlConnection(connectionString))
{
conn.Open();
return conn.GetTables(database, tableSchema);
}
}
public List<ColumnInfo> GetColumns(string connectionString, string tableSchema, string tableName)
{
using (var conn = new SqlConnection(connectionString))
{
conn.Open();
return conn.GetColumns(tableSchema, tableName);
}
}
}
They are supported by another few classes that hold the schema information and a few constants:
[AttributeUsage(AttributeTargets.Property)]
public class SchemaColumnNameAttribute : Attribute
{
private readonly string _name;
public SchemaColumnNameAttribute(string name) => _name = name;
public override string ToString() => _name;
}
public class TableInfo
{
public TableInfo(DataRow row)
{
var properties = typeof(TableInfo).GetProperties(BindingFlags.Public | BindingFlags.Instance);
foreach (var property in properties)
{
var value = row[property.GetCustomAttribute<SchemaColumnNameAttribute>().ToString()];
property.SetValue(this, DBNull.Value.Equals(value) ? null : value);
}
}
[SchemaColumnName("table_catalog")]
public string TableCatalog { get; set; }
[SchemaColumnName("table_schema")]
public string TableSchema { get; set; }
[SchemaColumnName("table_name")]
public string TableName { get; set; }
[SchemaColumnName("table_type")]
public string TableType { get; set; }
}
public class ColumnInfo
{
public ColumnInfo(DataRow row)
{
var properties = typeof(ColumnInfo).GetProperties(BindingFlags.Public | BindingFlags.Instance);
foreach (var property in properties)
{
var value = row[property.GetCustomAttribute<SchemaColumnNameAttribute>().ToString()];
property.SetValue(this, DBNull.Value.Equals(value) ? null : value);
}
}
[SchemaColumnName("column_name")]
public string ColumnName { get; set; }
[SchemaColumnName("ordinal_position")]
public int OrdinalPosition { get; set; }
[SchemaColumnName("column_default")]
public string ColumnDefault { get; set; }
[SchemaColumnName("is_nullable")]
public string IsNullable { get; set; }
[SchemaColumnName("data_type")]
public string DataType { get; set; }
[SchemaColumnName("character_maximum_length")]
public int CharacterMaximumLength { get; set; }
[SchemaColumnName("numeric_precision")]
public byte NumericPrecision { get; set; }
[SchemaColumnName("numeric_precision_radix")]
public short? NumericPrecisionRadix { get; set; }
[SchemaColumnName("numeric_scale")]
public int? NumericScale { get; set; }
[SchemaColumnName("datetime_precision")]
public short? DatetimePrecision { get; set; }
[SchemaColumnName("character_set_name")]
public string CharacterSetName { get; set; }
}
public static class SchemaCollectionNames
{
public const string Columns = "Columns";
public const string Tables = "Tables";
}
public class SchemaRestriction
{
public string Catalog { get; set; }
public string Owner { get; set; }
public string Table { get; set; }
public string TableType { get; set; }
public static implicit operator string[] (SchemaRestriction schemaRestriction)
{
return new[]
{
schemaRestriction.Catalog,
schemaRestriction.Owner,
schemaRestriction.Table,
schemaRestriction.TableType,
};
}
}
T4
The database reflection code is compiled into a separate assembly that I reference in the template.
Finally I use it to generate the actual config with a T4 template which is the first time I wrote something like this (and because of this I set the beginner tag).
<#@ template debug="false" hostspecific="false" language="C#" #>
<#@ assembly name="System.Core" #>
<#@ assembly name="System.Data" #>
<#@ assembly name="$(ProjectDir)\$(OutDir)\Reusable.Core.dll" #>
<#@ import namespace="System.Linq" #>
<#@ import namespace="System.Text" #>
<#@ import namespace="System.Data" #>
<#@ import namespace="System.Data.SqlClient" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ import namespace="Reusable" #>
<#@ output extension=".xml" #>
<#
var connectionString = "...";
var database = "TestDb";
var schema = "dbo";
var defaultColumns = new [] { "Id", "Timestamp", "Environment", "LogLevel", "Logger", "ThreadId", "Message", "Exception" };
var indentWidth = 3;
var idColumn = "Id";
var defaultLog = "_MainLog";
var tablePrefix = "Test_";
#>
<?xml version="1.0" encoding="utf-8" ?>
<nlog
xmlns="http://www.nlog-project.org/schemas/NLog.xsd"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
internalLogFile="c:\temp\NLog.test.log"
internalLogLevel="Error">
<!-- *** Autogenerated (<#= DateTime.UtcNow.ToString() #> (UTC)) *** -->
<!-- sub-templates -->
<variable name="environmentNotAvailable" value="N/A" />
<variable name="environmentLayout" value="${whenEmpty:whenEmpty=${environmentNotAvailable}:inner=${event-context:item=Environment}}" />
<variable name="callsiteLayout" value="${callsite:className=true:fileName=true:methodName:true}" />
<variable name="exceptionLayout" value="${exception:format=tostring:separator= | :innerFormat=tostring,message:maxInnerExceptionLevel=7:innerExceptionSeparator= | }" />
<variable name="stackTraceLayout" value="${stacktrace:format=raw:topFrames=7:separator= » }" />
<!-- full templates -->
<variable name="debuggerLayout" value="DEBUG » ${level:uppercase=true} | ${message}${onexception:${newline}${exceptionLayout}}" />
<#
var tables = new SqlServer().GetTables(connectionString, database, schema).Where(t => t.TableName.StartsWith(tablePrefix)).ToList();
#>
<targets>
<target xsi:type="Debugger" name="debugger" layout="${debuggerLayout}" />
<#
foreach(var table in tables)
{
var columns = new SqlServer().GetColumns(connectionString, schema, table.TableName);
#>
<target xsi:type="Database" name="<#=table.TableName#>" dbProvider="System.Data.SqlClient" connectionString="<#=connectionString#>">
<commandText>
INSERT INTO [<#=schema#>].[<#=table.TableName#>] (
<#
foreach(var column in columns.Where(c => !c.ColumnName.Equals(idColumn, StringComparison.OrdinalIgnoreCase)).Select((x, i) => new { x, i }))
{
PushIndent(CreateIndent(indentWidth, 4));
Write(column.x.ColumnName);
WriteLine(column.i < columns.Count - 2 ? "," : string.Empty);
PopIndent();
}
#>
) VALUES (
<#
foreach(var column in columns.Where(c => !c.ColumnName.Equals(idColumn, StringComparison.OrdinalIgnoreCase)).Select((x, i) => new { x, i }))
{
PushIndent(CreateIndent(indentWidth, 4));
Write(column.x.IsNullable == "YES" ? "NULLIF(@" + column.x.ColumnName + ", '')" : "@" + column.x.ColumnName);
WriteLine(column.i < columns.Count - 2 ? "," : string.Empty);
PopIndent();
}
#>
)
</commandText>
<!-- Default columns -->
<parameter name="@Timestamp" layout="${longdate:universalTime=true}" />
<parameter name="@Environment" layout="${environmentLayout}" />
<parameter name="@LogLevel" layout="${level:uppercase=true}" />
<parameter name="@Logger" layout="${logger}" />
<parameter name="@Message" layout="${Message}" />
<parameter name="@Exception" layout="${onexception:${exceptionLayout}}" />
<!-- Custom columns -->
<#
foreach(var column in columns.Where(c => !defaultColumns.Contains(c.ColumnName, StringComparer.OrdinalIgnoreCase)))
{
#>
<parameter name="@<#=column.ColumnName#>" layout="${<# Write(column.DataType == "float" ? "invariant" : "event");#>-properties:item=<#=column.ColumnName#>}" />
<#
}
#>
</target>
<#
}
#>
</targets>
<rules>
<!-- Log everything to debugger -->
<logger name="*" minlevel="Trace" writeTo="debugger" enabled="true" final="false" />
<#
foreach(var table in tables.Where(t => !t.TableName.EndsWith(defaultLog)))
{
#>
<logger name="<#=table.TableName#>" appendTo="<#=table.TableName#>" minlevel="Debug" enabled="true" final="true" />
<#
}
#>
<#
var mainLog = tables.SingleOrDefault(t => t.TableName.EndsWith(defaultLog));
if (mainLog != null)
{
#>
<!-- Use the MainLog if no other log matches -->
<logger name="*" appendTo="<#=mainLog.TableName#>" minlevel="Debug" enabled="true" final="true" />
<#
}
#>
</rules>
</nlog>
<#+
private string CreateIndent(int width, int depth)
{
return new string(' ', width * depth);
}
#>
