Database target

Writes log messages to the database using an ADO.NET provider. The database operation is always executed outside of a transaction.

Platforms Supported: All (Since NLog 5.0 then nuget-package NLog.Database is required)

You can use this target with various DB providers, e.g. System.Data, Microsoft.Data, Oracle, MySql, SqlLite etc. see Providers examples

ℹ️ Don't forget to install the needed nuget-package for your DB provider!

Configuration Syntax

<targets>
  <target xsi:type="Database"
          name="String"
          dbProvider="String"
          connectionString="Layout"
          connectionStringName="String"
          keepConnection="Boolean"
          isolationLevel="System.Data.IsolationLevel"
          dbDatabase="Layout"
          dbUserName="Layout"
          dbPassword="Layout"
          dbHost="Layout"
          commandType="Enum"
          commandText="Layout"
          installConnectionString="Layout">
    <install-command commandType="Enum" 
                     connectionString="Layout" 
                     ignoreFailures="Boolean"
                     text="Layout"/><!-- repeated -->
    <uninstall-command commandType="Enum" 
                       connectionString="Layout" 
                       ignoreFailures="Boolean"
                       text="Layout"/><!-- repeated -->
    <parameter name="String" 
              layout="Layout"
              precision="Byte" 
              scale="Byte" 
              size="Integer"
              dbType="DbType" 
              allowDbNull="Boolean"
              format="string" 
              parameterType="Type" /> <!-- repeated -->
  </target>
</targets>

Read more about using the Configuration File.

Parameters

General Options

name - Name of the target.

Connection Options

dbProvider - Name of the database provider. Required. Default: sqlserver Following values are recognized by default:
- sqlserver, mssql, microsoft or msde - Resolves to System.Data.SqlClient Data Provider
- odbc - ODBC Data Provider (Not recognized on NetCore)
- oledb - OLEDB Data Provider (Not recognized on NetCore)
Note for .NET Core one should install the Nuget-package for the DbProvider (Ex. System.Data.SqlClient), and instead use the fully qualified name of the provider connection type (class implementing IDbConnection). See also DbProvider Examples

Note for .NET Framework then one can also use the invariant name as registered in machine.config or app.config. Common values are (Not supported by NET Core):
- System.Data.SqlClient -
- System.Data.OracleClient - (deprecated in .NET Framework 4)
- Oracle.DataAccess.Client -
- System.Data.SQLite -
- Npgsql -
- MySql.Data.MySqlClient
If you get the following error in Internal-Logging then you might have to use the fully qualified name.

Error during initialization of DatabaseTarget(Name=db). Could not load type '<Name Of DbProvider>' from assembly
connectionString - Connection string. When provided, it overrides the values specified in DBHost, DBUserName, DBPassword, DBDatabase and DBProvider. Layout
connectionStringName - Name of the connection string to lookup in app.config. The ProviderName of the connectionstring will be used to determine the SQL type. Since NLog 4.3 this ProviderName attribute isn't required anymore and the dbProvider will be used as fallback.

Not supported on NetCore as app.config has been replaced with appsettings.json. Use ${configsetting} in connectionString instead
keepConnection - Indicates whether to keep the database connection open between the log events. Boolean Default: false
isolationLevel - Activates transactions when doing batching. This will improve performance as it will write multiple rows in a single transaction. Recommended to use ReadCommitted to enable. Consider adding ;ENLIST=FALSE to ConnectionString to disable auto-enlist. Default: Not set

Introduced with NLog 4.7
dbDatabase - Database name. If the ConnectionString is not provided this value will be used to construct the "Database=" part of the connection string. Layout
dbUserName - Database user name. If the ConnectionString is not provided this value will be used to construct the "User ID=" part of the connection string. Layout
dbPassword - Database password. If the ConnectionString is not provided this value will be used to construct the "Password=" part of the connection string. Layout
dbHost - Database host name. If the ConnectionString is not provided this value will be used to construct the "Server=" part of the connection string. Layout
useTransactions - This option was removed in NLog 4.0 because the logging code always runs outside of transaction. This ensures that the log gets written to the database if you rollback the main transaction because of an error and want to log the error.

SQL Statement

commandType - Type of the command. Required. Default: text
Possible values:
- StoredProcedure - The commandText is the stored procedure name.
- TableDirect -
- Text - regular query
commandText - Text of the SQL command to be run on each log level. Layout Required.
Typically this is a SQL INSERT statement or a stored procedure call. It should use the database-specific parameters (marked as @parameter for SQL server or :parameter for Oracle, other data providers have their own notation) and not the layout renderers, because the latter is prone to SQL injection attacks. The layout renderers should be specified as <parameter /> elements instead.
parameters - The collection of parameters. Each parameter contains a mapping between NLog layout and a database named or positional parameter. Collection Each collection item is represented by <parameter /> element with the following attributes:
- layout - Layout that should be use to calcuate the value for the parameter. Layout Required. Note: there is also ${db-null}
- name - Database parameter name. Required.
- precision - Database parameter precision. Byte Default: 0
- scale - Database parameter scale. Byte Default: 0
- size - Database parameter size. Integer Default: 0
- dbType - One of the values of DbType (e.g. "Int32", "Decimal", "DateTime"), or a value of DBType like prefixed with the property name, e.g. "SqlDbType.NChar" will set the property "SqlDbType" on "NChar". Another example: "NpgsqlDbType.Json" with NpgsqlParameter. Introduced in NLog 4.6. Since NLog 4.7 not case-sensitive any more
- parameterType - Optional parametertype (System.Type), will be recognized from the dbType. When using special DbType like SqlDbType.SmallInt then one can specify parameterType="System.Int16" to help - This will skip the overhead of first rendering to string, and parsing the string into the expected type. Introduced in NLog 4.6
- format - format for parsing the value when not using the rawValue, of if the rawValue conversion to has been failed. Introduced in NLog 4.6
- culture - culture for parsing the value, analogous as "format". Introduced in NLog 4.6
- allowDbNull - empty value will be translate into DbNull. Requires database column to allow NULL values. Introduced in NLog 4.7.4. Empty values are null for nullable types and null and '' for strings.

Installation Options

See Installing targets.

installConnectionString - Connection string using for installation and uninstallation. If not provided, regular ConnectionString is being used. Layout
InstallDdlCommands - The installation DDL commands. Collection . Each collection item is represented by <install-command /> element with the following attributes:
- commandType - Type of the command. Required. Default: text
  Possible values:
  - StoredProcedure - The command-text is the stored procedure name.
  - TableDirect -
  - Text - regular query
- Text - The command-text (Or stored procedure name)
- parameters - The collection of parameters. Each parameter contains a mapping between NLog layout and a database named or positional parameter. Collection
  Each collection item is represented by element with the following attributes:
  - layout - Layout that should be use to calcuate the value for the parameter. Layout Required.
  - name - Database parameter name. Required.
  - precision - Database parameter precision. Byte Default: 0
  - scale - Database parameter scale. Byte Default: 0
  - size - Database parameter size. Integer Default: 0
  - text - Command text. Layout Required.
- connectionString - Connection string to run the command against. If not provided, connection string from the target is used. Layout
- ignoreFailures - Indicates whether to ignore failures. Boolean
uninstallDdlCommands - The uninstallation DDL commands. Collection
Each collection item is represented by <uninstall-command /> element with the following attributes:
- commandType - Type of the command. Required. Default: text
  Possible values:
  - StoredProcedure - The command-text is the stored procedure name.
  - TableDirect -
  - Text - regular query
- Text - The command-text (Or stored procedure name)
- parameters - The collection of parameters. Each parameter contains a mapping between NLog layout and a database named or positional parameter. Collection
  Each collection item is represented by <parameter /> element with the following attributes:
  - layout - Layout that should be use to calcuate the value for the parameter. Layout Required.
  - name - Database parameter name. Required.
  - precision - Database parameter precision. Byte Default: 0
  - scale - Database parameter scale. Byte Default: 0
  - size - Database parameter size. Integer Default: 0
  - text - Command text. Layout Required.
- connectionString - Connection string to run the command against. If not provided, connection string from the target is used. Layout
- ignoreFailures - Indicates whether to ignore failures. Boolean

Command and connection properties

Introduced in NLog 4.7, you could add properties to the connection (e.g. SqlConnection, OracleConnection) and the Command (e.g. SqlCommand, OracleCommand). this could be useful for the Azure Access Token

Examples

<commandProperty name="CommandTimeout" layout="${gdc:DefaultCommandTimeout}" propertyType="System.Int32" />
<connectionProperty name="AccessToken" layout="${gdc:AccessToken}" propertyType="System.String" />

Full example:

<target name="db"
        xsi:type="Database"
        commandType="StoredProcedure"
        commandText="[dbo].[NLog_AddEntry_p]"
        >
  <commandProperty name="CommandTimeout" layout="${gdc:DefaultCommandTimeout}" propertyType="System.Int32" />
  <connectionProperty name="AccessToken" layout="${gdc:AccessToken}" propertyType="System.String" /> 
  <parameter name="@logged"         layout="${date}" />
  <parameter name="@level"          layout="${level}" />
  <parameter name="@message"        layout="${message}" />
  <parameter name="@logger"         layout="${logger}" />
</target>

About DbType and IRawValue

The DbType works as follows

NLog tries first if the Layout Renderer support IRawValue to get the non-string type. Then it will use the IPropertyTypeConverter for converting to the desired dbType.
If that fails, NLog will render the layout render to string and then parse it (with usage of the optional format option). Then it will also use the IPropertyTypeConverter for converting to the desired dbType.
NoRawValue layout renderer could be use for forcing to use the string value.

Example Configurations

NLog and SQL Server Example Configuration

<target name="database" xsi:type="Database">
  <connectionString>server=localhost;Database=*****;user id=****;password=*****</connectionString>

  <!--
  Script for creating the dbo.Log table.
      
  SET ANSI_NULLS ON
  SET QUOTED_IDENTIFIER ON
  CREATE TABLE [dbo].[Log] (
	  [Id] [int] IDENTITY(1,1) NOT NULL,
	  [MachineName] [nvarchar](50) NOT NULL,
	  [Logged] [datetime] NOT NULL,
	  [Level] [nvarchar](50) NOT NULL,
	  [Message] [nvarchar](max) NOT NULL,
	  [Logger] [nvarchar](250) NULL,
	  [Exception] [nvarchar](max) NULL,
    CONSTRAINT [PK_dbo.Log] 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]
  -->
      
  <commandText>
    insert into dbo.Log (
      MachineName, Logged, Level, Message,
      Logger, Exception
    ) values (
      @MachineName, @Logged, @Level, @Message,
      @Logger, @Exception
    );
  </commandText>

  <parameter name="@MachineName" layout="${machinename}" />
  <parameter name="@Logged" layout="${date}" />
  <parameter name="@Level" layout="${level}" />
  <parameter name="@Message" layout="${message}" />
  <parameter name="@Logger" layout="${logger}" />
  <parameter name="@Exception" layout="${exception:tostring}" />
</target>

NLog and SQL Server using a stored procedure

This approach keeps the NLog.config file simpler, and helps confine database logic to the database.

NLog target configuration

<target name="db"
        xsi:type="Database"
        connectionString="server=localhost;Database=*****;user id=****;password=*****"
        commandType="StoredProcedure"
        commandText="[dbo].[NLog_AddEntry_p]"
        >
  <parameter name="@machineName"    layout="${machinename}" />
  <parameter name="@logged"         layout="${date}" />
  <parameter name="@level"          layout="${level}" />
  <parameter name="@message"        layout="${message}" />
  <parameter name="@logger"         layout="${logger}" />
  <parameter name="@properties"     layout="${all-event-properties:separator=|}" />
  <parameter name="@exception"      layout="${exception:tostring}" />
</target>

SQL scripts to set up the database objects

Remember to grant permissions on the database objects so that the website can execute the stored procedure.

CREATE TABLE [dbo].[NLog] (
   [ID] [int] IDENTITY(1,1) NOT NULL,
   [MachineName] [nvarchar](200) NULL,
   [Logged] [datetime] NOT NULL,
   [Level] [varchar](5) NOT NULL,
   [Message] [nvarchar](max) NOT NULL,
   [Logger] [nvarchar](300) NULL,
   [Properties] [nvarchar](max) NULL,
   [Exception] [nvarchar](max) NULL,
 CONSTRAINT [PK_dbo.Log] 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] TEXTIMAGE_ON [PRIMARY];

GO

CREATE PROCEDURE [dbo].[NLog_AddEntry_p] (
  @machineName nvarchar(200),
  @logged datetime,
  @level varchar(5),
  @message nvarchar(max),
  @logger nvarchar(300),
  @properties nvarchar(max),
  @exception nvarchar(max)
) AS
BEGIN
  INSERT INTO [dbo].[NLog] (
    [MachineName],
    [Logged],
    [Level],
    [Message],
    [Logger],
    [Properties],
    [Exception]
  ) VALUES (
    @machineName,
    @logged,
    @level,
    @message,
    @logger,
    @properties,
    @exception
  );
END

Example: simple logs to database target

Logger call:

logger.Info("my info message");

config:

<target name="apiUsageLog" xsi:type="Database" connectionStringName="connStringName">
    <commandText>
        INSERT INTO Table
        (message, machinename)
        VALUES
        (@message, @machinenameParam)
    </commandText>
    <parameter name="@messageParam" layout="${message}" /> <!-- this will be "my info message"-->
    <parameter name="@machinenameParam" layout="${machinename}" /> <!-- defined in NLog, see https://nlog-project.org/config/?tab=layout-renderers-->
    </target>
</targets>

This will create a log record in the database with "my info message" and the machine name.

Example: log with custom properties:

Structured logging is used here. See structured logging

Logger call:

logger.Info("my info message with {Property1}", "value1");

config:

<target name="apiUsageLog" xsi:type="Database" connectionStringName="connStringName">
    <commandText>
        INSERT INTO Table
        (message, machinename, property1)
        VALUES
        (@message, @machinenameParam, @propertyParam1)
    </commandText>
    <parameter name="@messageParam" layout="${message}" /> <!-- this will be: my info message with "Value1" -->
    <parameter name="@machinenameParam" layout="${machinename}" /> <!-- defined in NLog, see https://nlog-project.org/config/?tab=layout-renderers-->
    <parameter name="@propertyParam1" layout="${event-properties:Property1}" /> <!-- this will be "value1" -->
    </target>
</targets>

This will create a log record in the database with "my info message" , the machine name and the custom property with "value1".

Example `allowDbNull`:

<parameter name="@activityid" layout="${activityid}" dbType="DbType.Guid" allowDbNull="true" />

Example: log with custom properties, not all in the message

This combines structured logging and WithProperty. You need at least NLog 4.6.3 for this.

Logger call:

logger.WithProperty("Property2", "value2")
      .Info("my info message {Property1}", "value1");

config:

<target name="apiUsageLog" xsi:type="Database" connectionStringName="connStringName">
    <commandText>
        INSERT INTO Table
        (message, machinename, property1, property2)
        VALUES
        (@message, @machinenameParam, @propertyParam2)
    </commandText>
    <parameter name="@messageParam" layout="${message}" /> <!-- this will be: my info message with "value1"-->
    <parameter name="@machinenameParam" layout="${machinename}" /> <!-- defined in NLog, see https://nlog-project.org/config/?tab=layout-renderers-->
    <parameter name="@propertyParam1" layout="${event-properties:Property1}" /> <!-- this will be "value1" -->
    <parameter name="@propertyParam2" layout="${event-properties:Property2}" /> <!-- this will be "value2" -->
    </target>
</targets>

This will create a log record in the database with my info message with "Value1" , the machine name and the custom properties "value1" and "value2"

Note, now "value1" is in the message and "value2" isn't.

DbProvider Examples

MySql and .NET Core

Install package: https://www.nuget.org/packages/MySql.Data/

dbProvider="MySql.Data.MySqlClient.MySqlConnection, MySql.Data"

or if using packages: https://www.nuget.org/packages/MySqlConnector/ and https://www.nuget.org/packages/MySqlConnector.Logging.NLog/

dbProvider="MySqlConnector.MySqlConnection, MySqlConnector"

System.Data.SQLite and .NET Core

Install package: https://www.nuget.org/packages/System.Data.SQLite

dbProvider="System.Data.SQLite.SQLiteConnection, System.Data.SQLite"

Microsoft.Data.SqlClient and .NET Core

Install package: https://www.nuget.org/packages/Microsoft.Data.SqlClient/

dbProvider="Microsoft.Data.SqlClient.SqlConnection, Microsoft.Data.SqlClient"

Microsoft.Data.Sqlite and .NET Core

Install package: https://www.nuget.org/packages/Microsoft.Data.SQLite/

dbProvider="Microsoft.Data.Sqlite.SqliteConnection, Microsoft.Data.Sqlite"

Npgsql and .NET core

Install package: https://www.nuget.org/packages/Npgsql/

dbProvider="Npgsql.NpgsqlConnection, Npgsql"

Oracle.ManagedDataAccess and .NET

dbProvider="Oracle.ManagedDataAccess.Client.OracleConnection, Oracle.ManagedDataAccess"

Mono.Data.Sqlite and .NET

dbProvider="Mono.Data.Sqlite.SqliteConnection, Mono.Data.Sqlite"

Microsoft Access

    <target xsi:type="Database"
            name="MdbLog"
            dbProvider="oledb"
            connectionString="Provider=Microsoft.JET.OLEDB.4.0;Data Source=C:\path\to\my.mdb">
    ...

- Troubleshooting Guide - See available NLog Targets and Layouts: https://nlog-project.org/config

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Database target

Configuration Syntax

Parameters

General Options

Connection Options

SQL Statement

Installation Options

Command and connection properties

Examples

About DbType and IRawValue

Example Configurations

NLog and SQL Server Example Configuration

NLog and SQL Server using a stored procedure

NLog target configuration

SQL scripts to set up the database objects

Example: simple logs to database target

Example: log with custom properties:

Example `allowDbNull`:

Example: log with custom properties, not all in the message

DbProvider Examples

MySql and .NET Core

System.Data.SQLite and .NET Core

Microsoft.Data.SqlClient and .NET Core

Microsoft.Data.Sqlite and .NET Core

Npgsql and .NET core

Oracle.ManagedDataAccess and .NET

Mono.Data.Sqlite and .NET

Microsoft Access

Home

Configuration

Programmatic Configuration

Advanced

Extending NLog

Clone this wiki locally

Database target

Configuration Syntax

Parameters

General Options

Connection Options

SQL Statement

Installation Options

Command and connection properties

Examples

About DbType and IRawValue

Example Configurations

NLog and SQL Server Example Configuration

NLog and SQL Server using a stored procedure

NLog target configuration

SQL scripts to set up the database objects

Example: simple logs to database target

Example: log with custom properties:

Example allowDbNull:

Example: log with custom properties, not all in the message

DbProvider Examples

MySql and .NET Core

System.Data.SQLite and .NET Core

Microsoft.Data.SqlClient and .NET Core

Microsoft.Data.Sqlite and .NET Core

Npgsql and .NET core

Oracle.ManagedDataAccess and .NET

Mono.Data.Sqlite and .NET

Microsoft Access

Home

Configuration

Programmatic Configuration

Advanced

Extending NLog

Clone this wiki locally

Example `allowDbNull`: