CODEWITHSUNDEEP
c#.netsqlitenlog
Shax
Shax

Reputation: 4307

How to use SQLite with NLog in .NET

I am trying to use SQLite with NLog so that I can store logging to a SQLite database rather a flat file. I followed a good tutorial, but even then my code is not enabling is working as expected.

My App.config file:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <connectionStrings>
    <add name="SQLite" connectionString="Data Source=Log.db3;Version=3;" providerName="System.Data.SQLite" />
  </connectionStrings>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.1" />
  </startup>
  <system.data>
    <DbProviderFactories>
      <remove invariant="System.Data.SQLite" />
      <add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".Net Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />
    </DbProviderFactories>
  </system.data>
</configuration>

And this is my NLog.config 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 name="file" xsi:type="File"
          layout="${longdate} ${logger} ${message}"
          fileName="${basedir}/logs/${shortdate}/${level}.txt"
          keepFileOpen="false"/>
    <target name="db" xsi:type="Database" keepConnection="false"
            useTransactions="false"
            dbProvider="System.Data.SQLite"
            connectionString="Data Source=${basedir}\Log.db3;Version=3;"
            commandText="INSERT into Log(Timestamp, Loglevel, Callsite, Message) values(@Timestamp, @Loglevel, @Callsite, @Message)">
      <parameter name="@Timestamp" layout="${longdate}"/>
      <parameter name="@Loglevel" layout="${level:uppercase=true}"/>
      <parameter name="@Callsite" layout="${callsite:filename=true}"/>
      <parameter name="@Message" layout="${message}"/>
    </target>
  </targets>
  <rules>
    <logger name="*" minlevel="Trace" writeTo="db" />
  </rules>
</nlog>

And this is my code:

 static class Program
    {
        static Logger log = LogManager.GetCurrentClassLogger();
        /// <summary>
        /// The main entry point for the application.
        /// </summary>
        [STAThread]
        static void Main()
        {
            try
            {
                Application.EnableVisualStyles();
                Application.SetCompatibleTextRenderingDefault(false);
                EnsureDb();
                log.Info("Logging like a boss");
                Application.Run(new Form1());
            }
            catch (Exception ex)
            {
                throw;
            }

        }

        private static void EnsureDb()
        {
             if (File.Exists("Log.db3"))
                 return;

             using (SQLiteConnection connection = new SQLiteConnection(ConfigurationManager.ConnectionStrings["SQLite"].ToString()))
             using (SQLiteCommand command = new SQLiteCommand(
                 "CREATE TABLE Log (Timestamp TEXT, Loglevel TEXT, Callsite TEXT, Message TEXT)",
                 connection))
             {
                 connection.Open();
                 command.ExecuteNonQuery();
             }
        }
    }

I have copied this code from the Internet, but unfortunately it's not working. The log.db3 file always gets created, but no log entry reaches to the log table. How can I fix this?

Upvotes: 3

Views: 6806

Answers (2)

Manuel
Manuel

Reputation: 36

For .NET core 2.0 I have to use:

dbProvider="Microsoft.Data.Sqlite.SqliteConnection, Microsoft.Data.Sqlite"

Upvotes: 2

TarasB
TarasB

Reputation: 2428

Try adding command type Text:

    <target name="db" xsi:type="Database"
        keepConnection="false"
        useTransactions="false"
        dbProvider="System.Data.SQLite"
        connectionString="Data Source=${basedir}\Log.db3;Version=3;"
        commandType="Text"
        commandText="INSERT into Log(Timestamp, Loglevel, Callsite, Message) values(@Timestamp, @Loglevel, @Callsite, @Message)">

Also for debugging purposes consider using LogManager.ThrowExceptions = true; in your Main - it may give some directions of what is wrong. It was throwing NotSupportedException which gave me a hint about CommandType:

Unhandled Exception: NLog.NLogRuntimeException: Exception occurred in NLog --->
System.NotSupportedException: Specified method is not supported.
   at System.Data.SQLite.SQLiteCommand.set_CommandType(CommandType value)

Upvotes: 2

Related Questions