Fast track to Log4Net database logging

Georg Jansen posted on October 15, 2005 13:11

The world is full of “home made” logging solutions, every developer have at least a couple of self-made solutions on there track record. Log4Net gives us a standardized framework for logging, using this framework we can take advantage of a standardized, feature rich, well proven and highly configurable logging framework. In addition, using Log4Net we get access to a large community of fellow loggers.

In this article I will walk you trough the steps necessary to install, configure and getting started with Log4net, and writing log rows to Microsoft Sql Server using the AdoNetAppender.

Configuring and starting to use Log4Net and to write log rows to Sql Server is pretty simple and strait forward, we just need to follow the six steps described below:

Download the framework from the Apache organization.
Install the Log4Net framework on your pc
Create a table in Sql Server to receive the Log4Net log rows.
Insert a configuration section into your application config file.
Granting access in the database.
Write log statements in your program.

1. Download the Log4Net framework from the Apache organization.

The Log4Net framework (with full source) is available on: http://logging.apache.org/log4net/downloads.html

Currently the version 1.2.9 beta is available and it’s widely used.

2. Install the Log4Net framework on your PC.

Unzip the downloaded zip file to a directory of your choice, for example c:\Log4Net, make sure to include the folder names when unzipping. Together with the source code, the binaries (dll’s targeting different environments) is also a copy of the complete Log4Net documentation, witch will be accessible on your local pc in <installation folder\doc\index.html.

3. Create a table in Sql Server to receive the Log4Net log rows.

You need to create at table in a Microsoft Sql Server database, this table will be used by Log4net to store the log rows. To create the table you can use the sample Sql script bellow:

CREATE TABLE [Log4Net] (

[Id] [int] IDENTITY (1, 1) NOT NULL ,

[Date] [datetime] NOT NULL ,

[Thread] [varchar] (255) NOT NULL ,

[Level] [varchar] (50) NOT NULL ,

[Logger] [varchar] (255) NOT NULL ,

[Message] [varchar] (4000) NOT NULL ,

[Exception] [varchar] (2000) NULL

)

If you prefer, you can give the log table another name.

You also have to decide, if you will put the log table in a separate database or use the database which the application use. Personally I prefer to create a separate database and store the log rows from all applications into that one table, keeping all logging in one centralized place. Using Log4Net Dashboard (L4NDash) you can easily filter and select log rows from one application and analyze the behavior of that application, and on the same time have total overview on the logging situation for all applications.

4. Insert a configuration section into your application config file.

Log4Net offers a rich collections of configurable options. In addition the configuration can be done following different methods (programmatically, separate config file and application config file). In this article I will focus on the simples and most strait forward method, putting the configuration into the application config file (web.config if you are writing an ASP.NET application).

In the beginning of your config file right after the <configuration> section you insert a ConfigSections, it is important that this section is the first section after the <configuration>.

<section name="log4net"

type="System.Configuration.IgnoreSectionHandler"/>

</configSections>

This tells the .NET framework that log4net will handle parsing and interception of the log4net configuration, if you don’t insert this section you will end up receiving some ugly error messages when you start your program.

Then you insert the log4net configuration section, I prefer to put this section in the end of the config file, just before the </configuration> end tag.

<connectionType

value="System.Data.SqlClient.SqlConnection, System.Data, Version=1.0.5000.0,

Culture=neutral, PublicKeyToken=b77a5c561934e089"/>

<connectionString

value="Data Source=MYSERVERNAME;initial Catalog=MYDATABASENAME;

Integrated Security=True;"/>

<commandText

value="INSERT INTO Log4Net ([Date], [Thread], [Level], [Logger], [Message],

[Exception]) VALUES

(@log_date, @thread, @log_level, @logger, @message, @exception)"/>

</parameter>

</layout>

</parameter>

</layout>

</parameter>

</layout>

</parameter>

</layout>

</parameter>

</parameter>

</appender>

<root>

<appender-ref ref="AdoNetAppender"/>

</root>

</log4net>

If you copy and paste the example above, you need to modify the connection type value=, connection string value= and the commandText value=, they all must be written on one line.

You will need to change the value specifying the server name (MYSERVERNAME) and the initial catalog (MYDATABASENAME). Replace the MYSSERVERNAME and MYDATABASENAME with values which are valid in your environment.

In the configuration section above, I have only defined one appender, the AdoNetAppender which will write log rows to a database. You can in addition define other appenders, for example FileAppender, RollingFileAppender, EventLogAppender, SMTPAppender etc.

The connection string is also closely related to the next paragraph – granting access in the database.

If you need to learn more about how to define connection string, a good place to start is the web site http://www.connectionstrings.com/.

5. Granting access in the database.

As already mention, you need to modify the connection string (servername and databasename). In addition you need to decide how to define the security part of the connection string.

It is two different methods to set up the security:

Use integrated security
State the username and the password in the connection string.

In both cases you need to make sure that the user has access to the, SQL server, the database and the databasetable that Log4Net is going to use.

If you are using integrated security and you are writing an ASP.NET application, the ASP.NET has a base process identity, typically MachineName\ASPNET on IIS5 or Network Service on IIS6, that is used if the application is not impersonating via <identity Impersonate=”true”/>, then the identity will be the anonymous user (typically IUSER_MACHINENAME). Anyway make sure you know who is running the ASP.NET application, and grant access to that user in the database.

Granting access to the user might involve adding a new login to SqlServer as well as adding that login to the database as a database user, in addition you also might need to grant access to the specific table.

To do the necessary steps in Sql Server, you can use Sql Server Enterprise Manager or do it manually; modifying and using the SQL script below, and execute it in QueryAnalyzer.

Use master

Exec sp_addlogin 'MYLOGIN','MyLogInPassword'

use MyDatabase

Exec sp_adduser 'MYLOGIN','MYUSER','public'

Grant all on Log4Net TO MYUSER

If you are adding a machine-account (for example machinename\ASPNET) then you don’t need to specify the password in the sp_addlogin procedure.

6. Write log statements in your program.

Before you can write any log stamens you need to add a reference to log4net.dll in your project. In Visual Studio right click on Reference folder and browse to the Log4Net.dll and add the log4net.dll as a reference to your project.

In one (and only one) of your source file you need to write the assembly directive:

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

Then you are ready to write log statements in your program.

In all your classes where you whish to write log statements you need to declare/create a logger. This is done using the statement:

namespace MyNameSpace

{

public class MyClass

{

private static readonly log4net.ILog log =

log4net.LogManager.GetLogger(“MyLoggerName”);

Instead of naming the logger yourself, as in the example above, I will recommend to use a more standardized and portable statement. This statement can be copied and pasted between classes:

private static readonly log4net.ILog log =

log4net.LogManager.GetLogger(

System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

In the example above the logger will be named: “MyNameSpace.MyClass”, in addition to being more portable, this statement will also give you standardized logger names which will reflect the name of your namespace/class names (Please note that the .NET Compact Framework 1.0 does not support System.Reflection.MethodBase.GetCurrentMethod()).

Using the log object you write log statements like:

log.Error("My message", MyExceptionObject);

log.Fatal("My message", MyExceptionObject);

log.Warn("My warning");

log.Info("My info message");

log.Debug("My debug message");

There are others methods available, a full description of the various log methods are available on http://logging.apache.org/log4net/release/sdk/log4net.ILogMembers.html

The documentation may also be access in your local copy of the documentation.

Sign yourself up on the log4net mailing list

The mailing list for log4net is a great source of information, simply send a mail to log4net-user-subscribe@logging.apache.org, and you are in the loop.

There are several mail archives available, one of them is:

http://mail-archives.apache.org/mod_mbox/logging-log4net-user/

The search capabilities of the mail archive are limited, but on the home page of http://www.l4ndash.com I have integrated a Google search, which will only search the mail archive.

Other sources of information

I have written a couple of other articles about using Log4Net you may find interesting: