[Access] C # Access an Access file through the COM Component

Note: 1. Using dynamic to call the COM component is applicable. NET4.0 and later versions support the dynamic version. 2. The execution speed is not flattering. It is only because it is used in the SilverlightOOB mode. 3. test environment. net4.5 + Silverlight5.0 + VisualStudio20134, see the following helper class (using must be referenced

Note: 1. Using dynamic to call the COM component is applicable. NET 4.0 and above support the dynamic version; 2, the execution speed is not flattering, but only because it is used in the Silverlight OOB mode to study one or two; 3, the test environment. net 4.5 + Silverlight 5.0 + Visual Studio 2013 4, see the following helper class (using must be referenced

Note:

1. Using dynamic to call the COM component is applicable to. NET 4.0 and later versions that support dynamic;

2. The execution speed is not flattering, but it is only because it is used in the Silverlight OOB mode;

3. Test Environment. Net 4.5+Silverlight 5.0+Visual Studio 2013

4. See the following helper class (to be referencedUsing 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 /// constructor 9 ///10 ///Whether Access is visible11 public SLAccessHelper (bool visible) 12 {13 m_AccessApp = AutomationFactory. CreateObject ("Access. Application"); 14 m_AccessApp.Visible = visible; 15} 16 17 ///18 /// open the database 19 ///20 ///Access database file path21 ///Share?22 ///Password23 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 /// obtain the name set of all tables in the current database 31 ///32 ///
 
  
All table names
 33 public List
 
  
GetTableNames () 34 {35 List
  
   
TableNames = new List
   
    
(); 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 // load table data 47 ///48 ///
    Table Name49 ///
    
     
Table Data
    50 public List
    
     
> LoadTable (string tableName) 51 {52 dynamic recordSet = m_Database.OpenRecordset (tableName); 53 int fieldsCount = recordSet. Fields. Count; 54 List
     
      
> Data = new List
      
        > (); 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 (); // return one-dimensional array 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 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 // Add new record 100 ///101 ///
           Table Name102 ///
           Data103 public void AddNewRecord (string tableName, List
           
             > Data) 104 {105 try106 {107 m_Recordset = m_Database.OpenRecordset (tableName, 1); // 1 = RecordsetTypeEnum. dbOpenTable108 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 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 finally129 {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 // update table data 141 ///142 ///
              Table Name143 ///
              Data144 public void UpdateTable (string tableName, List
              
                > Data) 145 {146 try147 {148 m_Recordset = m_Database.OpenRecordset (tableName, 1); // 1 = RecordsetTypeEnum. dbOpenTable149 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_Records Et. MoveNext (); 159} 160} 161 catch (Exception ex) 162 {163 throw new Exception (ex. Message); 164} 165 finally166 {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 // close 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.closecur1_database (); 190 // m_AccessApp.Quit (); // The Access homepage 191 (IDisposable) m_AccessApp) is displayed ). dispose (); 192 m_AccessApp = null; 193} 194 GC. collect (); 195} 196}
              
             
            
           
          
         
        
       
      
     
    
   
  
 

View Code

The COM Object built through dynamic must be manually closed and destroyed after use, for example, m_AccessApp, m_Database, and m_Recordset objects in the Code. Otherwise, only m_AccessApp is closed and cleared and released, the Access process still cannot be closed. Before the program is closed, there will always be a blank Access interface that cannot be closed;

Processing dynamic and C # type conversions in a loop will reduce program execution efficiency. For example, it takes two or three seconds to traverse the table name in a loop in the GetTableNames method, so try to be like object [] dataRow = recordSet. getRows (); directly obtains all the data and then traverses and processes it, which greatly improves the execution efficiency;

To modify data in Access, you must firstM_Recordset.Edit ();To allow you to edit the content;