[Access] C# 通过 COM 组件访问 Access 文件

说明：

1，采用 dynamic 调用 COM 组件，适用于 .NET 4.0 以上支持 dynamic 版本的才可以；

2，执行速度不敢恭维，只是因为要用于 Silverlight OOB 模式中才研究一二；

3，测试环境 .net 4.5 + Silverlight 5.0 + Visual Studio 2013

4，见如下 helper 类（需引用 using System.Runtime.InteropServices.Automation;）：

public class SLAccessHelper
{
    private dynamic m_AccessApp;// Access.Application
    private dynamic m_Database;// Database
    private dynamic m_Recordset;

    /// <summary>
    /// 构造函数
    /// </summary>
    /// <param name="visible">Access是否可见</param>
    public SLAccessHelper(bool visible)
    {
        m_AccessApp = AutomationFactory.CreateObject("Access.Application");
        m_AccessApp.Visible = visible;
    }

    /// <summary>
    /// 打开数据库
    /// </summary>
    /// <param name="filePath">Access数据库文件路径</param>
    /// <param name="exclusive">是否共享</param>
    /// <param name="bstrPassword">密码</param>
    public void OpenDb(string filePath, bool exclusive = false, string bstrPassword = "")
    {
        m_AccessApp.OpenCurrentDatabase(filePath, exclusive, bstrPassword);
        m_Database = m_AccessApp.CurrentDb();
    }

    /// <summary>
    /// 获取当前数据库中所有表名称集合
    /// </summary>
    /// <returns>所有表名称集合</returns>
    public List<string> GetTableNames()
    {
        List<string> tableNames = new List<string>();
        dynamic tableDefs = m_Database.TableDefs;
        foreach (dynamic tableDef in tableDefs)
        {
            tableNames.Add(tableDef.Name);
        }

        return tableNames;
    }

    /// <summary>
    /// 加载表数据
    /// </summary>
    /// <param name="tableName">表名称</param>
    /// <returns>表数据</returns>
    public List<List<string>> LoadTable(string tableName)
    {
        dynamic recordSet = m_Database.OpenRecordset(tableName);
        int fieldsCount = recordSet.Fields.Count;
        List<List<string>> data = new List<List<string>>();
        if (fieldsCount > 0)
        {
            try
            {
                List<string> fieldNames = new List<string>();
                for (int i = 0; i < fieldsCount; i++)
                {
                    fieldNames.Add(recordSet.Fields[i].Name);
                }
                data.Add(fieldNames);
                if (!recordSet.EOF)
                {
                    recordSet.MoveFirst();
                    while (!recordSet.EOF)
                    {
                        object[] dataRow = recordSet.GetRows();// 返回一维数组
                        List<string> dataRowStr = new List<string>();
                        for (int i = 0; i < dataRow.Length; i++)
                        {
                            dataRowStr.Add(dataRow[i] == null ? "" : dataRow[i].ToString());
                        }
                        data.Add(dataRowStr);
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (recordSet != null)
                {
                    recordSet.Close();
                    ((IDisposable)recordSet).Dispose();
                    recordSet = null;
                }
            }
        }

        return data;
    }

    /// <summary>
    /// 添加新纪录
    /// </summary>
    /// <param name="tableName">表格名称</param>
    /// <param name="data">数据</param>
    public void AddNewRecord(string tableName, List<Dictionary<string, object>> data)
    {
        try
        {
            m_Recordset = m_Database.OpenRecordset(tableName, 1);// 1=RecordsetTypeEnum.dbOpenTable
            int fieldsCount = m_Recordset.Fields.Count;
            List<string> fieldNames = new List<string>();
            for (int i = 0; i < fieldsCount; i++)
            {
                fieldNames.Add(m_Recordset.Fields[i].Name);
            }
            for (int rowIndex = 0; rowIndex < data.Count; rowIndex++)
            {
                m_Recordset.AddNew();
                foreach (string fieldName in fieldNames)
                {
                    m_Recordset.Fields[fieldName].Value = data[rowIndex][fieldName];
                }
                m_Recordset.Update();
            }
        }
        catch(Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            if (m_Recordset != null)
            {
                m_Recordset.Close();
                ((IDisposable)m_Recordset).Dispose();
                m_Recordset = null;
            }
        }
    }

    /// <summary>
    /// 更新表格数据
    /// </summary>
    /// <param name="tableName">表格名称</param>
    /// <param name="data">数据</param>
    public void UpdateTable(string tableName, List<Dictionary<string, string>> data)
    {
        try
        {
            m_Recordset = m_Database.OpenRecordset(tableName, 1);// 1=RecordsetTypeEnum.dbOpenTable
            m_Recordset.MoveFirst();
            for (int rowIndex = 0; rowIndex < data.Count; rowIndex++)
            {
                m_Recordset.Edit();
                foreach (string fieldName in data[rowIndex].Keys)
                {
                    m_Recordset.Fields[fieldName].Value = data[rowIndex][fieldName];
                }
                m_Recordset.Update();
                m_Recordset.MoveNext();
            }
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            if (m_Recordset != null)
            {
                m_Recordset.Close();
                ((IDisposable)m_Recordset).Dispose();
                m_Recordset = null;
            }
        }
    }

    /// <summary>
    /// 关闭
    /// </summary>
    public void Close()
    {
        if (m_Database != null)
        {
            m_Database.Close();
            ((IDisposable)m_Database).Dispose();
            m_Database = null;
        }
        if (m_AccessApp != null)
        {
            m_AccessApp.CloseCurrentDatabase();
            // m_AccessApp.Quit();// 导致最后会弹出Access主页面
            ((IDisposable)m_AccessApp).Dispose();
            m_AccessApp = null;
        }
        GC.Collect();
    }
}

 

通过 dynamic 构建的 COM 对象，在使用完成后都要手动关闭销毁，比如代码中的 m_AccessApp, m_Database, m_Recordset 三个对象，否则只是将 m_AccessApp 关闭清空释放掉，Access 进程还是无法关闭，在程序关闭之前，始终都会有一个空白的无法关闭的 Access 界面；

在循环中处理 dynamic 和 C# 类型转换会降低程序执行效率，就比如像 GetTableNames 方法中循环遍历表名，都要花两三秒时间，所以尽量像 object[] dataRow = recordSet.GetRows(); 直接获取其中的所有数据，然后再遍历处理，会极大提高执行效率；

要修改 Access 中的数据时，一定要先 m_Recordset.Edit(); 才会允许你编辑其中的内容；