NLog - Run-Time Parameters for Database Target

Question

I am attempting to add some customization to my database log target. In my NLog.config, I have this:

<target name="DatabaseExample1" xsi:type="Database"
 dbProvider="System.Data.SqlClient"
 dbDatabase="${event-context:item=dbDatabase}"
 dbUserName="${event-context:item=dbUserName}"
 dbPassword="${event-context:item=dbPassword}"
 dbHost="${event-context:item=dbHost}"
 commandText="${event-context:item=commandText}">
</target>

And in my C# code, I have this:

protected override void updateBeforeLog(LogEventInfo info)
{
    info.Properties["dbDatabase"] = "TempDB";
    info.Properties["dbUserName"] = "username";
    info.Properties["dbPassword"] = "password";
    info.Properties["dbHost"] = "SERVER\\SQLSERVER";
    info.Properties["commandText"] = "exec InsertLog @LogDate, @LogLevel, @Location, @Message";
    
    info.Parameters = new DatabaseParameterInfo[] {
        new DatabaseParameterInfo("@LogDate", Layout.FromString("${date:format=yyyy\\-MM\\-dd HH\\:mm\\:ss.fff}")), 
        new DatabaseParameterInfo("@LogLevel", Layout.FromString("${level}")),
        new DatabaseParameterInfo("@Location", Layout.FromString("${event-context:item=location}")),
        new DatabaseParameterInfo("@Message", Layout.FromString("${event-context:item=shortmessage}"))
    };
    
    log.Log(info);
}

But I'm getting a SQL error that reads "Must declare the scalar variable "@LogDate"".

The properties are working because the connection succeeds. But for some reason the parameters are not "binding" to the scalar variables in the command.

If I create the parameters manually in the NLog.config file, it works perfectly:

<target name="DatabaseExample1" xsi:type="Database"
 dbProvider="System.Data.SqlClient"
 dbDatabase="${event-context:item=dbDatabase}"
 dbUserName="${event-context:item=dbUserName}"
 dbPassword="${event-context:item=dbPassword}"
 dbHost="${event-context:item=dbHost}"
 commandText="${event-context:item=commandText}">
   <parameter name="@LogDate" layout="${date:format=yyyy\-MM\-dd HH\:mm\:ss.fff}" />
   <parameter name="@LogLevel" layout="${level}" />
   <parameter name="@Location" layout="${event-context:item=location}" />
   <parameter name="@Message" layout="${event-context:item=shortmessage}" />
</target>

But that defeats the whole purpose of being able to customize the commandText and parameter values at run-time.

What do I need to do to cause the target to correctly pick up the values for the parameters?

update

Overall, I want to have a way to customize the targets via C# code. I want to rely on the NLog.config file only has much as needed. But it seems like NLog is pretty tied to the config file settings, and I'm trying to work within that constraint but be as flexible as possible. With this database target, if I can figure out how to update the parameters programmatically, then I can have a fairly generic target in the config file, then update the LogEventInfo properties and parameters to suit the needs of whatever database to connect to, or stored procedure to execute.

Solution

update

It turns out that the LogEventInfo.Parameters collection is used for the LogEventInfo.FormattedMessage property. If you want to use the LogEventInfo.FormatProvider or even set LogEventInfo.Message equal to a string.format string, then the Parameters object[] array is used to provide the substitutions in the string. See here for the code.

Despite the similar naming, LogEventInfo.Parameters does not correspond to <target ><parameter /></target> in the NLog.config file. And there does not appear to be a way to get to the database parameters via the LogEventInfo object. (Thanks to Kim Christensen over at NLog project forum for that link)

---

I was able to get this working using a custom target. But I am still questioning why my previous approach did not work. It really seems like that if the Parameters array is accessible, that NLog should honor the parameters that are assigned to it.

That said, here is the code that I ended up using:

First I had to create the custom target and set it up to send data to the database:

[Target("DatabaseLog")]
public sealed class DatabaseLogTarget : TargetWithLayout
{
  public DatabaseLogTarget()
  {
  }

  protected override void Write(AsyncLogEventInfo logEvent)
  {
    //base.Write(logEvent);
    this.SaveToDatabase(logEvent.LogEvent);
  }

  protected override void Write(AsyncLogEventInfo[] logEvents)
  {
    //base.Write(logEvents);
    foreach (AsyncLogEventInfo info in logEvents)
    {
      this.SaveToDatabase(info.LogEvent);
    }
  }

  protected override void Write(LogEventInfo logEvent)
  {
    //string logMessage = this.Layout.Render(logEvent);
    this.SaveToDatabase(logEvent);
  }

  private void SaveToDatabase(LogEventInfo logInfo)
  {
    if (logInfo.Properties.ContainsKey("commandText") &&
      logInfo.Properties["commandText"] != null)
    {
      //Build the new connection
      SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();

      //use the connection string if it's present
      if (logInfo.Properties.ContainsKey("connectionString") && 
        logInfo.Properties["connectionString"] != null)
        builder.ConnectionString = logInfo.Properties["connectionString"].ToString();

      //set the host
      if (logInfo.Properties.ContainsKey("dbHost") &&
        logInfo.Properties["dbHost"] != null)
        builder.DataSource = logInfo.Properties["dbHost"].ToString();

      //set the database to use
      if (logInfo.Properties.ContainsKey("dbDatabase") &&
        logInfo.Properties["dbDatabase"] != null)
        builder.InitialCatalog = logInfo.Properties["dbDatabase"].ToString();

      //if a user name and password are present, then we're not using integrated security
      if (logInfo.Properties.ContainsKey("dbUserName") && logInfo.Properties["dbUserName"] != null &&
        logInfo.Properties.ContainsKey("dbPassword") && logInfo.Properties["dbPassword"] != null)
      {
        builder.IntegratedSecurity = false;
        builder.UserID = logInfo.Properties["dbUserName"].ToString();
        builder.Password = logInfo.Properties["dbPassword"].ToString();
      }
      else
      {
        builder.IntegratedSecurity = true;
      }

      //Create the connection
      using (SqlConnection conn = new SqlConnection(builder.ToString()))
      {
        //Create the command
        using (SqlCommand com = new SqlCommand(logInfo.Properties["commandText"].ToString(), conn))
        {
          foreach (DatabaseParameterInfo dbi in logInfo.Parameters)
          {
            //Add the parameter info, using Layout.Render() to get the actual value
            com.Parameters.AddWithValue(dbi.Name, dbi.Layout.Render(logInfo));
          }

          //open the connection
          com.Connection.Open();

          //Execute the sql command
          com.ExecuteNonQuery();
        }
      }
    }
  }
}

Next, I updated my NLog.config file to include a rule for the new target:

<?xml version="1.0" encoding="utf-8" ?>
<nlog xmlns="http://www.nlog-project.org/schemas/NLog.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <targets async="true">
    <target name="DatabaseLog1" xsi:type="DatabaseLog" />
  </targets>
  <rules>
    <logger name="LogDB"  minlevel="Trace" writeTo="DatabaseLog1" />
  </rules>
</nlog>

Then I created a class to wrap my database logging calls. It also provides a function to convert an Exception into an NLog LogEventInfo object:

public class DatabaseLogger
{
  public Logger log = null;

  public DatabaseLogger()
  {
    //Make sure the custom target is registered for use BEFORE using it
    ConfigurationItemFactory.Default.Targets.RegisterDefinition("DatabaseLog", typeof(DatabaseLogTarget));

    //initialize the log
    this.log = NLog.LogManager.GetLogger("LogDB");
  }
  
  /// <summary>
  /// Logs a trace level NLog message</summary>
  public void T(LogEventInfo info)
  {
    info.Level = LogLevel.Trace;
    this.Log(info);
  }
  
  /// <summary>
  /// Allows for logging a trace exception message to multiple log sources.
  /// </summary>
  public void T(Exception e)
  {
    this.T(FormatError(e));
  }
  
  //I also have overloads for all of the other log levels...
  
  /// <summary>
  /// Attaches the database connection information and parameter names and layouts
  /// to the outgoing LogEventInfo object. The custom database target uses
  /// this to log the data.
  /// </summary>
  /// <param name="info"></param>
  /// <returns></returns>
  public virtual void Log(LogEventInfo info)
  {
    info.Properties["dbHost"] = "SQLServer";
    info.Properties["dbDatabase"] = "TempLogDB";
    info.Properties["dbUserName"] = "username";
    info.Properties["dbPassword"] = "password";
    info.Properties["commandText"] = "exec InsertLog @LogDate, @LogLevel, @Location, @Message";
    
    info.Parameters = new DatabaseParameterInfo[] {
      new DatabaseParameterInfo("@LogDate", Layout.FromString("${date:format=yyyy\\-MM\\-dd HH\\:mm\\:ss.fff}")), 
      new DatabaseParameterInfo("@LogLevel", Layout.FromString("${level}")),
      new DatabaseParameterInfo("@Location", Layout.FromString("${event-context:item=location}")),
      new DatabaseParameterInfo("@Message", Layout.FromString("${event-context:item=shortmessage}"))
    };

    this.log.Log(info);
  }


  /// <summary>
  /// Creates a LogEventInfo object with a formatted message and 
  /// the location of the error.
  /// </summary>
  protected LogEventInfo FormatError(Exception e)
  {
    LogEventInfo info = new LogEventInfo();

    try
    {
      info.TimeStamp = DateTime.Now;

      //Create the message
      string message = e.Message;
      string location = "Unknown";

      if (e.TargetSite != null)
        location = string.Format("[{0}] {1}", e.TargetSite.DeclaringType, e.TargetSite);
      else if (e.Source != null && e.Source.Length > 0)
        location = e.Source;

      if (e.InnerException != null && e.InnerException.Message.Length > 0)
        message += "\nInnerException: " + e.InnerException.Message;

      info.Properties["location"] = location;

      info.Properties["shortmessage"] = message;

      info.Message = string.Format("{0} | {1}", location, message);
    }
    catch (Exception exp)
    {
      info.Properties["location"] = "SystemLogger.FormatError(Exception e)";
      info.Properties["shortmessage"] = "Error creating error message";
      info.Message = string.Format("{0} | {1}", "SystemLogger.FormatError(Exception e)", "Error creating error message");
    }

    return info;
  }
}

So when I start my application, I can start logging easily:

DatabaseLogger dblog = new DatabaseLogger();
dblog.T(new Exception("Error message", new Exception("Inner message")));

With a slight bit of effort, I can inherit from the DatabaseLogger class and override the Log method to create any number of different database logs. I can update the connection info dynamically if I want. I can change the commandText and Parameters to suit each database call. And I just have to have the one target.

If I want to provide functionality for multiple database types, I can add a info.Properties["dbProvider"] property which gets read in the SaveToDatabase method, which can then spawn off a different connection type.