Logging to Database with Log4net

I find it very useful to have all web applications and other executables within a solution log to a common table in the database, for monitoring and debugging purposes. This is fairly easy to accomplish with Log4net.

First, create the table:

CREATE TABLE [dbo].[Log4net] ( 
  [ID] [int] IDENTITY (1, 1) NOT NULL ,
  [Date] [datetime] NOT NULL ,
  [Hostname] [varchar] (255) NOT NULL ,
  [Thread] [varchar] (255) NOT NULL ,
  [Level] [varchar] (20) NOT NULL ,
  [Logger] [varchar] (255) NOT NULL ,
  [Message] [varchar] (8000) NOT NULL ,
  [Exception] [varchar] (8000) NULL

Here is an example Log4net appender configuration:

      <appender name="AdoNetAppender_SqlServer" type="log4net.Appender.AdoNetAppender">
        <connectionType value="System.Data.SqlClient.SqlConnection, System.Data, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
        <connectionString value="data source=foo;initial catalog=bar;integrated security=true" />
        <bufferSize value="1" />
        <UseTransactions value="false"/>
        <filter type="log4net.Filter.LevelRangeFilter">
          <levelMin value="INFO" />
        <commandText value="INSERT INTO Log4net ([Date],[Hostname],[Thread],[Level],[Logger],[Message],[Exception]) VALUES (@log_date, @hostname, @thread, @log_level, @logger, @message, @exception)" />
          <parameterName value="@log_date" />
          <dbType value="DateTime" />
          <layout type="log4net.Layout.PatternLayout" value="%date{yyyy'-'MM'-'dd HH':'mm':'ss'.'fff}" />
          <!--<layout type="log4net.Layout.PatternLayout" value="%date" />-->
          <parameterName value="@hostname" />
          <dbType value="String" />
          <size value="255" />
          <layout type="log4net.Layout.PatternLayout" value="%property{log4net:HostName}" />
          <parameterName value="@thread" />
          <dbType value="String" />
          <size value="255" />
          <layout type="log4net.Layout.PatternLayout" value="%thread" />
          <parameterName value="@log_level" />
          <dbType value="String" />
          <size value="50" />
          <layout type="log4net.Layout.PatternLayout" value="%level" />
          <parameterName value="@logger" />
          <dbType value="String" />
          <size value="255" />
          <layout type="log4net.Layout.PatternLayout" value="%logger" />
          <parameterName value="@message" />
          <dbType value="String" />
          <size value="-1" />
          <layout type="log4net.Layout.PatternLayout" value="%message" />
          <parameterName value="@exception" />
          <dbType value="String" />
          <size value="-1" />
          <layout type="log4net.Layout.PatternLayout" value="%exception" />

And here is how I write warnings and errors to both file and database:

        <level value="WARN" />
        <appender-ref ref="ErrorFileAppender" />
        <appender-ref ref="AdoNetAppender_SqlServer" />

For completeness, here is a pattern layout I usually use for file appenders:

<conversionPattern value="%date %-5level [%thread] %logger: %message%newline" /> 

A common problem with logging to the database is access rights. To debug such issues, it is convenient to turn on internal log4net logging. That is done with an application setting:

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

Internal debugging messages are written to the console and to System.Diagnostics.Trace. You can use the debugger to see these messages, or use DebugView from SysInternals.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s