Generating INSERT query from parameters

Question

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

Answer 1

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