log4Net and SQLite

• Download log4NetSQlite.zip - 1.1 MB
 

Introduction

log4Net is an Apache library used to make log for a .NET application. The log should be errors, warnings, user information, debug information and more. This article is a quick guide that how you can configure log4net to store log messages in a SQLite database file.  

Prerequirements

1. Log4net download from http://logging.apache.org/log4net/download_log4net.cgi
2. SQLite para .NET Framwork 4.0 download from http://system.data.sqlite.org/downloads/1.0.81.0/sqlite-netFx40-setup-bundle-x86-2010-1.0.81.0.exe

Content / Body

Step 1: Creating the SQLite Database 

First you need to create the SQLite database file using the Visual Studio Design Time or using another SQLite Manager (Ex: Firefox Plugin), after  that you need to create the log4net table using the below script, a database file can be downloaded using this link: log4net database file, the database file must be copied to the project folder, the example in article use the App_Data folder,  if the App_Data folder doesn't exists please add it (Right Click over the project/Add/Add ASP.NET Folder/App_Data). the project structure should look like the below image. 

The script to create the log4net table is the following: 

CREATE TABLE [log4net] ( 
    [appdomain] varchar, [aspnetcache] varchar, [aspnetcontext] varchar,
    [aspnetrequest] varchar, [aspnetsession] varchar, [date] datetime,
    [exception] varchar, [file] varchar, [identity] varchar, 
    [location] varchar, [level] varchar, [line] integer, 
    [logger] varchar, [message] varchar, [method] varchar, 
    [ndc] varchar, [property] varchar, [stacktrace] varchar, 
    [stacktracedetail] varchar, [timestamp] bigint,  [thread] varchar,
    [type] varchar,  [username] varchar, [utcdate] datetime, 
    [appfree1] varchar, [appfree2] varchar,  [appfree3] varchar);

Step 2: Configuring the web.config  / App.config

To configure the web.config or app.config you will include the following XML sections:
configSections:  Add a new section config 

<section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net" /> 

<configuration>
  <configSections>
    <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net" />
  </configSections>
…
…
</configuration>

log4net: After the configSections include the log4net section:

<log4net debug="true">
    <appender name="sqlite" type="log4net.Appender.AdoNetAppender">
      <bufferSize value="1" />
      <connectionType value="System.Data.SQLite.SQLiteConnection, System.Data.SQLite, Version=1.0.81.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139" />
      <connectionString value="data source=|DataDirectory|\log4net.db;Synchronous=Off " />
      <commandText value="INSERT INTO log4net(appdomain, aspnetcache, aspnetcontext, 
        aspnetrequest, aspnetsession, [date], exception, file, identity, location, level, 
        line, logger, message, method, ndc, property, stacktrace, stacktracedetail, 
        [timestamp], thread, type, username, utcdate) VALUES (@appdomain, @aspnetcache, 
        @aspnetcontext, @aspnetrequest, @aspnetsession, @date, @exception, @file, @identity, 
        @location, @level, @line, @logger, @message, @method, @ndc, @property, @stacktrace, 
        @stacktracedetail, @timestamp, @thread, @type, @username, @utcdate)" />
      <parameter>
        <parameterName value="@appdomain" />
        <dbType value="String" />
        <layout type="log4net.Layout.PatternLayout">
          <conversionPattern value="%appdomain" />
        </layout>
      </parameter>
      <parameter>
        <parameterName value="@aspnetcache" />
        <dbType value="String" />
        <layout type="log4net.Layout.PatternLayout">
          <conversionPattern value="%aspnet-cache" />
        </layout>
      </parameter>
      <parameter>
        <parameterName value="@aspnetcontext" />
        <dbType value="String" />
        <layout type="log4net.Layout.PatternLayout">
          <conversionPattern value="%aspnet-context" />
        </layout>
      </parameter>
      <parameter>
        <parameterName value="@aspnetrequest" />
        <dbType value="String" />
        <layout type="log4net.Layout.PatternLayout">
          <conversionPattern value="%aspnet-request" />
        </layout>
      </parameter>
      <parameter>
        <parameterName value="@aspnetsession" />
        <dbType value="String" />
        <layout type="log4net.Layout.PatternLayout">
          <conversionPattern value="%aspnet-session{Usuario}" />
        </layout>
      </parameter>
      <parameter>
        <parameterName value="@date" />
        <dbType value="DateTime" />
        <layout type="log4net.Layout.RawTimeStampLayout" />
      </parameter>
      <parameter>
        <parameterName value="@exception" />
        <dbType value="String" />
        <layout type="log4net.Layout.PatternLayout">
          <conversionPattern value="%exception" />
        </layout>
      </parameter>
      <parameter>
        <parameterName value="@file" />
        <dbType value="String" />
        <layout type="log4net.Layout.PatternLayout">
          <conversionPattern value="%file" />
        </layout>
      </parameter>
      <parameter>
        <parameterName value="@identity" />
        <dbType value="String" />
        <layout type="log4net.Layout.PatternLayout">
          <conversionPattern value="%identity" />
        </layout>
      </parameter>
      <parameter>
        <parameterName value="@location" />
        <dbType value="String" />
        <layout type="log4net.Layout.PatternLayout">
          <conversionPattern value="%location" />
        </layout>
      </parameter>
      <parameter>
        <parameterName value="@level" />
        <dbType value="String" />
        <layout type="log4net.Layout.PatternLayout">
          <conversionPattern value="%level" />
        </layout>
      </parameter>
      <parameter>
        <parameterName value="@line" />
        <dbType value="String" />
        <layout type="log4net.Layout.PatternLayout">
          <conversionPattern value="%line" />
        </layout>
      </parameter>
      <parameter>
        <parameterName value="@logger" />
        <dbType value="String" />
        <layout type="log4net.Layout.PatternLayout">
          <conversionPattern value="%logger" />
        </layout>
      </parameter>
      <parameter>
        <parameterName value="@message" />
        <dbType value="String" />
        <layout type="log4net.Layout.PatternLayout">
          <conversionPattern value="%message" />
        </layout>
      </parameter>
      <parameter>
        <parameterName value="@method" />
        <dbType value="String" />
        <layout type="log4net.Layout.PatternLayout">
          <conversionPattern value="%method" />
        </layout>
      </parameter>
      <parameter>
        <parameterName value="@ndc" />
        <dbType value="String" />
        <layout type="log4net.Layout.PatternLayout">
          <conversionPattern value="%ndc" />
        </layout>
      </parameter>
      <parameter>
        <parameterName value="@property" />
        <dbType value="String" />
        <layout type="log4net.Layout.PatternLayout">
          <conversionPattern value="%property" />
        </layout>
      </parameter>
      <parameter>
        <parameterName value="@stacktrace" />
        <dbType value="String" />
        <layout type="log4net.Layout.PatternLayout">
          <conversionPattern value="%stacktrace" />
        </layout>
      </parameter>
      <parameter>
        <parameterName value="@stacktracedetail" />
        <dbType value="String" />
        <layout type="log4net.Layout.PatternLayout">
          <conversionPattern value="%stacktracedetail" />
        </layout>
      </parameter>
      <parameter>
        <parameterName value="@timestamp" />
        <dbType value="String" />
        <layout type="log4net.Layout.PatternLayout">
          <conversionPattern value="%timestamp" />
        </layout>
      </parameter>
      <parameter>
        <parameterName value="@thread" />
        <dbType value="String" />
        <layout type="log4net.Layout.PatternLayout">
          <conversionPattern value="%thread" />
        </layout>
      </parameter>
      <parameter>
        <parameterName value="@type" />
        <dbType value="String" />
        <layout type="log4net.Layout.PatternLayout">
          <conversionPattern value="%type" />
        </layout>
      </parameter>
      <parameter>
        <parameterName value="@username" />
        <dbType value="String" />
        <layout type="log4net.Layout.PatternLayout">
          <conversionPattern value="%username" />
        </layout>
      </parameter>
      <parameter>
        <parameterName value="@utcdate" />
        <dbType value="DateTime" />
        <layout type="log4net.Layout.PatternLayout">
          <conversionPattern value="%utcdate{yyyy-MM-dd HH:mm:ss}" />
        </layout>
      </parameter>     
    </appender>
    <root>
      <level value="ALL" />
      <appender-ref ref="sqlite" />
    </root>
  </log4net>

* The paramter Synchronous=Off in the connectionString increase the perfomance for SQLite insert operations.

* If you are not using the App_Data, in the connectionString property you need to include the absolute path (c:\myproject ....).

Step 3: Using the library

To use the library in the the AssemblyInfo.cs located in the Properties folder add the following line code: 

[assembly: log4net.Config.XmlConfigurator(ConfigFile = "Web.config", Watch = true)]

(If the  AssemblyInfo.cs file doesn't exists please generate it given right click on the project/Properties/int the Application TAB, click on Assembly Information/Complete the information and click on "Ok") 

In each form, webform or class in the project you can generate the log as is show in the following code:

...
using log4net;

namespace log4NetExample
{
    public partial class log4netPage : System.Web.UI.Page
    {
        private static readonly ILog log = LogManager.GetLogger(typeof(log4netPage).FullName);
        protected void Page_Load(object sender, EventArgs e)
        {            
            Session["Usuario"] = "log4Net";
            log.Debug("Debug Message");
            log.Info("Debug Message");
            log.Warn("Debug Message");
            log.Fatal("Fatal error", new Exception("Fatal error generated"));
            log.Error("Error message", new Exception("Error message generated"));
            Response.Write("<H1>Log Generated Successfully...</H1>");
        }
    }
}

The log messages will be stored in the SQLite database specified in the conection string, you can check it using the server explorer in the Visual Studio or another SQLite Manager.

ANNEXES

Log4Net - Internal Debug (Trace Debug)

If you want to trace the internal log of log4net you need to make the following configuration:

First add a key in the appSettings section like this:

<appSettings>
    <!-- Set true if you want to trace the internal log for log4Net, see the  system.diagnostics for more details-->
    <add key="log4net.Internal.Debug" value="false"/>
  </appSettings>

after that add in the system.diagnostics  the internal trace section like this:

<system.diagnostics>
    <trace autoflush="true">
      <listeners>
        <add
            name="textWriterTraceListener"
            type="System.Diagnostics.TextWriterTraceListener"
            initializeData="C:\temp\log4net.txt" />
      </listeners>
    </trace>
  </system.diagnostics>

In the file specfied in the  initializeData will be stored the internal log for log4net, this is useful in case to trace internal errors in log4net (so keep in mind that the log4net library work in silent mode).

Log4net Log Levels

Log4Net support several log levels, the main levels are: ALL, DEBUG, INFO, WARN, ERROR, FATAL 

each message should be sent in each log level, it depend the method that you use to register the log for example:  

log.Debug("Debug Message"); //It writes the mesage in DEBUG level
log.Info("Info Message"); //Write the mesage in INFO level
log.Warn("Warning Message"); //Write the mesage in WARN level
log.Fatal("Fatal error", new Exception("Fatal error generated")); //Write the mesage in ERROR level
log.Error("Error message", new Exception("Error message generated")); //Write the mesage in FATAL level

you can define what will be the log level in your application using the web.config or app.config, please find the section log4net and in the subsection <root> you can change the level value.

<root>
      <level value="ALL" />
      <appender-ref ref="sqlite" />
</root>

In this configuration all the messages will be written to the log o database file, so is recomended that in production environments you use "WARN" level, use ALL or DEBUG level for Development environments.

The following diagram show the relation between the diferrents log4net log levels:

References

1. SQLite:  Office Page: http://www.sqlite.org/, .Net Diver Official Page: http://system.data.sqlite.org/ 

2. Log4Net: Official Page: http://logging.apache.org/log4net/, PatterLayout (Permited Parameters) http://logging.apache.org/log4net/release/sdk/log4net.Layout.PatternLayout.html

3. SQLite Performance Tips: http://stackoverflow.com/questions/1711631/how-do-i-improve-the-performance-of-sqlite

History 

• 14-Jun-2012: Elvin Deras: Article creation.
• 14-Jun-2012: Elvin Deras: Added source example and annexes