log4net not writing logs on the database - .NET 6.0

Question

I'm trying to log my errors/warnings... in my DB using log4net (.NET 6).

I've tried even with different packages (Microknights) but it still won't save anything in my database.

Am I missing something?

When I'm debugging it seems to be hitting the right methods all the right times, with no exceptions raised.

This is the config file

<?xml version="1.0" encoding="utf-8"?>
<configuration>
    <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="ConnectionStringDB" />
            <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="ConnectionStringDB" connectionString="Data Source=mydatabase;Initial Catalog=mycatalog;User Id=user;Password=password;TrustServerCertificate=true"  providerName="System.Data.SqlClient"  />
    </connectionStrings>

</configuration>

And this is what I have in Program.cs:

var builder = WebApplication.CreateBuilder(args);
XmlConfigurator.Configure(new FileInfo("log4net.config"));

// Add services to the container.

builder.Services.AddControllers();

builder.Services.AddMemoryCache();
builder.Services.AddHttpContextAccessor();
builder.Host.ConfigureServices(services =>
{
//...services configuration
.
});

// Log4Net
builder.Host.ConfigureLogging(log =>
{
    log.AddLog4Net("log4net.config");
    log.SetMinimumLevel(LogLevel.Warning);
});

Answer 1

Here are a few things for you to check/fix...

In the database check that you have the correct table schema specified. The older AdoNetAppendeder class documentation shows the following table definition without an Exception column:

CREATE TABLE [dbo].[Log] ( 
    [ID] [int] IDENTITY (1, 1) NOT NULL ,
    [Date] [datetime] NOT NULL ,
    [Thread] [varchar] (255) NOT NULL ,
    [Level] [varchar] (20) NOT NULL ,
    [Logger] [varchar] (255) NOT NULL ,
    [Message] [varchar] (4000) NOT NULL 
) ON [PRIMARY]

To be current (and also using your custom table name) it should instead be:

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
)

Also your configuration is formatted as though it's intended to be in an App.config or Web.config file but your program code is loading it from log4net.config instead. To use a standalone .config file like that you need to have <log4net> as the root element, i.e.:

<?xml version="1.0"?>
<log4net>
    <root>
        <level value="ALL" />
        <appender-ref ref="AdoNetAppender" />
    </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="ConnectionStringDB" />
        <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>
</log4net>

Note that there's no <connectionStrings> element nor any other ASP.NET Core configuration in the above. To work around that you also need to replace the <connectionStringName> element with a <connectionString> element, i.e.:

<!-- ... --->
  <connectionType value="System.Data.SqlClient.SqlConnection, System.Data, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
  <!-- NOPE: <connectionStringName value="ConnectionStringDB" /> -->
  <connectionString value="Data Source=mydatabase;Initial Catalog=mycatalog;User Id=user;Password=password;TrustServerCertificate=true" />
  <commandText value="INSERT INTO dbo.Log4NetLog ([Date],[Thread],[Level],[Logger],[Message],[Exception]) VALUES (@log_date, @thread, @log_level, @logger, @message, @exception)" />
<!-- ... --->