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):
- We can use a named connection contained in an app.config / web.config configuration file;
- We can add a connection string as an attribute of the NLog.config element for our database target; or
- 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
Great post!
I use connection string, with encryption like this:
if ProviderName is System.Data.SqlClient
if ProviderName is System.Data.EntityClient
Can I use it in NLog ? Maybe using custom class ? I would like if you have any suggestions about it.
@kiquenet: Probably the best thing to do is use the global diagnostics context, which would allow you to programmatically set the connection string for your loggera.
An example of doing that is shown at http://stackoverflow.com/questions/11891329/nlog-target-database-set-connection-information-at-runtime