log4net and system.data.sqlite

Question

Does anyone know of a good example on how to set up log4net to use the system.data.sqlite provider?

I've been playing around with it lately and I thought I had it all working. It makes a successful connection to the database and "writes" it out. However, when I look at the table data, it never actually commits the log.

Answer 1

http://logging.apache.org/log4net/release/config-examples.html

Answer 2

I was searching that same thing today and found http://sqlite.phxsoftware.com/forums/t/1667.aspx

It's a sample configuration from someone who managed to succesfully use the System.Data.SQLite ADO provider (instead of the other one that is documented in the official apache docs).

Hope it leads you in the right direction.

Answer 3

Make sure you have the following references set:

• log4net (obviously)
• System.Data.SQLite (you might forget about this one)

Close the project in Visual Studio and reopen the .csproject (or equivalent for VB) in a text editor, and look for the references section. Mine looks like this:

<Reference Include="log4net, Version=1.2.10.0, Culture=neutral, PublicKeyToken=1b44e1d426115821, processorArchitecture=MSIL">
  <SpecificVersion>False</SpecificVersion>
  <HintPath>..\..\..\..\externals\log4net-1.2.10\bin\net\2.0\release\log4net.dll</HintPath>
</Reference>
<Reference Include="System" />
<Reference Include="System.Core">
  <RequiredTargetFramework>3.5</RequiredTargetFramework>
</Reference>
<Reference Include="System.Data.SQLite, Version=1.0.65.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139, processorArchitecture=x86">
  <SpecificVersion>False</SpecificVersion>
  <HintPath>..\..\..\..\externals\sqlite.net\System.Data.SQLite.dll</HintPath>
</Reference>

Notice the reference for SQLite. In this case you need this text: System.Data.SQLite, Version=1.0.65.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139 This is the full name of the assembly which hosts the ADO.net compatible sql connection classes which is actually what log4net has a dependency on.

Reopen your project in Visual Studio, make sure you have .config file and open it in the XML text editor. It is going to be easier to paste my config file in here than explain everything. Notice that you are going to use the assembly name here.

Put this in the configSections XML node:

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

As a direct XML childnode of configuration, put this XML section:

<log4net>
    <appender name="ConsoleAppender" type="log4net.Appender.ConsoleAppender" >
        <layout type="log4net.Layout.PatternLayout">
            <conversionPattern value="%date [%thread] %-5level %logger [%ndc] - %message%newline" />
        </layout>
    </appender>
    <appender name="sqlite" type="log4net.Appender.AdoNetAppender">
        <bufferSize value="100" />
        <connectionType value="System.Data.SQLite.SQLiteConnection, System.Data.SQLite, Version=1.0.65.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139" />
        <connectionString value="Data Source=log4net.db;Version=3;" />
        <commandText value="INSERT INTO Log (Date, Level, Logger, Message) VALUES (@Date, @Level, @Logger, @Message)" />
        <parameter>
            <parameterName value="@Date" />
            <dbType value="DateTime" />
            <layout type="log4net.Layout.RawTimeStampLayout" />
        </parameter>
        <parameter>
            <parameterName value="@Level" />
            <dbType value="String" />
            <layout type="log4net.Layout.PatternLayout">
                <conversionPattern value="%level" />
            </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>
    </appender>
    <root>
        <level value="ALL" />
        <appender-ref ref="ConsoleAppender" />
        <appender-ref ref="sqlite" />
    </root>
</log4net>

As a last step you have to make sure that the sqlite db file that you specified pre-exists at the specified location. One way of doing this is to create the database and attach it as a resource to be copied to the output folder. Make sure it has the same name as what you specified (In this case log4net.db)