Context

I wanted log4net to write its log entries in a SQL Server database. log4net’s AdoNetAppender would have done the trick. But, there are three requirements I need to keep in mind.

  1. I have to use my logging framework
  2. The connectionstring has to be stored the app.config or web.config
  3. The machine’s hostname has to be added to the log entry

MssqlAppender

Creating your own log4net appender is fairly easy to do. We are going to base our MssqlAppender on the provided AdoNetAppender.

using System;
using System.Configuration;
using log4net.Appender;
public class MssqlAppender : AdoNetAppender
{
}

When using the logging framework, the log4net configuration is described in a separate file (log4net.config). Normally, when you configure the AdoNetAppender, you provide a connectionstring. According to the requirements, I have to retrieve the connectionstring from the application’s configuration file. Note: Add a reference to System.configuration in your project.

public class MssqlAppender : AdoNetAppender
{
    private const string Log4netConnectionString = "Log4netConnectionString";

	public new string ConnectionString
	{
		get { return base.ConnectionString; }
		set { base.ConnectionString = ConfigurationManager.ConnectionStrings[Log4netConnectionString].ConnectionString; }
	}
}

Of course, don’t forget to enter the connectionstring in the application’s configuration file, like:

<connectionStrings>
	<add name="Log4netConnectionString" providerName="System.Data.SqlClient" connectionString="Data Source=SERVER,PORT;Initial Catalog=DB;Persist Security Info=True;User ID=USERNAME;Password=PASSWORD" />
</connectionStrings>

To fulfill the third requirement, we need to edit our appender’s configuration. Watch carefully to the parameter @host

<configuration>
	<log4net debug="false">
		<appender name="AdoNetAppender" type="MssqlAppender">
			<bufferSize value="1" />
			<connectionType value="System.Data.SqlClient.SqlConnection, System.Data, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
			<connectionString value="Dynamic" />
			<commandText value="INSERT INTO ApplicationLog ([Date],[Level],[Logger],[Host],[Message],[Exception]) VALUES (@log_date, @log_level, @logger, @host, @message, @exception)" />
			<parameter>
				<parameterName value="@log_date" />
				<dbType value="DateTime" />
				<layout type="log4net.Layout.RawTimeStampLayout" />
			</parameter>
			<parameter>
				<parameterName value="@log_level" />
				<dbType value="String" />
				<size value="50" />
				<layout type="log4net.Layout.PatternLayout">
					<conversionPattern value="%p" />
				</layout>
			</parameter>
			<parameter>
				<parameterName value="@logger" />
				<dbType value="String" />
				<size value="255" />
				<layout type="log4net.Layout.PatternLayout">
					<conversionPattern value="%c" />
				</layout>
			</parameter>
			<parameter>
				<parameterName value="@host" />
				<dbType value="String" />
				<size value="50" />
				<layout type="log4net.Layout.PatternLayout">
					<conversionPattern value="%property{host}" />
				</layout>
			</parameter>
			<parameter>
				<parameterName value="@message" />
				<dbType value="String" />
				<size value="4000" />
				<layout type="log4net.Layout.PatternLayout">
					<conversionPattern value="%m" />
				</layout>
			</parameter>
			<parameter>
				<parameterName value="@exception" />
				<dbType value="String" />
				<size value="2000" />
				<layout type="log4net.Layout.ExceptionLayout" />
			</parameter>
		</appender>
		<root>
			<level value="INFO" />
			<appender-ref ref="AdoNetAppender" />
		</root>
	</log4net>
</configuration>

You can add custom properties to your log4net context. Setting the Host property is best done in the constructor. After editing the framework’s Logger constructor it will look like the code snippet below. More info about context and properties at log4net

public Logger()
{
	log = LogManager.GetLogger(typeof(Logger));
	log4net.GlobalContext.Properties["host"] = Environment.MachineName;
}

 

Debugging log4net

If you’re having trouble with the implementation, you could enable the internal debugging of log4net. Fe, if you have an invalid connectionstring, log4net will not throw an exception. To find out what’s going on: enable the internal debugging.

Sources and Example

Download the logging framework sources with example.