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

NLog ver. 4.7.4 adds the parameter-option AllowDbNull so you can do like this:

<parameter name="@exception" layout="${exception:format=tostring}" allowDbNull="true" />
<parameter name="@correlationid" layout="${activityid}" dbType="DbType.Guid" allowDbNull="true" />

Nlog will automatically convert empty-string/no-output to DbNull-value when allowDbNull="true".

Answer 2

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.