Parameters for Database Target

[Jason Watts]

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:

 

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 it is not working. I'm getting a SQL error that reads "Must declare the scalar variable "@LogDate"".

 

So it seems that all of the properties are working fine. The connection seems to succeed as the error occurs after the connection to the database. For some reason the parameters are not "binding" to the command.

 

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

<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}" />

But that defeats the whole purpose of this being customizable.

 

Please help me to understand what additional thing I need to do to cause the target to correctly pick up the values for the parameters.

Thank you!

[Kim Christensen]

Hi Jason,

How did you create the LogEventInfo object? By using the constructor or the static LogEventInfo.Create method?

The reason I ask is that for some reason the constructor does not set the logevent timestamp (you have to do it your self in that case), but if you use the static method, the timestamp will be set.

The DateLayoutRenderer uses the timestamp, so it could be the problem.

Regards,

Kim Christensen

[Jason Watts]

Hello! Thanks for your reply. I did set the timestamp. Here is the function I used to create the LogEventInfo object:

public LogEventInfo FormatError(Exception e)
{
 LogEventInfo info = new LogEventInfo();
 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);

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

 return info;
}

The specific "@LogDate" variable does not seem to be the problem. If I add only the "@LogDate" <parameter /> to the target, then the error will shift to the next scalar variable "@LogLevel". 

Any other thoughts on what may be causing this problem?

[Kim Christensen]

After I've looked more into the code, I have figured why this doesn't work.

LogEventInfo.Properties are used to format the log message, not provider parameters to the DatabaseTarget.

This means that you have to provide the parameters in the config file.

After some thought I can see the reason for choosing this solution in NLog, as the customization are provided by using the layouts to the parameter value.

What sort of customization were you thinking about?

Regards,

Kim

[Jason Watts]

Let me see if I can clarify. The `LogEventInfo` object is being created in the `public LogEventInfo FormatError(Exception e)` method I mentioned above. After it is created, it later gets passed to another function (that I referenced in my first post above) that updates the `LogEventInfo` object further before logging it:

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

Before the `LogEventInfo` object is logged, I am updating the `Parameters` array with the `DatabaseParameterInfo` objects that specify the parameter name and layout. In theory, this seems like it should work, as there is a Parameters array in the LogEventInfo object and even a DatabaseParameterInfo object to work with.

Overall, I want to have a way to create custom 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.

[Kim Christensen]

I think the best solution for you is to create a custom target, which can read the database parameters from the Properties collection or similar.

[Jason Watts]

Do custom targets still rely on the NLog.config file?  Are there examples and documentation for creating custom targets?

[Kim Christensen]

They rely somewhat on the configuration file, but NLog can also be configured programmatically, see https://github.com/NLog/NLog/wiki/Configuration-API.

A small introduction to developing custom targets can be found here, https://github.com/NLog/NLog/wiki/How-to-write-a-Target.

[Jason Watts]

Thank you Kim for you help. 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()
  {
    ConfigurationItemFactory.Default.Targets.RegisterDefinition("DatabaseLog", typeof(DatabaseLogTarget));
    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 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>
  private 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")));

[Kim Christensen]

The reason why you previous approach didn't work is that the Parameters collection, aren't used the way you think they are (I can see it is a bad name).

The Parameters property are used to enable you to pass in parameters to the string.Format method, used when generating the log message, see the method CalcFormattedMessage.

[Jason Watts]

Thank you, Kim, for all of your help!

[Kim Christensen]

Anytime