说明: 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 classSLAccessHelper2 {3 private dynamic m_AccessApp;//Access.Application
4 private dynamic m_Database;//Database
5 privatedynamic m_Recordset;6
7 ///
8 ///构造函数9 ///
10 /// Access是否可见
11 public SLAccessHelper(boolvisible)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 ListGetTableNames()34 {35 List tableNames = new List();36 dynamic tableDefs =m_Database.TableDefs;37 foreach (dynamic tableDef intableDefs)38 {39 tableNames.Add(tableDef.Name);40 }41
42 returntableNames;43 }44
45 ///
46 ///加载表数据47 ///
48 /// 表名称
49 /// 表数据
50 public Liststring>> LoadTable(stringtableName)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 fieldNames = new List();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 dataRowStr = new List();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 newException(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 returndata;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 fieldNames = new List();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 infieldNames)118 {119 m_Recordset.Fields[fieldName].Value =data[rowIndex][fieldName];120 }121 m_Recordset.Update();122 }123 }124 catch(Exception ex)125 {126 throw newException(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 indata[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 newException(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 voidClose()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();才会允许你编辑其中的内容;
本文原创发布php中文网,转载请注明出处,感谢您的尊重!