SQLite keeps the database locked even after the connection is closed

I had the same problem using the datasets/tableadapters generated with the designer shipped with System.Data.Sqlite.dll version 1.0.82.0 -- after closing the connection we were unable to read the database file using System.IO.FileStream. I was disposing correctly both connection and tableadapters and I was not using connection pooling.

According to my first searches (for example this and this thread) that seemed a problem in the library itself -- either objects not correctly released and/or pooling issues (which I don't use).

After reading your question I tried to replicate the problem using only SQLiteCommand objects and I found that the problem arises when you don't dispose them. Update 2012-11-27 19:37 UTC: this is further confirmed by this ticket for System.Data.SQLite, in which a developer explains that "all SQLiteCommand and SQLiteDataReader objects associated with the connection [should be] properly disposed".

I then turned back on the generated TableAdapters and I saw that there was no implementation of the Dispose method -- so in fact the created commands were not disposed. I implemented it, taking care of disposing all the commands, and I have got no problem.

Here's the code in C#, hope this helps. Please note that the code is converted from the original in Visual Basic, so expect some conversion errors.

//In Table Adapter    protected override void Dispose(bool disposing){   base.Dispose(disposing);    Common.DisposeTableAdapter(disposing, _adapter, _commandCollection);}public static class Common{    /// <summary>    /// Disposes a TableAdapter generated by SQLite Designer    /// </summary>    /// <param name="disposing"></param>    /// <param name="adapter"></param>    /// <param name="commandCollection"></param>    /// <remarks>You must dispose all the command,    /// otherwise the file remains locked and cannot be accessed    /// (for example, for reading or deletion)</remarks>    public static void DisposeTableAdapter(        bool disposing,        System.Data.SQLite.SQLiteDataAdapter adapter,        IEnumerable<System.Data.SQLite.SQLiteCommand> commandCollection)    {        if (disposing) {            DisposeSQLiteTableAdapter(adapter);            foreach (object currentCommand_loopVariable in commandCollection)            {                currentCommand = currentCommand_loopVariable;                currentCommand.Dispose();            }        }    }    public static void DisposeSQLiteTableAdapter(            System.Data.SQLite.SQLiteDataAdapter adapter)    {        if (adapter != null) {            DisposeSQLiteTableAdapterCommands(adapter);            adapter.Dispose();        }    }    public static void DisposeSQLiteTableAdapterCommands(            System.Data.SQLite.SQLiteDataAdapter adapter)    {        foreach (object currentCommand_loopVariable in {            adapter.UpdateCommand,            adapter.InsertCommand,            adapter.DeleteCommand,            adapter.SelectCommand})        {            currentCommand = currentCommand_loopVariable;            if (currentCommand != null) {                currentCommand.Dispose();            }        }    }}

Update 2013-07-05 17:36 UTC gorogm's answer highlights two important things:

• according to the changelog on the official site of System.Data.SQLite, starting from version 1.0.84.0 the above code should not be needed, since the library takes care of this. I haven't tested this, but in the worst case you only need this snippet:

  //In Table Adapter    protected override void Dispose(bool disposing){  base.Dispose(disposing);  this.Adapter.Dispose();}

• about the implementation of the Dispose call of the TableAdapter: it is is better to put this in a partial class, so that a dataset regeneration does not affected this code (and any additional code you may need to add).

I have the same problem. My scenario was after getting the data inside SQLite Database file I want to delete that file but it always throw an error "...using by other process". Even I dispose the SqliteConnection or SqliteCommand the error still occur. I've fixed the error by calling GC.Collect().

Code snippet

public void DisposeSQLite(){    SQLiteConnection.Dispose();    SQLiteCommand.Dispose();    GC.Collect();}

Hope this help.

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)    {        // Added using        using (var command = connection.CreateCommand())        {            command.CommandText = commandText;            return command.ExecuteScalar();        }    }}

Then you can use it like this

connection.ExecuteScalar(commandText);