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