SqliteConnection seems to work just fine in debug mode while firing an exception after being compiled in release mode

Connection = new SqliteConnection($"Data Source={ApplicationData.Current.LocalFolder.Path}\\{DB_NAME}");

I'm using the package System.Data.SQLite from SQLite Development Team, installed via NuGet, and imported as follows

using SqliteConnection = System.Data.SQLite.SQLiteConnection;

The connection is succesfull when the project runs in debug mode, while fires up an exception if ran after being compiled.

2024-08-09 11:37:58 - Value cannot be null.
Parameter name: path1 -    at System.IO.Path.Combine(String, String) + 0x50
   at System.Data.SQLite.SQLiteConnection..ctor(String, Boolean) + 0x34b
   at Prime.Db.Database.<Inizializza>d__12.MoveNext() + 0x16f
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() + 0x21
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task) + 0x70
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task) + 0x38
   at System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd(Task) + 0x17
   at System.Runtime.CompilerServices.TaskAwaiter.GetResult() + 0xb
   at Prime.SplashScreenPage.<CaricamentoIniziale>d__8.MoveNext() + 0x3e2a
 - Prime.Db.Database+<Inizializza>d__12 - <Inizializza>d__12

The string parmeter passed to SqliteConnection function has been checked with a log statement immediately before it and does contain a valid value. The working environment is Visual Studio 2022, the project is an UWP app

It's a bug to be fixed by Version 1.0.119.0. Hoping the new version would be released soon.

Any workaround at the current state?

As far as I know, it only happens at the first time. So I just give one retry by using try-catch for now. If the new version released, I guess I should remove that retry.

using SqliteConnection = System.Data.SQLite.SQLiteConnection;

1. That line, as it stands, seems suspect - not least because you have no database specified - and does not tie in with your claim.

2. Also, it is difficult to believe this claim. A connection will either succeed or fail NOT succeed the second time round (you have to ask what happens the nth time round!).

Try this code:

SQLiteConnection.ClearAllPools();
using (SqliteConnection cn = new System.Data.SQLite.SQLiteConnection(@"Data Source=e:/temp/newdb.db;Version=3;Journal Mode=Off;")))
{
  cn.Open();
  // Your code
  
}
  // cn.Close() /* will be called here implicitly */ 

How is this different?

a. SQLiteConnection.ClearAllPools();

b. The C# using syntax requires round brackets around its argument and curly brackets around its scope. There is no semi-colon at the end of the using line.

c. Note the words cn = new on this line SqliteConnection cn = new System.Data.SQLite.SQLiteConnection(...) SQLiteConnection is an object (NOT a variable); you need an instance of that object.

Re-factor your code, re-run in Release mode - without forgetting to change the data source - and then update this thread with what you experience.

No, nothing wrong there, I'm referring to the using used to reference the namespace. As stated here:
The using directive allows you to use types defined in a namespace without specifying the fully qualified namespace of that type. In its basic form, the using directive imports all the types from a single namespace, as shown in the following example:

using System.Text;

You can also create an alias for a namespace or a type with a using alias directive.

using Project = PC.MyCompany.Project;

This is my case, so your claim is wrong, the line is not suspicious at all.
That said, you looks like a bit confused as SQLiteConnection is a class, not an object, as an object is an instance of a class (you can clear out your terminology here: Objects are instances of a class).
Anyway you're missing the point here, this is not an issue in my code, as it runs just fine while debugging, is more likely a bug in the library that does something wrong while compiled.

In your line

using Project = PC.MyCompany.Project;

1. Project is an instance of PC.MyCompany.Project - it is an object. Your are using the class name as your object name without specifying its type.

2. I do not recognise your statement: I believe it is not the correct way to dispose of an object.

Your response does NOT state whether you tried the code snippet I suggested.

I have used System.Data.SQLite since version 1.0.115.0 and upgraded to

a. every pre-compiled released versions up to 1.0.118.0

b. and to the last checked-in version two days ago (2024-08-18) 1.0.119.0 which I compiled myself

without any issues, neither in Debug nor in Release mode.

I believe the problem lies in your code rather than the library but it is difficult to be sure as you have not posted your code to reproduce your issue.

No, I have not missed anything.

This, before the namespace declaration:

using System.Data.SQLite;

scopes the DLL to the lifespan of the executable.

This, before the namespace declaration:

using sds = System.Data.SQLite;

scopes the DLL to the lifespan of the executable, but uses sds as an alias in lieu of the DLL's full name.

This, within a class:

using (SQLConnection cn = new SQLConeection())
{
}
// cn becomes out of scope here

scopes the instance of the SQLiteConnection object; the lifespan of the object remains within the double braces only.

With using System.Data.SQLite at the top, before the namespace declaration, you simply refer to SQLConnection and not System.DAta.SQLite.SQLConnection.

Whatever your take on this, the issue is that the OPs code works in Debug Mode and fails in Release mode. As I pointed out, I use System.Data.SQLite and it has worked for me since version 1.0.115.0 up do version 1.0.118.0 (using the pre-compiled releases) and also with version 1.0.119.0 which I have compiled myself.

Given this, my contention is that it is OP's code that is suspect and the prognosis that there is a bug is System.Data.SQLite version 1.0.118.0 that causes the connection to fail only when first used but to succeed subsequently is incorrect.

To move forward, the OP needs to post the bare code being used that will allow the reported issue to be re-created independently.

On the news page, it has the list to be fixed on 1.0.119.0 and the 4th one shoud fix this problem, isn't it?

4th one

Nothing here about failing in Release mode nor about only happening first time

The problem is NullReferenceException from Path.Combine method when the PublishSingleFile property is enabled for a project. . Specifically, the source of the error is Path.Combine: surely, the workaround is to replace the call to that method with alternative code (such as use string interpolation or string concatenation) OR simply compile System.Data.SQLite from the latest check-in as I did.

Well, sorry that he didn't mension that he used the PublishSingleFile and so I maybe wrong for this problem.

For me, I'm having the problem as mensioned on the 4th one and waiting for the new version which was to be released on June XX but now September XX.

One more thing, sorry that I said it only happens at the first time. As I stated "as far as I know", since I'm not a tester nor anything, I'm thankfully using System.Data.SQLite and had the problem with my app so I just wrote what I'm doing for now.

The reported offending code is:

SQLiteConnectionStringBuilder csb = new SQLiteConnectionStringBuilder {
         DataSource = @"C:\Users\Luc\AppData\Roaming\VRS\sto\vrs.sto",
         Version = 3,
         BusyTimeout = 2500,
         ForeignKeys = true,
         DateTimeFormat = SQLiteDateFormats.ISO8601
      };
SQLiteConnection connection = new SQLiteConnection(csb.ConnectionString); //  The above mentioned exception is thrown here

a. With version 1.0.119.0 (which I compiled), there is no exception. However, that is no guarantee that the connection will succeed - see 1. & 2. below - I can't test because that location does not exist on my computer.

b. This line is suspect:

DataSource = @"C:\Users\Luc\AppData\Roaming\VRS\sto\vrs.sto"

It is a specific user's current directory which is hardcoded. It is better coded as follows:

DataSource=Environment.GetFolderPath(Environment.SpecialFolder.UserProfile) + @"\AppData\Roaming\VRS\sto\vrs.sto";

or preferably

DataSource=Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + @"\VRS\sto\vrs.sto";

As coded:

1. "\AppData\Roaming\VRS\sto\vrs.sto" must exist.

2. unless the user who is running in debug mode is the same user that is running in release mode and is called Luc, an exception at runtime is guaranteed.

3. What is vrs.sto? Is it the name of the database? (Usually, SQLite databases have suffix .db). If it is, unless vrs.sto exists in every user's directory, a runtime exception is guaranteed. A more sensible - that is, user independent - location is one to which every user has access to ensure success and portability. If vrs.sto is NOT the name of the database, I can only guess that the intention is to use an in-memory database. If so, the DataSurce specification is WRONG and will fail at runtime.

4. If you are using version 1.0.118.0 and getting a null reference exception, try this code instead - assuming you are Luc and have a roaming profile setup else change Luc to your username:

SQLiteConnection connection = new SQLiteConnection( String.Format("DataSource=\"{0}\";Version={1};BusyTimeout={2};ForeignKeys={3}, DateTimeFormat={4};", @"C: \Users\Luc\AppData\Roaming\VRS\sto\vrs.sto", 3, 2500, true, SQLiteDateFormats.ISO8601));

This may still fail because of 1 and/or 2 and/or 3 above.

If you understand the issues I've elaborated, re-factor your code with v 1.0.118.0 and update this thread.

I removed the aliasing line, which i don't blame, but that way is more plain.
The bare code is:

using System.Data.SQLite;
using Windows.Storage;
using System;

namespace Prime.Db
{
  public class Database
  {
    public const string DB_NAME = "Prime.sqlite";

    public SQLiteConnection Conn { get; set; } = null;
  
    public async Task Init()
    {
      string StrConn= $"Data Source={ApplicationData.Current.LocalFolder.Path}\\{DB_NAME}";
      string StmCreateTable= @"CREATE TABLE IF NOT EXISTS ""Test"" (
                            ""Id"" INTEGER NOT NULL UNIQUE,
                            ""Timestamp"" DATETIME,
                            PRIMARY KEY(""Id"" AUTOINCREMENT))";
      
      // Linking db
      Logger.Log("Database.Init", StrConn);
      Conn = new SQLiteConnection(StrConn);
      Conn .Open();
      
      // Create table
      SQLiteCommand Cmd = Conn.CreateCommand();
      Cmd.CommandText = CreaTabella;
      Cmd.ExecuteNonQuery();
    }
  }
}

Main code just instances once the Database class and calls the Init method on the object. A log line has been added to write down into a file the connection string immediately before the connection statement. I ran the code on the same pc and the log statement is exactly the same and the path exist. What is different is that while in debug mode the connection works like a charm, having the uwp app published throws an exception on the statement Conn = new SQLiteConnection(StrConn);, the exception is the one shared in the OP. This is happening, at least, with the version 1.0.118.0

1. Are you using the release executable from \bin\release or from \obj\Release? you should be using the one in \bin\release

2. You have added a reference to System,.Data.SQLite.DLL. Open Solution Explorer, highlight the reference, right-click and then select the Properties menu option. Is Copy Local set to True? It should be.

3. I've used 1.0.118.0 and 1.0.119.0 without any issues.

Your code.

I don't use the UWP template.

a. Is this Task Init() correct? or should it be Task_Init()

b. Cmd.CommandText = CreaTabella; // CreaTabella; does not exist!

c. You are missing Cmd.Dispose();

d. You are missing Conn.Close();

e. Your SQL:

There is no need to wrap the column names in double quotes as none of the names have embedded spaces or other forbidden characters. If you must, in order to make it easier to read, you can wrap the names within square brackets instead of double quotes.

Your table already had ROWID as a primary key. Did you mean to add ID as a primary key or is WITHOUT ROWID missing from your SQL?

Unless you create STRICT tables, the datatypes you have specified after the column names are simply ignored.

f. Instead of {ApplicationData.Current.LocalFolder.Path}, use another location to which ALL users have read/write privileges.

I do not encounter any problems except when I invoke the executable from \obj\Release (because it is missing System.Data.SQLite.DLL). Use the executable from \bin\release which is where the distributable executables are located. (\obj\Release is used by Visual Studio for various things).

Also, ensure that your project's architecture matches that if System.Data.SQLite.DLL ie. that both are either 32-bit or both are 64-bit.

1. Executable reference one in bin/release
2. Not clear, plain text does not contain the DLL at the end, also highlighting it does not provide a properties voice, i can found the reference under the References menu, but under its properties there is no Copy Local option visible
3. Good to you, have you used them to build an uwp app? If not, your statement means nothing

My code

a. Task Init is correct, more info here
b. I translated the variable names to english while posting, i've missed one, that variable should obviously be StmCreateTable.
c. No, I'm not, this is just a part of the init function, the code is disposing the command at the end of the function
d. No, I'm not, again, this is just a part of the program, the code is disposing the connection while closing
e. Nothing to say about the double quotes, this is a simple developer decision, if you like the square brackets just use them, it's not dogmatic as every Microsoft example does use the quotes instead of the brackets. Second assertion is pointless, as state here if a rowid table has a primary key that consists of a single column and the declared type of that column is "INTEGER" in any mixture of upper and lower case, then the column becomes an alias for the rowid. WITHOUT ROWID is not required, in fact everything works as expected. Your last assertion is simply wrong, i read this section and it looks like you're missing something, STRICT table requires everything to be explicit, that's ok, but does not automatically mean that non strict tables types are ignored, this is demonstrated by the fact that with a sqlite db browser I'm able to open the generated table and the datatypes are there.
f. Before suggesting stuff, you should know a little something about the OP used environment, uwp app are sandboxed, and you can't read and write around in the PC as you do not have the permission, again, read this post to understand what I'm saying.

That said, the issue here is the one reported by the 4th point under the realease notes regarding version 1.0.119.0 here, downgrading to version 1.0.117.0 solves the issues on the spot without changing anything to the code, that demonstrates how the issue is located somewhere in the library. I'll wait to the new version to be released being able to test and see if the issue introduced with the version 1.0.118.0 has been solved

This is really enigmatic. I can use both v1.0.118.0 and v1.0.119.0 without any issues in both debug and release mode.

It looks like you have an error that cannot be handled.

Try the following:

Step 1: Right at the start of your project, before anything else, insert this line:

AppDomain.CurrentDomain.UnhandledException += new UnhandledExceptionEventHandler(CatchUnhandledException);

Step 2: In the same class where you have added the above line, add this function:

        static void CatchUnhandledException(object sender, UnhandledExceptionEventArgs e)
        {
            using (System.IO.StreamWriter sw = new System.IO.StreamWriter(@"E:\temp\crash.txt"))
            {
                sw.WriteLine(((Exception)e.ExceptionObject).Message);
                System.Threading.Thread.Sleep(5000);
                sw.Flush();
                sw.Close();
            }
        }

Step 3: Change "E:\temp\crash.txt" to some location on your disk.

Step 4: Compile your project, ensuring that you can run in debug mode.

Step 5: Close your project.

Step 6: Run the release executable. Locate the executable in File Explorer and double click on it. A. Report back the exact file location of the executable.

Step 7: In notepad or any text editor, open "E:\temp\crash.txt" (or the file you have specified). B. What is the content of that file?

My guess is that the content reads something like this:

"Could not load file or assembly 'System.Data.SQLite, Version=1.0.119.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139' or one of its dependencies. The system cannot find the file specified."

If that is the case, the problem would be the manner in which you have added the reference to System.Data.SQLite.

UnhandledException are already catched and logged to a logging file. Reference to System.Data.SQLite has been auto generated as the package has been installed via NuGeT. As reported below, downgrading the package to the version 1.0.117.0 solved the issue, as it do not breaks anything else in my code, i'm leaving this version for the moment, waiting for the 1.0.119.0 to be released.

Thanks for your help.
Have a great day.

There was a similar issue with 1.118 .. have you tried setting the environment variable?

System.Environment.SetEnvironmentVariable("SQLite_NoConfigure", "1");

Do this before using the connection.

No, i didn't, i saw it was a solution suggested here but i decided to simply downgrade by one version.
Thank you for the suggestion anyway

The problem is the PublishSingleFile that causes the error. Thanks this work for me too.