How do I configure NLog to write to a database?

Question

I'm trying to get NLog to write to a database, however with my current code it throws an exception when I attempt to debug, the exception is: The type initializer for 'NotifyIcon.Program' threw an exception.

my NLog configuration file code is below, as this seems to be causing the issue as it's the only code I've changed.

<?xml version="1.0" encoding="utf-8" ?>
<nlog xmlns="http://www.nlog-project.org/schemas/NLog.xsd"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" autoReload="true">

  <!-- 
  See http://nlog-project.org/wiki/Configuration_file 
  for information on customizing logging rules and outputs.
   -->
  <targets>
    <!-- add your targets here -->

    <target name="database" xsi:type="Database" />
    <target xsi:type="Database"
          name="String"
          dbUserName="Layout"
          dbProvider="sqlserver"
          useTransactions="false"
          connectionStringName="String"
          connectionString="Data Source=AC-02\SQLEXPRESS;Initial Catalog=master;Integrated Security=True"
          keepConnection="true"
          dbDatabase="Layout"
          dbPassword="Layout"
          dbHost="Layout"
          installConnectionString="Layout"
          commandText="INSERT INTO Logs (Machine_Name, Username, Logon_Time, Screensaver_On, Screensaver_Off, Logoff_Time, Program_Start) Values @MachineName, @Username, @LogonTime, @Screensaver_On, @Screensaver_Off, @LogoffTime, @ProgramStart "/>

  </targets>

  <rules>

    <logger name="*" minlevel="Trace" writeTo="database" />

  </rules>
</nlog>

any and all help would be greatly appreciated =]

I am no expert in this , but on first look it appears that the error which you are getting is unrelated to this file. If you change this file back to what it was, does your code works ?? — abhinav, Jun 19 '13 at 10:16
Yes it does, I have managed to get Nlog to write to a bog-standard text file, However when I change the configuration file to the above it throws an exception, which is rather unhelpful as all it tells me is that an exception has been thrown — Captain_Custard, Jun 19 '13 at 10:21
another debugging tip, if you run your query in command text, on DB standalone, via console, does it work? — abhinav, Jun 19 '13 at 10:23
It doesn't appear to make a difference as the same error appears — Captain_Custard, Jun 19 '13 at 10:24
What's the full stack trace for the error? Also, how about attaching Sql Profiler and checking that the relevant SQL commands really get sent and are correct? — andreister, Jun 19 '13 at 11:47
the call stack doesn't appear contain any information, it just has [External Code] under the 'Name' Category — Captain_Custard, Jun 19 '13 at 12:52

score 16 · Accepted Answer · answered Jun 19 '13 at 19:19

16

You seem to be missing the parameters that are to be inserted.

See the examples at http://justinpdavis.blogspot.com/2010/04/logging-to-database-with-nlog.html

The nLog web page doesn't make it very clear that these are required, but if you squint your eyes and read https://github.com/nlog/NLog/wiki/Database-target you should find that they are required.

answered Jun 19 '13 at 19:19

Brad Bruce

7,638
3
39
60

thanks, this was very helpful with regards to the right syntax in the nlog.config file, I have resolved the issue thankyou very much =] – Captain_Custard Jun 20 '13 at 09:37

score 5 · Answer 2 · answered Jun 22 '19 at 23:20

A simple example,

Config:

<target type="Database" name="database" connectionstring="Server=localhost;Database=NLog;Trusted_Connection=True;">
  <commandText>
    INSERT INTO NLogEntries ([Origin], [Message], [LogLevel],[CreatedOn],[OrderId]) VALUES (@Origin,@Message,@LogLevel,@Date, @OrderId);
  </commandText>
  <parameter name="@Date" layout="${date}" dbType="DbType.Date"/>
  <parameter name="@Origin" layout="${callsite}"/>
  <parameter name="@LogLevel" layout="${level}"/>
  <parameter name="@message" layout="${message}"/>
  <parameter name="@OrderId" layout="${event-properties:MyOrderId}" dbType="DbType.Int32"/> <!-- custom field! Note also the DB Type. Using Logger.WithProperty -->
</target>

Note, NLog 4.6 has also support for DbType - See https://nlog-project.org/2019/03/20/nlog-4-6-is-live.html

score 3 · Answer 3 · edited Oct 14 '16 at 12:43

3

U also wrote 2 targets. And also a lot of attributes that u don't need to set. Should just be:

<target name="DbLog" xsi:type="Database" connectionString="YourConStr" 
        commandText="insert into [blablablabal] (Col1) values (@val1)">
  <parameter name="@val1" layout="${level}" /></target>

Something like this. Easy no? :)

edited Oct 14 '16 at 12:43

Julian

33,915
22
119
174

answered Mar 28 '16 at 06:33

WtFudgE

5,080
7
47
59

score 1 · Answer 4 · answered Jun 19 '13 at 11:36

1

It looks your insert string is not in the right format? You are missing () around the parameters list.

commandText="INSERT INTO Logs (Machine_Name, Username, Logon_Time, Screensaver_On, Screensaver_Off, Logoff_Time, Program_Start) Values (@MachineName, @Username, @LogonTime, @Screensaver_On, @Screensaver_Off, @LogoffTime, @ProgramStart) "

answered Jun 19 '13 at 11:36

automatic

2,727
3
34
31

I corrected my code however the exception is still being thrown, thanks for the help btw =] – Captain_Custard Jun 19 '13 at 12:24

How do I configure NLog to write to a database?

4 Answers4

Linked