////
    Licensed to the Apache Software Foundation (ASF) under one or more
    contributor license agreements.  See the NOTICE file distributed with
    this work for additional information regarding copyright ownership.
    The ASF licenses this file to You under the Apache License, Version 2.0
    (the "License"); you may not use this file except in compliance with
    the License.  You may obtain a copy of the License at

         http://www.apache.org/licenses/LICENSE-2.0

    Unless required by applicable law or agreed to in writing, software
    distributed under the License is distributed on an "AS IS" BASIS,
    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    See the License for the specific language governing permissions and
    limitations under the License.
////

[#adonetappender]
= 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.

[#mssqlserver]
== MS SQL Server

The database table definition is:

[source,sql]
----
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
[source,xml]
----
  <PackageReference Include="Microsoft.Data.SqlClient" Version="*" />
----

The appender configuration is:

[source,xml]
----
<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]
== Oracle

Please add a package reference for the SqlClient to your .csproj file
[source,xml]
----
  <PackageReference Include="Oracle.ManagedDataAccess.Core" Version="*" />
----

The database table definition is:

[source,sql]
----
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:

[source,xml]
----
<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]
== PostgreSQL

The database table definition is:

[source,sql]
----
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
[source,xml]
----
  <PackageReference Include="Npgsql" Version="*" />
----

The appender configuration is:

[source,xml]
----
<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]
== 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.
