public void SwitchCarTransaction(Car car)
{
trans?.Commit();
OracleCommand cmd1 = new OracleCommand("select * from cars where cnr = :cid for update nowait", conn);
cmd1.Parameters.Add(new OracleParameter("cid", car.CarId));
trans = conn.BeginTransaction(IsolationLevel);
cmd1.Transaction = trans;
cmd1.ExecuteNonQuery();
}
/// <summary>
/// Completes the transaction
/// </summary>
public void Complete()
{
// Note: if the local transaction is null, it means that we're part of a larger transaction
// (such as TransactionScope or externally managed) and so we don't control commit.
_transaction?.Commit();
// We don't nullify the _transaction, but this should not be used after a commit anyway,
// as there is no more active transaction.
// Note: keeping the transaction means it will be disposed... well in Dispose() of course.
}
/// <summary>
/// 表批量写入
/// 根据行数据 RowState 状态新增、修改
/// </summary>
/// <param name="dt">数据表</param>
/// <param name="sqlEmpty">查询空表脚本，默认*，可选列，会影响数据更新的列</param>
/// <param name="dataAdapter">执行前修改（命令行脚本、超时等信息）</param>
/// <param name="openTransaction">开启事务，默认开启</param>
/// <returns></returns>
public int BulkBatchOracle(DataTable dt, string sqlEmpty = null, Action <OracleDataAdapter> dataAdapter = null, bool openTransaction = true)
{
return(SafeConn(() =>
{
var connection = (OracleConnection)Connection;
OracleTransaction transaction = openTransaction ? (OracleTransaction)(Transaction = connection.BeginTransaction()) : null;
var cb = new OracleCommandBuilder();
if (string.IsNullOrWhiteSpace(sqlEmpty))
{
var sntn = SqlSNTN(dt.TableName, dt.Namespace, SharedEnum.TypeDB.Oracle);
sqlEmpty = SqlEmpty(sntn);
}
cb.DataAdapter = new OracleDataAdapter
{
SelectCommand = new OracleCommand(sqlEmpty, connection)
};
cb.ConflictOption = ConflictOption.OverwriteChanges;
var da = new OracleDataAdapter
{
InsertCommand = cb.GetInsertCommand(true),
UpdateCommand = cb.GetUpdateCommand(true)
};
da.InsertCommand.CommandTimeout = 300;
da.UpdateCommand.CommandTimeout = 300;
//执行前修改
dataAdapter?.Invoke(da);
var num = da.Update(dt);
transaction?.Commit();
return num;
}));
}
//处理数据插入到数据库中
private void OpreratorDealData(DataView dv, string status)
{
MitsUtils miutils = new MitsUtils();
DataTable dtRllxParam = OracleHelper.ExecuteDataSet(OracleHelper.conn, "select * from RLLX_PARAM", null).Tables[0];
string rllx = string.Empty;
DataTable TempDt = new DataTable();
switch (radioGroup1.SelectedIndex)
{
case 0:
rllx = "传统能源";
TempDt = miutils.C2E(miutils.dictCTNY, dv.Table, MitsUtils.CTNY);
break;
case 1:
rllx = "非插电式混合动力";
TempDt = miutils.C2E(miutils.dictFCDSHHDL, dv.Table, MitsUtils.FCDSHHDL);
break;
case 2:
rllx = "插电式混合动力";
TempDt = miutils.C2E(miutils.dictCDSHHDL, dv.Table, MitsUtils.CDSHHDL);
break;
case 3:
rllx = "纯电动";
TempDt = miutils.C2E(miutils.dictCDD, dv.Table, MitsUtils.CDD);
break;
case 4:
rllx = "燃料电池";
TempDt = miutils.C2E(miutils.dictRLDC, dv.Table, MitsUtils.RLDC);
break;
}
using (OracleConnection con = new OracleConnection(OracleHelper.conn))
{
con.Open();
OracleTransaction tra = null; //创建事务，开始执行事务
try
{
tra = con.BeginTransaction();
foreach (DataRow drMain in TempDt.Rows)
{
#region 待生成的燃料基本信息数据存入燃料基本信息表
string vin = drMain["VIN"].ToString().Trim();
string sqlDeleteBasic = String.Format("DELETE FROM FC_CLJBXX WHERE VIN='{0}'", vin);
OracleHelper.ExecuteNonQuery(tra, sqlDeleteBasic, null);
DateTime clzzrqDate;
try
{
clzzrqDate = DateTime.ParseExact(drMain["CLZZRQ"].ToString().Trim(), "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture);
}
catch (Exception)
{
clzzrqDate = Convert.ToDateTime(drMain["CLZZRQ"]);
}
OracleParameter clzzrq = new OracleParameter("@CLZZRQ", clzzrqDate)
{
OracleDbType = OracleDbType.Date
};
DateTime uploadDeadlineDate = miutils.QueryUploadDeadLine(clzzrqDate);
OracleParameter uploadDeadline = new OracleParameter("@UPLOADDEADLINE", uploadDeadlineDate)
{
OracleDbType = OracleDbType.Date
};
OracleParameter creTime = new OracleParameter("@CREATETIME", DateTime.Now)
{
OracleDbType = OracleDbType.Date
};
OracleParameter upTime = new OracleParameter("@UPDATETIME", DateTime.Now)
{
OracleDbType = OracleDbType.Date
};
string qtxx;
if (dv.Table.Columns.Contains("CT_QTXX"))
{
qtxx = drMain["CT_QTXX"].ToString().Trim();
}
else
{
qtxx = string.Empty;
}
OracleParameter[] param =
{
new OracleParameter("@VIN", drMain["VIN"].ToString().Trim()),
new OracleParameter("@USER_ID", Utils.localUserId),
new OracleParameter("@QCSCQY", drMain["QCSCQY"].ToString().Trim()),
new OracleParameter("@JKQCZJXS", drMain["JKQCZJXS"].ToString().Trim()),
clzzrq,
uploadDeadline,
new OracleParameter("@CLXH", drMain["CLXH"].ToString().Trim()),
new OracleParameter("@CLZL", drMain["CLZL"].ToString().Trim()),
new OracleParameter("@RLLX", drMain["RLLX"].ToString().Trim()),
new OracleParameter("@ZCZBZL", drMain["ZCZBZL"].ToString().Trim()),
new OracleParameter("@ZGCS", drMain["ZGCS"].ToString().Trim()),
new OracleParameter("@LTGG", drMain["LTGG"].ToString().Trim()),
new OracleParameter("@ZJ", drMain["ZJ"].ToString().Trim()),
new OracleParameter("@TYMC", drMain["TYMC"].ToString().Trim()),
new OracleParameter("@YYC", drMain["YYC"].ToString().Trim()),
new OracleParameter("@ZWPS", drMain["ZWPS"].ToString().Trim()),
new OracleParameter("@ZDSJZZL", drMain["ZDSJZZL"].ToString().Trim()),
new OracleParameter("@EDZK", drMain["EDZK"].ToString().Trim()),
new OracleParameter("@LJ", drMain["LJ"].ToString().Trim()),
new OracleParameter("@QDXS", drMain["QDXS"].ToString().Trim()),
new OracleParameter("@JYJGMC", drMain["JYJGMC"].ToString().Trim()),
new OracleParameter("@JYBGBH", drMain["JYBGBH"].ToString().Trim()),
new OracleParameter("@HGSPBM", drMain["HGSPBM"].ToString().Trim()),
new OracleParameter("@QTXX", qtxx),
new OracleParameter("@STATUS", status),
creTime,
upTime
};
OracleHelper.ExecuteNonQuery(tra, (string)@"INSERT INTO FC_CLJBXX
( VIN,USER_ID,QCSCQY,JKQCZJXS,CLZZRQ,UPLOADDEADLINE,CLXH,CLZL,
RLLX,ZCZBZL,ZGCS,LTGG,ZJ,
TYMC,YYC,ZWPS,ZDSJZZL,EDZK,LJ,
QDXS,JYJGMC,JYBGBH,HGSPBM,QTXX,STATUS,CREATETIME,UPDATETIME
) VALUES
( @VIN,@USER_ID,@QCSCQY,@JKQCZJXS,@CLZZRQ,@UPLOADDEADLINE,@CLXH,@CLZL,
@RLLX,@ZCZBZL,@ZGCS,@LTGG,@ZJ,
@TYMC,@YYC,@ZWPS,@ZDSJZZL,@EDZK,@LJ,
@QDXS,@JYJGMC,@JYBGBH,@HGSPBM,@QTXX,@STATUS,@CREATETIME,@UPDATETIME)", param);
#endregion
#region 插入参数信息
string sqlDelParam = String.Format("DELETE FROM RLLX_PARAM_ENTITY WHERE VIN ='{0}'", vin);
OracleHelper.ExecuteNonQuery(tra, sqlDelParam, null);
// 待生成的燃料参数信息存入燃料参数表
var rows = dtRllxParam.Select(String.Format("FUEL_TYPE='{0}' and STATUS='1'", rllx));
foreach (DataRow drParam in rows)
{
string paramCode = drParam["PARAM_CODE"].ToString().Trim();
OracleParameter[] paramList =
{
new OracleParameter("@PARAM_CODE", paramCode),
new OracleParameter("@VIN", drMain["VIN"].ToString().Trim()),
new OracleParameter("@PARAM_VALUE", drMain[paramCode].ToString().Trim()),
new OracleParameter("@V_ID", "")
};
OracleHelper.ExecuteNonQuery(tra, (string)@"INSERT INTO RLLX_PARAM_ENTITY
(PARAM_CODE,VIN,PARAM_VALUE,V_ID)
VALUES
(@PARAM_CODE,@VIN,@PARAM_VALUE,@V_ID)", paramList);
}
#endregion
}
tra.Commit();
}
catch (Exception ex)
{
tra.Rollback();
throw ex;
}
finally
{
tra.Dispose();
con.Close();
}
}
}
private void BtnNext_Click(object sender, EventArgs e)
{
OracleConnection conn = new OracleConnection(Global.ConnectionString);
OracleTransaction tran = null;
if (Check())
{
buildingno = Tbbuildingno.Text;
unitno = Tbunitno.Text;
roomno = Tbroomno.Text;
floors = Nufloor.Value.ToString();
rooms = Nurooms.Value.ToString();
workplace = Tbworkplace.Text;
housetype = Cbhousetype.Text;
purpose = Cbroompurpose.Text;
usestatus = Cbroomstatus.Text;
contactway = Tbcontactway.Text;
try
{
conn.Open();
tran = conn.BeginTransaction();
OracleCommand cmd = conn.CreateCommand();
cmd.Connection = conn;
cmd.Transaction = tran;
cmd.CommandText = "update T_RESIDENT set buildingno=:buildingno,unitno=:unitno,roomno=:roomno,floors=:floors,rooms=:rooms,workplace=:workplace,purpose=:purpose,usestatus=:usestatus,contactway=:contactway,housetype=:housetype where idn=:idn and commid=:commid";
//rid, idn, fullname, sex, nation, birthdate, address, registerdept, validbegindate, validenddate, fingercount, fingerdesc, fingerleftbmp, fingerrightbmp, bigimage,smallimage,
//buildingno,unitno,roomno,floors,rooms,workplace,purpose,usestatus,contactway,housetype
//:buildingno,:unitno,:roomno,:floors,:rooms,:workplace,:purpose,:usestatus,:contactway,:housetype
cmd.Parameters.Add("buildingno", OracleDbType.NVarchar2, 18).Value = buildingno;
cmd.Parameters.Add("unitno", OracleDbType.NVarchar2, 32).Value = unitno;
cmd.Parameters.Add("roomno", OracleDbType.NVarchar2, 32).Value = roomno;
cmd.Parameters.Add("floors", OracleDbType.Int32).Value = floors;
cmd.Parameters.Add("rooms", OracleDbType.Int32).Value = rooms;
cmd.Parameters.Add("workplace", OracleDbType.NVarchar2, 32).Value = workplace;
cmd.Parameters.Add("purpose", OracleDbType.NVarchar2, 100).Value = purpose;
cmd.Parameters.Add("usestatus", OracleDbType.NVarchar2, 32).Value = usestatus;
cmd.Parameters.Add("contactway", OracleDbType.NVarchar2, 32).Value = contactway;
cmd.Parameters.Add("housetype", OracleDbType.NVarchar2, 32).Value = housetype;
cmd.Parameters.Add("idn", OracleDbType.NVarchar2, 18).Value = idn;
cmd.Parameters.Add("commid", OracleDbType.NVarchar2, 32).Value = commid;
cmd.ExecuteNonQuery();
tran.Commit();
LaInfo.Text = "保存成功.....";
System.Timers.Timer timersTimer = new System.Timers.Timer();
timersTimer.Interval = 2000;
timersTimer.Elapsed += new System.Timers.ElapsedEventHandler(theout);
timersTimer.SynchronizingObject = this;
timersTimer.Enabled = true;
}
catch (Exception er)
{
tran.Rollback();
LogHelper.WriteLog(LogFile.Error, er.ToString());
LaInfo.Text = "保存失败，请重试.....";
}
finally
{
conn.Close();
}
}
}
private void btnOk_Click(object sender, EventArgs e)
{
///////执行创建库体
OracleConnectionStringBuilder Connectstrbuilder = new OracleConnectionStringBuilder();
Connectstrbuilder.DataSource = this.m_Server;
Connectstrbuilder.UserID = this.m_User;
Connectstrbuilder.Password = this.m_Password;
Connectstrbuilder.Unicode = true;
Connectstrbuilder.PersistSecurityInfo = true;
////////获取表名和建库的SQL语句进行建库
OracleConnection Con = new OracleConnection(Connectstrbuilder.ConnectionString);
OracleCommand Com = null;
OracleTransaction Tra = null;
FrmProcessBar ProcBar = new FrmProcessBar(this.list_Table.Items.Count);
ProcBar.SetFrmProcessBarText("正在创建表");
Application.DoEvents();
//////开始创建///////
try
{
Con.Open(); ////////////////////打开连接
Tra = Con.BeginTransaction(); ////事务对象
}
catch (Exception eError)
{
SysCommon.Error.ErrorHandle.ShowFrmErrorHandle("提示", "打开数据库失败！\n原因：" + eError.Message);
// throw new Exception("打开数据库失败！\n原因：" + eError.Message);
ProcBar.Close();
return;
}
for (int i = 0; i < this.list_Table.Items.Count; i++)
{
ProcBar.SetFrmProcessBarValue((long)i);
Application.DoEvents();
if (this.list_Table.GetItemChecked(i))
{
string sTableName = this.list_Table.Items[i].ToString().Trim();
string sSQL = string.Empty;
if (this.m_TableDic.ContainsKey(sTableName))
{
/////获取建表用的SQL语句
bool bGet = this.m_TableDic.TryGetValue(sTableName, out sSQL);
if (bGet)
{
ProcBar.SetFrmProcessBarText("正在创建表：" + sTableName);
Application.DoEvents();
try
{
Com = new OracleCommand(sSQL, Con);
Com.Transaction = Tra;
Com.ExecuteNonQuery();
if (!m_CreatedTable.ContainsKey(sTableName))
{
m_CreatedTable.Add(sTableName, sSQL);
}
}
catch (Exception eError)
{
Tra.Rollback();
SysCommon.Error.ErrorHandle.ShowFrmErrorHandle("提示", "创建表：" + sTableName + "失败！\n原因：" + eError.Message);
// throw new Exception("创建表：" + sTableName + "失败！\n原因：" + eError.Message);
ProcBar.Close();
if (Con.State == ConnectionState.Open)
{
Con.Close();
}
return;
}
}
}
}
}
///////创建完成，关闭连接，提交事务
try
{
Tra.Commit();
SysCommon.Error.ErrorHandle.ShowFrmErrorHandle("提示", "库体创建完成");
ProcBar.Close();
}
catch (Exception eError)
{
SysCommon.Error.ErrorHandle.ShowFrmErrorHandle("提示", "库体创建失败！\n原因：" + eError.Message);
//throw new Exception("库体创建失败！\n原因：" + eError.Message);
ProcBar.Close();
return;
}
finally
{
if (Con.State == ConnectionState.Open)
{
Con.Close();
}
}
this.DialogResult = DialogResult.OK;
this.Close();
}
/// <summary>
/// Modifie un utilisateur en base
/// </summary>
/// <param name="connection"></param>
/// <param name="strPswd"></param>
/// <param name="oldAdmin"></param>
/// <returns></returns>
public bool Modify(OracleConnection connection, string strPswd, short oldAdmin)
{
bool bResult = false;
OracleTransaction transaction = connection.BeginTransaction();
OracleCommand command = new OracleCommand();
command.Connection = connection;
string sql = "";
try
{
//modifie le mot de passe de l'utilisateur oracle
if (strPswd != this.m_strPassword)
{
sql = "ALTER USER " + this.m_strUserOracle + @" IDENTIFIED BY """ + strPswd + @"""";
command.CommandText = sql;
command.ExecuteNonQuery();
}
short dActivation = AccountMgmt.Common.Constants.DesactivationLevel;
if (this.m_bActivation)
{
dActivation = AccountMgmt.Common.Constants.ActivationLevel;
}
else
{
dActivation = AccountMgmt.Common.Constants.DesactivationLevel;
}
string strDateModification = "TO_DATE ('" + m_dtModification.Day.ToString("00") + "/" + m_dtModification.Month.ToString("00") + "/" + m_dtModification.Year.ToString("0000") + " " + m_dtModification.Hour.ToString("00") + ":" + m_dtModification.Minute.ToString("00") + ":" + m_dtModification.Second.ToString("00") + "', 'DD/MM/YYYY HH24:MI:SS')";
//string strDateModification = "TO_DATE ('" + this.m_dtModification.ToString("G") + "', 'DD/MM/YYYY HH24:MI:SS')";
string strDateBeginning = "NULL";
if (this.m_dtBeginning != new DateTime())
{
strDateBeginning = "TO_DATE ('" + m_dtBeginning.Day.ToString("00") + "/" + m_dtBeginning.Month.ToString("00") + "/" + m_dtBeginning.Year.ToString("0000") + " " + m_dtBeginning.Hour.ToString("00") + ":" + m_dtBeginning.Minute.ToString("00") + ":" + m_dtBeginning.Second.ToString("00") + "', 'DD/MM/YYYY HH24:MI:SS')";
}
//strDateBeginning = "TO_DATE ('" + this.m_dtBeginning.ToString("G") + "', 'DD/MM/YYYY HH24:MI:SS')";
string strDateEnd = "NULL";
if (this.m_dtEnd != new DateTime())
{
strDateEnd = "TO_DATE ('" + m_dtEnd.Day.ToString("00") + "/" + m_dtEnd.Month.ToString("00") + "/" + m_dtEnd.Year.ToString("0000") + " " + m_dtEnd.Hour.ToString("00") + ":" + m_dtEnd.Minute.ToString("00") + ":" + m_dtEnd.Second.ToString("00") + "', 'DD/MM/YYYY HH24:MI:SS')";
}
//strDateEnd = "TO_DATE ('" + this.m_dtEnd.ToString("G") + "', 'DD/MM/YYYY HH24:MI:SS')";
sql = "UPDATE MOU01.user_ SET user_='" + this.m_strUser.Replace("'", "''") +
"', service='" + this.m_strService.Replace("'", "''") +
"', commentary='" + this.m_strCommentary.Replace("'", "''") +
"', pwd='" + strPswd +
"', activation=" + dActivation.ToString("0") +
", date_beginning=" + strDateBeginning +
", date_end=" + strDateEnd +
", date_modification=" + strDateModification +
", dba_status=" + this.m_dAdmin.ToString("0") +
", id_user_modification=" + this.m_dIdUserModification.ToString("0") +
" WHERE id_user_=" + this.m_dIdUser.ToString("0");
command.CommandText = sql;
command.ExecuteNonQuery();
//granter les roles de manager si utilisateur est un responsable
if (this.m_dAdmin == AccountMgmt.Common.Constants.ResponsableLevel && oldAdmin != AccountMgmt.Common.Constants.ResponsableLevel)
{
sql = "GRANT MANAGE_RIGHT_SEL TO " + this.m_strUserOracle;
command.CommandText = sql;
command.ExecuteNonQuery();
sql = "GRANT MANAGE_RIGHT_MOD TO " + this.m_strUserOracle;
command.CommandText = sql;
command.ExecuteNonQuery();
}
//revoker les roles de manager si utilisateur ne l'est plus
if (this.m_dAdmin == AccountMgmt.Common.Constants.NoAdminLevel && oldAdmin == AccountMgmt.Common.Constants.ResponsableLevel)
{
sql = "REVOKE MANAGE_RIGHT_SEL FROM " + this.m_strUserOracle;
command.CommandText = sql;
command.ExecuteNonQuery();
sql = "REVOKE MANAGE_RIGHT_MOD FROM " + this.m_strUserOracle;
command.CommandText = sql;
command.ExecuteNonQuery();
}
transaction.Commit();
bResult = true;
}
catch (Exception error)
{
transaction.Rollback();
MessageBox.Show("Problème lors de la modification de l'utilisateur avec l'erreur : " + error.Message);
}
finally
{
//deconnexion
try
{
// Fermeture de la base de données
if (command != null)
{
command.Dispose();
}
}
catch (Exception error)
{
MessageBox.Show("Message d’erreur : " + error.Message);
}
}
return(bResult);
}
/// <summary>
/// 提交事务
/// </summary>
public void Commit()
{
p_trans.Commit();
}
/// <summary>
/// 将考勤记录导入数据库.
/// </summary>
/// <param name="xlsFilePath"></param>
/// <param name="randomStr"></param>
/// <param name="pb"></param>
/// <returns></returns>
public static MSG ImportAttendanceRecordToDB(string xlsFilePath, string randomStr, BackgroundWorker bgWork)
{
string excelName = Usual_Excel_Helper.getExcelName(xlsFilePath);
bgWork.ReportProgress(0, string.Format(@"lblPrompt.Text = {0}，准备读取：", excelName));
int pbLength = 0;
bgWork.ReportProgress(pbLength, "pb.Maximum");
int pbValue = 0;
bgWork.ReportProgress(pbValue, "pb.Value");
MSG msg = new MSG();
//用于确定本月最后一天.
Stack <int> sDate = new Stack <int>();
//Queue<AttendanceR> qAttendanceR = new Queue<AttendanceR>();
Queue <AttendanceRecordDetail> qARDetail = new Queue <AttendanceRecordDetail>();
AttendanceRecordDetail._random_str = randomStr;
//按指纹日期
string fingerPrintDate = String.Empty;
//行最大值.
int rowsMaxCount = 0;
int colsMaxCount = 0;
Usual_Excel_Helper uEHelper = null;
MyExcel myExcel = new MyExcel(xlsFilePath);
//打开该文档。
myExcel.openWithoutAlerts();
//只获取第一个表格。
Worksheet ws = myExcel.getFirstWorkSheetAfterOpen();
bgWork.ReportProgress(0, string.Format(@"lblPrompt.Text = {0}，正在读取：", excelName));
AttendanceRecordDetail._file_path = xlsFilePath;
//行;列最大值 赋值.
rowsMaxCount = ws.UsedRange.Rows.Count;
colsMaxCount = ws.UsedRange.Columns.Count;
AttendanceRecordDetail._sheet_name = ws.Name;
//判断首行是否为 考勤记录表;以此判断此表是否为考勤记录表.
string A1Str = ((Range)ws.Cells[1, 1]).Text.ToString().Trim().Replace("\n", "").Replace("\r", "").Replace(" ", "");
if (String.IsNullOrEmpty(A1Str))
{
msg.Msg = "工作表的A1单元格不能为空！";
msg.Flag = false;
myExcel.close();
return(msg);
}
//如果A1Str的内容不包含"考勤记录表"5个字。
if (!A1Str.Contains("考勤记录表"))
{
msg.Msg = "A1内容未包含'考勤记录表'";
msg.Flag = false;
myExcel.close();
return(msg);
}
#region 判断名称中是否区分了考勤记录。
string Seq_Attendance_Record = string.Empty;
int indexOfFullStop = xlsFilePath.LastIndexOf(".");
Seq_Attendance_Record = xlsFilePath.Substring(indexOfFullStop - 1, 1);
if (!CheckPattern.CheckNumber(Seq_Attendance_Record))
{
msg.Msg = "考勤记录表名称请以数字结尾！";
msg.Flag = false;
myExcel.close();
return(msg);
}
#endregion
AttendanceRecordDetail._prefix_Job_Number = excelName.Substring(excelName.Length - 1, 1).ToCharArray()[0];
string C3Str = ((Range)ws.Cells[3, 3]).Text.ToString().Trim();
// \0: 表空字符.
if (String.IsNullOrEmpty(C3Str))
{
msg.Msg = "异常: 考勤时间为空!";
msg.Flag = false;
myExcel.close();
return(msg);
}
//
string[] ArrayC3 = C3Str.Split('~');
if (ArrayC3.Length == 0)
{
msg.Msg = "异常: 考勤时间格式变更!";
msg.Flag = false;
myExcel.close();
return(msg);
}
AttendanceRecordDetail._start_date = ArrayC3[0].ToString().Trim().Replace('/', '-');
AttendanceRecordDetail._end_date = ArrayC3[1].ToString().Trim().Replace('/', '-');
//制表时间: L3 3行12列.
string L3Str = ((Range)ws.Cells[3, 12]).Text.ToString().Trim().Replace('/', '-');
if (String.IsNullOrEmpty(L3Str))
{
msg.Msg = "异常: 制表时间为空!";
msg.Flag = false;
myExcel.close();
return(msg);
}
//制表时间.
AttendanceRecordDetail._tabulation_time = L3Str;
//检查第4行是否为;考勤时间:
string A4Str = ((Range)ws.Cells[4, 1]).Text.ToString().Trim();
if (!"1".Equals(A4Str, StringComparison.CurrentCultureIgnoreCase))
{
msg.Msg = "异常: 第四行已变更!";
msg.Flag = false;
myExcel.close();
return(msg);
}
uEHelper = new Usual_Excel_Helper(ws);
//此刻不能删除，只是获取行号。
Queue <Range> rangeToDelQueue = new Queue <Range>();
//判断是否有空行。
for (int i = 5; i <= rowsMaxCount; i++)
{
if (uEHelper.isBlankRow(i))
{
//只要上一列不是
//删除掉此行。
//判断上一行中的A列是否为工号。
string temp = uEHelper.getSpecificCellValue("A" + (i - 1).ToString());
if ("工号:".Equals(temp))
{
//本行为空，上一行为工号行，则也统计。
continue;
}
//本行，为空，上一行非工号行。则删除本行。
Range rangeToDel = (Microsoft.Office.Interop.Excel.Range)uEHelper.WS.Rows[i, System.Type.Missing];
//不为工号
rangeToDelQueue.Enqueue(rangeToDel);
}
;
}
Range rangeToDelete;
//开始删除空行。
while (rangeToDelQueue.Count > 0)
{
rangeToDelete = rangeToDelQueue.Dequeue();
rangeToDelete.Delete(XlDeleteShiftDirection.xlShiftUp);
}
;
rowsMaxCount = ws.UsedRange.Rows.Count;
//进度条长度增加。
pbLength += colsMaxCount;
pbLength += (colsMaxCount * (rowsMaxCount - 5 + 1));
bgWork.ReportProgress(pbLength, "pb.Maximum");
//入队列值0
sDate.Push(0);
//显示进度条。
//考勤表中第4行，某月的最大考勤天数。
//lblPrompt.Text = excelName + "，正在读取：";
int actualMaxDay = 0;
//开始循环
for (int i = 1; i <= colsMaxCount; i++)
{
A4Str = ((Range)ws.Cells[4, i]).Text.ToString();
//碰到第4行某列为空，退出循环。
if (String.IsNullOrEmpty(A4Str))
{
break;
}
int aDate = 0;
//对A4Str进行分析.
if (!Int32.TryParse(A4Str, out aDate))
{
msg.Msg = String.Format(@"异常: 考勤日期行第{0}列出现非数字内容!", aDate);
msg.Flag = false;
myExcel.close();
return(msg);
}
//判断新增的日期是否大于上一个.
if (aDate <= sDate.Peek())
{
//跳出循环.
break;
}
actualMaxDay++;
sDate.Push(aDate);
//pb.Value++;
bgWork.ReportProgress(pbValue++, "pb.Value");
}
//取其中的最小值。
colsMaxCount = Math.Min(sDate.Count - 1, actualMaxDay);
//考勤日期
fingerPrintDate = AttendanceRecordDetail._start_date.Substring(0, 7).Replace('/', '-');
string tempStr = string.Empty;
//开始循环
for (int colIndex = 1; colIndex <= colsMaxCount; colIndex++)
{
//从第5行开始.
//奇数;偶数行共用一个对象.
AttendanceRecordDetail ARDetail = null;
//设定用于填充的对象
AttendanceRecordDetail._prefix_Job_Number = Seq_Attendance_Record.ToCharArray()[0];
for (int rowIndex = 5; rowIndex <= rowsMaxCount; rowIndex++)
{
//如果行数为奇数则为工号行.
if (rowIndex % 2 == 1)
{
//工号行.
//取工号
ARDetail = new AttendanceRecordDetail();
ARDetail.Job_number = ((Range)ws.Cells[rowIndex, 3]).Text.ToString().Trim();
//自行拼凑AR.
ARDetail.combine_Job_Number();
//取姓名: K5
ARDetail.Name = ((Range)ws.Cells[rowIndex, Usual_Excel_Helper.getColIndexByStr("K")]).Text.ToString().Trim();
//取部门: U5
ARDetail.Dept = ((Range)ws.Cells[rowIndex, Usual_Excel_Helper.getColIndexByStr("U")]).Text.ToString().Trim();
//部门为空，则填充为NULL;
ARDetail.Dept = !String.IsNullOrEmpty(ARDetail.Dept) ? ARDetail.Dept : "NULL";
//取日期.填充0;
ARDetail.Fingerprint_date = fingerPrintDate + "-" + colIndex.ToString().PadLeft(2, '0');
}
else
{
//偶数行取考勤结果.
//上班时间. 如B10;
tempStr = ((Range)ws.Cells[rowIndex, colIndex]).Text.ToString().Trim();
string tempFirstTime = String.Empty;
string tempLastTime = String.Empty;
List <string> strTimeList = null;
msg = getFPTimeReturnMSG(tempStr, out strTimeList);
if (!msg.Flag)
{
msg.Msg = string.Format(@"导入失败,提交数据尚未开始：第{0}行{1}列,{1}！", rowIndex, colIndex, msg.Msg);
myExcel.close();
return(msg);
}
;
//无打卡记录,不提交
if (strTimeList.Count == 0)
{
qARDetail.Enqueue(ARDetail);
}
//有打卡记录
for (int i = 0; i < strTimeList.Count; i++)
{
AttendanceRecordDetail ARDetailTemp = (AttendanceRecordDetail)CloneObject.Clone(ARDetail);
ARDetailTemp.Finger_print_time = ARDetailTemp.Fingerprint_date + " " + strTimeList[i].ToString();
qARDetail.Enqueue(ARDetailTemp);
}
}
//pb.Value++;
bgWork.ReportProgress(pbValue++, "pb.Value");
}
}
//释放对象
myExcel.close();
System.Threading.Thread.Sleep(2000);
GC.Collect();
GC.WaitForPendingFinalizers();
//lblResult.Text = "";
bgWork.ReportProgress(0, "lblResult.Text = ''");
//lblPrompt.Text = "提交数据: ";
bgWork.ReportProgress(0, string.Format(@"lblPrompt.Text = {0}, 提交数据:", excelName));
//
bgWork.ReportProgress(qARDetail.Count, "pb.Maximum");
//*******/
pbValue = 0;
bgWork.ReportProgress(pbValue, "pb.Value");
#region
//OracleDaoHelper.noLogging("Attendance_Record");
OracleDaoHelper.noLogging("Attendance_Record_Detail");
OracleConnection conn = OracleConnHelper.getConn();
OracleTransaction tran = conn.BeginTransaction();
//保存对象
while (qARDetail.Count > 0)
{
try
{
AttendanceRecordDetail aRDetail = qARDetail.Dequeue();
affectedCount += aRDetail.saveBySpecificConn(conn);
//pb.Value++;
bgWork.ReportProgress(pbValue++, "pb.Value");
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "提示:", MessageBoxButtons.OK, MessageBoxIcon.Error);
msg.Msg = DirectoryHelper.getFileName(xlsFilePath) + "：导入失败; " + ex.ToString();
msg.Flag = false;
tran.Rollback();
conn.Close();
conn.Dispose();
return(msg);
throw;
}
}
tran.Commit();
conn.Close();
conn.Dispose();
#endregion
//OracleDaoHelper.logging("Attendance_Record");
OracleDaoHelper.logging("Attendance_Record_Detail");
msg.Flag = true;
msg.Msg = String.Format(@"导入完成;总计{0}条.", affectedCount.ToString());
return(msg);
}
/// <summary>
///执行一个命令，没有返回数据集，只返回影响记录数
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", new OracleParameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">有效的 OracleTransaction</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">存储过程名称或 PL/SQL</param>
/// <param name="commandParameters">OracleParameter[]</param>
/// <returns>影响记录数</returns>
public static int ExecuteNonQuery(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
try
{
int returnInt = cmd.ExecuteNonQuery();
if (transaction != null)
{
transaction.Commit();
}
return returnInt;
}
catch
{
if (transaction != null)
{
transaction.Rollback();
}
return 0;
}
}
/// <summary>
///返回结果集的第一行第一列
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount", new OracleParameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">有效的 OracleTransaction</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-OleDb command</param>
/// <param name="commandParameters">params OracleParameter[]</param>
/// <returns>返回结果集的第一行第一列</returns>
public static object ExecuteScalar(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
//create a command and prepare it for execution
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
try
{
//execute the command & return the results
object obj = cmd.ExecuteScalar();
if (transaction != null)
{
transaction.Commit();
}
return obj;
}
catch
{
if (transaction != null)
{
// Rollback the transaction
transaction.Rollback();
}
return null;
}
}
/// <summary>
/// 返回执行响应的行数
/// </summary>
/// <param name="trans">command中执行的事物（OracleTransaction）</param>
/// <param name="cmdType">执行类型（过程/语句）</param>
/// <param name="cmdText">执行的过程名称/查询语句</param>
/// <param name="commandParameters">command执行的OracleParameter[]数组</param>
/// <returns>int 返回command执行响应的行数</returns>
public static int ExecuteNonQuery(OracleTransaction trans, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
{
int val = 0;
OracleCommand cmd = new OracleCommand();
try
{
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
//Close(trans.Connection);
//Dispose(trans.Connection);
trans.Commit();
return val;
}
catch
{
trans.Rollback();
return val;
}
}
public string execute_transaction(string[] strSql)
{
objConnection = open_connection();
try
{
if (open_con)
{
objTransaction = objConnection.BeginTransaction();
Int32 i = 0;
string strQuery = "";
for (i = 0; i <= strSql.Length - 1; i++)
{
strQuery = "";
strQuery = strSql[i];
if (strQuery.Length != 0)
{
objCommand = new OracleCommand(strQuery, objConnection);
objCommand.CommandType = CommandType.Text;
try
{
objCommand.ExecuteNonQuery();
err_flag = false;
}
catch (Exception objError)
{
ErrorStr = objError.Message;
err_flag = true;
objTransaction.Rollback();
objCommand.Dispose();
objConnection.Close();
objConnection.Dispose();
}
}
}
if (!err_flag)
{
objTransaction.Commit();
objCommand.Dispose();
objConnection.Close();
objConnection.Dispose();
}
else
{
objTransaction.Rollback();
objCommand.Dispose();
objConnection.Close();
objConnection.Dispose();
}
}
else
{
ErrorStr = "Error : Unable to connect to database.";
err_flag = true;
}
}
catch (Exception objError)
{
ErrorStr = objError.Message;
err_flag = true;
objTransaction.Rollback();
objCommand.Dispose();
objConnection.Close();
objConnection.Dispose();
}
finally
{
objCommand.Dispose();
objConnection.Close();
objConnection.Dispose();
}
return ErrorStr;
}
public void CommitTransaction(OracleTransaction trans, bool closeConnection)
{
trans.Commit();
if (closeConnection && trans.Connection.State != ConnectionState.Closed)
{
trans.Connection.Close();
}
trans = null;
}
//删除数据
private void barBtnDel_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
this.gvVIN_INFO.PostEditor();
var dataSource = (DataView)this.gvVIN_INFO.DataSource;
if (dataSource == null)
{
MessageBox.Show("请选择您要操作的记录！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
var dtSelected = dataSource.Table.Copy();
dtSelected.Clear();
if (dataSource != null && dataSource.Table.Rows.Count > 0)
{
for (int i = 0; i < dataSource.Table.Rows.Count; i++)
{
bool result = false;
bool.TryParse(dataSource.Table.Rows[i]["check"].ToString(), out result);
if (result)
{
dtSelected.Rows.Add(dataSource.Table.Rows[i].ItemArray);
}
}
}
if (dtSelected.Rows.Count == 0)
{
MessageBox.Show("请选择您要操作的记录！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
if (dtSelected.Select("MERGER_STATUS=1").Count() > 0)
{
MessageBox.Show("您选择要操作的记录中包含已合并数据！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
if (MessageBox.Show("确定要删除吗？", "删除确认", MessageBoxButtons.OKCancel, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) != DialogResult.OK)
{
return;
}
using (OracleConnection conn = new OracleConnection(FuelDataSysClient.Tool.OracleHelper.conn))
{
conn.Open();
using (OracleTransaction trans = conn.BeginTransaction())
{
foreach (DataRow dr in dtSelected.Rows)
{
try
{
FuelDataSysClient.Tool.OracleHelper.ExecuteNonQuery(FuelDataSysClient.Tool.OracleHelper.conn, string.Format("Delete from VIN_INFO where ID='{0}'", dr["ID"]), null);
}
catch (Exception ex)
{
trans.Rollback();
MessageBox.Show(String.Format("数据库操作出现异常，删除失败：{0}！", ex.Message), "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
if (trans.Connection != null)
{
trans.Commit();
}
}
}
this.refrashCurrentPage();
}
public static void BLOBTest(OracleConnection connection)
{
Console.WriteLine(" BEGIN TRANSACTION ...");
OracleTransaction transaction = connection.BeginTransaction();
Console.WriteLine(" Drop table BLOBTEST ...");
try
{
OracleCommand cmd2 = connection.CreateCommand();
cmd2.Transaction = transaction;
cmd2.CommandText = "DROP TABLE BLOBTEST";
cmd2.ExecuteNonQuery();
}
catch (OracleException)
{
// ignore if table already exists
}
Console.WriteLine(" CREATE TABLE ...");
OracleCommand create = connection.CreateCommand();
create.Transaction = transaction;
create.CommandText = "CREATE TABLE BLOBTEST (BLOB_COLUMN BLOB)";
create.ExecuteNonQuery();
Console.WriteLine(" INSERT RECORD ...");
OracleCommand insert = connection.CreateCommand();
insert.Transaction = transaction;
insert.CommandText = "INSERT INTO BLOBTEST VALUES (EMPTY_BLOB())";
insert.ExecuteNonQuery();
OracleCommand select = connection.CreateCommand();
select.Transaction = transaction;
select.CommandText = "SELECT BLOB_COLUMN FROM BLOBTEST FOR UPDATE";
Console.WriteLine(" SELECTING A BLOB (Binary Large Object) VALUE FROM BLOBTEST");
OracleDataReader reader = select.ExecuteReader();
if (!reader.Read())
{
Console.WriteLine("ERROR: RECORD NOT FOUND");
}
Console.WriteLine(" TESTING OracleLob OBJECT ...");
OracleLob lob = reader.GetOracleLob(0);
Console.WriteLine(" LENGTH: {0}", lob.Length);
Console.WriteLine(" CHUNK SIZE: {0}", lob.ChunkSize);
//try {
if (File.Exists(infilename) == false)
{
Console.WriteLine("Filename does not exist: " + infilename);
return;
}
FileStream fs = new FileStream(infilename, FileMode.Open, FileAccess.Read);
BinaryReader r = new BinaryReader(fs);
byte[] bytes = null;
int bufferLen = 8192;
bytes = r.ReadBytes(bufferLen);
while (bytes.Length > 0)
{
Console.WriteLine("byte count: " + bytes.Length.ToString());
lob.Write(bytes, 0, bytes.Length);
bytes1 = ByteArrayCombine(bytes1, bytes);
if (bytes.Length < bufferLen)
{
break;
}
bytes = r.ReadBytes(bufferLen);
}
r.Close();
fs.Close();
//}
//catch (Exception e) {
// Console.WriteLine("The file could not be read:");
// Console.WriteLine(e.Message);
//}
lob.Close();
Console.WriteLine(" CLOSING READER...");
reader.Close();
transaction.Commit();
transaction = null;
lob = null;
reader.Dispose();
reader = null;
create = null;
insert = null;
select = null;
}
/// <summary>
/// Execute an OracleCommand (that returns a resultset) against the specified OracleTransaction
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders", new OracleParameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">有效的 OracleTransaction</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">存储过程名称或 PL/SQL</param>
/// <param name="commandParameters">params OracleParameter[]</param>
/// <returns>返回DataSet</returns>
public static DataSet ExecuteDataset(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
//create a command and prepare it for execution
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
//create the DataAdapter & DataSet
OracleDataAdapter da;
DataSet ds = new DataSet();
try
{
da = new OracleDataAdapter(cmd);
//fill the DataSet using default values for DataTable names, etc.
da.Fill(ds);
if (transaction != null)
{
transaction.Commit();
}
}
catch
{
if (transaction != null)
{
// Rollback the transaction
transaction.Rollback();
}
}
//return the dataset
return ds;
}
private void LoadBlobCustomBackgroundWorker_DoWork(object sender, DoWorkEventArgs e)
{
OracleTransaction transaction = _oracleConnection.BeginTransaction(); // transakcja ładowania plików
OracleCommand command = _oracleConnection.CreateCommand(); // polecenie dla połączenia
// parametr dla polecenia ładowania pliku do blob - binarna zawartość pliku
OracleParameter blobDataParameter = new OracleParameter
{
OracleDbType = OracleDbType.Blob,
ParameterName = "blobDataParameter"
};
try
{
int fileCounter = 0; // licznik plików załadowanych. potrzebny do statusu i obliczenia procent ładowania
int filesSize = 0; // łączny rozmiar wszystkich załadowanych plików
int filesSizeAll = _blobFiles.Aggregate(0, (current, bFile) => current + bFile.FileSize);
// dla każdego wskazanego pliku
foreach (BlobFile blobFile in _blobFiles)
{
fileCounter++; // licznik załadowanych plików
string fileFullName = blobFile.FullFileName; // pełna nazwa pliku włączenie ze ścieżką
string fileShortName = blobFile.FileName; // tylko nazwa pliku
int fileSize = blobFile.FileSize; // rozmiar pliku
filesSize += fileSize; // zsumuj wielkość plików
toolStripStatusLabel.Text = $"Ładowanie pliku {fileCounter}/{_blobFiles.Count} [{filesSize / 1024}/{filesSizeAll / 1024} MB]: {fileFullName} [{Math.Round(fileSize / 1024.0, 2) } MB]";
// ----------------------------------------------------------------------------
// pobierz zawartość pliku
// ----------------------------------------------------------------------------
FileStream fs = new FileStream(fileFullName, FileMode.Open, FileAccess.Read);
byte[] blobDataValue = new byte[fs.Length];
fs.Read(blobDataValue, 0, Convert.ToInt32(fs.Length));
fs.Close();
// ----------------------------------------------------------------------------
// ----------------------------------------------------------------------------
// ładowanie pliku do BLOB
// ----------------------------------------------------------------------------
command.CommandText = "SELECT blob.kdok_plikisq.nextval FROM dual"; // pobranie sekwencji dla ładowanego rekordu BLOB
int idFileSq = int.Parse(command.ExecuteScalar().ToString());
// polecenie dodania pliku do blob
command.CommandText = $"INSERT INTO blob.kdok_pliki (id_file, pieczec_pliku, typ_pliku, data, data_d) VALUES({idFileSq}, {idFileSq}, '{fileShortName}', :blobDataParameter, to_date('{dateTimePickerDataD.Text}', 'YYYY-MM-DD HH24:MI:SS'))";
command.Parameters.Clear(); // przygotowanie parametru polecenia
command.Parameters.Add(blobDataParameter); // którym jest plik binarny
blobDataParameter.Value = blobDataValue; // ze skanem
command.ExecuteNonQuery(); // wykonaj polecenie
// ----------------------------------------------------------------------------
// ----------------------------------------------------------------------------
// ładowanie rekordu do KDOK_WSK
// ----------------------------------------------------------------------------
command.CommandText = "SELECT ewid4.kdok_wsksq.nextval FROM dual"; // pobranie sekwencji dla ładowanego rekordu kdok_wsk
int idDokSq = int.Parse(command.ExecuteScalar().ToString());
int idGr = Convert.ToInt32(textBoxDokId.Text);
string path = blobFile.FullFileName;
int idRodzDok = Convert.ToInt32(textBoxIdRodzDok.Text);
if (idRodzDok == 0)
{
idRodzDok = blobFile.PrefixId;
}
int userId = Convert.ToInt32(textBoxUserId.Text);
string wl = string.Empty;
Invoke(new MethodInvoker(() => wl = comboBoxWl.Text));
wl = wl.Substring(0, wl.IndexOf(" -", StringComparison.Ordinal));
command.CommandText = "INSERT INTO KDOK_WSK(ID_DOK, WL, ID_GR, ID_FILE, PATH, ID_RODZ_DOK, OPIS, USER_ID, DATA_D, USERM_ID, DATA_M) " +
$"VALUES({idDokSq}, '{wl}', {idGr}, {idFileSq}, '{path}', {idRodzDok}, '', {userId}, to_date('{dateTimePickerDataD.Text}', 'YYYY-MM-DD HH24:MI:SS'), {userId}, to_date('{dateTimePickerDataD.Text}', 'YYYY-MM-DD HH24:MI:SS'))";
command.ExecuteNonQuery();
// ----------------------------------------------------------------------------
int percentage = (fileCounter * 100) / dataGridView.Rows.Count; // oblicz procentowe zaawansowanie ładowania
loadBlobCustomBackgroundWorker.ReportProgress(percentage); // zaraportuj zaawansowanie
}
transaction.Commit(); // zatwierdzenie transakcji ładowania
toolStripStatusLabel.Text = $"Załadowano {dataGridView.Rows.Count} plików o łącznym rozmiarze {filesSize / 1024} MB";
}
catch (Exception exception)
{
transaction.Rollback(); // w przypadku wystąpienia błędu wycofaj transakcje
toolStripStatusLabel.Text = exception.Message + "\n" + command.CommandText;
}
finally
{
blobDataParameter.Dispose();
command.Dispose();
transaction.Dispose();
}
}
/// <summary>
/// Create and prepare an OracleCommand, and call ExecuteReader with the appropriate CommandBehavior.
/// </summary>
/// <remarks>
/// If we created and opened the connection, we want the connection to be closed when the DataReader is closed.
///
/// If the caller provided the connection, we want to leave it to them to manage.
/// </remarks>
/// <param name="connection">有效的 OracleConnection, on which to execute this command</param>
/// <param name="transaction">有效的 OracleTransaction, or 'null'</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">存储过程名称或 PL/SQL</param>
/// <param name="commandParameters">an array of OracleParameters to be associated with the command or 'null' if no parameters are required</param>
/// <param name="connectionOwnership">indicates whether the connection parameter was provided by the caller, or created by OracleProvider</param>
/// <returns>OracleDataReader containing the results of the command</returns>
private static OracleDataReader ExecuteReader(OracleConnection connection, OracleTransaction transaction, CommandType commandType, string commandText, OracleParameter[] commandParameters, OracleConnectionOwnership connectionOwnership)
{
//create a command and prepare it for execution
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters);
//create a reader
OracleDataReader dr = null;
try
{
// call ExecuteReader with the appropriate CommandBehavior
if (connectionOwnership == OracleConnectionOwnership.External)
{
dr = cmd.ExecuteReader();
}
else
{
dr = cmd.ExecuteReader((CommandBehavior)((int)CommandBehavior.CloseConnection));
}
if (transaction != null)
{
transaction.Commit();
}
return (OracleDataReader)dr;
}
catch
{
if (transaction != null)
{
// Rollback the transaction
transaction.Rollback();
}
throw;
}
}
/************************************************************
* Function name : SetFAQUpdate
* Purpose : FAQ 저장(기존 FAQ 저장 Update)
* Input : string[] rParams
* Output : String
*************************************************************/
#region public string SetFAQUpdate(params object[] rParams)
public string SetFAQUpdate(params object[] rParams)
{
string xRtn = Boolean.FalseString;
Database db = base.GetDataBase("LMS");
OracleConnection xCnnLMS = (OracleConnection)db.CreateConnection();
xCnnLMS.Open();
OracleTransaction xTransLMS = null;
OracleCommand xCmdLMS = null;
try
{
string xFilePath = string.Empty;
xTransLMS = xCnnLMS.BeginTransaction(); // 트랜잭션 시작
xCmdLMS = base.GetSqlCommand(db);
xCmdLMS.Connection = xCnnLMS;
xCmdLMS.Transaction = xTransLMS;
try
{
string xSql = string.Empty;
// 기존 공지사항 Update
xSql = string.Empty;
xSql += " UPDATE t_board SET ";
xSql += string.Format(" boa_kind = '{0}', ", "000002"); // 000001 Q&A, 000002 FAQ, 000003 자료실
xSql += string.Format(" boa_sub = '{0}', ", rParams[0]); // FAQ 제목
//xSql += string.Format(" boa_content = '{0}', ", rParams[1]); // FAQ 내용
xSql += "boa_content = :BOACONTENT, ";
xSql += string.Format(" upt_id = '{0}', ", rParams[2]); // 수정자 ID
xSql += " upt_dt = SYSDATE ";
xSql += string.Format(" WHERE boa_no = {0} ", Convert.ToInt32(rParams[3]));
xCmdLMS.CommandText = xSql;
OracleParameter BOACONTENT = new OracleParameter();
BOACONTENT.OracleType = OracleType.Clob;
BOACONTENT.ParameterName = "BOACONTENT";
BOACONTENT.Value = rParams[1];
xCmdLMS.Parameters.Add(BOACONTENT);
base.Execute(db, xCmdLMS, xTransLMS);
xTransLMS.Commit(); // 트랜잭션 커밋
xRtn = Boolean.TrueString;
}
catch (Exception ex)
{
xTransLMS.Rollback(); // Exception 발생시 롤백처리...
throw ex;
}
finally
{
if (xCmdLMS != null)
{
xCmdLMS.Dispose();
}
if (xTransLMS != null)
{
xTransLMS.Dispose();
}
}
}
catch (Exception ex)
{
bool rethrow = ExceptionPolicy.HandleException(ex, "Propagate Policy");
if (rethrow)
{
throw;
}
}
return(xRtn);
}
/// <summary>
/// Procédure publique qui va appeler la procédure stockée permettant de mettre à jour un participant
/// </summary>
/// <param name="pIdParticipant">Id du participant à mettre à jour</param>
/// <param name="pCleWifi">La clé wifi (généré ou null)</param>
public void MajParticipant(Int32 pIdParticipant,String pCleWifi )
{
String MessageErreur = "";
try
{
UneOracleCommand = new OracleCommand("pckparticipant.enregistrearriveparticipant", CnOracle); //Package.procedure
UneOracleCommand.CommandType = CommandType.StoredProcedure;
// Paramètres qui stocke l'ID du participant ainsi que la clé wifi de celui-ci
UneOracleCommand.Parameters.Add("pIdParticipant", OracleDbType.Int32, ParameterDirection.Input).Value = pIdParticipant;
UneOracleCommand.Parameters.Add("pCleWifi", OracleDbType.Char, ParameterDirection.Input).Value = pCleWifi;
// début de la transaction Oracle il vaut mieyx gérer les transactions dans l'applicatif que dans la bd dans les procédures stockées.
UneOracleTransaction = this.CnOracle.BeginTransaction();
//execution
UneOracleCommand.ExecuteNonQuery();
// fin de la transaction. Si on arrive à ce point, c'est qu'aucune exception n'a été levée
UneOracleTransaction.Commit();
}
catch (OracleException Oex)
{
MessageErreur = this.GetMessageOracle(Oex.Message);
}
catch (Exception)
{
MessageErreur = "Erreur de la mise à jour du participant";
}
finally
{
if (MessageErreur.Length > 0)
{
// annulation de la transaction
UneOracleTransaction.Rollback();
// Déclenchement de l'exception
throw new Exception(MessageErreur);
}
}
}
/// <summary>
/// Procédure publique qui va appeler la procédure stockée permettant d'inscrire un nouvel intervenant sans nuité
/// </summary>
/// <param name="Cmd">nom de l'objet command concerné par les paramètres</param>
/// <param name="pNom">nom du participant</param>
/// <param name="pPrenom">prénom du participant</param>
/// <param name="pAdresse1">adresse1 du participant</param>
/// <param name="pAdresse2">adresse2 du participant</param>
/// <param name="pCp">cp du participant</param>
/// <param name="pVille">ville du participant</param>
/// <param name="pTel">téléphone du participant</param>
/// <param name="pMail">mail du participant</param>
/// <param name="pIdQualite">qualité du licencié</param>
/// <param name="pNumeroLicence">numéro de licence du licencié</param>
/// <param name="pLesAteliers">collection des ateliers du licencié</param>
/// <param name="pLesCategories">collection des catégories de l'hotel du licencié</param>
/// <param name="pLesHotels">collection des hotels du licencié</param>
/// <param name="pLesNuits">collection des nuits du licencié</param>
/// <param name="pNumCheque">numéro du cheque du licencié</param>
/// <param name="pMontantCheque">montant du cheque du licencié</param>
/// <param name="pLesAccompagnants">collection des accompagnants du licencié</param>
/// <param name="pInscription">type du paiement du licencié</param>
public void InscrireLicencie(String pNom, String pPrenom, String pAdresse1, String pAdresse2, String pCp, String pVille, String pTel, String pMail, Int16 pIdQualite, Int64 pNumeroLicence, Collection<Int16> pLesAteliers, Collection<string> pLesCategories, Collection<string> pLesHotels, Collection<Int16> pLesNuits, Int64 pNumCheque, Double pMontantCheque, Collection<Int16> pLesAccompagnants, String pInscription)
{
String MessageErreur = "";
try
{
// pckparticipant.nouvelintervenant est une procédure surchargée
UneOracleCommand = new OracleCommand("pckparticipant.nouveaulicencie", CnOracle);
UneOracleCommand.CommandType = CommandType.StoredProcedure;
// début de la transaction Oracle : il vaut mieyx gérer les transactions dans l'applicatif que dans la bd.
UneOracleTransaction = this.CnOracle.BeginTransaction();
this.ParamCommunsNouveauxParticipants(UneOracleCommand, pNom, pPrenom, pAdresse1, pAdresse2, pCp, pVille, pTel, pMail);
this.ParamsSpecifiquesLicencie(UneOracleCommand, pIdQualite, pNumeroLicence, pNumCheque, pMontantCheque, pInscription);
if (pLesAteliers.Count == 0)
{
pLesAteliers.Add(0);
}
// Paramètre qui stocke les ateliers sélectionnées
OracleParameter pOraLesAteliers = new OracleParameter();
pOraLesAteliers.ParameterName = "pLesAteliers";
pOraLesAteliers.OracleDbType = OracleDbType.Int16;
pOraLesAteliers.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
pOraLesAteliers.Value = pLesAteliers.ToArray();
pOraLesAteliers.Size = pLesAteliers.Count;
UneOracleCommand.Parameters.Add(pOraLesAteliers);
//On va créer ici les paramètres spécifiques à l'inscription d'un intervenant qui réserve des nuits d'hôtel.
// Paramètre qui stocke les catégories sélectionnées
OracleParameter pOraLescategories = new OracleParameter();
pOraLescategories.ParameterName = "pLesCategories";
pOraLescategories.OracleDbType = OracleDbType.Char;
pOraLescategories.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
pOraLescategories.Value = pLesCategories.ToArray();
pOraLescategories.Size = pLesCategories.Count;
UneOracleCommand.Parameters.Add(pOraLescategories);
// Paramètre qui stocke les hotels sélectionnées
OracleParameter pOraLesHotels = new OracleParameter();
pOraLesHotels.ParameterName = "pLesHotels";
pOraLesHotels.OracleDbType = OracleDbType.Char;
pOraLesHotels.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
pOraLesHotels.Value = pLesHotels.ToArray();
pOraLesHotels.Size = pLesHotels.Count;
UneOracleCommand.Parameters.Add(pOraLesHotels);
// Paramètres qui stocke les nuits sélectionnées
OracleParameter pOraLesNuits = new OracleParameter();
pOraLesNuits.ParameterName = "pLesNuits";
pOraLesNuits.OracleDbType = OracleDbType.Int16;
pOraLesNuits.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
pOraLesNuits.Value = pLesNuits.ToArray();
pOraLesNuits.Size = pLesNuits.Count;
UneOracleCommand.Parameters.Add(pOraLesNuits);
if (pLesAccompagnants.Count == 0)
{
pLesAccompagnants.Add(0);
}
// Paramètres qui stocke les accompagnants sélectionnées
OracleParameter pOraLesAccompagnants = new OracleParameter();
pOraLesAccompagnants.ParameterName = "plesaccompagnants";
pOraLesAccompagnants.OracleDbType = OracleDbType.Int16;
pOraLesAccompagnants.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
pOraLesAccompagnants.Value = pLesAccompagnants.ToArray();
pOraLesAccompagnants.Size = pLesAccompagnants.Count;
UneOracleCommand.Parameters.Add(pOraLesAccompagnants);
//execution
UneOracleCommand.ExecuteNonQuery();
// fin de la transaction. Si on arrive à ce point, c'est qu'aucune exception n'a été levée
UneOracleTransaction.Commit();
}
catch (OracleException Oex)
{
//MessageErreur="Erreur Oracle \n" + this.GetMessageOracle(Oex.Message);
MessageBox.Show(Oex.Message);
}
catch (Exception ex)
{
MessageErreur = "Autre Erreur, les informations n'ont pas été correctement saisies";
}
finally
{
if (MessageErreur.Length > 0)
{
// annulation de la transaction
UneOracleTransaction.Rollback();
// Déclenchement de l'exception
throw new Exception(MessageErreur);
}
}
}
/// <summary>
/// Procédure publique qui va appeler la procédure stockée permettant d'ajouter une nouvelle vacation
/// </summary>
/// <param name="pIdAtelier">Id de l'atelier que l'on va associé à la vacation</param>
/// <param name="pHeureDebut">Heure debut de la vacation</param>
/// <param name="pHeureFin">Heure fin de la vacation</param>
public void creerVacation(Int32 pIdAtelier, String pHeureDebut, String pHeureFin)
{
/// <remarks>
///
/// </remarks>
///
String MessageErreur = "";
try
{
UneOracleCommand = new OracleCommand("MDL.pckgestionajoutmodif.ajoutvacation", CnOracle); //Package.procedure
UneOracleCommand.CommandType = CommandType.StoredProcedure;
// Paramètres qui stocke l'ID de l'atelier associé à la vacation ainsi que l'heure de debut et de fin de la vacation
UneOracleCommand.Parameters.Add("pIdAtelier", OracleDbType.Int32, ParameterDirection.Input).Value = pIdAtelier;
UneOracleCommand.Parameters.Add("pHeureDebut", OracleDbType.Varchar2, ParameterDirection.Input).Value = pHeureDebut;
UneOracleCommand.Parameters.Add("pHeureFin", OracleDbType.Varchar2, ParameterDirection.Input).Value = pHeureFin;
// début de la transaction Oracle il vaut mieyx gérer les transactions dans l'applicatif que dans la bd dans les procédures stockées.
UneOracleTransaction = this.CnOracle.BeginTransaction();
//execution
UneOracleCommand.ExecuteNonQuery();
// fin de la transaction. Si on arrive à ce point, c'est qu'aucune exception n'a été levée
UneOracleTransaction.Commit();
}
catch (OracleException Oex)
{
MessageErreur = this.GetMessageOracle(Oex.Message);
}
catch (Exception)
{
MessageErreur = "Autre Erreur, les informations n'ont pas été correctement saisies";
}
finally
{
if (MessageErreur.Length > 0)
{
// annulation de la transaction
UneOracleTransaction.Rollback();
// Déclenchement de l'exception
throw new Exception(MessageErreur);
}
}
}
/// <summary>
/// Supprime un utilisateur en base
/// </summary>
/// <param name="connection"></param>
/// <returns></returns>
public bool Delete(OracleConnection connection)
{
bool bResult = false;
OracleTransaction transaction = null /*=connection.BeginTransaction()*/;
OracleCommand command = new OracleCommand();
command.Connection = connection;
string sql = "", strRoleList = "", sqlDelete = "";
OracleDataReader sqlReader = null;
OracleCommand commandDelete = new OracleCommand();
commandDelete.Connection = connection;
try
{
//desaffecter les roles de tous les profiles affectés a l'utilisateur
//lock de la table affectation
sql = "LOCK TABLE MOU01.affectation IN EXCLUSIVE MODE";
command.CommandText = sql;
command.ExecuteNonQuery();
try
{
transaction = connection.BeginTransaction();
//Recherche des rôles à REVOKER
sql = "SELECT role.role nom FROM MOU01.role, MOU01.right, MOU01.profile, MOU01.affectation " +
"WHERE Role.id_role = Right.id_role " +
" AND right.id_profile = profile.id_profile " +
" AND affectation.id_profile = profile.id_profile " +
" AND affectation.id_user_=" + this.m_dIdUser.ToString("0") +
" GROUP BY role.role ";
command.CommandText = sql;
sqlReader = command.ExecuteReader();
//lecture des données
while (sqlReader.Read())
{
if (strRoleList != "")
{
strRoleList += ", ";
}
strRoleList = strRoleList + sqlReader.GetString(0);
}
//revoke les roles du profile à l'utilisateur
if (strRoleList != "")
{
sql = "REVOKE " + strRoleList + " FROM " + this.UserOracle;
command.CommandText = sql;
command.ExecuteNonQuery();
}
//Recherche des rôles et profiles à desaffecter
sql = "SELECT affectation.id_profile FROM MOU01.affectation " +
"WHERE affectation.id_user_=" + this.m_dIdUser.ToString("0");
command.CommandText = sql;
sqlReader = command.ExecuteReader();
//lecture des données
while (sqlReader.Read())
{
//mise à jour de la table affectation
sqlDelete = "DELETE FROM MOU01.affectation WHERE id_user_=" + this.m_dIdUser.ToString("0") + " AND id_profile=" + sqlReader.GetInt64(0).ToString();
commandDelete.CommandText = sqlDelete;
commandDelete.ExecuteNonQuery();
}
//Suppression du compte Oracle
sql = "DROP USER " + this.m_strUserOracle;
command.CommandText = sql;
command.ExecuteNonQuery();
//Mise à jour de la table temporary_tablespace
sql = "UPDATE MOU01.temporary_tablespace SET user_count=(user_count-1) WHERE id_temporary_tablespace=" + this.IdTemporaryTableSpace.ToString("0");
command.CommandText = sql;
command.ExecuteNonQuery();
//Suppression dand la table user_
sql = "DELETE FROM MOU01.user_ WHERE user_oracle='" + this.m_strUserOracle + "'";
command.CommandText = sql;
command.ExecuteNonQuery();
transaction.Commit();
sql = "COMMIT";
command.CommandText = sql;
command.ExecuteNonQuery();
bResult = true;
}
catch (Exception error)
{
transaction.Rollback();
sql = "ROLLBACK";
command.CommandText = sql;
command.ExecuteNonQuery();
MessageBox.Show("Problème lors de la suppression de l'utilisateur avec l'erreur : " + error.Message);
}
}
catch (Exception error)
{
sql = "ROLLBACK";
command.CommandText = sql;
command.ExecuteNonQuery();
MessageBox.Show("Problème lors du lock de la table Affectation avec l'erreur : " + error.Message);
}
finally
{
//deconnexion
try
{
// Fermeture de la base de données
if (sqlReader != null)
{
sqlReader.Close();
sqlReader.Dispose();
}
if (command != null)
{
command.Dispose();
}
}
catch (Exception error)
{
MessageBox.Show("Message d’erreur : " + error.Message);
}
}
return(bResult);
}
/// <summary>
/// Procédure publique qui va appeler la procédure stockée permettant d'inscrire un nouvel intervenant sans nuité
/// </summary>
/// <param name="Cmd">nom de l'objet command concerné par les paramètres</param>
/// <param name="pNom">nom du participant</param>
/// <param name="pPrenom">prénom du participant</param>
/// <param name="pAdresse1">adresse1 du participant</param>
/// <param name="pAdresse2">adresse2 du participant</param>
/// <param name="pCp">cp du participant</param>
/// <param name="pVille">ville du participant</param>
/// <param name="pTel">téléphone du participant</param>
/// <param name="pMail">mail du participant</param>
/// <param name="pIdAtelier"> Id de l'atelier où interviendra l'intervenant</param>
/// <param name="pIdStatut">statut de l'intervenant pour l'atelier : animateur ou intervenant</param>
public void InscrireIntervenant(String pNom, String pPrenom, String pAdresse1, String pAdresse2, String pCp, String pVille, String pTel, String pMail, Int16 pIdAtelier, String pIdStatut)
{
/// <remarks>
/// procédure qui va créer :
/// 1- un enregistrement dans la table participant
/// 2- un enregistrement dans la table intervenant
/// en cas d'erreur Oracle, appel à la méthode GetMessageOracle dont le rôle est d'extraire uniquement le message renvoyé
/// par une procédure ou un trigger Oracle
/// </remarks>
///
String MessageErreur = "";
try
{
UneOracleCommand = new OracleCommand("pckparticipant.nouvelintervenant", CnOracle);
UneOracleCommand.CommandType = CommandType.StoredProcedure;
// début de la transaction Oracle il vaut mieyx gérer les transactions dans l'applicatif que dans la bd dans les procédures stockées.
UneOracleTransaction = this.CnOracle.BeginTransaction();
// on appelle la procédure ParamCommunsNouveauxParticipants pour charger les paramètres communs aux intervenants
this.ParamCommunsNouveauxParticipants(UneOracleCommand, pNom, pPrenom, pAdresse1, pAdresse2, pCp, pVille, pTel, pMail);
// on appelle la procédure ParamsCommunsIntervenant pour charger les paramètres communs aux intervenants
this.ParamsSpecifiquesIntervenant(UneOracleCommand, pIdAtelier, pIdStatut);
//execution
UneOracleCommand.ExecuteNonQuery();
// fin de la transaction. Si on arrive à ce point, c'est qu'aucune exception n'a été levée
UneOracleTransaction.Commit();
}
catch (OracleException Oex)
{
MessageErreur = "Erreur Oracle \n" + this.GetMessageOracle(Oex.Message);
}
catch (Exception ex)
{
MessageErreur = "Autre Erreur, les informations n'ont pas été correctement saisies";
}
finally
{
if (MessageErreur.Length > 0)
{
// annulation de la transaction
UneOracleTransaction.Rollback();
// Déclenchement de l'exception
throw new Exception(MessageErreur);
}
}
}
private void finishBtn_Click(object sender, EventArgs e)
{
string new_id = getIdTrans();
if (entryLb.Items.Count > 0)
{
int total_biaya = 0;
foreach (object i in entries)
{
int harga = int.Parse(i.GetType().GetProperty("harga").GetValue(i, null).ToString());
total_biaya += harga;
}
conn.Open();
OracleTransaction trans = conn.BeginTransaction();
try {
if (beliRb.Checked)
{
string club_asal = clubTargetCb.SelectedValue.ToString();
string club_baru = clubCb.SelectedValue.ToString();
string htrans = "INSERT INTO h_transaksi VALUES ('" + new_id + "', '" + club_asal + "', '" + club_baru + "', " +
total_biaya + ", TO_DATE(SYSDATE, 'DD-MM-YYYY'))";
OracleCommand cmd = new OracleCommand(htrans, conn);
cmd.ExecuteNonQuery();
foreach (object i in entries)
{
string id_player = i.GetType().GetProperty("value").GetValue(i, null).ToString();
int harga = int.Parse(i.GetType().GetProperty("harga").GetValue(i, null).ToString());
string dtrans = "INSERT INTO d_transaksi VALUES ('" + new_id + "', '" + id_player + "', " + harga + ")";
cmd = new OracleCommand(dtrans, conn);
cmd.ExecuteNonQuery();
string player = "UPDATE player " +
"SET id_club = '" + club_baru + "' " +
"WHERE id_player = '" + id_player + "'";
cmd = new OracleCommand(player, conn);
cmd.ExecuteNonQuery();
}
showNews("beli", clubCb.Text);
}
else
{
string club_asal = clubCb.SelectedValue.ToString();
string club_baru = clubTargetCb.SelectedValue.ToString();
string htrans = "INSERT INTO h_transaksi VALUES ('" + new_id + "', '" + club_asal + "', '" + club_baru + "', " +
total_biaya + ", TO_DATE(SYSDATE, 'DD-MM-YYYY'))";
OracleCommand cmd = new OracleCommand(htrans, conn);
cmd.ExecuteNonQuery();
foreach (object i in entries)
{
string id_player = i.GetType().GetProperty("value").GetValue(i, null).ToString();
int harga = int.Parse(i.GetType().GetProperty("harga").GetValue(i, null).ToString());
string dtrans = "INSERT INTO d_transaksi VALUES ('" + new_id + "', '" + id_player + "', " + harga + ")";
cmd = new OracleCommand(dtrans, conn);
cmd.ExecuteNonQuery();
string player = "UPDATE player " +
"SET id_club = '" + club_baru + "' " +
"WHERE id_player = '" + id_player + "'";
cmd = new OracleCommand(player, conn);
cmd.ExecuteNonQuery();
}
showNews("jual", clubCb.Text);
}
trans.Commit();
entries.Clear();
entryLb.DataSource = null;
} catch (Exception ex) {
MessageBox.Show(ex.Message);
trans.Rollback();
}
conn.Close();
}
if (clubCb.SelectedValue != null)
{
showGrid(clubCb.SelectedValue);
}
}
/// <summary>
/// Procédure publique qui va appeler la procédure stockée permettant d'inscrire un nouvel intervenant qui aura des nuités
/// </summary>
/// <param name="Cmd">nom de l'objet command concerné par les paramètres</param>
/// <param name="pNom">nom du participant</param>
/// <param name="pPrenom">prénom du participant</param>
/// <param name="pAdresse1">adresse1 du participant</param>
/// <param name="pAdresse2">adresse2 du participant</param>
/// <param name="pCp">cp du participant</param>
/// <param name="pVille">ville du participant</param>
/// <param name="pTel">téléphone du participant</param>
/// <param name="pMail">mail du participant</param>
/// <param name="pIdAtelier"> Id de l'atelier où interviendra l'intervenant</param>
/// <param name="pIdStatut">statut de l'intervenant pour l'atelier : animateur ou intervenant</param>
/// <param name="pLesCategories">tableau contenant la catégorie de chambre pour chaque nuité à réserver</param>
/// <param name="pLesHotels">tableau contenant l'hôtel pour chaque nuité à réserver</param>
/// <param name="pLesNuits">tableau contenant l'id de la date d'arrivée pour chaque nuité à réserver</param>
public void InscrireIntervenant(String pNom, String pPrenom, String pAdresse1, String pAdresse2, String pCp, String pVille, String pTel, String pMail, Int16 pIdAtelier, String pIdStatut, Collection<string> pLesCategories, Collection<string> pLesHotels, Collection<Int16> pLesNuits)
{
/// <remarks>
/// procédure qui va :
/// 1- faire appel à la procédure
/// un enregistrement dans la table participant
/// 2- un enregistrement dans la table intervenant
/// 3- un à 2 enregistrements dans la table CONTENUHEBERGEMENT
///
/// en cas d'erreur Oracle, appel à la méthode GetMessageOracle dont le rôle est d'extraire uniquement le message renvoyé
/// par une procédure ou un trigger Oracle
/// </remarks>
///
String MessageErreur = "";
try
{
// pckparticipant.nouvelintervenant est une procédure surchargée
UneOracleCommand = new OracleCommand("pckparticipant.nouvelintervenant", CnOracle);
UneOracleCommand.CommandType = CommandType.StoredProcedure;
// début de la transaction Oracle : il vaut mieyx gérer les transactions dans l'applicatif que dans la bd.
UneOracleTransaction = this.CnOracle.BeginTransaction();
this.ParamCommunsNouveauxParticipants(UneOracleCommand, pNom, pPrenom, pAdresse1, pAdresse2, pCp, pVille, pTel, pMail);
this.ParamsSpecifiquesIntervenant(UneOracleCommand, pIdAtelier, pIdStatut);
//On va créer ici les paramètres spécifiques à l'inscription d'un intervenant qui réserve des nuits d'hôtel.
// Paramètre qui stocke les catégories sélectionnées
OracleParameter pOraLescategories = new OracleParameter();
pOraLescategories.ParameterName = "pLesCategories";
pOraLescategories.OracleDbType = OracleDbType.Char;
pOraLescategories.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
pOraLescategories.Value = pLesCategories.ToArray();
pOraLescategories.Size = pLesCategories.Count;
UneOracleCommand.Parameters.Add(pOraLescategories);
// Paramètre qui stocke les hotels sélectionnées
OracleParameter pOraLesHotels = new OracleParameter();
pOraLesHotels.ParameterName = "pLesHotels";
pOraLesHotels.OracleDbType = OracleDbType.Char;
pOraLesHotels.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
pOraLesHotels.Value = pLesHotels.ToArray();
pOraLesHotels.Size = pLesHotels.Count;
UneOracleCommand.Parameters.Add(pOraLesHotels);
// Paramètres qui stocke les nuits sélectionnées
OracleParameter pOraLesNuits = new OracleParameter();
pOraLesNuits.ParameterName = "pLesNuits";
pOraLesNuits.OracleDbType = OracleDbType.Int16;
pOraLesNuits.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
pOraLesNuits.Value = pLesNuits.ToArray();
pOraLesNuits.Size = pLesNuits.Count;
UneOracleCommand.Parameters.Add(pOraLesNuits);
//execution
UneOracleCommand.ExecuteNonQuery();
// fin de la transaction. Si on arrive à ce point, c'est qu'aucune exception n'a été levée
UneOracleTransaction.Commit();
}
catch (OracleException Oex)
{
//MessageErreur="Erreur Oracle \n" + this.GetMessageOracle(Oex.Message);
MessageBox.Show(Oex.Message);
}
catch (Exception ex)
{
MessageErreur = "Autre Erreur, les informations n'ont pas été correctement saisies";
}
finally
{
if (MessageErreur.Length > 0)
{
// annulation de la transaction
UneOracleTransaction.Rollback();
// Déclenchement de l'exception
throw new Exception(MessageErreur);
}
}
}
public Usuario crearUsuario(Usuario usuario)
{
OracleConnection conn = null;
try
{
conn = new OracleConnection(this.strCadenaConexion);
conn.Open();
}
catch
{
throw new Exception("Error de conexión a la Base de Datos");
}
using (OracleCommand cmd = conn.CreateCommand())
{
using (OracleTransaction trx = conn.BeginTransaction())
{
cmd.Transaction = trx;
try
{
usuario = UsuariosBD.CrearUsuario(cmd, usuario);
usuario.Contrasena = Encriptacion.MD5(usuario.Contrasena);
int contrasenaResul = UsuariosBD.ActualizarContrasena(cmd, usuario.Id, usuario.Contrasena, usuario.User);
if (usuario.Id < 0 && contrasenaResul < 0)
{
throw new Exception("Ocurrió un error al guardar el usuario");
}
if (usuario.Permisos != null)
{
for (int i = 0; i < usuario.Permisos.Count; i++)
{
int resultPermXUsr = UsuariosBD.MantenimientoUsuarioXPermiso(cmd, usuario.Id, usuario.Permisos[i].Id, usuario.User, usuario.Permisos[i].Estado);
if (resultPermXUsr < 0)
{
throw new Exception("Ocurrió un error al guardar el usuario");
}
}
}
if (usuario.Perfiles != null)
{
for (int i = 0; i < usuario.Perfiles.Count; i++)
{
int resultUserXPerf = UsuariosBD.MantenimientoUsuarioXPerfil(cmd, usuario.Id, usuario.Perfiles[i].Id, usuario.User, usuario.Perfiles[i].Estado);
if (resultUserXPerf < 0)
{
throw new Exception("Ocurrió un error al guardar el usuario");
}
}
}
trx.Commit();
}
catch
{
trx.Rollback();
throw new Exception("Ocurrió un error al guardar el usuario");
}
}
}
CerrarConexion(conn);
return(usuario);
}
/// <summary>
/// Procédure publique qui va appeler la procédure stockée permettant de mettre à jour une vacation
/// </summary>
/// <param name="pHeureDebutVac">Heure de début de la vacation</param>
/// <param name="pHeureFinVac">Heure de fin de la vacation</param>
/// <param name="pNumeroVac">Numero de la vacation à mettre à jour</param>
public void MajVacation(DateTime pHeureDebutVac, DateTime pHeureFinVac, Int16 pNumeroVac)
{
String MessageErreur = "";
try
{
UneOracleCommand = new OracleCommand("MDL.pckgestionajoutmodif.modifvacation", CnOracle); //Package.procedure
UneOracleCommand.CommandType = CommandType.StoredProcedure;
// Paramètres qui stocke l'ID de l'atelier associé à la vacation, le numero de la vacation ainsi que l'heure de debut et de fin de la vacation
UneOracleCommand.Parameters.Add("pHeureDebut", OracleDbType.Date, ParameterDirection.Input).Value = pHeureDebutVac;
UneOracleCommand.Parameters.Add("pHeureFin", OracleDbType.Date, ParameterDirection.Input).Value = pHeureFinVac;
UneOracleCommand.Parameters.Add("pNumero", OracleDbType.Int16, ParameterDirection.Input).Value = pNumeroVac;
// début de la transaction Oracle il vaut mieyx gérer les transactions dans l'applicatif que dans la bd dans les procédures stockées.
UneOracleTransaction = this.CnOracle.BeginTransaction();
//execution
UneOracleCommand.ExecuteNonQuery();
// fin de la transaction. Si on arrive à ce point, c'est qu'aucune exception n'a été levée
UneOracleTransaction.Commit();
}
catch (OracleException Oex)
{
MessageErreur = this.GetMessageOracle(Oex.Message);
}
catch (Exception)
{
MessageErreur ="Erreur de la mise à jour de la vacation";
}
finally
{
if (MessageErreur.Length > 0)
{
// annulation de la transaction
UneOracleTransaction.Rollback();
// Déclenchement de l'exception
throw new Exception(MessageErreur);
}
}
}
//删除
private void btn_Delete_ItemClick(object sender, ItemClickEventArgs e)
{
if (this.gvDataInfo.DataSource == null)
{
XtraMessageBox.Show("没有可以操作的记录！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
this.gvDataInfo.PostEditor();
var dataSource = (DataView)this.gvDataInfo.DataSource;
var dtSelected = dataSource.Table.Copy();
dtSelected.Clear();
if (dataSource != null && dataSource.Table.Rows.Count > 0)
{
for (int i = 0; i < dataSource.Table.Rows.Count; i++)
{
bool result = false;
bool.TryParse(dataSource.Table.Rows[i]["check"].ToString(), out result);
if (result)
{
dtSelected.Rows.Add(dataSource.Table.Rows[i].ItemArray);
}
}
}
if (dtSelected.Rows.Count == 0)
{
XtraMessageBox.Show("请选择您要操作的记录！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
if (XtraMessageBox.Show("确定要删除吗？", "删除确认", MessageBoxButtons.OKCancel, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) != DialogResult.OK)
{
return;
}
using (OracleConnection conn = new OracleConnection(OracleHelper.conn))
{
conn.Open();
using (OracleTransaction trans = conn.BeginTransaction())
{
foreach (DataRow dr in dtSelected.Rows)
{
try
{
OracleHelper.ExecuteNonQuery(trans, string.Format("DELETE FROM DB_NOTICEPARAM WHERE ID = '{0}'", dr["ID"]), null);
}
catch (Exception ex)
{
trans.Rollback();
XtraMessageBox.Show(String.Format("数据库操作出现异常，删除失败：{0}！", ex.Message), "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
if (trans.Connection != null)
{
trans.Commit();
}
}
}
this.refrashCurrentPage();
//记录操作日志
LogUtils.ReviewLogManager.ReviewLog(Properties.Settings.Default.LocalUserName, String.Format("{0}-{1}", this.Text, this.btn_Delete.Caption));
}
//******************************************* debut des modif**********************************************************************//
/// <summary>
/// Procédure publique qui va appeler la procédure stockée permettant d'ajouter un nouvel Atelier
/// </summary>
/// <param name="pLibelleAtelier">Libelle de l'atelier</param>
/// <param name="pNbPlacesMax">Nombre de places Maximum pour participer à un atelier</param>
public void creerAtelier(String pLibelleAtelier, Int32 pNbPlacesMax, String pLibelleTheme, DateTime pHeureDebVac, DateTime pHeureFinVac)
{
String MessageErreur = "";
try
{
UneOracleCommand = new OracleCommand("MDL.pckgestionajoutmodif.ajoutatelier", CnOracle); //Package.procedure
UneOracleCommand.CommandType = CommandType.StoredProcedure;
// Paramètres qui stocke le libelle et le nbre de place max d'un atelier
UneOracleCommand.Parameters.Add("pLibelleAtelier", OracleDbType.Varchar2, ParameterDirection.Input).Value = pLibelleAtelier;
UneOracleCommand.Parameters.Add("pNbPlacesMax", OracleDbType.Int32, ParameterDirection.Input).Value = pNbPlacesMax;
UneOracleCommand.Parameters.Add("pLibelleTheme", OracleDbType.Varchar2, ParameterDirection.Input).Value = pLibelleTheme;
UneOracleCommand.Parameters.Add("pHeureDebutVac", OracleDbType.Date, ParameterDirection.Input).Value = pHeureDebVac;
UneOracleCommand.Parameters.Add("pHeureFinVac", OracleDbType.Date, ParameterDirection.Input).Value = pHeureFinVac;
// début de la transaction Oracle il vaut mieyx gérer les transactions dans l'applicatif que dans la bd dans les procédures stockées.
UneOracleTransaction = this.CnOracle.BeginTransaction();
//execution
UneOracleCommand.ExecuteNonQuery();
// fin de la transaction. Si on arrive à ce point, c'est qu'aucune exception n'a été levée
UneOracleTransaction.Commit();
}
catch (OracleException Oex)
{
MessageErreur = this.GetMessageOracle(Oex.Message);
}
catch (Exception)
{
MessageErreur = "Autre Erreur, les informations n'ont pas été correctement saisies";
}
finally
{
if (MessageErreur.Length > 0)
{
// annulation de la transaction
UneOracleTransaction.Rollback();
// Déclenchement de l'exception
throw new Exception(MessageErreur);
}
}
}
static async Task RunAsync(string ACCESSNUMBER, string STUDY_KEY, string report, string patient_name, string creation_data, OracleCommand command, OracleConnection connection, string CRM, OracleTransaction transaction)
{
var client = new HttpClient();
var host = ConfigurationManager.AppSettings["host"];
var token = "username=tor&password=tor@1234&grant_type=password";
client.BaseAddress = new Uri(host);
client.DefaultRequestHeaders.Accept.Clear();
error += $"Start: {STUDY_KEY} \r\n";
var teste = default(string);
command = new OracleCommand();
try
{
/*Token token = new Token {
* UserName = "******",
* Password = "******",
* Operations = new string[] { "CadastraLaudo" }
* };*/
//teste = JsonConvert.SerializeObject(token);
var tk = await GetTokenAsync(client, token);
error += $" token: {JsonConvert.SerializeObject(tk)} \r\n";
teste += $" token: {JsonConvert.SerializeObject(tk)} \r\n";
var chv = string.Empty;
var os = string.Empty;
if (ACCESSNUMBER.Length >= 10)
{
chv = ACCESSNUMBER.Substring(ACCESSNUMBER.Length - 2);
os = ACCESSNUMBER.Substring(0, ACCESSNUMBER.Length - 2);
}
else
{
chv = ACCESSNUMBER;
}
client.DefaultRequestHeaders.Accept.Clear();
client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", tk.access_token);
var laudo = new Laudos {
Id = !string.IsNullOrEmpty(chv) ? Convert.ToInt32(chv) : 0,
Laudo = PlainTextToRtf(report),
Crm = CRM ?? V
};
// Create a new product
var cadastroLaudo = new CadastraLaudo {
Laudos = new object[] { laudo },
NomePaciente = patient_name,
Os = !string.IsNullOrEmpty(os) ? Convert.ToInt64(os) : 0
};
error += $"laudo: {JsonConvert.SerializeObject(cadastroLaudo)} \r\n";
teste += $"laudo: {JsonConvert.SerializeObject(cadastroLaudo)} \r\n";
var ret = await SendLaudoAsync(client, cadastroLaudo);
error += $"retorno laudo: {JsonConvert.SerializeObject(ret)}";
teste += $"retorno laudo: {JsonConvert.SerializeObject(ret)}";
command = new OracleCommand();
command.Connection = connection;
command.CommandText = $"UPDATE MOVEREPORT SET STATUS = 1, ERROR = :clobparam WHERE STUDY_KEY = '{STUDY_KEY}'";
OracleParameter clobparam = new OracleParameter("clobparam", OracleDbType.Clob, teste.Length);
clobparam.Direction = ParameterDirection.Input;
clobparam.Value = teste;
command.Parameters.Add(clobparam);
command.Transaction = transaction;
command.ExecuteNonQuery();
transaction.Commit();
//command.Transaction.Commit();
/*if(ret.Descricao.Contains("Requisição não encontrada"))
* {
* command = new OracleCommand($"UPDATE STUDY SET STUDY_COMMENTS = '{teste}' WHERE STUDY_KEY = '{STUDY_KEY}'", connection);
* command.ExecuteNonQuery();
* //command.Transaction.Commit();
* }*/
}
catch (Exception e)
{
transaction.Rollback();
teste = $" laudo: {teste} exception: {e} \r\n ";
command = new OracleCommand();
command.Connection = connection;
command.CommandText = $"UPDATE MOVEREPORT SET STATUS = 1, ERROR = :clobparam WHERE STUDY_KEY = '{STUDY_KEY}'";
OracleParameter clobparam = new OracleParameter("clobparam", OracleDbType.Clob, teste.Length);
clobparam.Direction = ParameterDirection.Input;
clobparam.Value = teste;
command.Parameters.Add(clobparam);
command.Transaction = transaction;
command.ExecuteNonQuery();
transaction.Commit();
}
finally
{
connection.Close();
}
error += "End \r\n";
}
public int Update(BookModel record, int price, int instock)
{
OracleConnection oc = GetOracleConnection();
oc.Open();
OracleTransaction ot = oc.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
OracleCommand command = new OracleCommand()
{
CommandType = System.Data.CommandType.Text,
CommandText = "UPDATE books SET price = :price, instock = :instock WHERE isbn = :isbn"
};
#region Parameters
OracleParameter priceParameter = new OracleParameter()
{
DbType = System.Data.DbType.Int32,
ParameterName = ":price",
Direction = System.Data.ParameterDirection.Input,
Value = price
};
command.Parameters.Add(priceParameter);
OracleParameter instockParameter = new OracleParameter()
{
DbType = System.Data.DbType.Int32,
ParameterName = ":instock",
Direction = System.Data.ParameterDirection.Input,
Value = instock
};
command.Parameters.Add(instockParameter);
OracleParameter isbnParameter = new OracleParameter()
{
DbType = System.Data.DbType.String,
ParameterName = ":isbn",
Direction = System.Data.ParameterDirection.Input,
Value = record.Isbn
};
command.Parameters.Add(isbnParameter);
#endregion
command.Connection = oc;
command.Transaction = ot;
int affectedRows = 0;
try
{
affectedRows = command.ExecuteNonQuery();
ot.Commit();
}
catch (OracleException ex)
{
throw new FaultException <ArgumentFault>(new ArgumentFault(ex.Message));
}
catch (Exception ex)
{
ot.Rollback();
throw new FaultException(new FaultReason(ex.Message));
}
return(affectedRows);
}
public object ExecuteTransactionalOperations(string _sql, DbParameter[] dbParameters, string scalarORnonQuery, bool lastOperation)
{
System.Data.Common.DbParameter[] retParameters = PrepareParameters(dbParameters);
_sql = sqlBeautifier(_sql);
object result = null;
try
{
if (connection.DatabaseType == Enums.DbTypes.ORACLE)
{
if (_sql == null && dbParameters == null && scalarORnonQuery == null && lastOperation)
{
transactionOracle.Commit();
transactionOracle.Dispose();
cmdOracle.Connection.Close();
connectionOracle = null;
cmdOracle = null;
transactionOracle = null;
}
else
{
if (cmdOracle == null)
{
connectionOracle = connectionProvider.getOracleConnection();
cmdOracle = connectionOracle.CreateCommand();
transactionOracle = connectionOracle.BeginTransaction();
cmdOracle.Transaction = transactionOracle;
}
cmdOracle.CommandText = _sql;
if (dbParameters != null && dbParameters.Length > 0)
{
cmdOracle.Parameters.AddRange(retParameters);
}
if (scalarORnonQuery.Equals("scalar"))
{
result = cmdOracle.ExecuteScalar();
}
else if (scalarORnonQuery.Equals("nonQuery"))
{
result = cmdOracle.ExecuteNonQuery();
}
cmdOracle.CommandText = "";
cmdOracle.Parameters.Clear();
if (lastOperation)
{
transactionOracle.Commit();
transactionOracle.Dispose();
cmdOracle.Connection.Close();
connectionOracle = null;
cmdOracle = null;
transactionOracle = null;
}
}
}
else
{
if (_sql == null && dbParameters == null && scalarORnonQuery == null && lastOperation)
{
transactionSqlServer.Commit();
transactionSqlServer.Dispose();
cmdSqlServer.Connection.Close();
connectionSqlServer = null;
cmdSqlServer = null;
transactionSqlServer = null;
}
else
{
if (cmdSqlServer == null)
{
connectionSqlServer = connectionProvider.getSqlConnection();
cmdSqlServer = connectionSqlServer.CreateCommand();
transactionSqlServer = connectionSqlServer.BeginTransaction();
cmdSqlServer.Transaction = transactionSqlServer;
}
cmdSqlServer.CommandText = _sql;
if (dbParameters != null && dbParameters.Length > 0)
{
cmdSqlServer.Parameters.AddRange(retParameters);
}
if (scalarORnonQuery.Equals("scalar"))
{
result = cmdSqlServer.ExecuteScalar();
}
else if (scalarORnonQuery.Equals("nonQuery"))
{
result = cmdSqlServer.ExecuteNonQuery();
}
cmdSqlServer.CommandText = "";
cmdSqlServer.Parameters.Clear();
if (lastOperation)
{
transactionSqlServer.Commit();
transactionSqlServer.Dispose();
cmdSqlServer.Connection.Close();
connectionSqlServer = null;
cmdSqlServer = null;
transactionSqlServer = null;
}
}
}
return(result);
}
catch (SqlException ex)
{
transactionSqlServer.Rollback();
transactionSqlServer.Dispose();
cmdSqlServer.Connection.Close();
connectionSqlServer = null;
cmdSqlServer = null;
transactionSqlServer = null;
return(new ApplicationException("SQL Yürütme hatası : " + Environment.NewLine + ex.Message + Environment.NewLine + _sql));
}
catch (OracleException ex)
{
transactionOracle.Rollback();
transactionOracle.Dispose();
cmdOracle.Connection.Close();
connectionOracle = null;
cmdOracle = null;
transactionOracle = null;
return(new ApplicationException("SQL Yürütme hatası : " + Environment.NewLine + ex.Message + Environment.NewLine + _sql));
}
catch (Exception ex)
{
if (connection.DatabaseType == Enums.DbTypes.ORACLE)
{
connectionOracle = null;
cmdOracle = null;
transactionOracle = null;
}
else
{
connectionSqlServer = null;
cmdSqlServer = null;
transactionSqlServer = null;
}
return(new ApplicationException("SQL Yürütme hatası : " + Environment.NewLine + ex.Message + Environment.NewLine + _sql));
}
}
public int DiscountByAuthor(string author, int percentage)
{
OracleConnection oc = GetOracleConnection();
oc.Open();
OracleTransaction ot = oc.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
try
{
if (author == string.Empty)
{
throw new ArgumentNullException();
}
if (percentage < 0)
{
throw new ArgumentException();
}
OracleCommand command = new OracleCommand()
{
CommandType = System.Data.CommandType.Text,
CommandText = "UPDATE books SET discount_price = price * (1 - :percentage) WHERE author = :author"
};
OracleParameter PercentageParameter = new OracleParameter()
{
DbType = System.Data.DbType.Double,
ParameterName = ":percentage",
Direction = System.Data.ParameterDirection.Input,
Value = percentage / 100.0
};
command.Parameters.Add(PercentageParameter);
OracleParameter authorParameter = new OracleParameter()
{
DbType = System.Data.DbType.String,
ParameterName = ":author",
Direction = System.Data.ParameterDirection.Input,
Value = author
};
command.Parameters.Add(authorParameter);
command.Connection = oc;
command.Transaction = ot;
int affectedRows = 0;
affectedRows = command.ExecuteNonQuery();
ot.Commit();
return(affectedRows);
}
catch (OracleException ex)
{
throw new FaultException <ArgumentFault>(new ArgumentFault(ex.Message));
}
catch (ArgumentNullException ex)
{
throw new FaultException <ArgumentNullFault>(new ArgumentNullFault(ex.Message));
}
catch (ArgumentException ex)
{
throw new FaultException <ArgumentFault>(new ArgumentFault(ex.Message));
}
catch (Exception ex)
{
ot.Rollback();
throw new FaultException(new FaultReason(ex.Message));
}
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public static bool ExecuteSqlTran(string conStr, List <CommandInfo> cmdList)
{
using (OracleConnection conn = new OracleConnection(conStr))
{
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
OracleTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
foreach (CommandInfo c in cmdList)
{
if (!String.IsNullOrEmpty(c.CommandText))
{
PrepareCommand(cmd, conn, tx, CommandType.Text, c.CommandText, (OracleParameter[])c.Parameters);
if (c.EffentNextType == EffentNextType.WhenHaveContine || c.EffentNextType == EffentNextType.WhenNoHaveContine)
{
if (c.CommandText.ToLower().IndexOf("count(") == -1)
{
tx.Rollback();
throw new Exception("Oracle:违背要求" + c.CommandText + "必须符合select count(..的格式");
//return false;
}
object obj = cmd.ExecuteScalar();
bool isHave = false;
if (obj == null && obj == DBNull.Value)
{
isHave = false;
}
isHave = Convert.ToInt32(obj) > 0;
if (c.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
{
tx.Rollback();
throw new Exception("Oracle:违背要求" + c.CommandText + "返回值必须大于0");
//return false;
}
if (c.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
{
tx.Rollback();
throw new Exception("Oracle:违背要求" + c.CommandText + "返回值必须等于0");
//eturn false;
}
continue;
}
int res = cmd.ExecuteNonQuery();
if (c.EffentNextType == EffentNextType.ExcuteEffectRows && res == 0)
{
tx.Rollback();
throw new Exception("Oracle:违背要求" + c.CommandText + "必须有影像行");
// return false;
}
}
}
tx.Commit();
return(true);
}
catch (System.Data.OracleClient.OracleException E)
{
tx.Rollback();
throw E;
}
finally
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
}
}
/// <summary>
/// 提交一个事务
/// </summary>
public void CommitTrans()
{
trans.Commit();
}
public override void TerminarTransaccion()
{
trn.Commit();
}
/// <summary>
/// 发送打印命令
/// </summary>
/// <param name="connId"></param>
/// <param name="clientHandle"></param>
/// <param name="commandNum"></param>
/// <param name="commandString"></param>
/// <param name="para_arry"></param> 不定参数 但实际运行只有2或8个参数（对应Oracle 过程重载）
/// <returns></returns>
public static int SendPrtCommand(int connId, int clientHandle, int commandNum, string commandString, params string[] para_arry)
{
OracleCommand cmd = new OracleCommand("pkg_business.prc_SendPrtCommand", SqlAssist.conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
OracleTransaction trans = null;
//连接Id
OracleParameter op_connId = new OracleParameter("in_connectId", OracleDbType.Int32, 10);
op_connId.Direction = ParameterDirection.Input;
op_connId.Value = connId;
//打印客户端窗口
OracleParameter op_clientHandle = new OracleParameter("in_clientHandle", OracleDbType.Int32);
op_clientHandle.Direction = ParameterDirection.Input;
op_clientHandle.Value = clientHandle;
//打印命令编号
OracleParameter op_commandNum = new OracleParameter("in_commandNum", OracleDbType.Int32);
op_commandNum.Direction = ParameterDirection.Input;
op_commandNum.Value = commandNum;
//打印命令字符串
OracleParameter op_commandString = new OracleParameter("ic_commandString", OracleDbType.Varchar2, 50);
op_commandString.Direction = ParameterDirection.Input;
op_commandString.Value = commandString;
List <OracleParameter> para_list = new List <OracleParameter>();
OracleParameter op_1 = null;
for (int i = 0; i < para_arry.Length; i++)
{
op_1 = new OracleParameter("ic_para" + (i + 1).ToString(), OracleDbType.Varchar2, 200);
op_1.Direction = ParameterDirection.Input;
op_1.Value = para_arry[i];
para_list.Add(op_1);
}
////参数1
//OracleParameter op_para1 = new OracleParameter("ic_para1", OracleDbType.Varchar2, 50);
//op_para1.Direction = ParameterDirection.Input;
//op_para1.Value = para1;
////参数2
//OracleParameter op_para2 = new OracleParameter("ic_para2", OracleDbType.Varchar2, 50);
//op_para2.Direction = ParameterDirection.Input;
//op_para2.Value = para2;
////参数3 - 客户名称
//OracleParameter op_para3 = new OracleParameter("ic_para3", OracleDbType.Varchar2, 200);
//op_para3.Direction = ParameterDirection.Input;
//op_para3.Value = para3;
////参数4 - 客户税号
//OracleParameter op_para4 = new OracleParameter("ic_para4", OracleDbType.Varchar2, 50);
//op_para4.Direction = ParameterDirection.Input;
//op_para4.Value = para4;
////参数5 - 客户 开户行及账号
//OracleParameter op_para5 = new OracleParameter("ic_para5", OracleDbType.Varchar2, 200);
//op_para5.Direction = ParameterDirection.Input;
//op_para5.Value = para5;
////参数6 - 客户 地址及电话
//OracleParameter op_para6 = new OracleParameter("ic_para6", OracleDbType.Varchar2, 200);
//op_para6.Direction = ParameterDirection.Input;
//op_para6.Value = para6;
////参数7 - 收款人
//OracleParameter op_para7 = new OracleParameter("ic_para7", OracleDbType.Varchar2, 50);
//op_para7.Direction = ParameterDirection.Input;
//op_para7.Value = para7;
////参数8 - 审核人
//OracleParameter op_para8 = new OracleParameter("ic_para8", OracleDbType.Varchar2, 50);
//op_para8.Direction = ParameterDirection.Input;
//op_para8.Value = para8;
OracleParameter appcode = new OracleParameter("on_appcode", OracleDbType.Int16);
appcode.Direction = ParameterDirection.Output;
OracleParameter apperror = new OracleParameter("oc_error", OracleDbType.Varchar2, 100);
apperror.Direction = ParameterDirection.Output;
try
{
trans = SqlAssist.conn.BeginTransaction();
para_list.InsertRange(0, new OracleParameter[] { op_connId, op_clientHandle, op_commandNum, op_commandString });
para_list.AddRange(new OracleParameter[] { appcode, apperror });
cmd.Parameters.AddRange(para_list.ToArray());
cmd.ExecuteNonQuery();
if (int.Parse(appcode.Value.ToString()) < 0)
{
trans.Rollback();
MessageBox.Show(apperror.Value.ToString(), "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(-1);
}
trans.Commit();
SendMessage(Envior.prtservHandle, 0x2710, commandNum, 0);
return(1);
}
catch (InvalidOperationException e)
{
trans.Rollback();
MessageBox.Show("执行过程错误!\n" + e.ToString(), "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(-1);
}
finally
{
cmd.Dispose();
}
}
/************************************************************
* Function name : SetFAQ
* Purpose : FAQ 저장 (Insert)
* Input : string[] rParams
* Output : string
*************************************************************/
#region public string SetFAQ(string[] rParams)
public string SetFAQ(string[] rParams)
{
string xRtn = Boolean.FalseString;
Database db = base.GetDataBase("LMS");
OracleConnection xCnnLMS = (OracleConnection)db.CreateConnection();
xCnnLMS.Open();
OracleTransaction xTransLMS = null;
OracleCommand xCmdLMS = null;
try
{
string xFilePath = string.Empty;
xTransLMS = xCnnLMS.BeginTransaction(); // 트랜잭션 시작
xCmdLMS = base.GetSqlCommand(db);
xCmdLMS.Connection = xCnnLMS;
xCmdLMS.Transaction = xTransLMS;
//string xSeq = GetMaxIDOfCode(new string[] { "boa_no", "t_board" }, xCmdLMS);
try
{
string xSql = string.Empty;
xSql += " INSERT INTO t_board ( ";
xSql += " boa_no, ";
xSql += " boa_sub, ";
xSql += " boa_content, ";
xSql += " hit_cnt, ";
xSql += " del_yn, ";
xSql += " boa_kind, ";
xSql += " upt_id, ";
xSql += " upt_dt, ";
xSql += " ins_id, ";
xSql += " ins_dt) ";
xSql += " VALUES ( ";
xSql += string.Format(" {0}, ", rParams[3]);
xSql += string.Format(" '{0}', ", rParams[0]);
//xSql += string.Format(" '{0}', ", rParams[1]);
xSql += " :BOACONTENT, ";
xSql += " 0, ";
xSql += " 'N', ";
xSql += " '000002', "; // 000001 Q&A, 000002 FAQ, 000003 자료실
xSql += string.Format(" '{0}', ", rParams[2]); // 수정자 ID
xSql += " SYSDATE, "; // 수정일시
xSql += string.Format(" '{0}', ", rParams[2]); // 생성자 ID
xSql += " SYSDATE) "; // 생성일시
xCmdLMS.CommandText = xSql;
OracleParameter BOACONTENT = new OracleParameter();
BOACONTENT.OracleType = OracleType.Clob;
BOACONTENT.ParameterName = "BOACONTENT";
BOACONTENT.Value = rParams[1];
xCmdLMS.Parameters.Add(BOACONTENT);
base.Execute(db, xCmdLMS, xTransLMS);
xTransLMS.Commit(); // 트랜잭션 커밋
xRtn = Boolean.TrueString;
}
catch (Exception ex)
{
xTransLMS.Rollback(); // Exception 발생시 롤백처리...
throw ex;
}
finally
{
if (xCmdLMS != null)
{
xCmdLMS.Dispose();
}
if (xTransLMS != null)
{
xTransLMS.Dispose();
}
}
}
catch (Exception ex)
{
bool rethrow = ExceptionPolicy.HandleException(ex, "Propagate Policy");
if (rethrow)
{
throw;
}
}
return(xRtn);
}
public string SaveRecoginze()
{
JObject json_user = Extension.Get_UserInfo(HttpContext.User.Identity.Name);
string id = Request["id"]; string cusno = Request["cusno"]; string ordercode = Request["ordercode"];
string result = "{success:false}"; string sql = "";
sql = "select * from list_filerecoginze where id =" + id;
DataTable dt = DBMgr.GetDataTable(sql);
if (dt == null)
{
return(result);
}
if (dt.Rows.Count != 1)
{
return(result);
}
if (dt.Rows[0]["STATUS"].ToString() == "已关联")//已经关联
{
return("{success:true,flag:'Y'}");
}
//----------------------------------------------------------------------------------------------------------------------
//--list_filerecoginze
string filepath = dt.Rows[0]["FILEPATH"].ToString();
string originalname = dt.Rows[0]["FILENAME"].ToString(); string filesuffix = originalname.Substring(originalname.LastIndexOf(".") + 1).ToUpper();
string direc_pdf = Request.PhysicalApplicationPath;
string bakpath = direc_pdf + @"/FileUpload/filereconginze/bak/";//备份原始文件目录
if (!Directory.Exists(bakpath))
{
Directory.CreateDirectory(bakpath);
}
FileInfo fi = new FileInfo(direc_pdf + filepath);
System.Uri Uri = new Uri("ftp://" + ConfigurationManager.AppSettings["FTPServer"] + ":" + ConfigurationManager.AppSettings["FTPPortNO"]);
string UserName = ConfigurationManager.AppSettings["FTPUserName"];
string Password = ConfigurationManager.AppSettings["FTPPassword"];
FtpHelper ftp = new FtpHelper(Uri, UserName, Password);
DataTable dt_order = new DataTable();
//根据识别出的订单号，查询是否存在此订单
sql = "select * from list_order a where a.ISINVALID=0";
if (ordercode != "")
{
sql += " and a.code='" + ordercode + "'";
}
if (cusno != "")
{
sql += " and a.cusno='" + cusno + "'";
}
dt_order = DBMgr.GetDataTable(sql);
if (dt_order == null)
{
return("{success:true,flag:'E'}");
}
if (dt_order.Rows.Count <= 0)
{
return("{success:true,flag:'E'}");
}
//如果为空的话，再次赋值
if (ordercode == "")
{
ordercode = dt_order.Rows[0]["CODE"].ToString();
}
if (cusno == "")
{
cusno = dt_order.Rows[0]["CUSNO"].ToString();
}
string associateno = dt_order.Rows[0]["ASSOCIATENO"].ToString();
string newfilepath = "/44/" + ordercode + "/" + filepath.Substring(filepath.LastIndexOf(@"/") + 1);
OracleConnection conn = null;
OracleTransaction ot = null;
conn = DBMgr.getOrclCon();
try
{
conn.Open();
ot = conn.BeginTransaction();
string sql_insert = "";
if (associateno != "")//两单关联
{
sql_insert = @"insert into LIST_ATTACHMENT (id
,filename,originalname,filetype,uploadtime,ordercode,sizes,filetypename
,filesuffix,IETYPE,uploaduserid,uploadusername)
values(List_Attachment_Id.Nextval
,'{0}','{1}','{2}',sysdate,'{3}','{4}','{5}'
,'{6}','{7}','{8}','{9}')";
sql_insert = string.Format(sql_insert
, "/44/" + ordercode + "/" + filepath.Substring(filepath.LastIndexOf(@"/") + 1), originalname, "44", ordercode, fi.Length, "订单文件"
, filesuffix, dt_order.Rows[0]["BUSITYPE"].ToString() == "40" ? "仅出口" : "仅进口", json_user.Value <string>("ID"), json_user.Value <string>("REALNAME"));
DBMgr.ExecuteNonQuery(sql_insert, conn);
DataTable dt_asOrder = new DataTable();
if (associateno != "")//两单关联
{
dt_asOrder = DBMgr.GetDataTable("select * from list_order a where a.ISINVALID=0 and ASSOCIATENO='" + associateno + "' and code!='" + ordercode + "'");
}
if (dt_asOrder == null)
{
}
else if (dt_asOrder.Rows.Count < 0)
{
}
else
{
sql_insert = @"insert into LIST_ATTACHMENT (id
,filename,originalname,filetype,uploadtime,ordercode,sizes,filetypename
,filesuffix,IETYPE,uploaduserid,uploadusername)
values(List_Attachment_Id.Nextval
,'{0}','{1}','{2}',sysdate,'{3}','{4}','{5}'
,'{6}','{7}','{8}','{9}')";
sql_insert = string.Format(sql_insert
, "/44/" + dt_asOrder.Rows[0]["code"].ToString() + "/" + filepath.Substring(filepath.LastIndexOf(@"/") + 1), originalname, "44", dt_asOrder.Rows[0]["code"].ToString(), fi.Length, "订单文件"
, filesuffix, dt_asOrder.Rows[0]["BUSITYPE"].ToString() == "40" ? "仅出口" : "仅进口", json_user.Value <string>("ID"), json_user.Value <string>("REALNAME"));
DBMgr.ExecuteNonQuery(sql_insert, conn);
}
}
else
{
sql_insert = @"insert into LIST_ATTACHMENT (id
,filename,originalname,filetype,uploadtime,ordercode,sizes,filetypename
,filesuffix,uploaduserid,uploadusername)
values(List_Attachment_Id.Nextval
,'{0}','{1}','{2}',sysdate,'{3}','{4}','{5}'
,'{6}','{7}','{8}')";
sql_insert = string.Format(sql_insert
, "/44/" + ordercode + "/" + filepath.Substring(filepath.LastIndexOf(@"/") + 1), originalname, "44", ordercode, fi.Length, "订单文件"
, filesuffix, json_user.Value <string>("ID"), json_user.Value <string>("REALNAME"));
DBMgr.ExecuteNonQuery(sql_insert, conn);
}
//关联成功 ，文件挪到自动上传到文件服务器的目录，并删除原始目录的文件、修改原始路径为服务器新路径
DBMgr.ExecuteNonQuery("update list_filerecoginze set status='已关联',ordercode='" + ordercode + "',cusno='" + cusno
+ "',filepath='/44/" + ordercode + "/" + filepath.Substring(filepath.LastIndexOf(@"/") + 1) + "' where id=" + id, conn);
bool res = ftp.UploadFile(direc_pdf + filepath, newfilepath, true);
if (res)
{
ot.Commit();
fi.CopyTo(bakpath + filepath.Substring(filepath.LastIndexOf(@"/") + 1));
fi.Delete();
}
else
{
ot.Rollback();
DBMgr.ExecuteNonQuery("update list_filerecoginze set status='关联失败',ordercode='" + ordercode + "',cusno='" + cusno + "' where id=" + id);
}
//Submit(ordercode, json_user);//add 提交委托
result = "{success:true}";
}
catch (Exception ex)
{
ot.Rollback();
}
finally
{
conn.Close();
}
return(result);
}
/************************************************************
* Function name : SetFAQDelete
* Purpose : 공지사항 삭제(기존 공지사항 저장 Update 삭제유무 Y 로 변경)
* Input : string rParams(not_no)
* Output : String
*************************************************************/
#region public string SetFAQDelete(string rParams)
public string SetFAQDelete(string rParams)
{
string xRtn = Boolean.FalseString;
Database db = base.GetDataBase("LMS");
OracleConnection xCnnLMS = (OracleConnection)db.CreateConnection();
xCnnLMS.Open();
OracleTransaction xTransLMS = null;
OracleCommand xCmdLMS = null;
try
{
xTransLMS = xCnnLMS.BeginTransaction(); // 트랜잭션 시작
xCmdLMS = base.GetSqlCommand(db);
xCmdLMS.Connection = xCnnLMS;
xCmdLMS.Transaction = xTransLMS;
try
{
string xSql = string.Empty;
xSql += " UPDATE t_board SET ";
xSql += string.Format(" del_yn = '{0}' ", "Y");
xSql += string.Format(" WHERE boa_no = {0} ", Convert.ToInt32(rParams));
xCmdLMS.CommandText = xSql;
base.Execute(db, xCmdLMS, xTransLMS);
xTransLMS.Commit(); // 트랜잭션 커밋
xRtn = Boolean.TrueString;
}
catch (Exception ex)
{
xTransLMS.Rollback(); // Exception 발생시 롤백처리...
throw ex;
}
finally
{
if (xCmdLMS != null)
{
xCmdLMS.Dispose();
}
if (xTransLMS != null)
{
xTransLMS.Dispose();
}
}
}
catch (Exception ex)
{
bool rethrow = ExceptionPolicy.HandleException(ex, "Propagate Policy");
if (rethrow)
{
throw;
}
}
return(xRtn);
}
// 保存
private void saveParam()
{
using (OracleConnection con = new OracleConnection(OracleHelper.conn))
{
con.Open();
OracleTransaction tra = con.BeginTransaction(); //创建事务，开始执行事务
try
{
string strBah = this.tbbah.Text.Trim().ToUpper();
OracleHelper.ExecuteNonQuery(tra, String.Format("DELETE FROM FC_CLJBXX WHERE VIN = '{0}' AND STATUS='1'", strBah), null);
OracleHelper.ExecuteNonQuery(tra, String.Format("DELETE FROM RLLX_PARAM_ENTITY WHERE VIN ='{0}'", strBah), null);
#region 遍历参数,保存
foreach (Control c in this.tlp.Controls)
{
if (c is TextEdit || c is DevExpress.XtraEditors.ComboBoxEdit)
{
string paramCode = c.Name;
string paramValue = c.Text;
OracleParameter[] paramList =
{
new OracleParameter("PARAM_CODE", paramCode),
new OracleParameter("VIN", strBah),
new OracleParameter("PARAM_VALUE", paramValue),
new OracleParameter("V_ID", "")
};
OracleHelper.ExecuteNonQuery(tra, (string)@"INSERT INTO RLLX_PARAM_ENTITY (PARAM_CODE,VIN,PARAM_VALUE,V_ID)
VALUES(:PARAM_CODE,:VIN,:PARAM_VALUE,:V_ID)", paramList);
}
}
#endregion
#region 保存车辆基本信息
// 保存车辆基本信息
DateTime clzzrqDate = DateTime.Parse(this.dtclzzrq.Text.Trim());
OracleParameter clzzrq = new OracleParameter("CLZZRQ", clzzrqDate)
{
DbType = DbType.Date
};
DateTime uploadDeadlineDate = Utils.QueryUploadDeadLine(clzzrqDate);
OracleParameter uploadDeadline = new OracleParameter("UPLOADDEADLINE", uploadDeadlineDate)
{
DbType = DbType.Date
};
OracleParameter creTime = new OracleParameter("CREATETIME", DateTime.Now)
{
DbType = DbType.Date
};
OracleParameter upTime = new OracleParameter("UPDATETIME", DateTime.Now)
{
DbType = DbType.Date
};
OracleParameter[] param =
{
new OracleParameter("VIN", this.tbbah.Text.Trim().ToUpper()),
new OracleParameter("HGSPBM", this.tbHgspbm.Text.Trim().ToUpper()),
new OracleParameter("USER_ID", Utils.localUserId),
new OracleParameter("QCSCQY", this.tbqcscqy.Text.Trim()),
new OracleParameter("JKQCZJXS", this.tbjkqczjxs.Text.Trim()),
new OracleParameter("CLXH", this.tbclxh.Text.Trim()),
new OracleParameter("CLZL", this.cbclzl.Text.Trim()),
new OracleParameter("RLLX", this.cbrllx.Text.Trim()),
new OracleParameter("ZCZBZL", this.tbzczbzl.Text.Trim()),
new OracleParameter("ZGCS", this.tbzgcs.Text.Trim()),
new OracleParameter("LTGG", this.tbltgg.Text.Trim()),
new OracleParameter("ZJ", this.tbzj.Text.Trim()),
clzzrq,
uploadDeadline,
new OracleParameter("TYMC", this.tbtymc.Text.Trim()),
new OracleParameter("YYC", this.cbyyc.Text.Trim()),
new OracleParameter("ZWPS", this.tbzwps.Text.Trim()),
new OracleParameter("ZDSJZZL", this.tbzdsjzzl.Text.Trim()),
new OracleParameter("EDZK", this.tbedzk.Text.Trim()),
new OracleParameter("LJ", this.tblj.Text.Trim()),
new OracleParameter("QDXS", this.cbqdxs.Text.Trim()),
new OracleParameter("STATUS", "1"),
new OracleParameter("JYJGMC", this.tbjcjgmc.Text.Trim()),
new OracleParameter("JYBGBH", this.tbbgbh.Text.Trim()),
new OracleParameter("QTXX", this.tbQtxx.Text.Trim()),
creTime,
upTime,
};
OracleHelper.ExecuteNonQuery(tra, (string)@"INSERT INTO FC_CLJBXX
( VIN,
HGSPBM,
USER_ID,
QCSCQY,
JKQCZJXS,
CLXH,
CLZL,
RLLX,
ZCZBZL,
ZGCS,
LTGG,
ZJ,
CLZZRQ,
UPLOADDEADLINE,
TYMC,
YYC,
ZWPS,
ZDSJZZL,
EDZK,
LJ,
QDXS,
STATUS,
JYJGMC,
JYBGBH,
QTXX,
CREATETIME,
UPDATETIME
) VALUES
( :VIN,
:HGSPBM,
:USER_ID,
:QCSCQY,
:JKQCZJXS,
:CLXH,
:CLZL,
:RLLX,
:ZCZBZL,
:ZGCS,
:LTGG,
:ZJ,
:CLZZRQ,
:UPLOADDEADLINE,
:TYMC,
:YYC,
:ZWPS,
:ZDSJZZL,
:EDZK,
:LJ,
:QDXS,
:STATUS,
:JYJGMC,
:JYBGBH,
:QTXX,
:CREATETIME,
:UPDATETIME)", param);
tra.Commit();
strVin = strBah; //备案号
#endregion
}
catch (Exception ex)
{
tra.Rollback();
throw ex;
}
}
}
/// <summary>
/// Ajout en base d'un utilisateur
/// </summary>
/// <param name="connection"></param>
/// <param name="strDefaultTablespace"></param>
/// <returns></returns>
public bool Add(OracleConnection connection, string strDefaultTablespace)
{
bool bResult = false;
// Start a local transaction
OracleTransaction transaction = connection.BeginTransaction();
OracleCommand command = new OracleCommand();
command.Connection = connection;
string sql = "";
OracleDataReader sqlReader = null;
/*long idTemporaryTablespace = -1;
* string strTemporaryTableSpace = "";*/
try
{
/*//récupère la tablespace disponible avec le nombre d'utilisateur min
* sql = "SELECT id_temporary_tablespace, temporary_tablespace FROM MOU01.temporary_tablespace WHERE (max_user-user_count)=(select min(max_user-user_count) from MOU01.temporary_tablespace where (max_user-user_count) > 0)";
* command.CommandText = sql;
* sqlReader=command.ExecuteReader();
* //lecture des données
* if (sqlReader.Read())
* {
* idTemporaryTablespace = sqlReader.GetInt64(0);
* strTemporaryTableSpace = sqlReader.GetString(1);
* }
* if(idTemporaryTablespace == -1)
* MessageBox.Show("Pas de TableSpace disponible, impossible de créer un nouvel utilisateur.");
* else
* {*/
//création d'un utilisateur oracle
sql = "CREATE USER " + this.m_strUserOracle + @" IDENTIFIED BY """ + this.m_strPassword + @""" " +
"DEFAULT TABLESPACE " + strDefaultTablespace.Replace("'", "''").ToUpper() + " " +
"TEMPORARY TABLESPACE " + this.TemporaryTableSpace.Replace("'", "''").ToUpper();
command.CommandText = sql;
command.ExecuteNonQuery();
//Mise à jour de la Tablespace temporaire
sql = "UPDATE MOU01.temporary_tablespace SET user_count=(user_count+1) WHERE id_temporary_tablespace=" + this.IdTemporaryTableSpace.ToString("0");
command.CommandText = sql;
command.ExecuteNonQuery();
//Ajout de l'utilisateur dans la table user_
short dActivation = AccountMgmt.Common.Constants.DesactivationLevel;
if (this.m_bActivation)
{
dActivation = AccountMgmt.Common.Constants.ActivationLevel;
}
else
{
dActivation = AccountMgmt.Common.Constants.DesactivationLevel;
}
string strDateCreation = "TO_DATE ('" + m_dtCreation.Day.ToString("00") + "/" + m_dtCreation.Month.ToString("00") + "/" + m_dtCreation.Year.ToString("0000") + " " + m_dtCreation.Hour.ToString("00") + ":" + m_dtCreation.Minute.ToString("00") + ":" + m_dtCreation.Second.ToString("00") + "', 'DD/MM/YYYY HH24:MI:SS')";
//string strDateCreation = "TO_DATE ('" + this.m_dtCreation.ToString("G") + "', 'DD/MM/YYYY HH24:MI:SS')";
string strDateBeginning = "NULL";
if (this.m_dtBeginning != new DateTime())
{
strDateBeginning = "TO_DATE ('" + m_dtBeginning.Day.ToString("00") + "/" + m_dtBeginning.Month.ToString("00") + "/" + m_dtBeginning.Year.ToString("0000") + " " + m_dtBeginning.Hour.ToString("00") + ":" + m_dtBeginning.Minute.ToString("00") + ":" + m_dtBeginning.Second.ToString("00") + "', 'DD/MM/YYYY HH24:MI:SS')";
}
//strDateBeginning = "TO_DATE ('" + this.m_dtBeginning.ToString("G") + "', 'DD/MM/YYYY HH24:MI:SS')";
string strDateEnd = "NULL";
if (this.m_dtEnd != new DateTime())
{
strDateEnd = "TO_DATE ('" + m_dtEnd.Day.ToString("00") + "/" + m_dtEnd.Month.ToString("00") + "/" + m_dtEnd.Year.ToString("0000") + " " + m_dtEnd.Hour.ToString("00") + ":" + m_dtEnd.Minute.ToString("00") + ":" + m_dtEnd.Second.ToString("00") + "', 'DD/MM/YYYY HH24:MI:SS')";
}
//strDateEnd = "TO_DATE ('" + this.m_dtEnd.ToString("G") + "', 'DD/MM/YYYY HH24:MI:SS')";
sql = "INSERT INTO MOU01.user_ VALUES(MOU01.SEQ_USER_.NEXTVAL,33,1,'" + this.m_strUser.Replace("'", "''") + "','" +
this.m_strUserOracle + "','" + this.m_strService.Replace("'", "''") + "'," + strDateCreation + ",NULL,'" + this.m_strCommentary.Replace("'", "''") +
"'," + dActivation.ToString("0") + ",33,'" + this.m_strPassword.Replace("'", "''") + "'," + strDateBeginning + "," + strDateEnd + "," + this.m_dIdUserModification.ToString("0") + "," + this.IdTemporaryTableSpace.ToString("0") +
", NULL, " + this.m_dAdmin.ToString("0") + ", " + this.m_dIdSource.ToString("0") + ")";
command.CommandText = sql;
command.ExecuteNonQuery();
//granter les roles de manager si utilisateur est un responsable
if (this.m_dAdmin == AccountMgmt.Common.Constants.ResponsableLevel)
{
sql = "GRANT MANAGE_RIGHT_SEL TO " + this.m_strUserOracle;
command.CommandText = sql;
command.ExecuteNonQuery();
sql = "GRANT MANAGE_RIGHT_MOD TO " + this.m_strUserOracle;
command.CommandText = sql;
command.ExecuteNonQuery();
}
//recherche de l'identificateur du nouvel utilisateur
sql = "SELECT id_user_ FROM MOU01.list_user WHERE user_oracle='" + this.m_strUserOracle + "'";
command.CommandText = sql;
sqlReader = command.ExecuteReader();
//lecture des données
if (sqlReader.Read())
{
this.m_dIdUser = long.Parse(sqlReader.GetValue(0).ToString()); //sqlReader.GetInt64(0);
}
bResult = true;
//}
if (bResult)
{
transaction.Commit();
}
}
catch (Exception error)
{
transaction.Rollback();
MessageBox.Show("Problème lors de la création de l'utilisateur avec l'erreur : " + error.Message);
}
finally
{
//deconnexion
try
{
// Fermeture de la base de données
if (sqlReader != null)
{
sqlReader.Close();
sqlReader.Dispose();
}
if (command != null)
{
command.Dispose();
}
}
catch (Exception error)
{
MessageBox.Show("Message d’erreur : " + error.Message);
}
}
return(bResult);
}
/// <summary>
/// Fonction qui permet d'enregistrer un paiement
/// </summary>
/// <param name="pMontantCheque">Numero du cheque du licencie</param>
/// <param name="pNumeroCheque">Numero du cheque du licencie</param>
/// <param name="pNumeroLicencie">Numero de licence du licencie</param>
/// <param name="pTypePaiement">Type de paiement du licencie</param>
public void EnregistrerPaiement(Double pMontantCheque, Int64 pNumeroCheque, Int64 pNumeroLicencie, String pTypePaiement)
{
Int64 MonIdLicencie = 0;
UneOracleCommand = new OracleCommand("Select Idlicencie from licencie where numerolicence =" + pNumeroLicencie, CnOracle);
OracleDataReader rdr = UneOracleCommand.ExecuteReader();
while (rdr.Read())
{
MonIdLicencie = System.Convert.ToInt64(rdr[0]);
}
UneOracleCommand = new OracleCommand("pckparticipant.ENREGISTREPAIEMENT", CnOracle);
UneOracleCommand.CommandType = CommandType.StoredProcedure;
// début de la transaction Oracle : il vaut mieyx gérer les transactions dans l'applicatif que dans la bd.
UneOracleTransaction = this.CnOracle.BeginTransaction();
this.ParamsSpecifiquePaiement(UneOracleCommand, pMontantCheque, pNumeroCheque, pTypePaiement, MonIdLicencie);
UneOracleCommand.ExecuteNonQuery();
// fin de la transaction. Si on arrive à ce point, c'est qu'aucune exception n'a été levée
UneOracleTransaction.Commit();
}
