3
\$\begingroup\$

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 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);
}
#>
\$\endgroup\$
1
\$\begingroup\$

T4 Review


NLog.xsd is available, so you could have saved yourself the trouble manually creating the xml, when you could have generated classes from the xsd (using T4 perhaps :p) instead. Then you could create the NLog.xml in memory using POCO's before serialising it using built-in xml serialisation.


With your current settings, your xml is generated nested below the T4.

<#@ template debug="false" hostspecific="false" language="C#" #>
// ..
<#@ output extension=".xml" #>

But you might want to generate the xml in a different location. What you could is attaching the T4 to the host (VS IDE) and writing to a different path. The standard output file will only contain log entries.

<#@ template debug="false" hostspecific="true" language="C#" #>
// ..
<#@ output extension=".log" #>

<# 
 // Get the Visual Studio API as a service:
 var dte = ((IServiceProvider)this.Host).GetCOMService(typeof(DTE)) as DTE;

 // Get the location of the NLog.xml file:
 var nlogFile = this.Host.ResolvePath(@"Configuration\NLog.xml");
#>

Writing to another file than the standard output is facilitated using TemplateFileManager. A tutorial can be found here.

<#@ include file="TemplateFileManagerV2.1.ttinclude" #>

<# 
    TemplateFileManager manager = TemplateFileManager.Create(this); 
    // create a new output file 
    manager.StartNewFile(nlogFile);
#>

One massive step forward is to use Runtime T4, rather than Design T4. This way you can build re-usable templates with markup and code-behind. Tangible T4 Extensions for VS is available as plugin for an IDE. I leave it up to you to find out how these work. I use Runtime T4 to generate classes all over my solution.sln. The power of these beasts is huge!

|improve this answer|||||
\$\endgroup\$
  • 1
    \$\begingroup\$ I'll try these tricks with my next template... but maybe you have some ideas what else I could generate? :-P The tangible extensions seems worth buying, and even the price is ok. If JetBrains really implements their own T4 engine in Rider this would be awsome. \$\endgroup\$ – t3chb0t Jul 29 '19 at 7:28
  • \$\begingroup\$ Good candidates to template are unit tests (or skeletonts), entity builders, DTO and DAO objects from business entities, end user email templates, state machines from xml or uml. \$\endgroup\$ – dfhwze Jul 29 '19 at 14:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.