// TODO: Determine what type of table we are dealing with e.g. normal local table, linked table and export appropriately. public int ExportTables() { int tablesExported = 0; string textToWrite; string fileName; string filePath; Dao.Database database = application.CurrentDb(); foreach (Dao.TableDef tableDef in database.TableDefs) { // Don't export system tables. if (tableDef.Name.StartsWith("MSys")) { continue; } fileName = string.Format("{0}.table.{1}.txt", databaseFileName, tableDef.Name); filePath = Path.Combine(outputDirectory, fileName); textToWrite = ""; textToWrite += string.Format("Name: {0}", tableDef.Name) + Environment.NewLine; textToWrite += string.Format("Connect: {0}", tableDef.Connect) + Environment.NewLine; textToWrite += string.Format("SourceTableName: {0}", tableDef.SourceTableName); WriteToFile(filePath, textToWrite); tablesExported++; } database = null; logger.Log(string.Format("Exported {0} tables.", tablesExported)); return(tablesExported); }
public int ExportQueries() { int queriesExported = 0; string textToWrite; string fileName; string filePath; Dao.Database database = application.CurrentDb(); foreach (Dao.QueryDef queryDef in database.QueryDefs) { fileName = string.Format("{0}.query.{1}.txt", databaseFileName, queryDef.Name); filePath = Path.Combine(outputDirectory, fileName); textToWrite = ""; textToWrite += string.Format("Name: {0}", queryDef.Name) + Environment.NewLine; textToWrite += string.Format("Connect: {0}", queryDef.Connect) + Environment.NewLine; textToWrite += string.Format("SQL: {0}", queryDef.SQL); WriteToFile(filePath, textToWrite); queriesExported++; } database = null; logger.Log(string.Format("Exported {0} queries.", queriesExported)); return(queriesExported); }
public override void Init(int flowCount, long flowRecordCount) { DBEngine dbEng = new DBEngine(); Microsoft.Office.Interop.Access.Dao.Database db = dbEng.CreateDatabase(String.Format(@"{0}\{1}", DataDirectory, Name), LanguageConstants.dbLangGeneral); db.Close(); connections = new IDbConnection[flowCount]; commands = new IDbCommand[flowCount]; for (int i = 0; i < flowCount; i++) { IDbConnection connection = new OleDbConnection(ConnectionString); connection.Open(); connections[i] = connection; if (connection.State == ConnectionState.Closed) { connection.Open(); } commands[i] = CreateCommand(connection); } connections[0].ExecuteNonQuery(CreateTableQuery()); }
public void bulkInsert(DataTable inputTable) { DAO.DBEngine dbEngine = new DAO.DBEngine(); DAO.Database db = dbEngine.OpenDatabase(FilePath); DAO.Recordset rs = db.OpenRecordset(inputTable.TableName); DAO.Field[] myFields = new DAO.Field[inputTable.Columns.Count]; int k = 0; foreach (DataColumn column in inputTable.Columns) { myFields[k] = rs.Fields[column.ColumnName]; k++; } for (int i = 0; i < inputTable.Rows.Count; i++) { rs.AddNew(); k = 0; foreach (DataColumn column in inputTable.Columns) { Type t = column.DataType; if (t == typeof(string)) { myFields[k].Value = inputTable.Rows[i].Field <string>(k); } else { myFields[k].Value = inputTable.Rows[i].Field <Single>(k); } k++; } rs.Update(); } rs.Close(); db.Close(); }
/// <summary> /// 設定MsAccess的建構式. /// </summary> public MsAccessDAO(string dbName) { try { db = dbEngine.OpenDatabase(dbName); } catch (Exception e) { throw e; } }
private void button2_Click(object sender, EventArgs e) { string path = "z:\\blank.accdb"; DAO.DBEngine dbe = new DAO.DBEngine(); DAO.Database db = dbe.OpenDatabase(path); DAO.TableDef tbdef = db.CreateTableDef(); tbdef.Name = "linkedtable2"; //Whatever you want the linked table to be named tbdef.Connect = "ODBC;Driver={Oracle in XE};dsn={Oracle - NXESL};dbq=XE;Uid=nxesl;Pwd=admin;Database=NXESL;Trusted_Connection=YES"; tbdef.SourceTableName = "NXESL.Article"; //Whatever the SQL Server Table Name is db.TableDefs.Append(tbdef); }
public bool BulkCopy(IDataReader reader, string tableName, string command = null, SqlBulkCopyOptions options = SqlBulkCopyOptions.Default) { HDAAccessReader accessReader = reader as HDAAccessReader; if (accessReader == null) { CallException("批量拷贝数据到Access数据库失败:BulkCopy 函数中,IDataReader 转 HDAAccessReader 失败!"); return(false); } DAO.DBEngine dbEngine = new DAO.DBEngine(); DAO.Database db = dbEngine.OpenDatabase(DataHelper.DataPath); if (!string.IsNullOrEmpty(command)) { db.Execute(command); } DAO.Recordset rs = db.OpenRecordset(tableName); try { DAO.Field[] myFields = new DAO.Field[accessReader.FieldCount]; myFields[0] = rs.Fields[accessReader.GetName(0)]; myFields[1] = rs.Fields[accessReader.GetName(1)]; myFields[2] = rs.Fields[accessReader.GetName(2)]; while (accessReader.Read()) { rs.AddNew(); myFields[0].Value = accessReader.GetValue(0); myFields[1].Value = accessReader.GetValue(1); myFields[2].Value = accessReader.GetValue(2); rs.Update(); } } catch (Exception e) { CallException(e.Message); return(false); } finally { rs.Close(); db.Close(); } return(true); }
static void Main(string[] args) { var accApp = new Microsoft.Office.Interop.Access.Application(); accApp.OpenCurrentDatabase(new FileInfo("db.accdb").FullName); Microsoft.Office.Interop.Access.Dao.Database cdb = accApp.CurrentDb(); foreach (TableDef item in cdb.TableDefs) { Console.WriteLine(item.Name); } }
/// <summary> /// 数据批量插入到数据库 /// </summary> /// <param name = "sList" ></ param > public static void add(List <Model.Record> sList) { DAO.DBEngine dbEngine = new DAO.DBEngine(); DAO.Database db = dbEngine.OpenDatabase(FileName); DAO.Recordset rs = db.OpenRecordset("DataSheet"); DAO.Field[] myFields = new DAO.Field[18]; myFields[0] = rs.Fields["serialNumber"]; myFields[1] = rs.Fields["operatorName"]; myFields[2] = rs.Fields["jobNumber"]; myFields[3] = rs.Fields["voltage"]; myFields[4] = rs.Fields["current"]; myFields[5] = rs.Fields["power"]; myFields[6] = rs.Fields["frequency"]; myFields[7] = rs.Fields["energy"]; myFields[8] = rs.Fields["temperature1"]; myFields[9] = rs.Fields["temperature2"]; myFields[10] = rs.Fields["temperature3"]; myFields[11] = rs.Fields["temperature4"]; myFields[12] = rs.Fields["flow1"]; myFields[13] = rs.Fields["flow2"]; myFields[14] = rs.Fields["flow3"]; myFields[15] = rs.Fields["pressure"]; myFields[16] = rs.Fields["heatTime"]; myFields[17] = rs.Fields["coolingTime"]; for (int i = 0; i < sList.Count; i++) { rs.AddNew(); myFields[0].Value = sList[i].serialNumber; myFields[1].Value = sList[i].operatorName; myFields[2].Value = sList[i].jobNumber; myFields[3].Value = sList[i].voltage; myFields[4].Value = sList[i].current; myFields[5].Value = sList[i].power; myFields[6].Value = sList[i].frequency; myFields[7].Value = sList[i].energy; myFields[8].Value = sList[i].temperature1; myFields[9].Value = sList[i].temperature2; myFields[10].Value = sList[i].temperature3; myFields[11].Value = sList[i].temperature4; myFields[12].Value = sList[i].flow1; myFields[13].Value = sList[i].flow2; myFields[14].Value = sList[i].flow3; myFields[15].Value = sList[i].pressure; myFields[16].Value = sList[i].heatTime; myFields[17].Value = sList[i].coolingTime; rs.Update(); } rs.Close(); db.Close(); }
public void UploadDataToAccess(DataTable sourceData, string DBPath, string TblName, bool ClearTbl) { Boolean CheckFl = false; DAO.DBEngine dbEngine = new DAO.DBEngine(); try { DAO.Database db = dbEngine.OpenDatabase(DBPath); DAO.Recordset AccessRecordset = db.OpenRecordset(TblName); DAO.Field[] AccessFields = new DAO.Field[sourceData.Columns.Count]; //Whether to clear table before pasting if (ClearTbl) { db.Execute("DELETE FROM " + TblName); } for (Int32 rowCount = 0; rowCount < sourceData.Rows.Count; rowCount++) { AccessRecordset.AddNew(); for (Int32 colCount = 0; colCount < sourceData.Columns.Count; colCount++) { if (!CheckFl) { AccessFields[colCount] = AccessRecordset.Fields[sourceData.Columns[colCount].ColumnName]; } AccessFields[colCount].Value = sourceData.Rows[rowCount][colCount]; } try { AccessRecordset.Update(); } catch (Exception ex) { MessageBox.Show(ex.ToString() + sourceData.Rows[rowCount][3].ToString()); } CheckFl = true; } AccessRecordset.Close(); db.Close(); } finally { System.Runtime.InteropServices.Marshal.ReleaseComObject(dbEngine); dbEngine = null; } }
/// <summary> /// 關閉連線. /// </summary> public void ConnClose() { try { if (rs != null) { rs.Close(); rs = null; } if (db != null) { db.Close(); db = null; } } catch (Exception e) { throw e; } }
/// <summary> /// 写入Access /// </summary> /// <param name="path">csv文件路径</param> public static void InsertTable(string path, string strDbpath, string strSelPath) { string strTableName = Path.GetFileNameWithoutExtension(path); DAO.DBEngine dbEngine = new DAO.DBEngine(); DAO.Database db = dbEngine.OpenDatabase(strDbpath); string sql = "SELECT * INTO [" + strTableName + "] FROM [Text;FMT=Delimited;DATABASE=" + strSelPath + ";HDR=No].[" + strTableName + ".csv]"; db.Execute(sql); //删除垃圾数据 sql = "delete from " + strTableName + " where F2 is null or F2 = '时间'"; db.Execute(sql); //增加主键 sql = "alter table " + strTableName + " add PRIMARY KEY(F1,F2)"; db.Execute(sql); db.Close(); }
public static int BulkExportToAccess(DataTable dtOutData, String DBPath, String TableNm) { DAO.DBEngine dbEngine = new DAO.DBEngine(); Boolean CheckFl = false; int rowInserted = 0; try { DAO.Database db = dbEngine.OpenDatabase(DBPath); DAO.Recordset AccesssRecordset = db.OpenRecordset(TableNm); DAO.Field[] AccesssFields = new DAO.Field[dtOutData.Columns.Count]; //Loop on each row of dtOutData for (Int32 rowCounter = 0; rowCounter < dtOutData.Rows.Count; rowCounter++) { AccesssRecordset.AddNew(); //Loop on column for (Int32 colCounter = 0; colCounter < dtOutData.Columns.Count; colCounter++) { // for the first time... setup the field name. if (!CheckFl) { AccesssFields[colCounter] = AccesssRecordset.Fields[dtOutData.Columns[colCounter].ColumnName]; } AccesssFields[colCounter].Value = dtOutData.Rows[rowCounter][colCounter]; } AccesssRecordset.Update(); CheckFl = true; rowInserted = rowCounter; } AccesssRecordset.Close(); db.Close(); } finally { System.Runtime.InteropServices.Marshal.ReleaseComObject(dbEngine); dbEngine = null; } return(rowInserted); }
/// <summary> /// 執行 SQL Insert, Update, Delete 指令 /// </summary> public string NonQueryDAO(string dbName, string[] sqlcmds) { DAO.DBEngine dbe = new DAO.DBEngine(); DAO.Database db = dbe.OpenDatabase(dbName); try { db.BeginTrans(); foreach (string sql in sqlcmds) { if (!string.IsNullOrWhiteSpace(sql)) { db.Execute(sql); } } db.CommitTrans(); } catch { db.Rollback(); return("FAIL"); } return("OK"); }