CODEWITHSUNDEEP
c#sqlitefiledelete-filehandle
PauMAVA
PauMAVA

Reputation: 1243

SQLite DB file handle is never closed C#

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:

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:

Upvotes: 7

Views: 1021

Answers (3)

Victor V.
Victor V.

Reputation: 46

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()

Upvotes: 1

Michael Brown
Michael Brown

Reputation: 1731

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.

Upvotes: 2

Ritt App
Ritt App

Reputation: 31

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();

Upvotes: 3

Related Questions