Log4Net - Logging to the Database and Troubleshooting logging Issues

There are a three parts to log4net. There is the configuration, the setup, and the call

There are seven logging levels, five of which can be called in your code.

OFF - nothing gets logged (cannot be called)
FATAL
ERROR
WARN
INFO
DEBUG
ALL - everything gets logged (cannot be called)

1) Setting up log for net in your project

Select Tools menu > NuGet package manager > Manage NuGet packages for the solution

Once the NuGet is installed you get the log4net dll under the References list.

2) Add the below line to the AssemblyInfo.cs file

[assembly: log4net.Config.XmlConfigurator(Watch = true)]

Configurations

•  In The App.config file add the log4net configurations

<configuration>

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

• Adding the appender and the logger details.

  <log4net>
    <appender name="AdoNetAppender" type="log4net.Appender.AdoNetAppender">
            <bufferSize value="1" />
            <connectionType value="System.Data.SqlClient.SqlConnection, System.Data, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
            <connectionString value="data source=MyDatabaseServer;initial catalog=MyDatabaseName;integrated security=false;persist security info=True;User ID=sa;Password=****" />
            <commandText value="INSERT INTO Log ([logdate],[message]) VALUES (@logdate, @message)" />
            <parameter>
              <parameterName value="@logdate" />
              <dbType value="DateTime" />
              <layout type="log4net.Layout.RawTimeStampLayout" />
            </parameter>
          <parameter>
          <parameterName value="@message" />
          <dbType value="String" />
          <size value="255" />
          <layout type="log4net.Layout.PatternLayout">
            <conversionPattern value="%message" />
          </layout>
        </parameter>
    </appender>
    <logger name="AdoNetAppender">
      <level value="INFO" />
      <appender-ref ref="AdoNetAppender" />
    </logger>
  </log4net>

In the code, declare the logger

private static ILog myLog = LogManager.GetLogger("AdoNetAppender");

In the code add logging

myLog.Info(string.Format("{0}\t{1}\t{2}", DateTime.Now, "Before do work", i));

The logger pushes logging info to the DB in batches. So maker sure to push each log by using the <bufferSize value="1" /> as configured in the app.config above.

Trouble shooting Logger issues

Example 1 : Logger does not log anything to the database

To troubleshoot logger issues add the below configurations to the app.config file.

     <appSettings>
      <add key="log4net.Internal.Debug" value="true"/>
   </appSettings>

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

This will log issues in the mentioned text file.

Example 2 : After the DB goes offline or losing DB connectivity and comes back online, the logger does not Log anymore.

to solve this add the tag  <reconnectonerror value="true" />

Example:

    <appender name="AdoNetAppender" type="log4net.Appender.AdoNetAppender">
            <bufferSize value="1" />
            <reconnectonerror value="true" />

Adding this might slow down the app performance since, log4net trys to reconnect to the server till the connection attempt time outs.

To overcome that you need to set a connection timeout in the connection string as shown below:

 <connectionString value="data source=MyDatabaseServer;initial catalog=MyDatabaseName;integrated security=false;persist security info=True;User ID=sa;Password=****;Connect Timeout=1" />

If you had the troubleshooting configurations as shown in an above step, then you would get an output similar to this: