Logging with NLog and SQLite

This article explains how to achieve a portable and elegant logging solution with NLog and SQLite, the solution targets the .NET platform and only requires a few configurations.

NLog

NLog is a free and lightweight logging library, supports .NET, Silverlight and Windows Phone http://nlog-project.org/. It is an excellent option for a wide range of scenarios: from simple day-to-day utilities, to critical production services. It also enables your application to have multiple and diverse targets, both files, databases, output consoles, networks, email and so on are supported.

Setup

Package Manager Console:

PM> Install-Package NLog

NuGet browser:

SQLite

SQLite is a simple database engine, it became famous in the mobile world because of its qualities: a server deamon is not required nor any particular configuration. The database is usually represented by a simple file (usually .db3), read and writes are handled by platform-specific libraries, which usually are available for free.

Setup

The most famous interface for .NET is System.Data.SQLite

Package Manager Console

PM> Install-Package System.Data.SQLite

NuGet browser

The NLog configuration file

This is optional, but warmly suggested: have a separate file for NLog configurations. Suppose someone screws up the configurations, wouldn't be safer to have just the logging part of your application failing? Separation of concerns, when possibile, is a good thing.

Setup

Package Manager Console

PM> Install-Package NLog.Config

NuGet browser

Get them to work together

1. Get the SQLite database ready, use SQLite Database Browser or your client of choice to create the database, then execute the following T-SQL statement to create the Log table:

    CREATE TABLE Log (Timestamp TEXT, Loglevel TEXT, Logger TEXT, 
     Callsite TEXT, Message TEXT) 

2. Connect your App to the database, change the NLog configuration as follows:

     <?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"
        throwExceptions="false">
      <targets>
        <target name="db" xsi:type="Database" 
            dbProvider="System.Data.SQLite" keepConnection="false" 
            connectionString="Data Source=${basedir}\Log.db3;Version=3;"
            commandText="INSERT into Log(Timestamp, Loglevel, Logger, Callsite, Message) 
             values(@Timestamp, @Loglevel, @Logger, @Callsite, @Message)">
              <parameter name="@Timestamp" layout="${longdate}"/>
              <parameter name="@Loglevel" layout="${level:uppercase=true}"/>
              <parameter name="@Logger" layout="${logger}"/>
              <parameter name="@Callsite" layout="${callsite:filename=true}"/>
              <parameter name="@Message" layout="${message}"/>
        </target>
      </targets>
      <rules>
        <logger name="*" minlevel="Warn" writeTo="db" />
    </rules>
    </nlog>

3. Start logging

   class Program {
    static Logger log = LogManager.GetCurrentClassLogger();
   
    static void Main(string[] args)
    {
        log.Info("Logging like a boss");
    }
   }
   

Done!

You can review the source code and download the sample project here:
https://github.com/aazzola/nlog-sqlite/