c# The process cannot access the file because it is being used by another process. Even after closing the connection on sqlite 3

Question

So i was trying to create multiple SQLite file base on the number of my network. It has successfully created the SQLite files but when i tried to make it a zip file it gave me an exception that cannot access the file because it is being used by another process.

 SqlConnection conn = new SqlConnection(cmn.connString);
            conn.Open();
            string query = "select networkid, network from custom_networkList";
            SqlCommand cmd = new SqlCommand(query, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                int networkid = Convert.ToInt32(reader["networkid"]);
                string network = reader["network"].ToString();

                File.Copy(Templatefile, newfile + network + ".sqlite", true);
                SQLiteConnection m_dbConnection = new SQLiteConnection(@"Data Source=" + newfile + network + ".sqlite;Version=3;");

                m_dbConnection.Open();
                SQLiteCommand command = new SQLiteCommand("begin", m_dbConnection);
                command.ExecuteNonQuery();

                insertZone(m_dbConnection);
                InsertJunctions(m_dbConnection, networkid);
                InsertHydrant(m_dbConnection, networkid);
                insertWaterTank(m_dbConnection, networkid);
                insertPump(m_dbConnection, networkid);
                InsertReservoir(m_dbConnection, networkid);
                insertValve(m_dbConnection, networkid);
                insertPipe(m_dbConnection, networkid);

                command = new SQLiteCommand("end", m_dbConnection);
                command.ExecuteNonQuery();
                m_dbConnection.Close();
                command.Dispose();
                m_dbConnection.Dispose();
            }
            conn.Close();


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

            if (!Directory.Exists(newfilename))
            {
                // Try to create the directory.
                File.Delete(newfilename);
            }
            ZipFile.CreateFromDirectory(newfile, newfilename, CompressionLevel.Fastest, true);
            Directory.Delete(newfile,true);

            return newfilename2;

Answer 1

On more recent SQLite client versions, the error "The process cannot access the file '...' because it is being used by another process" can happen due to a change of behaviour: the introduction of connection pool. I have used effectively the following workaround to disable pooling by adding Pooling=false to the connection string.

From: https://github.com/dotnet/efcore/issues/27139#issuecomment-1007588298

The SQLite provider now pools connections, improving connection speed significantly but keeping connections open. You can disable pooling by adding Pooling=false to the connection string or calling SqliteConnection.ClearAllPools() at the point where you want them to be closed.

Also documented in https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-6.0/breaking-changes#sqlite-connections-are-pooled