2
\$\begingroup\$

I use NLog everywhere and I really don't like writing the inserts everytime. You need to write the same variable three times: two for the insert and one for the parameter for example:

<target xsi:type="Database" name="Log" dbProvider="System.Data.SqlClient" connectionString="...">
    <commandText>
        INSERT
        INTO [dbo].[Log]([Foo], [Bar], ...)
        VALUES(@FOO, @BAR, ...)
    </commandText>
    <parameter name="@FOO" layout="..." />
    <parameter name="@BAR" layout="..." />
    ...   
</target>

It's very error-prone (I always make a mistake somewhere by forgetting one or two of them) and hard to maintain.

I just want to add the parameters and create the INSERT automatically because it's nothing unusual and can be automated so I came up with this idea:

The commandText must not be empty for database targets and I also need to somehow specify the table name. So initially I put the table name there.

<target xsi:type="Database" name="TestLog" commandText="[dbo].[TestLog]" dbProvider="System.Data.SqlClient" connectionString="Data Source=(local);Initial Catalog=TestDb;Integrated Security=True;Connect Timeout=30">
    <parameter name="@Timestamp" layout="${longdate:universalTime=true}" />
    <parameter name="@LogLevel" layout="${level:uppercase=true}" />
    <parameter name="@Logger" layout="${logger}" />
    <parameter name="@Message" layout="${message}" />
    <parameter name="@Exception" layout="${onexception:${exceptionLayout}}" />
</target>

In this case the INSERT would be:

INSERT INTO [dbo].[TestLog](Timestamp, LogLevel, Logger, Message, Exception) 
VALUES(@Timestamp, @LogLevel, @Logger, @Message, @Exception)

Next I use a set of extensions to update the commandText. Fortunatelly the CommandText property has a public setter.

static class NLogExtensions
{
    public static void UpdateCommandText(this NLog.Config.LoggingConfiguration config)
    {
        var tableNameMatcher = new Regex(@"^(\[(?<schemaName>.+?)\].)?\[(?<tableName>.+?)\]$");

        var autoCommandTextDatabaseTargets =
            config.AllTargets
                .OfType<DatabaseTarget>()
                .Where(x => tableNameMatcher.IsMatch(x.CommandText()))
                .Select(x => x);

        foreach (var databaseTarget in autoCommandTextDatabaseTargets)
        {
            databaseTarget.CommandText = databaseTarget.CreateCommandText();
        }
    }

    public static string CommandText(this DatabaseTarget databaseTarget)
    {
        return ((NLog.Layouts.SimpleLayout)databaseTarget.CommandText).OriginalText;
    }

    public static string CreateCommandText(this DatabaseTarget databaseTarget)
    {
        const string insertQueryTemplate = "INSERT INTO {0}({1}) VALUES({2})";

        return string.Format(
                insertQueryTemplate,
                databaseTarget.CommandText(),
                string.Join(", ", databaseTarget.Parameters.Select(x => x.Name.TrimStart('@'))),
                string.Join(", ", databaseTarget.Parameters.Select(x => x.Name)));
    }
}

Then I use it like this:

    var config = NLog.LogManager.Configuration;
    config.UpdateCommandText();

    var logger = NLog.LogManager.GetLogger("Program");
    logger.Debug("Hallo NLog!");

I cannot use C# 6

\$\endgroup\$
  • \$\begingroup\$ Is there any reason for CreateCommandText() being public ? \$\endgroup\$ – Heslacher Sep 7 '16 at 16:00
  • \$\begingroup\$ @Heslacher not really, I just thought maybe I use it later for something else but here it could be private indeed - actually it should be internal \$\endgroup\$ – t3chb0t Sep 7 '16 at 16:02
  • \$\begingroup\$ If you intend to use it somewhete else you should at least add a null check for the methods argument. \$\endgroup\$ – Heslacher Sep 7 '16 at 16:04
1
\$\begingroup\$

I've splitted this class into multiple pieces and added a new feature that now allows me to insert NULL into nullable columns.

It starts with the LoggingConfigurationExtensions to get the DatabaseTargets

public static class LoggingConfigurationExtensions
{
    public static IEnumerable<DatabaseTarget> DatabaseTargets(
        this LoggingConfiguration loggingConfiguration, 
        Func<DatabaseTarget, bool> predicate
    )
    {
        return loggingConfiguration.AllTargets.OfType<DatabaseTarget>().Where(predicate);
    }
}

The query generation logic now belongs to the DatabaseTargetQueryGenerator

public static class DatabaseTargetQueryGenerator
{
    public static void GenerateInsertQueries()
    {
        // The commandText attribute must conatain at least the table name. 
        // Each identifier must be enclosed in square brackets: [schemaName].[tableName].

        // https://msdn.microsoft.com/en-us/library/ms175874.aspx?f=255&MSPPError=-2147217396
        // https://regex101.com/r/19GjqR/1
        // h t t ps://regex101.com/delete/QObrsCHVjaX8gGCysAn6yApJ

        var tableNameMatcher = new Regex(@"^(\[(?<schemaName>[a-z_][a-z0-9_@$#]*)\].)?\[(?<tableName>[a-z_][a-z0-9_@$#]*)\]$");

        var databaseTargets = LogManager.Configuration.DatabaseTargets(databaseTarget => tableNameMatcher.IsMatch(databaseTarget.CommandText()));
        foreach (var databaseTarget in databaseTargets)
        {
            databaseTarget.CommandText = databaseTarget.GenerateInsertQuery();
        }
    }

    private static string GenerateInsertQuery(this DatabaseTarget databaseTarget)
    {
        const string insertQueryTemplate = "INSERT INTO {0}({1}) VALUES({2})";

        return string.Format(
            insertQueryTemplate,
            databaseTarget.CommandText(),
            string.Join(", ", databaseTarget.Parameters.Select(x => x.Name())),
            string.Join(", ", databaseTarget.Parameters.Select(x =>
            {
                var sql =
                    x.Nullable()
                        ? string.Format("NULLIF({0}, '')", x.FullName())
                        : x.FullName();

                // Rename the SqlParameter because otherwise SqlCommand will complain about it.
                x.Name = x.FullName();

                return sql;
            })));
    }
}

another small piece helps me to get the actual commandText

public static class DatabaseTargetExtensions
{
    public static string CommandText(this DatabaseTarget databaseTarget)
    {
        return ((SimpleLayout)databaseTarget.CommandText).OriginalText;
    }
}

these extensions encapsulate the parameter name:

public static class DatabaseParameterInfoExtensions
{
    // https://regex101.com/r/wgoA3q/1
    // h t t ps://regex101.com/delete/BDmR7fAqwYQiT5DW5PKAJFAm

    private static readonly Regex ParamRegex = new Regex("^(?<prefix>.)(?<name>[a-z0-9_-]+)(?:[:](?<null>null))?", RegexOptions.IgnoreCase);

    public static string Prefix(this DatabaseParameterInfo parameter)
    {
        return ParamRegex.Match(parameter.Name).Groups["prefix"].Value;
    }

    public static string Name(this DatabaseParameterInfo parameter)
    {
        return ParamRegex.Match(parameter.Name).Groups["name"].Value;
    }

    public static string FullName(this DatabaseParameterInfo parameter)
    {
        return string.Format("{0}{1}", parameter.Prefix(), parameter.Name());
    }

    public static bool Nullable(this DatabaseParameterInfo parameter)
    {
        return ParamRegex.Match(parameter.Name).Groups["null"].Success;
    }
}

this means I can now create a config like this one

<target xsi:type="Database" name="Log" commandText="[dbo].[Log]" dbProvider="System.Data.SqlClient" connectionString="..">
  <parameter name="@Timestamp" layout="${longdate:universalTime=true}" />
  <parameter name="@LogLevel" layout="${level:uppercase=true}" />
  <parameter name="@Logger" layout="${logger}" />
  <parameter name="@Message" layout="${message}" />
  <parameter name="@Exception:null" layout="${onexception:${exceptionLayout}}" />
</target>

where

  • commandText="[dbo].[Log] specify the [Schema].[Table] to generate the INSERT for

  • @Exception:null means it is nullable column

|improve this answer|||||
\$\endgroup\$

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.