How to store log in database using log4net

One of the best ways to store log in the database using log4net coz it is easy to use and its world wide popularity. For storing log in the database all task are manage by log4net internally.

At first you need to create a table for Storing log in the SqlServer database

The table should be like as

CREATE TABLE [dbo].[Log] (

    [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

)

1.    Download log4net from http://logging.apache.org/log4net/download.html

2.    Open visual studio and create an application.

3.    Add to the project a reference to the \bin\net\2.0\release\log4net.dll assembly in the log4net distribution.

4.    Now put this web.config/app.config file in configuration tag.

<configSections> <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler,Log4net"/> </configSections> <log4net> <root> <level value="DEBUG" /> <appender-ref ref="ADONetAppender" /> </root> <appender name="ADONetAppender" type="log4net.Appender.ADONetAppender"> <bufferSize value="100" /> <connectionType value="System.Data.SqlClient.SqlConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" /> <connectionString value="server=servername; uid=Lion; pwd=Lionman; database=databasename" /> <commandText value="INSERT INTO Log ([Date],[Thread],[Level],[Logger],[Message],[Exception]) VALUES (@log_date, @thread, @log_level, @logger, @message, @exception)" />
<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> </appender> </log4net> 

      In the connection string tag you need to change server name and database. You also can decide how to define the security part of the connection string.

There are two way to define the security part

·         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 use integrated security then the connection string should be like as

<connectionString value=”Data Source=servername;initial Catalog=databasename; Integrated Security=True;”/> 

 

 

 

 5.    To use log4net put this as a local class variable:    private static readonly log4net.ILog log =log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

6.    And do this to write messages in the log file.   log.Debug(”this text will be in log file”); 

For Example,

using System;
using System.Collections.Generic;
using System.Text; using log4net;
using log4net.Config; using log4net.Core;
using log4net.Repository.Hierarchy;
using log4net.Appender;

namespace LogPractice
{
    class Program
    {
        private static readonly log4net.ILog log =log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
        static void Main(string[] args)
        {
            log4net.Config.XmlConfigurator.Configure();
            log.Debug("log Debug");
            log.Info("log Info");
            log.Warn("log Warn");
            log.Error("log Error");
            log.Fatal("log Fatal");
        }
    }
}

Now run the code then you can see the log stored in the database table.