c# - 在 MS Access 文件上使用处置和 SuppressFinalize 后文件已在使用异常

Question

我正在开发通过 GUI 更改数据库的软件。我想在用户点击保存后压缩数据库。保存后用户可以继续使用软件或关闭，所以我不是使用“正在使用”。我创建了 databaseAccess 对象，它与其他几个对象一起保存 OleDbConnection 连接对象。这是我的数据库 Access 类。

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。