SQLite DB file handle is never closed C#

Question

I am trying to delete a SQLite database file from my C# application. The same application performs multiple RW queries to the DB by creating and disposing a new connection each time.

When trying to delete the DB, (on a moment when I can assure the program is not querying the DB, thus no active connections) I was facing the error:

IOException: The process cannot gain access to <filename> because is being used by another process.

I have investigated for several hours: my code, SO questions, using procmon and resmon to be sure that my process was the only one holding an active handle to the file.

After all this, I determined that the DB file is not being closed correctly whenever I create a DB connection. Explanation follows:

I have the following function to perform a query and load the results into a DataTable:

public DataTable PerformQuery(string query) {
    try {
        DataTable table = new DataTable();
        using(SQLiteConnection connection = new SQLiteConnection(connString)) {
            SQLiteCommand cmd = connection.CreateCommand();
            cmd.CommandText = query;
            connection.Open();
            if (!query.StartsWith("SELECT")) {
                cmd.ExecuteNonQuery();
            } else {
                SQLiteDataReader reader = cmd.ExecuteReader();
                FillTable(reader, table);
            }
            // Despite using the using scope, close and dispose the connection manually
            connection.Close();
            connection.Dispose();
        }
        // Kill all pools and call GC
        SQLiteConnection.ClearAllPools();
        GC.Collect();
        GC.WaitForPendingFinalizers();
        return table;
    } catch (Exception ex) {
        // Handle error... (not relevant)
        return null;
    }
}

Well, using SysInternals handle.exe in an infinite loop (running every second) and debugging the program step-by-step using Visual Studio's Debugger I found out that the file handle to my DB file is not being closed despite:

• Calling Close
• Calling Dispose
• Getting out of the using scope
• Killing all pools
• Executing GC and waiting for it to finish

I really need to delete my DB file from inside the code but I cannot as the file handle is never closed.

How could I solve this?

EDIT 1:

• Using .NET Winforms Application with System.Data.SQLite
• Already tried adding Pooling=false to the connection string.

Fraction

Is this .NET Framework, EFCore, what? – Pelorus

.NET, Winform App. Using System.Data.SQLite. – Fraction

Try adding "Pooling=false" to your connection string. That is, don't use connection pooling. – Pelorus

Thanks for your suggestion. Unfortunately, I have tried this but also without success. – Fraction

SQLiteCommand is also disposable. Try disposing it. – Delorisdelorme

Same disposing stuff to SQLiteDataReader – Delorisdelorme

Answer 1

Have you tried to close the SQLiteCommand as well?

using (SQLiteConnection connection = new SQLiteConnection(connString)) {
    using (SQLiteCommand cmd = connection.CreateCommand()){
        // do stuff here
    }
}
SQLiteConnection.ClearAllPools();
GC.Collect();
GC.WaitForPendingFinalizers();

Hy

ClearAllPools() is needed so that the SqliteDB FileHandles are relieved (when Connection Pooling is used) – Crinkly

Answer 2

Here's how I solved this with Entity Framework 8, as other/older solutions did not work for me:

async Task CloseDatabaseAsync()
{
  try 
  {
    await CloseSqliteDatabaseAsync();
  }
  catch(ObjectDisposedException) 
  {
    // connection will be disposed, and fail due to invalid handle
  }
}

async Task CloseSqliteDatabaseAsync()
{
  await using var context = await _contextFactory.CreateDbContextAsync();
  var conn = context.Database.GetDbConnection() as SqliteConnection;
  conn.Open();
  var result = SQLitePCL.raw.sqlite3_close_v2(conn.Handle);
  conn.Handle.Close();
  conn.Handle.Dispose();

  GC.Collect();
  GC.WaitForPendingFinalizers();

  // delete or overwrite the database, whatever you need to do
  File.Delete(filename);
}

Internally calling sqlite3_close_v2 with the connection handle forces the connection to be closed and the file lock is released.

Priebe

Answer 3

You can either change your connection string to disable pooling Data Source=file.db;Pooling=false; which will release the file lock as soon as the connection is disposed.

Or when you want to keep the pooling enabled (improves performance), you can close the pools exactly at the point in code where you want to manipulate the database file by calling Microsoft.Data.Sqlite.SqliteConnection.ClearAllPools()

Connive