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 reflectionTo 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,        };    }}T4The 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);}#>
SqlServer database reflection and autogenerating NLog.xml
c#;beginner;xml;t sql;t4