[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;）：
  1 public class SLAccessHelper
  2 {
  3     private dynamic m_AccessApp;// Access.Application
  4     private dynamic m_Database;// Database
  5     private dynamic m_Recordset;
  6 
  7     /// <summary>
  8     /// 构造函数
  9     /// </summary>
 10     /// <param name="visible">Access是否可见</param>
 11     public SLAccessHelper(bool visible)
 12     {
 13         m_AccessApp = AutomationFactory.CreateObject("Access.Application");
 14         m_AccessApp.Visible = visible;
 15     }
 16 
 17     /// <summary>
 18     /// 打开数据库
 19     /// </summary>
 20     /// <param name="filePath">Access数据库文件路径</param>
 21     /// <param name="exclusive">是否共享</param>
 22     /// <param name="bstrPassword">密码</param>
 23     public void OpenDb(string filePath, bool exclusive = false, string bstrPassword = "")
 24     {
 25         m_AccessApp.OpenCurrentDatabase(filePath, exclusive, bstrPassword);
 26         m_Database = m_AccessApp.CurrentDb();
 27     }
 28 
 29     /// <summary>
 30     /// 获取当前数据库中所有表名称集合
 31     /// </summary>
 32     /// <returns>所有表名称集合</returns>
 33     public List<string> GetTableNames()
 34     {
 35         List<string> tableNames = new List<string>();
 36         dynamic tableDefs = m_Database.TableDefs;
 37         foreach (dynamic tableDef in tableDefs)
 38         {
 39             tableNames.Add(tableDef.Name);
 40         }
 41 
 42         return tableNames;
 43     }
 44 
 45     /// <summary>
 46     /// 加载表数据
 47     /// </summary>
 48     /// <param name="tableName">表名称</param>
 49     /// <returns>表数据</returns>
 50     public List<List<string>> LoadTable(string tableName)
 51     {
 52         dynamic recordSet = m_Database.OpenRecordset(tableName);
 53         int fieldsCount = recordSet.Fields.Count;
 54         List<List<string>> data = new List<List<string>>();
 55         if (fieldsCount > 0)
 56         {
 57             try
 58             {
 59                 List<string> fieldNames = new List<string>();
 60                 for (int i = 0; i < fieldsCount; i++)
 61                 {
 62                     fieldNames.Add(recordSet.Fields[i].Name);
 63                 }
 64                 data.Add(fieldNames);
 65                 if (!recordSet.EOF)
 66                 {
 67                     recordSet.MoveFirst();
 68                     while (!recordSet.EOF)
 69                     {
 70                         object[] dataRow = recordSet.GetRows();// 返回一维数组
 71                         List<string> dataRowStr = new List<string>();
 72                         for (int i = 0; i < dataRow.Length; i++)
 73                         {
 74                             dataRowStr.Add(dataRow[i] == null ? "" : dataRow[i].ToString());
 75                         }
 76                         data.Add(dataRowStr);
 77                     }
 78                 }
 79             }
 80             catch (Exception ex)
 81             {
 82                 throw new Exception(ex.Message);
 83             }
 84             finally
 85             {
 86                 if (recordSet != null)
 87                 {
 88                     recordSet.Close();
 89                     ((IDisposable)recordSet).Dispose();
 90                     recordSet = null;
 91                 }
 92             }
 93         }
 94 
 95         return data;
 96     }
 97 
 98     /// <summary>
 99     /// 添加新纪录
100     /// </summary>
101     /// <param name="tableName">表格名称</param>
102     /// <param name="data">数据</param>
103     public void AddNewRecord(string tableName, List<Dictionary<string, object>> data)
104     {
105         try
106         {
107             m_Recordset = m_Database.OpenRecordset(tableName, 1);// 1=RecordsetTypeEnum.dbOpenTable
108             int fieldsCount = m_Recordset.Fields.Count;
109             List<string> fieldNames = new List<string>();
110             for (int i = 0; i < fieldsCount; i++)
111             {
112                 fieldNames.Add(m_Recordset.Fields[i].Name);
113             }
114             for (int rowIndex = 0; rowIndex < data.Count; rowIndex++)
115             {
116                 m_Recordset.AddNew();
117                 foreach (string fieldName in fieldNames)
118                 {
119                     m_Recordset.Fields[fieldName].Value = data[rowIndex][fieldName];
120                 }
121                 m_Recordset.Update();
122             }
123         }
124         catch(Exception ex)
125         {
126             throw new Exception(ex.Message);
127         }
128         finally
129         {
130             if (m_Recordset != null)
131             {
132                 m_Recordset.Close();
133                 ((IDisposable)m_Recordset).Dispose();
134                 m_Recordset = null;
135             }
136         }
137     }
138 
139     /// <summary>
140     /// 更新表格数据
141     /// </summary>
142     /// <param name="tableName">表格名称</param>
143     /// <param name="data">数据</param>
144     public void UpdateTable(string tableName, List<Dictionary<string, string>> data)
145     {
146         try
147         {
148             m_Recordset = m_Database.OpenRecordset(tableName, 1);// 1=RecordsetTypeEnum.dbOpenTable
149             m_Recordset.MoveFirst();
150             for (int rowIndex = 0; rowIndex < data.Count; rowIndex++)
151             {
152                 m_Recordset.Edit();
153                 foreach (string fieldName in data[rowIndex].Keys)
154                 {
155                     m_Recordset.Fields[fieldName].Value = data[rowIndex][fieldName];
156                 }
157                 m_Recordset.Update();
158                 m_Recordset.MoveNext();
159             }
160         }
161         catch (Exception ex)
162         {
163             throw new Exception(ex.Message);
164         }
165         finally
166         {
167             if (m_Recordset != null)
168             {
169                 m_Recordset.Close();
170                 ((IDisposable)m_Recordset).Dispose();
171                 m_Recordset = null;
172             }
173         }
174     }
175 
176     /// <summary>
177     /// 关闭
178     /// </summary>
179     public void Close()
180     {
181         if (m_Database != null)
182         {
183             m_Database.Close();
184             ((IDisposable)m_Database).Dispose();
185             m_Database = null;
186         }
187         if (m_AccessApp != null)
188         {
189             m_AccessApp.CloseCurrentDatabase();
190             // m_AccessApp.Quit();// 导致最后会弹出Access主页面
191             ((IDisposable)m_AccessApp).Dispose();
192             m_AccessApp = null;
193         }
194         GC.Collect();
195     }
196 }
View Code
 
通过 dynamic 构建的 COM 对象，在使用完成后都要手动关闭销毁，比如代码中的 m_AccessApp, m_Database, m_Recordset 三个对象，否则只是将 m_AccessApp 关闭清空释放掉，Access 进程还是无法关闭，在程序关闭之前，始终都会有一个空白的无法关闭的 Access 界面；

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

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

posted @ 2015-02-04 14:57  01星河  阅读(750)  评论(0)  编辑  收藏  举报