NLog - Write NULL to optional database column

Question

I am using NLog for logging in an ASP.Net application and making use of the database target with Microsoft Sql Server.

I have some logging parameters that are optional and not always specified. I would like these to be written as null when they are not provided, however NLog seems to always write them as empty strings.

Is there a way to configure it to write null as the default?

Ref: https://github.com/nlog/NLog/wiki/Database-target

Answer 1

I use a slightly different approach.

---

Because I don't like writing queries I created an extension that does this for me and my NLog configuration looks like:

<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>

Notice two things here:

• the commandText="[dbo].[Log]" that must follow the format [Schema].[Table]
• the @Exception:null where the null means it is nullable

There is no <commandText> element but instead I use this extension to create the INSERT automatically from the parameters.

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

    public static void GenerateDatabaseTargetInsertQueries(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();
        }
    }

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

    internal 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())),
                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;
                })));
    }
}

and

public static class NLogDatabaseTarget
{
    public static void GenerateInsertQueries()
    {
        NLog.LogManager.Configuration.GenerateDatabaseTargetInsertQueries();
    }
}

---

Additionaly it can parse the parameter name and insert the NULLIF for nullable parameters.

Another extension helps me to parse it:

public static class DatabaseParameterInfoExtensions
{
    // https://regex101.com/r/wgoA3q/2

    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 you need add the commandText="[dbo].[Log]" attribute to the database target, remove the query and add the :null to the parameter name of nullable columns.

In code you just call this and the extensions will do the magic.

NLogDatabaseTarget.GenerateInsertQueries();

Answer 2

[EDIT]

Maybe a more obvious solution than what I propose below would be to change from using an INSERT statement to using a database procedure to log. If you use a database procedure then you could handle the swizzling from empty string to null yourself. I don't know for sure that you can use a database procedure with NLog's Database target. Log4net supports it so my guess is that NLog does as well.

Here is one example (in the answer to the linked question) I found of the configuration from someone using NLog to log to a database using stored procedure.

http://nlog-forum.1685105.n2.nabble.com/Using-a-stored-procedure-for-the-DB-Target-td2621725.html

I see here:

http://nlog.codeplex.com/workitem/5418

A complaint that it doesn't work (at least in the NLog 2.0 beta).

One difference between the two examples is that the working example uses "exec LoggingProcedureName ..." while the nonworking one uses "LoggingProcedureName ..."

Hope this helps.

[End EDIT]

I can't comment on why NLog writes emptry strings rather than null or how to make NLog write null instead of empty strings, but I wonder if you could make this work they way you want through additional configuration?

When are the logging parameters optional? Are there certain places in your code where you ALWAYS log some values and other places where you NEVER log some values? Can you (as the developer) know which optional parameters are applicable in which sections of your application?

Could you configure multiple Database Targets, each with the "right" parameters specified? You could then point Loggers to the specific Database Target that is appropriate for the code location.

Say that your application is divided (by namespace) into code that (generally) executes "before", "during", and "after".

In the "before" code, you might be logging parameter A. In the "during" code, you might be logging parameter B. In the "after" code, you might be logging parameter C. So, your logging table might have columns like:

DateTime, Logger, LogLevel, A, B, C, Message, Exception

Right now you have a Database Target that inserts all of those values for every logging statement.

What if you have three Database Targets that insert the values like so:

DataTime, Logger, LogLevel, A, Message, Exception
DataTime, Logger, LogLevel, B, Message, Exception
DataTime, Logger, LogLevel, C, Message, Exception

You could configure your section something like this:

  <rules>
    <logger name="Before.*" minlevel="Trace" writeTo="databaseA" />
    <logger name="During.*" minlevel="Trace" writeTo="databaseB" />
    <logger name="After.*" minlevel="Trace" writeTo="databaseC" />
  </rules>

Obviously, there could be several problems with this idea:

1. It might be possible (or easy) to divide your loggers up to match the "optionality" of the parameters.

2. There might be too many combinations of optional parameters to make this feasible (probably the same drawback as 1).

3. It might not be a good idea to have a log of database targets active at one time. Maybe this would lead to performance issues.

Well, that's all that I have. I don't know my idea would even work, much less if it is practical.

Probably the better solution would be for NLog to allow an extra property on each database parameter that would allow you to say "send null instead of empty string".

I guess I should suggest that you might also want to ask this question in the NLog forum. "Craig" on that forum asked the same (or similar) question earlier today. Maybe you are Craig.

Answer 3

This is an old question but due to the solutions given are a little 'hacky' I wanted to give my own which I consider it is way more simple to implement than a db procedure and more elegant that using a case.

You can try to write the NULL with the NULLIF function that compares 2 expressions and returns NULL if they are equal, otherwise it returns the first expression (msdn NULLIF page).

This way the commandText on your NLog config file would look like:

INSERT INTO [dbo].[log] ([message], [optional]) 
VALUES (@message, NULLIF(@optional, ''))

Answer 4

NLog uses StringBuilder to make parameter value. Even if a parameter isn't specified it initializes a value as builder.ToString() which is empty string.

You may change your commandText like this:

INSERT INTO [dbo].[log] ([message], [optional]) 
VALUES 
(
    @message, 
    case 
      when len(@optional) = 0 then null 
      else @optional 
    end
)

It seems like a hack for me though. I hope there is a better solution.