c#进程无法访问该文件，因为它正被其他进程使用。即使在关闭sqlite 3上的连接之后

Question

因此，我试图根据我的网络数量创建多个SQLite文件。它已经成功地创建了SQLite文件，但是当我试图使它成为一个zip文件时，它给了我一个异常，它无法访问该文件，因为它正在被其他进程使用。

 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

在我看来，使用使用会更好

 using (SqlConnection conn = new SqlConnection(cmn.connString))
        {
            conn.Open();
            string query = "select networkid, network from custom_networkList";
            using (SqlCommand cmd = new SqlCommand(query, conn))
            using (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);
                    using (SQLiteConnection m_dbConnection = new SQLiteConnection(@"Data Source=" + newfile + network + ".sqlite;Version=3;"))
                    {
                        m_dbConnection.Open();
                        using (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);

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

                    }
                }

            }
        }

        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;

为了处理任何文件，您必须这样做

using (FileStream fs = File.Open(filePath, FileMode.Open, FileAccess.ReadWrite, FileShare.ReadWrite))
        {

            using (StreamReader sr = new StreamReader(fs, Encoding.Default))
            {




            }

            // or 
            using (StreamWriter sw = new StreamWriter(fs, Encoding.Default))
            {




            }


//  you can zip file or do what you want  here
        }

希望你能解决这个问题

Answer 2

在最近的SQLite客户端版本中，由于行为的改变(引入连接池)，“进程无法访问文件‘.’，因为它正在被另一个进程使用”。通过将Pooling=false添加到连接字符串，我有效地使用了以下解决方法来禁用池。

来自：https://github.com/dotnet/efcore/issues/27139#issuecomment-1007588298

SQLite提供程序现在集中连接，显着地提高连接速度，但保持连接打开。可以通过将Pooling=false添加到连接字符串或在希望关闭连接字符串的地方调用SqliteConnection.ClearAllPools()来禁用池。

在https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-6.0/breaking-changes#sqlite-connections-are-pooled中也有记载