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…".

Log4Net

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

Log4Net

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

Log4Net

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)]

Log4Net

Step 3

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

  1. <configSections>  
  2.     <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net" />  
  3.   </configSections>  
  4.   <log4net>  
  5.     <root>  
  6.       <level value="ALL"></level>  
  7.       <appender-ref ref="AdoNetAppender"></appender-ref>  
  8.     </root>  
  9.       
  10.     <appender name="AdoNetAppender" type="log4net.Appender.AdoNetAppender">  
  11.       <bufferSize value="1" />  
  12.       <connectionType value="System.Data.SqlClient.SqlConnection,   
  13.    System.Data, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />  
  14.      
  15.       <connectionStringName value="ConnectionString1" />  
  16.       <commandText value="INSERT INTO dbo.Log4NetLog ([Date],[Thread],[Level],[Logger],  
  17.     [Message],[Exception]) VALUES (@log_date, @thread, @log_level,   
  18.     @logger, @message, @exception)" />  
  19.       <parameter>  
  20.         <parameterName value="@log_date" />  
  21.         <dbType value="DateTime" />  
  22.         <layout type="log4net.Layout.RawTimeStampLayout" />  
  23.       </parameter>  
  24.       <parameter>  
  25.         <parameterName value="@thread" />  
  26.         <dbType value="String" />  
  27.         <size value="255" />  
  28.         <layout type="log4net.Layout.PatternLayout">  
  29.           <conversionPattern value="%thread" />  
  30.         </layout>  
  31.       </parameter>  
  32.       <parameter>  
  33.         <parameterName value="@log_level" />  
  34.         <dbType value="String" />  
  35.         <size value="50" />  
  36.         <layout type="log4net.Layout.PatternLayout">  
  37.           <conversionPattern value="%level" />  
  38.         </layout>  
  39.       </parameter>  
  40.       <parameter>  
  41.         <parameterName value="@logger" />  
  42.         <dbType value="String" />  
  43.         <size value="255" />  
  44.         <layout type="log4net.Layout.PatternLayout">  
  45.           <conversionPattern value="%logger" />  
  46.         </layout>  
  47.       </parameter>  
  48.       <parameter>  
  49.         <parameterName value="@message" />  
  50.         <dbType value="String" />  
  51.         <size value="4000" />  
  52.         <layout type="log4net.Layout.PatternLayout">  
  53.           <conversionPattern value="%message" />  
  54.         </layout>  
  55.       </parameter>  
  56.       <parameter>  
  57.         <parameterName value="@exception" />  
  58.         <dbType value="String" />  
  59.         <size value="2000" />  
  60.         <layout type="log4net.Layout.ExceptionLayout" />  
  61.       </parameter>  
  62.     </appender>  
  63.     <appender name="RollingLogFileAppender" type="log4net.Appender.RollingFileAppender">  
  64.       <file value="Logs\web-log.txt" />  
  65.       <appendToFile value="true" />  
  66.       <rollingStyle value="Size" />  
  67.       <maxSizeRollBackups value="10" />  
  68.       <maximumFileSize value="50000KB" />  
  69.       <staticLogFileName value="true" />  
  70.       <layout type="log4net.Layout.PatternLayout">  
  71.         <conversionPattern value="%-5p %d %5rms %-22.22c{1} %-18.18M - %m%n" />  
  72.       </layout>  
  73.     </appender>  
  74.   
  75.   </log4net>  
  76. <connectionStrings>  
  77.     <add name="ConnectionString1" connectionString="Data Source=LHRL; Persist Security Info=True; Initial Catalog=AdoNetAssignment;Integrated Security=True"  providerName="System.Data.SqlClient"  />  
  78.   </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.

Log4Net

Table Script

  1. CREATE TABLE [dbo].[Log4NetLog](  
  2.     [Id] [int] IDENTITY(1,1) NOT NULL,  
  3.     [Date] [datetime] NOT NULL,  
  4.     [Thread] [varchar](255) NOT NULL,  
  5.     [Level] [varchar](50) NOT NULL,  
  6.     [Logger] [varchar](255) NOT NULL,  
  7.     [Message] [varchar](4000) NOT NULL,  
  8.     [Exception] [varchar](2000) NULL,  
  9.  CONSTRAINT [PK_Log4NetLog] PRIMARY KEY CLUSTERED   
  10. (  
  11.     [Id] ASC  
  12. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]  
  13. ON [PRIMARY

Step 5

Use Log4Net logger in class.

Add directive

  1. using log4net;  

 

Log4Net
Initialize logger in the class by using the following code.

  1. 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.

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

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

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

Output in table

Log4Net

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