A RetroSearch Logo

Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Search Query:

Showing content from https://github.com/nlog/NLog/wiki/Database-target below:

Database target · NLog/NLog Wiki · GitHub

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

Platforms Supported: All - Requires nuget-package NLog.Database since NLog 5.0

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!

<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.

See Installing targets.

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

<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 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".

<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.

AOT-builds does not support usage of DbProvider-option for doing type-reflection, instead one can create instance of DatabaseTarget with DbConnectionFactory-delegate:

var databaseTarget = new NLog.Targets.DatabaseTarget(() => new Npgsql.NpgsqlConnection());
NLog.LogManager.Setup().LoadConfiguration(cfg => cfg.ForLogger().WriteTo(databaseTarget));

For Database-Parameters then one can use DbTypeEnum-property, and if needing custom DbType, then one can create instance of DatabaseParameterInfo with delegate:

var databaseParameter = new DatabaseParameterInfo("@context",
    "${all-event-properties}",
    (p) => ((NpgsqlParameter)p).NpgsqlDbType = NpgsqlDbType.VarChar);
databaseTarget.Parameters.Add(databaseParameter);

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"

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

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4