Log4Net seems to connect to the database but not Insert

Question

I have debug turned on so I'm pretty sure it's connecting to the db. I say that because if I misspell the database name it writes an error to output.

I'm not sure what I'm doing wrong. I'm aware I have parameters not being used yet.

What I did was adapt this tutorial into one project. If you need to see more please let me know.

Log4Net.config

<?xml version="1.0" encoding="utf-8" ?>
<log4net debug="true">
  <root>
    <level value="ALL"/>
    <appender-ref ref="AdoNetAppender"/>
    <appender-ref ref="DebugAppender"/>
  </root>
  <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=server;Initial Catalog=db; User Id=user; Password=pass" />
  <commandText value="INSERT INTO LogException ([LogLevel],[LogMessage],[StackTrace],[Object],[CreateDateTime]) VALUES (@log_level, @message, @stacktrace, @exception, @date)" />
    <parameter>
      <parameterName value="@log_date" />
      <dbType value="DateTime" />
      <layout type="log4net.Layout.RawTimeStampLayout" />
    </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="@entryAssembly" />
      <dbType value="String" />
      <size value="200" />
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%property{entryAssembly}" />
      </layout>
    </parameter>
    <parameter>
      <parameterName value="@callingAssembly" />
      <dbType value="String" />
      <size value="200" />
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%property{callingAssembly}" />
      </layout>
    </parameter>
    <parameter>
      <parameterName value="@method" />
      <dbType value="String" />
      <size value="2000" />
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%property{method}" />
      </layout>
    </parameter>
    <parameter>
      <parameterName value="@stacktrace" />
      <dbType value="String" />
      <size value="2000" />
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%stacktrace" />
      </layout>
    </parameter>
  </appender>
</log4net>

Log4netLoggingService.cs

using System;
using System.Diagnostics;
using System.IO;
using System.Linq;
using Logging.Contracts.Log;
using log4net;
using log4net.Appender;
using log4net.Config;
using log4net.Filter;
using log4net.Util;


namespace Logging
{
    public class Log4NetLoggingService : ILoggingService
    {
        private readonly ILog _logger;

        static Log4NetLoggingService()
        {
            var log4NetConfigFilePath = @"C:\Work\folder\Main\Logging\Log4Net.config";

            XmlConfigurator.ConfigureAndWatch(new FileInfo(log4NetConfigFilePath));

        }

        //targets reads from and enum to know where to save.
        public Log4NetLoggingService(LogTarget targets = LogTarget.All)
        {
            _logger = LogManager.GetLogger(new StackFrame(1).GetMethod().DeclaringType);
#if DEBUG
            var error = LogManager.GetRepository().ConfigurationMessages.Cast<LogLog>();
#endif

            if (targets.HasFlag(LogTarget.All))
                return;

            SwitchOffLogTargets(targets);
        }

        protected ILog logger { get { return _logger; } }

        public void Fatal(ErrorLogEntry logEntry)
        {
            logEntry.Level = Level.Fatal.ToString();
            if (_logger.IsFatalEnabled)
                _logger.Fatal(logEntry);
        }

        public void Error(ErrorLogEntry logEntry)
        {
            logEntry.Level = Level.Error.ToString();
            if (_logger.IsErrorEnabled)
                _logger.Error(logEntry);
        }

        public void Warn(LogEntry logEntry)
        {
            logEntry.Level = Level.Warn.ToString();
            if (_logger.IsWarnEnabled)
                _logger.Warn(logEntry);
        }

        public void Info(LogEntry logEntry)
        {
            logEntry.Level = Level.Info.ToString();
            if (_logger.IsInfoEnabled)
                _logger.Info(logEntry);
        }

        public void Debug(LogEntry logEntry)
        {
            logEntry.Level = Level.Debug.ToString();
            if (_logger.IsDebugEnabled)
                _logger.Debug(logEntry);
        }

        private void SwitchOffLogTargets(LogTarget targets)
        {
            var appenders = _logger.Logger.Repository.GetAppenders().ToList();

            if (!targets.HasFlag(LogTarget.Database))
            {
                var db = appenders.FirstOrDefault(piA => piA is AdoNetAppender);
                if (db != null)
                    ((AdoNetAppender)db).AddFilter(new DenyAllFilter());
            }

            if (!targets.HasFlag(LogTarget.TextFile))
            {
                var file = appenders.FirstOrDefault(piA => piA is RollingFileAppender);
                if (file != null)
                    ((RollingFileAppender)file).AddFilter(new DenyAllFilter());
            }

            if (!targets.HasFlag(LogTarget.Trace))
            {
                var trace = appenders.FirstOrDefault(piA => piA is AspNetTraceAppender);
                if (trace != null)
                    ((AspNetTraceAppender)trace).AddFilter(new DenyAllFilter());
            }

        }
    }
}

UPDATES:

It turns out my latest attempt works. I just misspelled the config file name. I hope this helps someone in the future. I plan on writing a blog post about this.

Log4Net.config

<?xml version="1.0" encoding="utf-8" ?>
<log4net>
  <root>
    <level value="ALL" debug="true"/>
    <!--Add the appenders you want to use here-->
    <appender-ref ref="AdoNetAppender"/>
    <!--to debug log4net. check the output window of Visual Studio-->
    <appender-ref ref="DebugAppender"/>
  </root>
  <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=(localdb)\MSSQLLocalDB;initial catalog=log4NetTestDB;integrated security=false;persist security info=True;" />
    <commandText value="INSERT INTO LogException ([Message]) VALUES (@message)" />
      <parameter>
        <parameterName value="@message" />
        <dbType value="String" />
        <size value="4000" />
        <layout type="log4net.Layout.PatternLayout">
          <conversionPattern value="%message" />
        </layout>
      </parameter>
  </appender>
</log4net>

ILoggingAdapter

namespace Logging
{
    public interface ILoggingAdapter
    {
        TimeSpan ExecutionTime { get; set; }
        int Counter { get; set; }
        void Info(string message);
        void Warn(string message);

    }
}

Logger

namespace Logging
{
    public sealed class Logger : ILoggingAdapter
    {
        private ILog _log = LogManager.GetLogger(typeof(Logger));

        public TimeSpan ExecutionTime { get; set; }
        public int Counter { get; set; }
        public string Info { get; set; }
        public string Warn { get; set; }


        void ILoggingAdapter.Info(string message)
        {
            throw new NotImplementedException();
        }

        void ILoggingAdapter.Warn(string message)
        {
            _log.Warn(message);
        }
    }
}

Answer 1

Alright so several notes

• Have you tried logging into your database as the specified user and running the insert query you want?
• Have you tried enabling debug mode in log4net to see whats going on under the hood?
• Related to the above, use two loggers at the root - one for the db, another for a file. It's a terrible idea to use only db logging since if the db fails you will not get a log about it failing. At the very least, your local dev environment should be logging to a file
• beefycoder has written a ton about understanding log4net note the many parts to that tutorial
• I have no clue why you are doing stuff like if (_logger.IsInfoEnabled) - that's what LogInfo already does.
• Why on earth are you taking LogEntry as a parameter type? The entire point of using a service class like this is to break the hard dependency on log4net. By using that class type you've just created a hard dependency on log4net in your consumers. Just pass in strings. Also that way you won't need that whole logEntry.Level = Level.Warn.ToString(); nonsense
• Hard coded path string in your file. This will now not work on the machines of other developers.
• Stack check in the constructor - you've now slowed down significantly construction of any classes that use this, and it will give you different results when built in RELEASE mode with inlining turned on. Rather than using a service class, just create an extension method or pass in the object you want the logger to be sourced in as a parameter which does the same thing.
• Your code configuration overrides xml configuration (I think...log4net's rules on order of precedence are fuzzy - thats the big reason I prefer NLog)
• SwitchOffLogTargets why!? Just change the config file if you want that behavior. The config file can define whatever targets it wants and be changed at any time, but here you're just assuming it happens to have certain things in it.