[Access] C # accessing Access files through COM components

Description: 1, the use of dynamic calls COM components, for. NET 4.0 supports dynamic version of the Can, 2, execution speed is not flattering, just because to be used in Silverlight OOB mode only study one or two; 3, test environment . NET 4.5+ Silverlight 5.0+ Visual Studio4, see the following helper class (reference using System.Runtime.InteropServices.Automation;）：

public class Slaccesshelper{private Dynamic m_accessapp;//access.applicationprivate dynamic m_database;// Databaseprivate Dynamic m_recordset;///<summary>///constructor///</summary>///<param name= "visible" > Whether access is visible </param>public slaccesshelper (bool visible) {M_accessapp = Automationfactory.createobject (" Access.Application "); m_accessapp.visible = Visible;} <summary>///Open Database///</summary>///<param name= "filePath" >access database file path </param>///< Param name= "Exclusive" > whether to share </param>///<param name= "bstrpassword" > Password </param>public void opendb (String filePath, bool exclusive = False, string bstrpassword = "") {M_accessapp.opencurrentdatabase (FilePath, exclusive, bstrpassword); m_database = M_accessapp.currentdb ();} <summary>///gets the collection of all the table names in the current database///</summary>///<returns> all table name collections </returns>public list< String> gettablenames () {list<string> tablenames = new list<string> ();d ynamic tableDefs = M_database.tabledefs;foreach (Dynamic TableDef in tabledefs) {tablenames.add (tabledef.name);} return tablenames;} <summary>///loading table data///</summary>///<param name= "tableName" > table name </param>///<returns > table Data </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 ();//Returns a one-dimensional array list<string> datarowstr = new list<string> (); for (int i = 0; i < Datarow.lengt H 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>///Add new Record///</summary>///<param name= "tableName" > table name </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.dbopentableint Fieldscount = M_Recordset.Fields.Count; list<string> FieldNames = new list<string> (); for (int i = 0; i < Fieldscount; i++) {Fieldnames.add (m_records Et. 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>///updating tabular data///</summary>///<param name= "tableName" > table name </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.dbopentablem_ 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>///Close//</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 ();//causes the Access Main page ((IDisposable) M_accessapp) to pop up at the end. Dispose (); m_accessapp = null;} Gc. Collect ();}}

COM objects built through the dynamic, after the use of the completion of the manual to close the destruction, such as the Code of M_accessapp, M_database, m_recordset three objects, otherwise just the M_accessapp off the empty release, The access process cannot be closed, and there will always be a blank access interface that cannot be closed until the program is closed;

Handling dynamic and C # type conversions in loops can reduce program execution efficiency, such as looping through table names like the Gettablenames method, which takes two or three seconds, so try to resemble object[] DataRow = Recordset.getrows (); Direct access to all of the data, and then traverse the processing, will greatly improve the efficiency of execution;

To modify data in Access, you must first m_recordset.edit (); will allow you to edit the content;

[Access] C # accessing Access files through COM components