System.Data.SQLite Close() not releasing database file

Encountered the same problem a while ago while writing a DB abstraction layer for C# and I never actually got around to finding out what the issue was. I just ended up throwing an exception when you attempted to delete a SQLite DB using my library.

Anyway, this afternoon I was looking through it all again and figured I would try and find out why it was doing that once and for all, so here is what I've found so far.

What happens when you call SQLiteConnection.Close() is that (along with a number of checks and other things) the SQLiteConnectionHandle that points to the SQLite database instance is disposed. This is done through a call to SQLiteConnectionHandle.Dispose(), however this doesn't actually release the pointer until the CLR's Garbage Collector performs some garbage collection. Since SQLiteConnectionHandle overrides the CriticalHandle.ReleaseHandle() function to call sqlite3_close_interop() (through another function) this does not close the database.

From my point of view this is a very bad way to do things since the programmer is not actually certain when the database gets closed, but that is the way it has been done so I guess we have to live with it for now, or commit a few changes to System.Data.SQLite. Any volunteers are welcome to do so, unfortunately I am out of time to do so before next year.

TL;DRThe solution is to force a GC after your call to SQLiteConnection.Close() and before your call to File.Delete().

Here is the sample code:

string filename = "testFile.db";SQLiteConnection connection = new SQLiteConnection("Data Source=" + filename + ";Version=3;");connection.Close();GC.Collect();GC.WaitForPendingFinalizers();File.Delete(filename);

Good luck with it, and I hope it helps

Just GC.Collect() didn't work for me.

I had to add GC.WaitForPendingFinalizers() after GC.Collect() in order to proceed with the file deletion.

In my case I was creating SQLiteCommand objects without explicitly disposing them.

var command = connection.CreateCommand();command.CommandText = commandText;value = command.ExecuteScalar();

I wrapped my command in a using statement and it fixed my issue.

static public class SqliteExtensions{    public static object ExecuteScalar(this SQLiteConnection connection, string commandText)    {        using (var command = connection.CreateCommand())        {            command.CommandText = commandText;            return command.ExecuteScalar();        }    }}

The using statement ensures that Dispose is called even if an exception occurs.

Then it's a lot easier to execute commands as well.

value = connection.ExecuteScalar(commandText)// Command object created and disposed