title: How to write NULL to a column in a database using NLog Configuration API?
body: <p>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. <a href="https://stackoverflow.com/q/4831560/2362036">NLog - Write NULL to optional database column</a></p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>I have tried to configure NLog to execute a stored procedure, but it does not seem to work.</p>
  
  <p>At the moment I am setting up a database target with username, password, etc, and the following command text:</p>
  
  <pre><code>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);";
  </code></pre>
  
  <p>Then adding parameters to the target, e.g.</p>
  
  <pre><code>var param = new DatabaseParameterInfo();
  param.Name = "@time_stamp";
  param.Layout = "${date}";
  target.Parameters.Add(param);
  </code></pre>
  
  <p>So this would work to add just an empty string:</p>
  
  <pre><code>var param = new DatabaseParameterInfo();
  param.Name = "@type";
  param.Layout = "";
  target.Parameters.Add(param);
  </code></pre>
  
  <p>But this does not work as Layout is required:</p>
  
  <pre><code>var param = new DatabaseParameterInfo();
  param.Name = "@time_stamp";
  param.Layout = null;
  target.Parameters.Add(param);
  </code></pre>
  
  <p>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):</p>
  
  <pre><code>target.CommandText = "exec usp_InsertLog @time_stamp, @level, @logger, @message, @type, @target, @trace, @data, @inner";
  </code></pre>
  
  <p>Which generates this, according to SQL Server Profiler (line breaks for clarity):</p>
  
  <pre><code>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''
  </code></pre>
  
  <p>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.</p>
  
  <p>Here is the stored procedure at the moment, which does not work whether the values are empty or not:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Is there something I have missed, or a better way to do it?</p>
  
  <p>Many thanks!</p>