Parameters empty (or not supplied to CommandText) when using NLog with ASP.NET Core and SQLite

Question

I've set up NLog in an ASP.NET Core MVC application using the examples in the documentation. Logging to a file (target=file) works without any problems.

However, logging to the Sqlite database results in an exception:

2018-10-30 20:04:41.4394 Error DatabaseTarget(Name=db): Error when writing to database. Exception: System.InvalidOperationException: Must add values for the following parameters: @MachineName, @Logged, @Level, @Message, @Logger, @Callsite, @Exception at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.Data.Sqlite.SqliteCommand.ExecuteNonQuery() at NLog.Targets.DatabaseTarget.WriteEventToDatabase(LogEventInfo logEvent) at NLog.Targets.DatabaseTarget.Write(LogEventInfo logEvent)

Any ideas why the values of these parameters are empty? Or maybe the parameters are not passed at all?

NLog configuration file:

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

  <targets>
    <target xsi:type="File" name="file" fileName="nlog-${shortdate}.log"
            layout="${longdate}|${machinename}|${level:upperCase=true}|${logger}|${callsite}|${message} ${exception:tostring}" />

    <target xsi:type="Database"
            name="db"
            dbProvider="Microsoft.Data.Sqlite.SqliteConnection, Microsoft.Data.Sqlite"
            connectionString="Data Source=database.db;">

      <commandText>
        INSERT INTO Log (MachineName, Logged, Level, Message, Logger, CallSite, Exception)
        VALUES (@MachineName, @Logged, @Level, @Message, @Logger, @Callsite, @Exception);
      </commandText>

      <parameter name="@MachineName" layout="${machinename}" />
      <parameter name="@Logged" layout="${longdate}" />
      <parameter name="@Level" layout="${level:upperCase=true}" />
      <parameter name="@Message" layout="${message}" />
      <parameter name="@Logger" layout="${logger}" />
      <parameter name="@CallSite" layout="${callsite}" />
      <parameter name="@Exception" layout="${exception:tostring}" />
    </target>
  </targets>

  <rules>
    <logger name="*" minlevel="Trace" writeTo="file" />
    <logger name="*" minlevel="Info" writeTo="db" />
  </rules>
</nlog>

Maybe check if command-text-casing, parameter-name-casing and column--name-casing in database is completely identical. Sqlite seems to be case-sensitive. — Rolf Kristensen
@RolfKristensen It doesn't look like a Sqlite issue, but I can confirm that the column names and parameter names match both in writing and casing. — Niels R.

Niels R. Niels R. · Accepted Answer · 2018-11-09T10:42:00

Unable to fix the issue using Microsoft.Data.Sqlite I switched to using System.Data.SQLite (NuGet) instead, which fixed the issue right away.

Remove the Microsoft.Data.Sqlite reference and add a reference System.Data.SQLite (1.0.109.2 at the time of writing) in your .csproj file. Update the dbProvider attribute value in nlog.config to System.Data.SQLite.SQLiteConnection, System.Data.SQLite and you're set.

nlog.config snippet:

<target xsi:type="Database"
            name="db"
            dbProvider="System.Data.SQLite.SQLiteConnection, System.Data.SQLite"
            connectionString="Data Source=database.db;">
   ...
</target>

Parameters empty (or not supplied to CommandText) when using NLog with ASP.NET Core and SQLite

1 Answers