Configuring Log4Net With Database - A Tutorial For Beginners

Log4Net

Log4Net is a framework for logging .NET applications. It is an open source library that can be used to log the output for different targets, like logging output in a text file, Database, Console, Mail (SMTP), Remote, and others. In this article, I will explain how to log output in a database.

Logging Levels

Log4Net has seven logging levels, five of which we can use in our code.

• OFF (nothing gets logged) cannot be called
• FATAL
• ERROR
• WARN
• INFO
• DEBUG
• ALL (everything gets logged) can be called

Configure Log4Net with database

Step 1

Download the Log4Net library. Right-click on the project and select "Manage NuGet Packages…".



Write Log4Net in the search bar and browse for the right library. Then, click "Install" button.

After installation, it will be added to References folder, like in the below screenshot.

Now, our library is ready to use in code.

Step 2

Add the following piece of code in Properties -> AssemblyInfo.cs.

[assembly: log4net.Config.XmlConfigurator(ConfigFile = "Web.config", Watch = true)]

Step 3

Write the below code in Web.config file under <configuration> tag.

<configSections>
    <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net" />
  </configSections>
  <log4net>
    <root>
      <level value="ALL"></level>
      <appender-ref ref="AdoNetAppender"></appender-ref>
    </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" />

      <connectionStringName value="ConnectionString1" />
      <commandText value="INSERT INTO dbo.Log4NetLog ([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>
    <appender name="RollingLogFileAppender" type="log4net.Appender.RollingFileAppender">
      <file value="Logs\web-log.txt" />
      <appendToFile value="true" />
      <rollingStyle value="Size" />
      <maxSizeRollBackups value="10" />
      <maximumFileSize value="50000KB" />
      <staticLogFileName value="true" />
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%-5p %d %5rms %-22.22c{1} %-18.18M - %m%n" />
      </layout>
    </appender>

  </log4net>
<connectionStrings>
    <add name="ConnectionString1" connectionString="Data Source=LHRL; Persist Security Info=True; Initial Catalog=AdoNetAssignment;Integrated Security=True"  providerName="System.Data.SqlClient"  />
  </connectionStrings>

My database name is AdoNetAssignment and the machine name is LHRL. Replace the database name and machine name with your database and machine names respectively or you can also write localhost in place of machine name.

Step 4

In SQL Server, make a table with a name ‘Log4NetLog’ to log the output in this table.

The table design is shown below.

Table Script

CREATE TABLE [dbo].[Log4NetLog](
    [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,
 CONSTRAINT [PK_Log4NetLog] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Step 5

Use Log4Net logger in class.

Add directive

using log4net;

 

Initialize logger in the class by using the following code.

private static readonly ILog Logger=LogManager.GetLogger(System.Environment.MachineName);

Step 6

Call the logger functions in the class where you want to log the output.

Logger.Info(“Testing information log”);
Logger.Debug(“Testing Debug log”);
Logger.Fatal(“Testing Fatal log”);

If you want to log Exception, then use Error function.

Logger.Error(exception); // exception is of type Exception

Output in table

In my case, no error occurred. If an error occurs, then it will go to the Exception column.