当前位置:Gxlcms > mysql > [Access]C#通过COM组件访问Access文件

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

时间:2021-07-01 10:21:17 帮助过:40人阅读

说明: 1,采用dynamic调用COM组件,适用于.NET 4.0以上支持dynamic版本的才可以; 2,执行速度不敢恭维,只是因为要用于Silverlight OOB模式中才研究一二; 3,测试环境 .net 4.5 + Silverlight 5.0 + Visual Studio 2013 4,见如下helper类(需引用 using

说明:

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     /// 
  8     /// 构造函数
  9     /// 
 10     /// Access是否可见
 11     public SLAccessHelper(bool visible)
 12     {
 13         m_AccessApp = AutomationFactory.CreateObject("Access.Application");
 14         m_AccessApp.Visible = visible;
 15     }
 16 
 17     /// 
 18     /// 打开数据库
 19     /// 
 20     /// Access数据库文件路径
 21     /// 是否共享
 22     /// 密码
 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     /// 
 30     /// 获取当前数据库中所有表名称集合
 31     /// 
 32     /// 所有表名称集合
 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     /// 
 46     /// 加载表数据
 47     /// 
 48     /// 表名称
 49     /// 表数据
 50     public Liststring>> LoadTable(string tableName)
 51     {
 52         dynamic recordSet = m_Database.OpenRecordset(tableName);
 53         int fieldsCount = recordSet.Fields.Count;
 54         Liststring>> data = new Liststring>>();
 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     /// 
 99     /// 添加新纪录
100     /// 
101     /// 表格名称
102     /// 数据
103     public void AddNewRecord(string tableName, Liststring, 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     /// 
140     /// 更新表格数据
141     /// 
142     /// 表格名称
143     /// 数据
144     public void UpdateTable(string tableName, Liststring, 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     /// 
177     /// 关闭
178     /// 
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();才会允许你编辑其中的内容;

人气教程排行