AdoNetAppender

The AdoNetAppender is used to log events directly to a database table. It writes log events in batches (with a default size of 100, controlled by the BufferSize setting).

The configuration of AdoNetAppender depends on the database provider you’re using. Here are the key configuration elements:

  • ConnectionType: Specifies the fully qualified type name for the System.Data.IDbConnection used to connect to the database.

  • ConnectionString: The connection string that is specific to the database provider (e.g., SQL Server, MySQL).

  • CommandText: Defines the SQL command to execute. This can either be a prepared statement or a stored procedure. In the examples below, a prepared statement is used.

Each parameter in the prepared statement or stored procedure is defined with:

  • Name: The name of the parameter.

  • DbType: The database type for the parameter.

  • Layout: The layout used to render the value for the parameter.

The following examples illustrate how to configure the AdoNetAppender for different databases.

MS SQL Server

The database table definition is:

create table dbo.Log
(
  Id bigint identity (1, 1) not null,
  LogDate datetime not null,
  Thread nvarchar(255) not null,
  LogLevel nvarchar(50) not null,
  Logger nvarchar(255) not null,
  LogMessage nvarchar(2000) not null,
  Exception nvarchar(2000) null,
  constraint Log_PKEY primary key (Id)
) with (data_compression = page)

Please add a package reference for the SqlClient to your .csproj file

  <PackageReference Include="Microsoft.Data.SqlClient" Version="*" />

The appender configuration is:

<appender name="AdoNetAppender" type="log4net.Appender.AdoNetAppender">
  <bufferSize value="100"/>
	<connectionType value="Microsoft.Data.SqlClient.SqlConnection, Microsoft.Data.SqlClient"/>
  <connectionString value="data source=[database server];initial catalog=[database name];integrated security=false;persist security info=True;User ID=[user];Password=[password]"/>
  <commandText value="insert into dbo.Log (LogDate, Thread, LogLevel, Logger, LogMessage, Exception) values (@LogDate, @Thread, @LogLevel, @Logger, @LogMessage, @Exception)"/>
  <parameter>
    <parameterName value="@LogDate"/>
    <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="@LogLevel"/>
    <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="@LogMessage"/>
    <dbType value="String"/>
    <size value="2000"/>
    <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>

Oracle

Please add a package reference for the SqlClient to your .csproj file

  <PackageReference Include="Oracle.ManagedDataAccess.Core" Version="*" />

The database table definition is:

create table Log
(
  Id number(10) generated by default as identity,
  LogDate date not null,
  Thread varchar2(255) not null,
  LogLevel varchar2(50) not null,
  Logger varchar2(255) not null,
  LogMessage varchar2(2000) not null,
  Exception varchar2(2000) null,
  constraint Log_PKEY primary key (Id)
)

The appender configuration is:

<appender name="AdoNetAppender" type="log4net.Appender.AdoNetAppender">
  <bufferSize value="100"/>
	<connectionType value="Oracle.ManagedDataAccess.Client.OracleConnection, Oracle.ManagedDataAccess.Client"/>
  <connectionString value="data source=[mydatabase];User ID=[user];Password=[password]"/>
  <commandText value="insert into dbo.Log (LogDate, Thread, LogLevel, Logger, LogMessage, Exception) values (@LogDate, @Thread, @LogLevel, @Logger, @LogMessage, @Exception)"/>
  <parameter>
    <parameterName value="@LogDate"/>
    <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="@LogLevel"/>
    <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="@LogMessage"/>
    <dbType value="String"/>
    <size value="2000"/>
    <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>

PostgreSQL

The database table definition is:

create table Log
(
  Id bigint generated by default as identity,
  LogDate date not null,
  Thread varchar(255) not null,
  LogLevel varchar(50) not null,
  Logger varchar(255) not null,
  LogMessage varchar(2000) not null,
  Exception varchar(2000) null,
  constraint Log_PKEY primary key (Id)
)

Please add a package reference for the SqlClient to your .csproj file

  <PackageReference Include="Npgsql" Version="*" />

The appender configuration is:

<appender name="AdoNetAppender" type="log4net.Appender.AdoNetAppender">
  <bufferSize value="100"/>
	<connectionType value="Npgsql.NpgsqlConnection, Npgsql"/>
  <connectionString value="data source=[database server];initial catalog=[database name];User ID=[user];Password=[password]"/>
  <commandText value="insert into Log (LogDate, Thread, LogLevel, Logger, LogMessage, Exception) values (@LogDate, @Thread, @LogLevel, @Logger, @LogMessage, @Exception)"/>
  <parameter>
    <parameterName value="@LogDate"/>
    <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="@LogLevel"/>
    <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="@LogMessage"/>
    <dbType value="String"/>
    <size value="2000"/>
    <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>

Other databases

To adapt the provided examples for use with other databases, follow these steps:

  • Find a NuGet package that provides the necessary ADO.NET provider for your database.

  • Modify the CREATE TABLE script to match the data types used by your database.

  • Update the ConnectionType property in the AdoNetAppender configuration to reference the connection type from the selected NuGet package.