Log4Net and PostgreSQL

By | 2016-06-06

In this short tutorial, we will look at how to create a custom appender to work with Log4Net and PostgreSQL. Log4Net is a great tool for handling logging in .NET applications and PostgreSQL is a great open source database. The built in AdoNetAppender in Log4Net can handle many types of databases, but if your like me, it just didn’t seem to work with PostgreSQL. Here is any easy way to write your own custom appender in Log4Net to write to PostgreSQL.

NuGet Packages
We’ll need the following NuGet packages:

  • Log4Net
  • NpgSQL

Create Database
Create the database and table:

CREATE DATABASE logging
  WITH OWNER = postgres
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       LC_COLLATE = 'English_United States.1252'
       LC_CTYPE = 'English_United States.1252'
       CONNECTION LIMIT = -1;

CREATE TABLE public.logs
(
  app_name text,
  thread text,
  level text,
  location text,
  message text,
  exception text,
  log_date timestamp with time zone
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.logs
  OWNER TO postgres;

Connection String
Set the database connection string in the app.config/web.config:

<connectionStrings>
    <add name="postgres" providerName="Npgsql.NpgsqlConnection" connectionString="Server=localhost;Database=logging;User ID=postgres; Password=password;" />
</connectionStrings>

Creating a Custom Log4Net Appender
The key to creating a custom Log4Net appender is to inherit the AppenderSkeleton base class, which implements the IAppender interface, and override the Append method:

using log4net.Appender;
using System;
using log4net.Core;

namespace Log4Net_and_PostgreSQL
{
    public class PostgresAppender : AppenderSkeleton
    {
        protected override void Append(LoggingEvent loggingEvent)
        {
            //custom implementation
        }
    }
}

We want our implementation to write to a PostgreSQL database, so in our Append method we will use Npgsql to insert the log into the database:

protected override void Append(LoggingEvent loggingEvent)
{
    using (NpgsqlConnection conn = new NpgsqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["postgres"].ConnectionString))
    {
        conn.Open();
        using (NpgsqlCommand command = new NpgsqlCommand("insert into public.logs(app_name,thread,level,location,message,log_date,exception) values(:app_name,:thread,:level,:location,:message,:log_date,:exception)", conn))
        {
            var appName = command.CreateParameter();
            appName.Direction = System.Data.ParameterDirection.Input;
            appName.DbType = System.Data.DbType.String;
            appName.ParameterName = ":app_name";
            appName.Value = loggingEvent.LookupProperty("AppName");
            command.Parameters.Add(appName);

            var thread = command.CreateParameter();
            thread.Direction = System.Data.ParameterDirection.Input;
            thread.DbType = System.Data.DbType.String;
            thread.ParameterName = ":thread";
            thread.Value = loggingEvent.ThreadName;
            command.Parameters.Add(thread);

            var level = command.CreateParameter();
            level.Direction = System.Data.ParameterDirection.Input;
            level.DbType = System.Data.DbType.String;
            level.ParameterName = ":level";
            level.Value = loggingEvent.Level;
            command.Parameters.Add(level);

            var location = command.CreateParameter();
            location.Direction = System.Data.ParameterDirection.Input;
            location.DbType = System.Data.DbType.String;
            location.ParameterName = ":location";
            location.Value = loggingEvent.LocationInformation.FullInfo;
            command.Parameters.Add(location);

            var message = command.CreateParameter();
            message.Direction = System.Data.ParameterDirection.Input;
            message.DbType = System.Data.DbType.String;
            message.ParameterName = ":message";
            message.Value = loggingEvent.RenderedMessage;
            command.Parameters.Add(message);

            var log_date = command.CreateParameter();
            log_date.Direction = System.Data.ParameterDirection.Input;
            log_date.DbType = System.Data.DbType.DateTime2;
            log_date.ParameterName = ":log_date";
            log_date.Value = loggingEvent.TimeStamp;
            command.Parameters.Add(log_date);

            var exception = command.CreateParameter();
            exception.Direction = System.Data.ParameterDirection.Input;
            exception.DbType = System.Data.DbType.String;
            exception.ParameterName = ":exception";
            exception.Value = loggingEvent.GetExceptionString();
            command.Parameters.Add(exception);

            command.ExecuteNonQuery();
        }
        conn.Close();
    }
}

Log4Net Configuration
In our app.config/web.config, all we need to do is set our Log4Net configuration to use our new appender and set our logging level:

  <configSections>
    <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net"/>
  </configSections>
  <log4net>
    <appender name="PostgresAppender" type="Log4Net_and_PostgreSQL.PostgresAppender, Log4Net_and_PostgreSQL">
    </appender>
    <root>
      <level value="DEBUG"/>
      <appender-ref ref="PostgresAppender"/>
    </root>
  </log4net>

Test
To test our logging, in the entry point of our application we will have Log4Net write an Info log and a Fatal log with exception details:

[assembly: log4net.Config.XmlConfigurator()]
namespace Log4Net_and_PostgreSQL
{
    class Program
    {
        private static readonly ILog _log = LogManager.GetLogger(typeof(Program));
        static void Main(string[] args)
        {
            log4net.GlobalContext.Properties["AppName"] = Assembly.GetExecutingAssembly().FullName;


            _log.Info("App Started");

            try
            {
                throw new Exception("A fatal error as occurred.");
            }
            catch(Exception ex)
            {
                _log.Fatal("Fatal Error: ", ex);
            }

            Console.WriteLine("Press <Enter> to exit.");
            Console.ReadLine();
        }
    }
}

Now if we run this, we should get two logs in our table, one Info and one Fatal, and the Fatal log should contain the entire Exception message and stacktrace.