log4net config SqLite in code

Question

Earlier today I asked a question about configuring log4net from code and got an answer very quickly which allowed me to configure it to output to a text file. Since then my needs have changed and I need to use SqLite as the appender. So I created the following class to allow this:

public static class SqLiteAppender
{
    public static IAppender GetSqliteAppender(string dbFilename)
    {
        var dbFile = new FileInfo(dbFilename);

    if (!dbFile.Exists)
    {
        CreateLogDb(dbFile);
    }

    var appender = new AdoNetAppender
                       {
                           ConnectionType = "System.Data.SQLite.SQLiteConnection, System.Data.SQLite",
                           ConnectionString = String.Format("Data Source={0};Version=3;", dbFilename),
                           CommandText = "INSERT INTO Log (Date, Level, Logger, Message) VALUES (@Date, @Level, @Logger, @Message)"
                       };

    appender.AddParameter(new AdoNetAppenderParameter
                              {
                                  ParameterName = "@Date",
                                  DbType = DbType.DateTime,
                                  Layout = new log4net.Layout.RawTimeStampLayout()

                              });

    appender.AddParameter(new AdoNetAppenderParameter
                              {
                                  ParameterName = "@Level",
                                  DbType = DbType.String,
                                  Layout = new log4net.Layout.RawPropertyLayout { Key = "Level" }
                              });

    appender.AddParameter(new AdoNetAppenderParameter
                              {
                                  ParameterName = "@Logger",
                                  DbType = DbType.String,
                                  Layout = new log4net.Layout.RawPropertyLayout { Key = "LoggerName" }
                              });

    appender.AddParameter(new AdoNetAppenderParameter
                              {
                                  ParameterName = "@Message",
                                  DbType = DbType.String,
                                  Layout = new log4net.Layout.RawPropertyLayout { Key = "RenderedMessage" }
                              });

    appender.BufferSize = 100;
    appender.ActivateOptions();
    return appender;
}

public static void CreateLogDb(FileInfo file)
{
    using (var conn = new SQLiteConnection())
    {
        conn.ConnectionString = string.Format("Data Source={0};New=True;Compress=True;Synchronous=Off", file.FullName);
        conn.Open();
        var cmd = conn.CreateCommand();

        cmd.CommandText =
                         @"CREATE TABLE Log(
                            LogId     INTEGER PRIMARY KEY,
                            Date      DATETIME NOT NULL,
                            Level     VARCHAR(50) NOT NULL,
                            Logger    VARCHAR(255) NOT NULL,
                            Message   TEXT DEFAULT NULL
                        );";

        cmd.ExecuteNonQuery();
        cmd.Dispose();
        conn.Close();
    }
}

}

The problem is that although the database is created and the table added, I am getting no logging to this.

The class is used like this:

BasicConfigurator.Configure(SqLiteAppender.GetSqliteAppender(applicationContext.GetLogFile().FullName));

any help to point me in the right direction would be appreciated.

Thanks

Answer 1

Did you try changing the buffer size from 100 to 1?

appender.BufferSize = 100;

to

appender.BufferSize = 1;

Currently your file is waiting for a 100 messages before it outputs any.

Answer 2

The problem is in the RawPropertyLayout instances. In my testing, they don't pull out the Level and LoggerName properties as one would expect, which results in null constraint violations on the database. These can be fixed by using a PatternLayout as follows:

Layout = new Layout2RawLayoutAdapter(new PatternLayout("%level"))

and

Layout = new Layout2RawLayoutAdapter(new PatternLayout("%logger"))

Here's a complete working example:

using System;
using System.Data;
using System.Data.SQLite;
using System.IO;
using log4net;
using log4net.Appender;
using log4net.Config;
using log4net.Layout;

namespace ConsoleApplication1
{
    class SQLiteLogging
    {
        public static void Test()
        {
            BasicConfigurator.Configure(SqLiteAppender.GetSqliteAppender("D:/test.dat"));
            LogManager.GetLogger(typeof (SqLiteAppender)).Info("Hello there");
        }

        public static class SqLiteAppender
        {
            public static IAppender GetSqliteAppender(string dbFilename)
            {
                var dbFile = new FileInfo(dbFilename);

                if (!dbFile.Exists)
                {
                    CreateLogDb(dbFile);
                }

                var appender = new AdoNetAppender
                                   {
                                       ConnectionType = "System.Data.SQLite.SQLiteConnection, System.Data.SQLite",
                                       ConnectionString = String.Format("Data Source={0};Version=3;", dbFilename),
                                       CommandText = "INSERT INTO Log (Date, Level, Logger, Message) VALUES (@Date, @Level, @Logger, @Message)"
                                   };

                appender.AddParameter(new AdoNetAppenderParameter
                                          {
                                              ParameterName = "@Date",
                                              DbType = DbType.DateTime,
                                              Layout = new RawTimeStampLayout()

                                          });

                appender.AddParameter(new AdoNetAppenderParameter
                                          {
                                              ParameterName = "@Level",
                                              DbType = DbType.String,
                                              Layout = new Layout2RawLayoutAdapter(new PatternLayout("%level"))
                                          });

                appender.AddParameter(new AdoNetAppenderParameter
                                          {
                                              ParameterName = "@Logger",
                                              DbType = DbType.String,
                                              Layout = new Layout2RawLayoutAdapter(new PatternLayout("%logger"))
                                          });

                appender.AddParameter(new AdoNetAppenderParameter
                                          {
                                              ParameterName = "@Message",
                                              DbType = DbType.String,
                                              Layout = new RawPropertyLayout { Key = "RenderedMessage" }
                                          });

                appender.ActivateOptions();
                return appender;
            }

            public static void CreateLogDb(FileInfo file)
            {
                using (var conn = new SQLiteConnection())
                {
                    conn.ConnectionString = string.Format("Data Source={0};New=True;Compress=True;Synchronous=Off", file.FullName);
                    conn.Open();
                    var cmd = conn.CreateCommand();

                    cmd.CommandText =
                                     @"CREATE TABLE Log(
                            LogId     INTEGER PRIMARY KEY,
                            Date      DATETIME NOT NULL,
                            Level     VARCHAR(50) NOT NULL,
                            Logger    VARCHAR(255) NOT NULL,
                            Message   TEXT DEFAULT NULL
                        );";

                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                    conn.Close();
                }
            }
        }
    }
}