Using Sql Server Stored Procedures With NLog

One of my favorite .NET libraries is NLog, which makes application event logging pretty much as easy as deciding on the error level you want to raise and the message you want to display.

It can be tricky to get NLog to save log events to destinations other than the local file system — “targets“, as NLog calls them. This is especially true for SQL Server database entries, and triply so if you want to use stored procedures for inserting log events.

You can find out how to properly configure NLog 4.x to use SQL Server stored procedures if you use enough Google searches; but it struck me that someone ought to write down how to do it, all in one place, and since I’ve wired this up a few times, that someone might as well be me.

Creating The Table And Stored Procedure

The setup of our table, and our stored procedure, doesn’t need to be anything special.

I’m going to create a table that contains an autoincremented primary key, a timestamp, an origin (that is, the name of the machine that generated the error), the level and the message I assigned to the error.

USE [mydatabase]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[NLogErrorLog] (
    [ErrorId]         INT            IDENTITY (1, 1) NOT NULL,
    [ErrorDatetime]   DATETIME       NOT NULL,
    [ErrorSource]     NVARCHAR (MAX) NOT NULL,
    [ErrorLevel]      NVARCHAR (10)  NOT NULL,
    [ErrorMessage]    TEXT           NOT NULL
);

The stored procedure to insert records is equally straightforward:

USE [mydatabase]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[NLogErrorLogInsert]
	@errordate DATETIME,
	@source NVARCHAR(MAX),
	@level NVARCHAR(10),
	@message TEXT
AS
	INSERT INTO NLogErrorLog (
		ErrorDatetime,
		ErrorSource,
		ErrorLevel,
		ErrorMessage
	) 
	VALUES (
		@errordate,
		@source,
		@level,
		@message
	)
RETURN 0

This code as a Github Gist: https://gist.github.com/dougvdotcom/5cd42d9df14c6b7aff6e

The app.config / web.config Connection String

There are thee ways to specify a SQL Server database connection in an NLog database target (in order of priority):

1. We can use a named connection contained in an app.config / web.config configuration file;
2. We can add a connection string as an attribute of the NLog.config element for our database target; or
3. We can specify the host, username, password and database values to use as individual attributes in the NLog.config element for our database target.

Note that you should only use one of these three methods to define your database target connection. While NLog is prepared to deal with multiple connection definitions, elegance dictates that you provide only one connection means, since NLog will use only one means.

I prefer using a named connection set up in a configuration file, primarily for abstraction: If I ever need to change the connection string to a database, I only need to do it in one place within an application, and it will change everyplace it needs to change.

Also, given the work I do at my day job, if I have an application that’s complex enough to need logging, there’s a strong chance that application also uses a SQL Server database, so I probably already have a named connection configured.

If you are going to use a named connection in a configuration file, that connection must specify the System.Data.SqlClient namespace as its provider.

That’s because NLog relies on the Entity Framework to configure its data objects; if your named connection does not represent itself as being provided by Sql.Data.SqlClient, then NLog cannot resolve the proper form of its data objects, and will throw an error such as:

Error initializing target Database Target[X] System.ArgumentException: Expecting non-empty string for 'providerInvariantName' parameter.

So your configuration file connection string entry, for whichever database is going to host your NLog records, should look something like this:

<configuration>
	<connectionStrings>
		<add name="DefaultConnection" providerName="System.Data.SqlClient" connectionString="Server=localhost;Database=mydatabase;User ID=username;Password=password;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;" />
	</connectionStrings>
</configuration>

Again, note that I have added the providerName attribute and set it to be System.Data.SqlClient. This is required for NLog to use the named connection from your configuration file.

Once we have a named connection set up — or we’ve decided to go with a connection string we’ll enter directly into NLog — it’s time to create the target in the NLog.config file.

Here are the attributes for a SQL Server database target that uses a named connection, stored in my app.config file with the name “DefaultConnection”:

<target
	xsi:type="Database"
	name="d"
	dbProvider="System.Data.SqlClient"
	connectionStringName="DefaultConnection"
	commandText="we will come back to this"
>

Alternative Connection Methods

If I don’t want to use a named connection, and would prefer to use a connection string specified in the NLog.config file, it would look like this:

<target
	xsi:type="Database"
	name="d"
	dbProvider="System.Data.SqlClient"
	connectionString="Server=localhost;Database=mydatabase;User ID=username;Password=password;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;"
	commandText="we will come back to this"
>

If you don’t want to provide a connection string, but would rather prefer to specify the host, username and password as their own values, your target attributes would look like this:

<target
	xsi:type="Database"
	name="d"
	dbUserName="username"
	dbProvider="System.Data.SqlClient"
	dbDatabase="mydatabase"
	dbPassword="password"
	dbHost="localhost"
	commandText="we will come back to this"
>

Setting The Command Text

The commandText attribute of the database target should be set to call EXEC on the stored procedure you created for inserting records, followed by all the parameter names you are passing in.

In the case of this example, the name of my stored procedure is NLogErrorLogInsert and it requires parameters named @errordate, @source, @level and @message. So my target would set a commandText like:

commandText="EXEC NLogErrorLogInsert @errordate, @source, @level, @message"

Passing In Parameters

The final step is to create parameter elements in NLog.config for each stored procedure parameter. These elements, nested within the database <target> element, take two attributes: A name that is the same as the name of the parameter in the stored procedure, and a layout that represents a combination of variables that NLog generates for each message:

<target xsi:type="Database" ...>
	<parameter name="@errordate" layout="${date:universalTime=true:format=s}" />
	<parameter name="@source" layout="${machinename}" />
	<parameter name="@level" layout="${level:uppercase=true}" />
	<parameter name="@message" layout="${message}" />
</target>

Note that when I pass the date to the stored procedure, I format that date as a sortable date, which is one of the string formats SQL Server will natively convert into datetime.

Adding The Rule

And finally, we need to create a rule that will actually fire off requests to log to the database. In this example, I am going to have all loggers insert any message that is a Warn or higher level.

<rules>
	<logger name="*" minlevel="Warn" writeTo="d" />
</rules>

Putting It All Together

So, here’s what my entire NLog.config file looks like if I am only going to log to SQL Server, using a stored procedure and a named connection in a web.config / app.config file:

<?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"
      xsi:schemaLocation="http://www.nlog-project.org/schemas/NLog.xsd NLog.xsd"
      autoReload="true"
      throwExceptions="false"
      internalLogLevel="Off">
  <targets>
    <target
      xsi:type="Database"
      name="d"
      dbProvider="System.Data.SqlClient"
      connectionStringName="DefaultConnection"
      commandText="EXEC NLogErrorLogInsert @errordate, @source, @level, @message"
    >
      <parameter name="@errordate" layout="${date:universalTime=true:format=s}" />
      <parameter name="@source" layout="${machinename}" />
      <parameter name="@level" layout="${level:uppercase=true}" />
      <parameter name="@message" layout="${message}" />
    </target>
  </targets>

  <rules>
    <logger name="*" minlevel="Warn" writeTo="d" />
  </rules>
</nlog>

This code as a Github Gist: https://gist.github.com/dougvdotcom/abad7d395e135a33b882