Generating INSERT query from parameters 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:

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


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.

&lt;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"&gt;
    &lt;parameter name="@Timestamp" layout="${longdate:universalTime=true}" /&gt;
    &lt;parameter name="@LogLevel" layout="${level:uppercase=true}" /&gt;
    &lt;parameter name="@Logger" layout="${logger}" /&gt;
    &lt;parameter name="@Message" layout="${message}" /&gt;
    &lt;parameter name="@Exception" layout="${onexception:${exceptionLayout}}" /&gt;
&lt;/target&gt;


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(@"^(\[(?&lt;schemaName&gt;.+?)\].)?\[(?&lt;tableName&gt;.+?)\]$");

        var autoCommandTextDatabaseTargets =
            config.AllTargets
                .OfType&lt;DatabaseTarget&gt;()
                .Where(x =&gt; tableNameMatcher.IsMatch(x.CommandText()))
                .Select(x =&gt; 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 =&gt; x.Name.TrimStart('@'))),
                string.Join(", ", databaseTarget.Parameters.Select(x =&gt; 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
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&lt;DatabaseTarget&gt; DatabaseTargets(
        this LoggingConfiguration loggingConfiguration, 
        Func&lt;DatabaseTarget, bool&gt; predicate
    )
    {
        return loggingConfiguration.AllTargets.OfType&lt;DatabaseTarget&gt;().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&amp;MSPPError=-2147217396
        // https://regex101.com/r/19GjqR/1
        // h t t ps://regex101.com/delete/QObrsCHVjaX8gGCysAn6yApJ

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

        var databaseTargets = LogManager.Configuration.DatabaseTargets(databaseTarget =&gt; 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 =&gt; x.Name())),
            string.Join(", ", databaseTarget.Parameters.Select(x =&gt;
            {
                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("^(?&lt;prefix&gt;.)(?&lt;name&gt;[a-z0-9_-]+)(?:[:](?&lt;null&gt;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

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


where 


commandText="[dbo].[Log] specify the [Schema].[Table] to generate the INSERT for
@Exception:null means it is nullable column