Six Steps to Create a Custom Database Logger using Log4Net and NuGet

I had a requirement to create a global logger for a company.  The company has 10-20 services and they wanted a central store to log errors.  In order to do this, I wanted a simple and easy way to implement a logger through the system;  I've used Log4net a lot so I naturally chose that.  I didn't want to use a single service that every other service relied on in case the logging service went down and the system could deadlock.  Instead, I decided to bundle my logger into a Nuget package.  The benefits of using a Nuget package make it easy to upgrade on the build server, also if the database server ever goes down, each service can write to the local file system, meaning the system can keep on running.


I set-up my project with two class libraries.  The first one is where the code for the logger will live.  The second one is where the files for the Nuget project will live.  So, for reference, I've referred to the projects as:

  • Logger (Added Log4Net and Entity Framework  via Nuget)
  • Logger.Nuspec (Added a project reference to Logger)

Software required:

Step One: Creating the custom table to store the log requests

Create your database script:

[sql] CREATE TABLE [dbo].[Log]( [Id] [int] IDENTITY(1,1) NOT NULL, [Date] [datetime] NOT NULL, [Thread] [varchar](255) NOT NULL, [Level] [varchar](50) NOT NULL, [Logger] [varchar](255) NOT NULL, [Message] [varchar](4000) NOT NULL, [Exception] [varchar](2000) NULL, [ServiceId] [int] NOT NULL)

The thing to note about my script compared to the ones you can find from Log4Net, is that I've added an extra column called ServiceId.  My logger will be used by a number of services and I want an easy way to distinguish between them, so I'll use this field to comply with this requirement. It will require some extra configuration tweaking of Log4Net though.

Step Two: Configuring Log4Net to Log To A Database

As I'm using a class library I'm using an app.config for configuration. After you have added Log4net via Nuget, you'll need to add the following sections:

<log4net debug="true">
      <appender name="ADONetAppender" type="log4net.Appender.ADONetAppender">
        <bufferSize value="1" />
        <connectionType value="System.Data.SqlClient.SqlConnection, System.Data, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
        <connectionString value="INSERT_HERE" />
        <commandText value="INSERT INTO Log ([Date],[Thread],[Level],[Logger],[Message],[Exception],[ServiceId]) VALUES
       (@log_date, @thread, @log_level, @logger, @message, @exception, @ServiceId)" />
          <parameterName value="@log_date" />
          <dbType value="DateTime" />
          <layout type="log4net.Layout.RawTimeStampLayout" />
          <parameterName value="@thread" />
          <dbType value="String" />
          <size value="32" />
          <layout type="log4net.Layout.PatternLayout">
            <conversionPattern value="%t" />
          <parameterName value="@log_level" />
          <dbType value="String" />
          <size value="512" />
          <layout type="log4net.Layout.PatternLayout">
            <conversionPattern value="%p" />
          <parameterName value="@logger" />
          <dbType value="String" />
          <size value="512" />
          <layout type="log4net.Layout.PatternLayout">
            <conversionPattern value="%c" />
          <parameterName value="@message" />
          <dbType value="String" />
          <size value="4000" />
          <layout type="log4net.Layout.PatternLayout">
            <conversionPattern value="%m" />
          <parameterName value="@exception" />
          <dbType value="String" />
          <size value="2000" />
          <layout type="log4net.Layout.ExceptionLayout" />
          <parameterName value="@ServiceId" />
          <dbType value="Int32" />
          <layout type="log4net.Layout.RawPropertyLayout">
            <key value="ServiceId" />
        <level value="DEBUG" />
        <appender-ref ref="ADONetAppender" />

Things to note about the above:

  • You need to set the connection string to point your database instance.
  • I've modified the standard database query to include a ServiceId that is an int.  you might want to change/remove this.  Simply update the commandtext and the Service parameter accordingly.
  • The way the ServiceId has been configured we will need to pass on this service ID into Log4net otherwise it will not insert.

Step Three: Creating the Logger

Now all the configuration has been set-up, it's time to write the code for the logger.  The code is pretty easy, all we need to do is create a ILog instance and ensure the ServiceId is configured correctly.

using System;
using log4net;

namespace Logger
    public static class Logger
        private static readonly ILog Log = LogManager.GetLogger(typeof(Logger));
        private const string SourceIdIdReference = "SourceId";
        public static readonly int SourceId;

        public static ILog LoggingInstance
            get { return Log; }

        static Logger()
            SourceId = GetAndValidateAppId();

            GlobalContext.Properties[SourceIdIdReference] = SourceId;

        private static int GetAndValidateAppId()
            var clientId = System.Configuration.ConfigurationManager.AppSettings[SourceIdIdReference];

            int appIdPlaceholder;
            if (string.IsNullOrEmpty(clientId) || !int.TryParse(clientId, out appIdPlaceholder))
                throw new ArgumentException();
            return appIdPlaceholder;


The above code is pretty straightforward.  I'm creating a ILog instance that any calling classes can use.  I'm also adding a serviceId.  In this code I'm adding a magic number of  '1' as the ServiceId.  In my version I get this ID from AppSettings.  I'm then configuring Log4Net to use this parameter as the ServiceId call for every request. NOTE: If this code is not added, then Log4Net will error saying the ServiceId parameter is a required database field.

Step Four: Creating the Nuspec file

So we're done with all the Log4Net configuration for the time being.  It would be worth you testing all the above stuff out now to make sure it works before you wrap it into a package.  In order to create a Nuget package you first need to create a Nuspec file.  If you use the Nuget command prompt, navigate to your nu spec project and then type:

nuget spec

In your project you should now have a nuspec file.  You can open this file with the package manager to configure it as you wish.  Below shows my NuSpec file.

<?xml version="1.0" encoding="utf-8"?>
<package xmlns="">
        <authors>Jon Jones</authors>
        <owners>Jon Jones</owners>
        <description>Logging provider for error and debugging information</description>
            <group targetFramework=".NETFramework4.5">
                <dependency id="log4net" version="2.0.3" />
        <file src="content\app.config.transform" target="content\app.config.transform" />
        <file src="content\app.config.install.xdt" target="content\app.config.install.xdt" />
        <file src="content\app.config.uninstall.xdt" target="content\app.config.uninstall.xdt" />
        <file src="bin\Debug\Logger.dll" target="lib\net45\Logger.dll" />

A quick overview.  I'm adding in log4net via Nuget. I'm adding in four files that will be run when the package is installed:

  • app.config.transform (Adds all the configuration bits you want to copy)
  • app.config.install.xdt - Adds any transforms to the config on install.  This provides you with more flexibility to do things like find and replace etc..
  • app.config.uninstall.xdt - Same as the install file but only run when the package is uninstalled.
  • The code!  This is pulled in via a project reference

Below shows my install and uninstall files.  The only main feature is if the ServiceId already exists then the script will not add a duplicate key. app.config.install.xdt

<?xml version="1.0"?>
  <configuration xmlns:xdt="">



<?xml version="1.0"?>
<configuration xmlns:xdt="">

  In 'Package Explorer' you should end up with something like this: Screenshot_1

Remember, after you finish tweaking your Nuspec file, hit the File ->Save MetaDataFile as otherwise you'll lose your change.

Step Five : Adding Your Package to Nuget

As this is an internal application I don't want to add my package directly into Nugets public feed.  Instead, I need to create a privude feed.  The simplest way to do this is creating a feed in a folder somewhere on your server and create a build script to copy your package there.  I'm not going into that part in this article so I'll show you how to create your package and add it to visual studio. In Package explorer with your Nuspec file loaded, click File -> Save.  This will prompt you to create your package.. simple.  Now in visual studio open:

Tools -> Options -> Package Manager -> Package Sources.  

In here click the add button and use the ellipses to point to the area that you've saved your package.


Now go to the project you want to install the logger in, right click on it and select 'Manage Nuget Packages'.  The Nuget manager should now load and in the 'Online' section you should see your area.  In here you should also see your package.  Hit install!


Step Six: Test it Out

Here's the code you'll need to test it.  Give it a try and you should start to see error being logged into your database.

private readonly ILog _log = ChilliSauceLogger.LoggingInstance;

public void TestLogger()
      throw new Exception("Test");
   catch (Exception ex)

Tip To Get it Working

Ok, I might have lied for this blogs title... getting log4net to work sometimes is a pain, here are some common issues I've come across:

1. Having the Log4Net debugging enabled will show you where it is failing over your Visual Solution Output window.  To enable debugging add the debug property in the log4net declaration

<log4net debug="true">

2. Can't get your custom Int to save, read this and this.  

Source Code

As always all the code for this can be found on my Github, here.

submit to reddit

Jon D Jones

Software Architect, Programmer and Technologist Jon Jones is founder and CEO of London-based tech firm Digital Prompt. He has been working in the field for nearly a decade, specializing in new technologies and technical solution research in the web business. A passionate blogger by heart , speaker & consultant from England.. always on the hunt for the next challenge

Back to top