CODEWITHSUNDEEP
asp.net-coreasp.net-core-mvcnlogasp.net-core-2.1system.data.sqlite
Niels R.
Niels R.

Reputation: 7364

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

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>

Upvotes: 0

Views: 691

Answers (1)

Niels R.
Niels R.

Reputation: 7364

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>

Upvotes: 1

Related Questions