Configuring Log4Net With Database - A Tutorial For Beginners

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

<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 the 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,  
      NOT NULL,  
      NOT NULL,  
      NOT NULL,  
      NOT NULL,  
      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 the 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 the 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.