在MS访问文件上使用dispose和SuppressFinalize后，文件已在使用异常

Question

我正在开发通过GUI对数据库进行更改的软件。我想在用户点击保存后压缩数据库。保存后用户可以继续使用软件或关闭，所以我不使用"正在使用"。我已经创建了databaseAccess对象，它与其他一些对象保持OleDbConnection连接对象。这是我的数据库访问类。

using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Data;
using System.Diagnostics;
using System.Windows.Forms;
namespace TreeTool
{
    public class DataBaseAccess
    {
        #region Properties
        private string m_directory;
        public List<string> selectedTableNames;
        private Dictionary<String, DataTable> selectedTables;
        private OleDbConnection mdbConnection;
        DataTable dataTable;
        //Constructor
        public DataBaseAccess()
        {
            selectedTableNames = new List<string>();
            selectedTables = new Dictionary<string, DataTable>();
        }
        public string directory
        {
            get
            {
                return m_directory;
            }
            set
            {
                m_directory = value;
            }
        }
        #endregion
        public List<string> GetAllTableNames()
        {
            if (dataTable != null)
            {
                List<string> tableList = new List<string>();
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    string TableName = dataTable.Rows[i][2].ToString();
                    tableList.Add(TableName);
                }
                return tableList;
            }
            return null;
        }
        /// <summary>
        /// Returns Table Columns
        /// </summary>
        /// <returns></returns>
        public DataTable GetTable(string TableName)
        {
            DataTable mdbTable;
            if (selectedTables.TryGetValue(TableName, out mdbTable))
            {
                return mdbTable;
            }
            else
            {
                mdbTable = new DataTable();
                //mdbConnection.Open();
                string mdbCommandString = "SELECT * FROM [" + TableName + "]";
                OleDbDataAdapter QueryCommand = new OleDbDataAdapter(mdbCommandString, mdbConnection);
                QueryCommand.Fill(mdbTable);
                //mdbConnection.Close();
                selectedTables.Add(TableName, mdbTable);
                return mdbTable;
            }
        }
        public void SetTable(String TableName, DataTable dataTable)
        {
            //mdbConnection.Open();
            OleDbCommand ac = new OleDbCommand("delete from [" + TableName + "]", mdbConnection);
            ac.ExecuteNonQuery();
            foreach (DataRow row in dataTable.Rows)
            {
                String query = "INSERT INTO [" + TableName + "] (TaskID, HTMLTopic, nRelative, [Group], nKey,"
                    + " [nText], nImage, nSelImage, nFontName, nFontInfo, Keywords) VALUES (@TaskID,"
                    + " @HTMLTopic, @nRelative, @Group, @nKey, @nText, @nImage, @nSelImage, @nFontName, "
                    + " @nFontInfo, @Keywords)";
                OleDbCommand command = new OleDbCommand(query, mdbConnection);
                command.Parameters.AddWithValue("@TaskID", row["TaskID"]);
                command.Parameters.AddWithValue("@HTMLTopic", row["HTMLTopic"]);
                command.Parameters.AddWithValue("@nRelative", row["nRelative"]);
                command.Parameters.AddWithValue("@Group", row["Group"]);
                command.Parameters.AddWithValue("@nKey", row["nKey"]);
                command.Parameters.AddWithValue("@nText", row["nText"]);
                command.Parameters.AddWithValue("@nImage", row["nImage"]);
                command.Parameters.AddWithValue("@nSelImage", row["nSelImage"]);
                command.Parameters.AddWithValue("@nFontName", row["nFontName"]);
                command.Parameters.AddWithValue("@nFontInfo", row["nFontInfo"]);
                command.Parameters.AddWithValue("@Keywords", row["Keywords"]);
                command.ExecuteNonQuery();
            }
            //mdbConnection.Close();
        }
        internal bool validTable(string TableName)
        {
            DataTable mdbTable = new DataTable();
            //mdbConnection.Open();
            string mdbCommandString = "SELECT * FROM [" + TableName + "]";
            OleDbDataAdapter QueryCommand = new OleDbDataAdapter(mdbCommandString, mdbConnection);
            QueryCommand.Fill(mdbTable);
            //mdbConnection.Close();
            // check if table contains all columns necessary
            String[] columnNames = new string[] { "TaskID" , "HTMLTopic", "nRelative", "Group", "nKey",
                "nText", "nImage", "nSelImage", "nFontName", "nFontInfo", "Keywords"};
            Boolean missingColumn = false;
            DataColumnCollection columns = mdbTable.Columns;
            foreach (String columnName in columnNames)
            {
                if (columns.Contains(columnName) == false)
                {
                    // print the message
                    MessageBox.Show("Database: " + directory + " Table: " + TableName + " is missing column "" + columnName
                        + "". Add it to make changes.",
                        "Missing column",
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Exclamation,
                        MessageBoxDefaultButton.Button1);
                    missingColumn = true;
                }
            }
            if (missingColumn == true)
            {
                return false;
            }
            return true;
        }
        public void insertTable(String tableName)
        {
            selectedTableNames.Add(tableName);
        }
        public List<String> getSelectedTables()
        {
            return selectedTableNames;
        }
        public Boolean isConnected()
        {
            if (mdbConnection == null)
            {
                return false;
            }
            return true;
        }
        public void connect()
        {
            if (mdbConnection == null)
            {
                String m_mdbDirectory = @"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + m_directory;
                mdbConnection = new OleDbConnection(m_mdbDirectory);
                mdbConnection.Open();
                string[] restrictions = new string[4];
                restrictions[3] = "Table";
                dataTable = mdbConnection.GetSchema("TABLES", restrictions);
                //mdbConnection.Close();
            }
        }
        public void disconnect()
        {
            mdbConnection.Close();
            mdbConnection.Dispose();
            GC.SuppressFinalize(mdbConnection);
            mdbConnection = null;
        }
        public void clearSelectedTables()
        {
            selectedTableNames.Clear();
        }
    }
}

保存和压缩功能与类似

    private void save()
    {   
        foreach(DataBaseAccess database in databases)
        {
            // save changes code
            database.disconnect();
            CompactAndRepairAccessDB(database.directory);
            database.connect();
        }
    }
    private void CompactAndRepairAccessDB(string accessFile)
    {
        string tempFile = @"temp.mdb";
        FileInfo temp = new FileInfo(tempFile);
        // Required COM reference for project:
        // Microsoft Office 14.0 Access Database Engine Object Library
        var dbe = new Microsoft.Office.Interop.Access.Dao.DBEngine();
        try
        {
            dbe.CompactDatabase(accessFile, tempFile);
            temp.CopyTo(accessFile, true);
            temp.Delete();
        }
        catch (Exception e)
        {
            Console.WriteLine("Error: " + e.Message);
        }
    }

异常发生在"dbe.CompactDatabase（accessFile，tempFile）；"行。

Answer 1

在使用OleDbDataAdapter和OleDbCommand的所有方法的代码中，请确保在这些对象上使用using Pattern。即使实际连接已经被释放，他们也可以保持mdb文件的打开状态。

需要修改的方法似乎是GetTable、SetTable和ValidTable。