/// <summary> /// 執行 SQL Insert, Update, Delete 指令 /// </summary> public string NonQuery(string sqlcmd) { try { db.BeginTrans(); //db.Execute(sqlcmd, DAO.RecordsetOptionEnum.dbDenyWrite); db.Execute(sqlcmd); db.CommitTrans(); return("Succeed"); } catch (Exception e) { db.Rollback(); Debug.WriteLine(e.Message); FileRW.Write(true, Environment.CurrentDirectory + @"\NxESL_Log.txt", $"\r\n{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")} : NonQuery Error : {sqlcmd}"); return("Failed"); } }
/// <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 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); }
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> /// 執行 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"); }