// 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");
}
