SQLite .db file is not being removed/renamed even after closing the connection of that particular file

Question

I'm using SQLite3 database for a small level system. The problem is that when I try to rename the db file using IO file system, System.IO returns an exception that the file is being used by another process however I've already closed the connection using connection.Close() method. I searched on this topic but cold not find any thing useful for me. Someone please suggest me the right way to release an SQLite connection..

Thanks in Advance

Answer 1

One of the things I learned programming with SQLite is that closing the connection is simply not good enough. You need to wrap everything in a using block. Make sure your SQLiteConnection object is wrapped in a using block, your SQLiteCommand is wrapped in a using block and your SQLiteDataReader is wrapped in a using block. Your code should look similar to this:

using (var connection = new SQLiteConnection())
{
    connection.ConnectionString = connectionString;

    using (var command = new SQLiteCommand())
    {
        connection.Open();
        command.CommandType = CommandType.Text;
        command.CommandText = sql;
        command.Connection = connection;

        using (SQLiteDataReader reader = command.ExecuteReader())
        {
            if (reader.HasRows)
            {
                // Do something
            }
        }
    }
}