How to write NULL to a column in a database using NLog Configuration API?

Question

I have looked at this question, but as it uses the NLog.config file I can't not translate the answer into what I need. NLog - Write NULL to optional database column

I have just started using NLog, after it was suggested as an answer to another question I asked. As I am using it in a class library, and I don't want to have to place the NLog.config file in every app that uses the library, I am using the Configuration API.

What I want to do is set some columns to NULL in my log table, when they are not required/specified. I can get NLog to write an empty string, but not a NULL.

I have tried to configure NLog to execute a stored procedure, but it does not seem to work.

At the moment I am setting up a database target with username, password, etc, and the following command text:

target.CommandText = "insert into Log(time_stamp,log_level,logger,message,exception_type,target_site,stack_trace,data,inner_exception) values(@time_stamp, @level, @logger, @message, @type, @target, @trace, @data, @inner);";

Then adding parameters to the target, e.g.

var param = new DatabaseParameterInfo();
param.Name = "@time_stamp";
param.Layout = "${date}";
target.Parameters.Add(param);

So this would work to add just an empty string:

var param = new DatabaseParameterInfo();
param.Name = "@type";
param.Layout = "";
target.Parameters.Add(param);

But this does not work as Layout is required:

var param = new DatabaseParameterInfo();
param.Name = "@time_stamp";
param.Layout = null;
target.Parameters.Add(param);

I have tried executing a stored procedure, changing the command text as follows (and adding the parameters as above afterwards, including empty strings instead of nulls):

target.CommandText = "exec usp_InsertLog @time_stamp, @level, @logger, @message, @type, @target, @trace, @data, @inner";

Which generates this, according to SQL Server Profiler (line breaks for clarity):

exec sp_executesql N'
exec usp_InsertLog @time_stamp, @level, @logger, @message, @type, @target, @trace, @data, @inner',
N'@time_stamp nvarchar(19),@level nvarchar(5),@logger nvarchar(62),@message nvarchar(8),@type nvarchar(4000),@target nvarchar(4000),@trace nvarchar(4000),@data nvarchar(4000),@inner nvarchar(4000)',
@time_stamp=N'01/03/2014 17:00:38',@level=N'Debug',@logger=N'Testbed, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null',@message=N'an error',@type=N'',@target=N'',@trace=N'',@data=N'',@inner=N''

If I can get the stored procedure to work then I can get it to insert NULL instead of an empty string, if that is what is passed in by NLog.

Here is the stored procedure at the moment, which does not work whether the values are empty or not:

ALTER PROCEDURE [dbo].[usp_InsertLog]
    @time_stamp datetime,
    @level nvarchar(100),
    @logger nvarchar(100),
    @message nvarchar(MAX),
    @type nvarchar(150) = NULL,
    @target nvarchar(100) = NULL,
    @trace nvarchar(MAX) = NULL,
    @data nvarchar(MAX) = NULL,
    @inner nvarchar(MAX) = NULL
AS

SET NOCOUNT ON

BEGIN TRY
insert into Log(
    time_stamp,
    log_level,
    logger,
    [message],
    exception_type,
    target_site,
    stack_trace,
    data,
    inner_exception) 
values(
    @time_stamp, 
    @level, 
    @logger, 
    @message, 
    @type, 
    @target, 
    @trace, 
    @data, 
    @inner) 
END TRY

Is there something I have missed, or a better way to do it?

Many thanks!

Answer 1

This is a solution that I also posted on NLog - Write NULL to optional database column, I hope it can be useful because the stored procedure solution I consider it's like cracking a nut with a sledgehammer.

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 2

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();