Using SQL Server Ce 4.0 with log4net

Question

I'm trying to use SQL Server Compact Edition (v4.0) with log4net. There's a great guide here. It uses Compact Edition 3.5, but most of the steps should be the same.

So, I've created a SQL Server CE 4.0 database inside "App_Data" with a "Log" table in my MVC web app, and made sure that System.Data.SqlServerCe "copy local" is true. I also changed the connection string to account for the app_data folder.

My appender looks like this:

<appender name="SqlCeAppender" type="log4net.Appender.AdoNetAppender">
    <connectionType value="System.Data.SqlServerCe.SqlCeConnection, System.Data.SqlServerCe" />
    <connectionString value="Data Source=|DataDirectory|\='log4net.sdf'" />
    <commandText
    value="INSERT INTO Log 
      ([Date],[Thread],[Level],[Logger],[Message],
      [Exception], [UserName], [Custom]) 
      VALUES 
      (@log_date, @thread, @log_level, @logger, @message, 
      @exception, @username, @custom)" />
    <parameter>
      <parameterName value="@log_date" />
      <dbType value="DateTime" />
      <layout type="log4net.Layout.RawUtcTimeStampLayout" />
    </parameter>
    <parameter>
      <parameterName value="@thread" />
      <dbType value="String" />
      <size value="255" />
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%thread" />
      </layout>
    </parameter>
    <parameter>
      <parameterName value="@log_level" />
      <dbType value="String" />
      <size value="50" />
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%level" />
      </layout>
    </parameter>
    <parameter>
      <parameterName value="@logger" />
      <dbType value="String" />
      <size value="255" />
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%logger" />
      </layout>
    </parameter>
    <parameter>
      <parameterName value="@message" />
      <dbType value="String" />
      <size value="4000" />
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%message" />
      </layout>
    </parameter>
    <parameter>
      <parameterName value="@exception" />
      <dbType value="String" />
      <size value="2000" />
      <layout type="log4net.Layout.ExceptionLayout" />
    </parameter>
    <parameter>
      <parameterName value="@username" />
      <dbType value="String" />
      <size value="255" />
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%username" />
      </layout>
    </parameter>
    <parameter>
      <parameterName value="@custom" />
      <dbType value="String" />
      <size value="255" />
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%property{Custom}" />
      </layout>
    </parameter>
  </appender>

And... nothing happens - no logs. When I swap out to my text file appender I get the logs so it's something to do with this appender - but I don't know what. Can anyone help?

Answer 1

I believe the issue you are experiencing is the bufferSize is not set. Set the bufferSize option to be one like so:

<bufferSize value="1" />

Put that inside your appender and you should be good to go.

Answer 2

I actually had your same problem, but managed to solve it.

I found a couple of problems in your example:

<connectionString value="Data Source=|DataDirectory|\='log4net.sdf'" />

Must be changed to:

<connectionString value="Data Source=|DataDirectory|log4net.sdf" />

And I also removed those custom fields that are not in the default log4net table schema (username and custom) so the query becomes:

INSERT INTO Log 
      ([Date],[Thread],[Level],[Logger],[Message],
      [Exception]) 
      VALUES 
      (@log_date, @thread, @log_level, @logger, @message, 
      @exception)

And of course I removed the 2 related <parameter> blocks.

I also added the <bufferSize> node as suggested by @BiggsTRC .

Then everything started working.

Answer 3

Ok - I gave up with this in the end and decided to do it by writing a custom Appender, which uses Entity Framework to write the logs - worked out better for me actually since I can add more information about the error in my custom appender.