NLog - Documentation

Last updated: 2006-09-18

This website is based on NLog v1.0. Click here to view the documentation for other versions.

Database Target

Writes logging messages to the database using an ADO.NET provider.

Assembly	Class	.NET Framework			.NET CF		Mono on Windows		Mono on Unix
Assembly	Class	1.0	1.1	2.0	1.0	2.0	1.0	2.0	1.0	2.0
NLog.dll	NLog.Targets.DatabaseTarget

Parameters (blue fields are required):

Name Type Description

commandText

string ${}

The text of the SQL command to be run on each log level.

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 <parameters />> instead.

dbProvider

string

The name of the database provider. It can be: sqlserver, mssql, microsoft, msde (all for MSSQL database), oledb, odbc or other name in which case it's treated as a fully qualified type name of the data provider *Connection class.

Default value is: sqlserver.

name

string

The name of the target.

connectionString

string ${}

The connection string. When provided, it overrides the values specified in DBHost, DBUserName, DBPassword, DBDatabase.

dbDatabase

string ${}

The database name. If the ConnectionString is not provided this value will be used to construct the "Database=" part of the connection string.

dbHost

string ${}

The database host name. If the ConnectionString is not provided this value will be used to construct the "Server=" part of the connection string.

dbPassword

string ${}

The database password. If the ConnectionString is not provided this value will be used to construct the "Password=" part of the connection string.

dbuserName

string ${}

The database user name. If the ConnectionString is not provided this value will be used to construct the "User ID=" part of the connection string.

keepConnection

boolean

Keep the database connection open between the log events.

Default value is: True.

useTransactions

boolean

Use database transactions. Some data providers require this.

Default value is: False.

Parameters Collection of DatabaseParameterInfo. Each element is represented as <parameter/>

Name Type Description

name

string

Database parameter name.

layout

string ${}

The layout that should be use to calcuate the value for the parameter.

size

integer

Database parameter size.

Default value is: 0.

precision

Byte

Database parameter precision.

Default value is: 0.

scale

Byte

Database parameter scale.

Default value is: 0.

Example:

The configuration is dependent on the database type, because there are differnet methods of specifying connection string, SQL command and command parameters. MS SQL Server using System.Data.SqlClient:

<nlog autoReload="true"> 
  <targets> 
    <target name="database" type="Database"> 
      <dbprovider> 
        mssql 
      </dbprovider> 
      <!-- database connection parameters --> 
      <!-- alternatively you could provide a single 'connectionstring' parameter --> 
      <dbhost> 
        . 
      </dbhost> 
      <dbdatabase> 
        NLogDatabase 
      </dbdatabase> 
      <dbusername> 
        nloguser 
      </dbusername> 
      <dbpassword> 
        nlogpassword 
      </dbpassword> 
      <commandText> 
        insert into LogTable(time_stamp,level,logger,message) values(@time_stamp, @level, @logger, @message); 
      </commandText> 
      <parameter name="@time_stamp" layout="${date}"/> 
      <parameter name="@level" layout="${level}"/> 
      <parameter name="@logger" layout="${logger}"/> 
      <parameter name="@message" layout="${message}"/> 
    </target> 
  </targets> 
  <rules> 
    <logger name="*" minlevel="Debug" appendTo="database"/> 
  </rules> 
</nlog>

Oracle using System.Data.OracleClient:

<nlog xmlns="http://www.nlog-project.org/schemas/NLog.xsd" 
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> 
  <!-- configuration contributed by David Maly --> 
  <targets> 
    <target name="database" xsi:type="Database" keepConnection="false" 
            useTransactions="true" 
            dbProvider="System.Data.OracleClient.OracleConnection,System.Data.OracleClient, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" 
            connectionString="Data Source=MYORACLEDB;User Id=DBO;Password=MYPASSWORD;Integrated Security=no;" 
            commandText="insert into LOGTABLE( TIME_STAMP,LOGLEVEL,LOGGER,CALLSITE,MESSAGE) values( :TIME_STAMP,:LOGLEVEL,:LOGGER,:CALLSITE,:MESSAGE)"> 
      <parameter name="TIME_STAMP" layout="${longdate}"/> 
      <parameter name="LOGLEVEL" layout="${level:uppercase=true}"/> 
      <parameter name="LOGGER" layout="${logger}"/> 
      <parameter name="CALLSITE" layout="${callsite:filename=true}"/> 
      <parameter name="MESSAGE" layout="${message}"/> 
    </target> 
  </targets> 
  <rules> 
    <logger name="*" minlevel="Debug" writeTo="database"/> 
  </rules> 
</nlog>

Oracle using System.Data.OleDbClient:

<nlog xmlns="http://www.nlog-project.org/schemas/NLog.xsd" 
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> 
  <!-- configuration contributed by David Maly --> 
  <targets> 
    <target name="database" xsi:type="Database" keepConnection="false" 
            useTransactions="true" dbProvider="oledb" 
            connectionString="Provider=msdaora;Data Source=MYORACLEDB;User Id=DBO;Password=MYPASSWORD;" 
            commandText="insert into LOGTABLE( TIME_STAMP,LOGLEVEL,LOGGER,CALLSITE,MESSAGE) values(?,?,?,?,?)"> 
      <parameter name="TIME_STAMP" layout="${longdate}"/> 
      <parameter name="LOGLEVEL" layout="${level:uppercase=true}"/> 
      <parameter name="LOGGER" layout="${logger}"/> 
      <parameter name="CALLSITE" layout="${callsite:filename=true}"/> 
      <parameter name="MESSAGE" layout="${message}"/> 
    </target> 
  </targets> 
  <rules> 
    <logger name="*" minlevel="Debug" writeTo="database"/> 
  </rules> 
</nlog>

To set up the log target programmatically use code like this (an equivalent of MSSQL configuration):

using NLog; 
using NLog.Targets; 
 
class Example 
{ 
    static void Main(string[] args) 
    { 
        DatabaseTarget target = new DatabaseTarget(); 
        DatabaseParameterInfo param; 
         
        target.DBProvider = "mssql"; 
        target.DBHost = "."; 
        target.DBUserName = "nloguser"; 
        target.DBPassword = "pass"; 
        target.DBDatabase = "databasename"; 
        target.CommandText = "insert into LogTable(time_stamp,level,logger,message) values(@time_stamp, @level, @logger, @message);"; 
 
        param = new DatabaseParameterInfo(); 
        param.Name = "@time_stamp"; 
        param.Layout = "${date}"; 
        target.Parameters.Add(param); 
         
        param = new DatabaseParameterInfo(); 
        param.Name = "@level"; 
        param.Layout = "${level}"; 
        target.Parameters.Add(param); 
         
        param = new DatabaseParameterInfo(); 
        param.Name = "@logger"; 
        param.Layout = "${logger}"; 
        target.Parameters.Add(param); 
         
        param = new DatabaseParameterInfo(); 
        param.Name = "@message"; 
        param.Layout = "${message}"; 
        target.Parameters.Add(param); 
 
        NLog.Config.SimpleConfigurator.ConfigureForTargetLogging(target, LogLevel.Debug); 
 
        Logger logger = LogManager.GetLogger("Example"); 
        logger.Debug("log message"); 
    } 
}

Back to the target list.