public bool CreateMyCustomer(Customer customer, out string errorMsg) { errorMsg = String.Empty; using (OracleConnection conn = _connectionDao.GetConnection()) { OracleTransaction tran = null; try { conn.Open(); tran = conn.BeginTransaction(); using (var cmd = conn.CreateCommand()) { cmd.CommandText = "MY_TEST_PACKAGE.USR_CUSTOMER_INST"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Clear(); cmd.Parameters.Add("PCUSNAME", OracleDbType.Varchar2, customer.CusName, ParameterDirection.Input); cmd.Parameters.Add("PCUSFATHERNAME", OracleDbType.Varchar2, customer.CusFatherName, ParameterDirection.Input); cmd.Parameters.Add("PCUSMOTHERNAME", OracleDbType.Varchar2, customer.CusMotherName, ParameterDirection.Input); cmd.Parameters.Add("PCUSPHONE", OracleDbType.Varchar2, customer.CusPhone, ParameterDirection.Input); OracleParameter parError = cmd.Parameters.Add("PERROR", OracleDbType.Varchar2, ParameterDirection.Output); parError.Size = 500; cmd.ExecuteNonQuery(); if (parError.Status == OracleParameterStatus.Success) { errorMsg = $"{parError.Value}"; } } tran.Commit(); return(errorMsg.ToUpper() == "NOERROR"); } #if DEBUG catch (OracleException oraEx) { tran?.Rollback(); throw new DatabaseException(oraEx.Message, ExceptionConstants.CommonUserExceptionMessage); } #endif catch (Exception ex) { tran?.Rollback(); throw new DatabaseException(ex.Message, ExceptionConstants.CommonUserExceptionMessage); } } }
public override void Save() { OracleConnection connection = GetConnection(); OracleTransaction transaction = null; try { connection.Open(); transaction = connection.BeginTransaction(); foreach (var entity in eStates) { if (entity.EState == State.NEW) { InsertOne(entity.Entity, connection, transaction); } if (entity.EState == State.DELETED) { DeleteOne(entity.Entity, connection, transaction); } if (entity.EState == State.CHANGED) { UpdateOne(entity.Entity, connection, transaction); } } transaction.Commit(); } catch (Exception ex) { transaction?.Rollback(); Console.WriteLine(ex.Message); } finally { connection?.Close(); } }
public override void Save(IEnumerable <Entity> entitiesForInsert, IEnumerable <Entity> entitiesForUpdate, IEnumerable <Entity> entitiesForDelete) { OracleConnection connection = GetConnection(); OracleTransaction transaction = null; try { connection.Open(); transaction = connection.BeginTransaction(); if ((entitiesForInsert != null) && entitiesForInsert.Count() > 0) { InsertImpl(entitiesForInsert, connection, transaction); } if ((entitiesForUpdate != null) && entitiesForUpdate.Count() > 0) { UpdateImpl(entitiesForUpdate, connection, transaction); } if ((entitiesForDelete != null) && entitiesForDelete.Count() > 0) { DeleteImpl(entitiesForDelete, connection, transaction); } transaction.Commit(); } catch (Exception ex) { transaction?.Rollback(); Console.WriteLine(ex.Message); } finally { connection?.Close(); } }
public void Dispose() { _trans.Rollback(); _membershipTrans.Rollback(); }
/************************************************************ * Function name : SetNotice * Purpose : 공지사항 저장 * * Input : string[] rParams * Output : String *************************************************************/ public string SetNotice(params object[] rParams) { string xRtn = Boolean.FalseString; string xDebug = string.Empty; 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; string xFilePath = string.Empty; string[] rMasterParams = (string[])rParams[0]; object[,] rDetailParams = (object[, ])rParams[1]; string xSeq = rMasterParams[7]; string xSendFLG = rMasterParams[8]; //string xSeq = GetMaxIDOfCode(new string[] { "not_no" }, xCmdLMS); //xDebug = "seq : " + xSeq; try { string xSql = string.Empty; xSql += " INSERT INTO t_notice ( "; xSql += " not_no, "; // 공지사항 번호 xSql += " not_kind, "; // 공지사항 타입 m_cd = '0055' , 000001: 일반공지 , 000002: 과정공지 xSql += " open_course_id, "; // 과정 ID xSql += " notice_begin_dt, "; // 공지사항 게시일자 xSql += " notice_end_dt, "; // 공지사항 종료일자 xSql += " not_sub, "; // 공지사항 제목 xSql += " not_content, "; // 공지사항 내용 xSql += " hit_cnt, "; // 읽은수 xSql += " del_yn, "; // 삭제유무 xSql += " send_flg, "; // 전송유무 1: 전송대기, 2: 전송 xSql += " company_kind, "; // 업체구분 m_cd = 0061 회사구분 (그룹사, 사업자 위수탁) xSql += " upt_id, "; // 수정자 ID xSql += " upt_dt, "; // 수정일시 xSql += " ins_id, "; // 생성자 ID xSql += " ins_dt) "; // 생성일시 xSql += " VALUES( "; xSql += string.Format(" {0}, ", Convert.ToInt32(xSeq)); // 공지사항 번호 xSql += string.Format(" '{0}', ", rMasterParams[0]); // 공지사항 타입 m_cd = '0055' , 000001: 일반공지 , 000002: 과정공지 xSql += string.Format(" '{0}', ", rMasterParams[1]); // 과정 ID (일반공지의 경우 Empty Value) xSql += string.Format(" TO_DATE('{0}','YYYY.MM.DD.HH24MISS'), ", rMasterParams[2] + "000000"); // 공지 게시일자 xSql += string.Format(" TO_DATE('{0}','YYYY.MM.DD.HH24MISS'), ", rMasterParams[3] + "235959"); // 공지 종료일자 xSql += string.Format(" '{0}', ", rMasterParams[4]); // 공지사항 제목 xSql += " :NOTCONTENT, "; //xSql += string.Format(" '{0}', ", rMasterParams[5]); // 공지사항 내용 xSql += " 0, "; // 읽은수 xSql += " 'N', "; // 삭제유무 xSql += string.Format(" '{0}', ", xSendFLG); // 전송유무 1: 전송대기, 2: 전송 xSql += string.Format(" '{0}', ", rMasterParams[9]); // 업체구분 m_cd = 0061 회사구분 (그룹사, 사업자 위수탁) xSql += string.Format(" '{0}', ", rMasterParams[6]); // 수정자 ID xSql += " SYSDATE, "; // 수정일시 xSql += string.Format(" '{0}', ", rMasterParams[6]); // 생성자 ID xSql += " SYSDATE) "; // 생성일시 OracleParameter NOTCONTENT = new OracleParameter(); NOTCONTENT.OracleType = OracleType.Clob; NOTCONTENT.ParameterName = "NOTCONTENT"; NOTCONTENT.Value = rMasterParams[5]; xCmdLMS.Parameters.Add(NOTCONTENT); xCmdLMS.CommandText = xSql; base.Execute(db, xCmdLMS, xTransLMS); for (int i = 0; i < rDetailParams.GetLength(0); i++) { byte[] xFileData = (byte[])rDetailParams[i, 0]; string[] xTemp = (string[])rDetailParams[i, 1]; xSql = string.Empty; xSql += " INSERT INTO t_notice_att ( "; xSql += " NOT_NO, "; xSql += " FILE_NM, "; xSql += " att_file "; xSql += " ) "; xSql += " VALUES ( "; xSql += string.Format(" {0}, ", Convert.ToInt32(xSeq)); xSql += string.Format(" '{0}', ", xTemp[0]); xSql += " :ATTFILE "; xSql += " ) "; OracleParameter ATTFILE = new OracleParameter(); ATTFILE.OracleType = OracleType.Blob; ATTFILE.ParameterName = "ATTFILE"; ATTFILE.Value = xFileData; xCmdLMS.Parameters.Add(ATTFILE); xCmdLMS.CommandText = xSql; base.Execute(db, xCmdLMS, xTransLMS); } if (xSendFLG == "1") // 전송대상이면 { OracleParameter[] oOraParams = new OracleParameter[2]; oOraParams[0] = base.AddParam("p_in_table", OracleType.VarChar, "T_NOTICE"); oOraParams[1] = base.AddParam("p_out_table", OracleType.VarChar, "T_BULL"); int i = base.Execute(db, CommandType.StoredProcedure, "pkg_lms_datasync.lms_export", oOraParams, xTransLMS); } xTransLMS.Commit(); // 트랜잭션 커밋 xRtn = Boolean.TrueString; } catch (Exception ex) { // 파일 제거 if (xFilePath.Trim() != "") { File.Delete(xFilePath); } xTransLMS.Rollback(); // Exception 발생시 롤백처리... //throw new Exception(xDebug + " | " + ex.Message); bool rethrow = ExceptionPolicy.HandleException(ex, "Propagate Policy"); if (rethrow) { throw; } } finally { if (xCmdLMS != null) { xCmdLMS.Dispose(); } if (xTransLMS != null) { xTransLMS.Dispose(); } } } catch (Exception ex) { throw ex; } return(xRtn); }
/// <summary> /// 回滚事务 /// </summary> public void Rollback() { p_trans.Rollback(); }
/// <summary> /// 执行多条SQL语句,实现数据库事务。 /// </summary> /// <param name="SQLStringList">多条SQL语句</param> public static int ExecuteSqlTranWithParaMeter(Dictionary <string, OracleParameter[]> dicParameter) { using (OracleConnection connection = new OracleConnection(connectionString)) { connection.Open(); OracleCommand cmd = new OracleCommand(); cmd.Connection = connection; OracleTransaction tx = connection.BeginTransaction(); cmd.Transaction = tx; try { int count = 0; foreach (string sql in dicParameter.Keys) { string strsql = sql; string dd = ""; if (strsql.Trim().Length > 1) { cmd.CommandText = strsql; cmd.Parameters.Clear(); for (int i = 0; i < dicParameter[sql].Length; i++) { if (dicParameter[sql][i] == null) { continue; } cmd.Parameters.Add(dicParameter[sql][i]); dd += dicParameter[sql][i].ParameterName + "-->" + dicParameter[sql][i].Value.ToString() + "\r\n"; } try { count += cmd.ExecuteNonQuery(); } catch (Exception es) { LogHelper.Log.Error("执行单条数据报错,原因:" + es.Message); throw new Exception(); } } } tx.Commit(); return(count); } catch (Exception ex) { //Trace.WriteLine("事务提交报错" + "原因:" + ex.Message); LogHelper.Log.Error("事务提交报错,原因:" + ex.Message); tx.Rollback(); return(0); } finally { cmd.Dispose(); if (connection.State == ConnectionState.Open) { connection.Close(); } } } }
/// <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 Rollback() { m_Transaction.Rollback(); }
/// <summary> /// Apply the given changeset. /// </summary> /// <param name="change_set"></param> public override void ApplyChange(ChangeSet change_set) { if (change_set != null && change_set.Changes != null) { foreach (Change change in change_set.Changes) { switch (change.Type) { case ChangeType.Create: foreach (OsmGeo geo in change.OsmGeo) { // start applying the simplechange. OracleTransaction trans = _connection.BeginTransaction(); try { if (geo is Node) { this.Create(geo as Node); OsmSharp.Logging.Log.TraceEvent("OsmSharp.Data.Oracle.Osm.Streams.OracleOsmChangesetStreamTarget", OsmSharp.Logging.TraceEventType.Information, "+(n:{0})", geo.Id.Value); } else if (geo is Way) { this.Create(geo as Way); OsmSharp.Logging.Log.TraceEvent("OsmSharp.Data.Oracle.Osm.Streams.OracleOsmChangesetStreamTarget", OsmSharp.Logging.TraceEventType.Information, "+(w:{0})", geo.Id.Value); } else if (geo is Relation) { this.Create(geo as Relation); OsmSharp.Logging.Log.TraceEvent("OsmSharp.Data.Oracle.Osm.Streams.OracleOsmChangesetStreamTarget", OsmSharp.Logging.TraceEventType.Information, "+(r:{0})", geo.Id.Value); } trans.Commit(); } catch (OracleException ex) { trans.Rollback(); if (!_pragmatic) { throw ex; } else { OsmSharp.Logging.Log.TraceEvent("OsmSharp.Data.Oracle.Osm.Streams.OracleOsmChangesetStreamTarget", OsmSharp.Logging.TraceEventType.Information, "+(E:{0}-{1})", geo.Id.Value, geo.Type.ToString()); } } } break; case ChangeType.Delete: foreach (OsmGeo geo in change.OsmGeo) { // start applying the simplechange. OracleTransaction trans = _connection.BeginTransaction(); try { if (geo is Node) { this.Delete(geo as Node); OsmSharp.Logging.Log.TraceEvent("OsmSharp.Data.Oracle.Osm.Streams.OracleOsmChangesetStreamTarget", OsmSharp.Logging.TraceEventType.Information, "-(n:{0})", geo.Id.Value); } else if (geo is Way) { this.Delete(geo as Way); OsmSharp.Logging.Log.TraceEvent("OsmSharp.Data.Oracle.Osm.Streams.OracleOsmChangesetStreamTarget", OsmSharp.Logging.TraceEventType.Information, "-(w:{0})", geo.Id.Value); } else if (geo is Relation) { this.Delete(geo as Relation); OsmSharp.Logging.Log.TraceEvent("OsmSharp.Data.Oracle.Osm.Streams.OracleOsmChangesetStreamTarget", OsmSharp.Logging.TraceEventType.Information, "-(r:{0})", geo.Id.Value); } trans.Commit(); } catch (OracleException ex) { trans.Rollback(); if (!_pragmatic) { throw ex; } else { OsmSharp.Logging.Log.TraceEvent("OsmSharp.Data.Oracle.Osm.Streams.OracleOsmChangesetStreamTarget", OsmSharp.Logging.TraceEventType.Information, "-(E:{0}-{1})", geo.Id.Value, geo.Type.ToString()); } } } break; case ChangeType.Modify: foreach (OsmGeo geo in change.OsmGeo) { // start applying the simplechange. OracleTransaction trans = _connection.BeginTransaction(); try { if (geo is Node) { this.Modify(geo as Node); OsmSharp.Logging.Log.TraceEvent("OsmSharp.Data.Oracle.Osm.Streams.OracleOsmChangesetStreamTarget", OsmSharp.Logging.TraceEventType.Information, "/(n:{0})", geo.Id.Value); } else if (geo is Way) { this.Modify(geo as Way); OsmSharp.Logging.Log.TraceEvent("OsmSharp.Data.Oracle.Osm.Streams.OracleOsmChangesetStreamTarget", OsmSharp.Logging.TraceEventType.Information, "/(w:{0})", geo.Id.Value); } else if (geo is Relation) { this.Modify(geo as Relation); OsmSharp.Logging.Log.TraceEvent("OsmSharp.Data.Oracle.Osm.Streams.OracleOsmChangesetStreamTarget", OsmSharp.Logging.TraceEventType.Information, "/(r:{0})", geo.Id.Value); } trans.Commit(); } catch (OracleException ex) { trans.Rollback(); if (!_pragmatic) { throw ex; } else { OsmSharp.Logging.Log.TraceEvent("OsmSharp.Data.Oracle.Osm.Streams.OracleOsmChangesetStreamTarget", OsmSharp.Logging.TraceEventType.Information, "/(E:{0}-{1})", geo.Id.Value, geo.Type.ToString()); } } } break; } } } }
/// <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; } }
private static void TryRollback(OracleTransaction transaction) { try { if (transaction != null) transaction.Rollback(); } catch { } }
/// <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 void trans_execute_sql(string strSql, OracleConnection objConnection, OracleTransaction objTransaction) { //objConnection=open_connection(); objCommand = new OracleCommand(strSql, objConnection); objCommand.CommandType = CommandType.Text; try { //objCommand.Transaction=objTransaction; objCommand.ExecuteNonQuery(); err_flag = false; } catch (OracleException objError) { if (objError.Message.Substring(0, 21) == "Table does not exist.") { ErrorStr = ErrorMsg(objConnection, "MSG1201"); err_flag = true; } else if (objError.Message.Substring(0, 28) == "ORA-00001: unique constraint") { ErrorStr = ErrorMsg(objConnection, "MSG1103"); err_flag = true; } else if (objError.Message.Substring(0, 31) == "ORA-02292: integrity constraint") { ErrorStr = ErrorMsg(objConnection, "MSG1107"); err_flag = true; } else if ((objError.Message.Length >= 87) && (objError.Message.Substring(59, 30) == "ORA-00904: invalid column name")) { ErrorStr = ErrorMsg(objConnection, "MSG1202"); err_flag = true; } else { ErrorStr = objError.Message; err_flag = true; } objTransaction.Rollback(); objCommand.Dispose(); objConnection.Close(); objConnection.Dispose(); } catch (Exception objError) { ErrorStr = objError.Message; objTransaction.Rollback(); objCommand.Dispose(); objConnection.Close(); objConnection.Dispose(); err_flag = true; } /*finally { //objConnection.Close(); null; }*/ }
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; }
/************************************************************ * Function name : SetExam * Purpose : * Input : * Output : string *************************************************************/ #region public string SetExam(string rParam) public string SetExam(string rParam) { Database db = null; string xRtn = string.Empty; string xSql = string.Empty; try { db = base.GetDataBase("LMS"); //Database 생성 using (OracleConnection xCnnLMS = (OracleConnection)db.CreateConnection()) // base.CreateConnection("LMS"); { xCnnLMS.Open(); OracleTransaction xTransLMS = xCnnLMS.BeginTransaction(); // 트랜잭션 시작 OracleCommand xCmdLMS = null; try { xCmdLMS = base.GetSqlCommand(db); OracleParameter[] xPara = null; ArrayList xarr = STR2MD2ArrayList(rParam); string[] xParam; for (int i = 0; i < xarr.Count; i++) { //xarr.Add(new string[] { dr["USER_ID"].ToString() // , dr["OPEN_COURSE_ID"].ToString() // , dr["COURSE_RESULT_SEQ"].ToString() // , dr["COURSE_ID"].ToString() // , dr["QUESTION_ID"].ToString() // , xAnswer // }); xParam = (string[])xarr[i]; //--USER_ID //--OPEN_COURSE_ID //--COURSE_RESULT_SEQ //--COURSE_ID //--QUESTION_ID xSql = " SELECT OPEN_COURSE_ID FROM T_ASSESS_RESULT "; xSql += " WHERE USER_ID = '" + xParam[0] + "' "; xSql += " AND OPEN_COURSE_ID = '" + xParam[1] + "' "; xSql += " AND COURSE_RESULT_SEQ = " + xParam[2] + " "; xSql += " AND COURSE_ID = '" + xParam[3] + "' "; xSql += " AND QUESTION_ID = '" + xParam[4] + "' "; xCmdLMS.CommandText = xSql; object xobj = base.ExecuteScalar(db, xCmdLMS, xTransLMS); if (xobj != null && xobj.ToString() != string.Empty) { //update xSql = @" UPDATE T_ASSESS_RESULT SET USER_ANSWER = :USER_ANSWER , UPT_ID = :USER_ID , UPT_DT = SYSDATE WHERE USER_ID = :USER_ID AND OPEN_COURSE_ID = :OPEN_COURSE_ID AND COURSE_RESULT_SEQ = :COURSE_RESULT_SEQ AND COURSE_ID = :COURSE_ID AND QUESTION_ID = :QUESTION_ID "; xPara = new OracleParameter[6]; xPara[0] = base.AddParam("USER_ID", OracleType.VarChar, xParam[0]); xPara[1] = base.AddParam("OPEN_COURSE_ID", OracleType.VarChar, xParam[1]); xPara[2] = base.AddParam("COURSE_RESULT_SEQ", OracleType.Number, xParam[2]); xPara[3] = base.AddParam("COURSE_ID", OracleType.VarChar, xParam[3]); xPara[4] = base.AddParam("QUESTION_ID", OracleType.VarChar, xParam[4]); xPara[5] = base.AddParam("USER_ANSWER", OracleType.VarChar, xParam[5]); xCmdLMS.CommandText = xSql; base.Execute(db, xCmdLMS, xPara, xTransLMS); } else { //insert xSql = @" INSERT INTO T_ASSESS_RESULT (USER_ID, OPEN_COURSE_ID, COURSE_RESULT_SEQ, COURSE_ID, QUESTION_ID , USER_ANSWER , QUESTION_SEQ , INS_ID, INS_DT, UPT_ID, UPT_DT) VALUES (:USER_ID, :OPEN_COURSE_ID, :COURSE_RESULT_SEQ, :COURSE_ID, :QUESTION_ID , :USER_ANSWER , :QUESTION_SEQ , :USER_ID, SYSDATE, :USER_ID, SYSDATE) "; xPara = new OracleParameter[7]; xPara[0] = base.AddParam("USER_ID", OracleType.VarChar, xParam[0]); xPara[1] = base.AddParam("OPEN_COURSE_ID", OracleType.VarChar, xParam[1]); xPara[2] = base.AddParam("COURSE_RESULT_SEQ", OracleType.Number, xParam[2]); xPara[3] = base.AddParam("COURSE_ID", OracleType.VarChar, xParam[3]); xPara[4] = base.AddParam("QUESTION_ID", OracleType.VarChar, xParam[4]); xPara[5] = base.AddParam("USER_ANSWER", OracleType.VarChar, xParam[5]); xPara[6] = base.AddParam("QUESTION_SEQ", OracleType.Number, i + 1); xCmdLMS.CommandText = xSql; base.Execute(db, xCmdLMS, xPara, xTransLMS); } } xRtn = "Y"; xTransLMS.Commit(); //트렌잭션 커밋 } catch (Exception ex) { // 트랜잭션 롤백 xTransLMS.Rollback(); throw ex; } finally { if (xCmdLMS != null) { xCmdLMS.Dispose(); } if (xTransLMS != null) { xTransLMS.Dispose(); } if (xCnnLMS != null) { if (xCnnLMS.State == ConnectionState.Open) { xCnnLMS.Close(); } } } } } catch (Exception ex) { throw ex; } finally { db = null; } return(xRtn);//리터값 }
public Boolean ConfereProdutoBonus(ConferenciaBonus dados) { OracleConnection connection = DataBase.novaConexao(); OracleTransaction transacao = connection.BeginTransaction(); OracleCommand exec = connection.CreateCommand(); StringBuilder conferenteCab = new StringBuilder(); StringBuilder validaPrimeiroVol = new StringBuilder(); StringBuilder confereBonusi = new StringBuilder(); StringBuilder confereBonusiConf = new StringBuilder(); StringBuilder confereBonusiVolume = new StringBuilder(); exec.Transaction = transacao; try { //Atribui conferente no cabeçalho do bônus conferenteCab.Append($"UPDATE PCBONUSC SET DATARM = TRUNC(SYSDATE), CODFUNCRM = {dados.Codfuncconf} WHERE NUMBONUS = {dados.Numbonus}"); exec.CommandText = conferenteCab.ToString(); OracleDataReader cabBonus = exec.ExecuteReader(); //Confere item na PCBONUSI confereBonusi.Append($"UPDATE PCBONUSI SET QTENTRADA = QTENTRADA + {dados.Qtconf}, QTAVARIA = QTAVARIA + {dados.Qtavaria}, DTVALIDADE = TO_DATE('{dados.Dtvalidade}', 'DD/MM/YYYY') "); confereBonusi.Append($" WHERE NUMBONUS = {dados.Numbonus} AND CODPROD = {dados.Codprod}"); exec.CommandText = confereBonusi.ToString(); OracleDataReader bonusi = exec.ExecuteReader(); //Confere item na PCBONUSICONF confereBonusiConf.Append("INSERT INTO PCBONUSICONF (NUMBONUS, CODPROD, DATACONF, DATAVALIDADE, CODFUNCCONF, NUMLOTE, QT, QTAVARIA, CODAUXILIAR)"); confereBonusiConf.Append($" VALUES ({dados.Numbonus}, {dados.Codprod}, SYSDATE, TO_DATE('{dados.Dtvalidade}', 'DD/MM/YYYY'), {dados.Codfuncconf}, 1, {dados.Qtconf}, {dados.Qtavaria}, NULL)"); exec.CommandText = confereBonusiConf.ToString(); OracleDataReader bonusiconf = exec.ExecuteReader(); //Verifica se é o primeiro registro validaPrimeiroVol.Append($"SELECT COUNT(*) AS TOT_REG FROM PCBONUSIVOLUME WHERE NUMBONUS = {dados.Numbonus} AND CODPROD = {dados.Codprod} AND QTENTRADA = 0"); exec.CommandText = validaPrimeiroVol.ToString(); OracleDataReader valida = exec.ExecuteReader(); if (valida.Read()) { int primeiraConferencia = valida.GetInt32(0); if (primeiraConferencia > 0) { confereBonusiVolume.Append($"UPDATE PCBONUSIVOLUME SET NUMBONUS = {dados.Numbonus}, CODPROD = {dados.Codprod}, NUMLOTE = 1, CODAGREGACAO = NULL, DTVALIDADE = TO_DATE('{dados.Dtvalidade}', 'DD/MM/YYYY'), "); confereBonusiVolume.Append($" QTPECAS = 0, QTENTRADA = {dados.Qtconf}, QTNF = {dados.Qtnf}, TIPO = 'N', CODIGOUMA = 0, ENDERECADO = 'N', CODMOTIVO = 0, DTLANCAMENTO = SYSDATE, "); confereBonusiVolume.Append($" CODFILIALESTOQUE = NULL, CODFILIALGESTAO = NULL, CODFUNCCONFERENTE = {dados.Codfuncconf}"); confereBonusiVolume.Append($" WHERE NUMBONUS = { dados.Numbonus } AND CODPROD = { dados.Codprod } AND QTENTRADA = 0"); exec.CommandText = confereBonusiVolume.ToString(); OracleDataReader bonusivolume = exec.ExecuteReader(); } else { //Confere (inserindo) item na PCBONUSIVOLUME confereBonusiVolume.Append("INSERT INTO PCBONUSIVOLUME (NUMBONUS, CODPROD, NUMLOTE, CODAGREGACAO, DTVALIDADE, QTPECAS, QTENTRADA, QTNF, TIPO, "); confereBonusiVolume.Append(" CODIGOUMA, ENDERECADO, CODMOTIVO, DTLANCAMENTO, CODFILIALESTOQUE, CODFILIALGESTAO, CODFUNCCONFERENTE)"); confereBonusiVolume.Append($" VALUES ({dados.Numbonus}, {dados.Codprod}, 1, NULL, TO_DATE('{dados.Dtvalidade}', 'DD/MM/YYYY'), 0, {dados.Qtconf}, {dados.Qtnf}, 'N', 0, 'N', 0, SYSDATE, NULL, NULL, {dados.Codfuncconf})"); exec.CommandText = confereBonusiVolume.ToString(); OracleDataReader bonusivolume = exec.ExecuteReader(); } } transacao.Commit(); return(true); } catch (Exception ex) { if (connection.State == ConnectionState.Open) { transacao.Rollback(); connection.Close(); throw new Exception(ex.ToString()); } transacao.Rollback(); exec.Dispose(); connection.Dispose(); throw new Exception(ex.ToString()); } finally { if (connection.State == ConnectionState.Open) { connection.Close(); } exec.Dispose(); connection.Dispose(); } }
/// <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; } }
private bool load_file(object sender, DoWorkEventArgs e, out string msg) { msg = null; OracleConnection connection = new OracleConnection(config.connectionstring); connection.Open(); OracleTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted); OracleCommand command = connection.CreateCommand(); // Start a local transaction // transaction = connection.BeginTransaction(); // Assign transaction object for a pending local transaction command.Transaction = transaction; try { int pc = 0; int i = 0; int cnt = data.Count; // command.CommandText = string.Format("DELETE FROM Rcd.CHECKWEBPAYDATA Where FILENAME='{0}'", FileName); // command.ExecuteNonQuery(); foreach (STRowWebPayData item in data) { if (backgroundWorker1.CancellationPending) { transaction.Rollback(); e.Cancel = true; backgroundWorker1.ReportProgress(0); return(false); } command.CommandText = "INSERT INTO Rcd.VALID_WEBPAYDATA (IDSYS, RRN, IDSTATUS, LTIME, AMOUNT, FILENAME, TAGS) values (:1, :2, :3, :4, :5, :6, :7)"; command.Parameters.Clear(); command.Parameters.Add(crp(OracleType.Int32, item.idsys, "1", false)); command.Parameters.Add(crp(OracleType.Char, item.rrn, "2", false)); command.Parameters.Add(crp(OracleType.Int32, item.idstatus, "3", false)); command.Parameters.Add(crp(OracleType.DateTime, item.ltime, "4", false)); command.Parameters.Add(crp(OracleType.Number, Math.Round(item.amount, 2), "5", false)); command.Parameters.Add(crp(OracleType.Char, item.filename, "6", false)); command.Parameters.Add(crp(OracleType.Clob, item.tags, "7", false)); command.ExecuteNonQuery(); i += 1; // pc = i / cnt * 100; double d = ((i * 1.0) / cnt) * 100; // double f = Math.Round((double)(i / cnt * 100.0)); backgroundWorker1.ReportProgress((int)d); } transaction.Commit(); } catch (Exception ex) { transaction.Rollback(); msg = ex.Message; log.LogLine("Neither record was written to database."); return(false); } return(true); }
/// <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; }
public string DeleteBomEntry(BOMModel objBOMModel) { string strMsg = ""; string[] TranIdArray = objBOMModel.GridTranId.Split(','); int x = TranIdArray.Count(); for (int i = 0; i < x; i++) { var arrayTranId = TranIdArray[i]; OracleTransaction objOracleTransaction = null; OracleCommand objOracleCommand = new OracleCommand("pro_bill_of_material_delete"); objOracleCommand.CommandType = CommandType.StoredProcedure; if (objBOMModel.StyleNo != "") { objOracleCommand.Parameters.Add("p_STYLE_NO", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = objBOMModel.StyleNo; } else { objOracleCommand.Parameters.Add("p_STYLE_NO", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = null; } if (objBOMModel.BuyerId != "") { objOracleCommand.Parameters.Add("p_BUYER_ID", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = objBOMModel.BuyerId; } else { objOracleCommand.Parameters.Add("p_BUYER_ID", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = null; } if (objBOMModel.SeasonId != "") { objOracleCommand.Parameters.Add("p_SEASON_ID", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = objBOMModel.SeasonId; } else { objOracleCommand.Parameters.Add("p_SEASON_ID", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = null; } if (objBOMModel.SeasonYear != "") { objOracleCommand.Parameters.Add("p_SEASON_YEAR", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = objBOMModel.SeasonYear; } else { objOracleCommand.Parameters.Add("p_SEASON_YEAR", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = null; } if (arrayTranId != null) { objOracleCommand.Parameters.Add("p_TRAN_ID", OracleDbType.Varchar2, ParameterDirection.Input).Value = arrayTranId; } else { objOracleCommand.Parameters.Add("p_TRAN_ID", OracleDbType.Varchar2, ParameterDirection.Input).Value = null; } objOracleCommand.Parameters.Add("p_update_by", OracleDbType.Varchar2, ParameterDirection.Input).Value = objBOMModel.UpdateBy; objOracleCommand.Parameters.Add("p_head_office_id", OracleDbType.Varchar2, ParameterDirection.Input).Value = objBOMModel.HeadOfficeId; objOracleCommand.Parameters.Add("p_branch_office_id", OracleDbType.Varchar2, ParameterDirection.Input).Value = objBOMModel.BranchOfficeId; objOracleCommand.Parameters.Add("P_MESSAGE", OracleDbType.Varchar2, 500).Direction = ParameterDirection.Output; using (OracleConnection strConn = GetConnection()) { try { objOracleCommand.Connection = strConn; strConn.Open(); trans = strConn.BeginTransaction(); objOracleCommand.ExecuteNonQuery(); trans.Commit(); strConn.Close(); strMsg = objOracleCommand.Parameters["P_MESSAGE"].Value.ToString(); } catch (Exception ex) { throw new Exception("Error : " + ex.Message); objOracleTransaction.Rollback(); } finally { strConn.Close(); } } } return(strMsg); }
/// <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; } }
public void RollbackTransaction(OracleTransaction trans, bool closeConnection) { trans.Rollback(); if (closeConnection && trans.Connection.State != ConnectionState.Closed) { trans.Connection.Close(); } trans = null; }
/// <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); } } }
public static bool SavePratiuttar(ATTPratiuttar objPratiuttar) { GetConnection GetConn = new GetConnection(); OracleTransaction Tran = GetConn.GetDbConn(Module.CMS).BeginTransaction(); double pratiuttarID = 0; List <OracleParameter> paramArray = new List <OracleParameter>(); try { paramArray.Add(Utilities.GetOraParam(":P_CASE_ID", objPratiuttar.CaseID, OracleDbType.Double, ParameterDirection.Input)); paramArray.Add(Utilities.GetOraParam(":P_PRATIUTTAR_ID", pratiuttarID, OracleDbType.Int32, ParameterDirection.InputOutput)); paramArray.Add(Utilities.GetOraParam(":P_PRATIUTTAR_DATE", objPratiuttar.PratiuttarDate, OracleDbType.Varchar2, ParameterDirection.Input)); paramArray.Add(Utilities.GetOraParam(":P_ACCOUNT_FORWARD", objPratiuttar.AccountForward, OracleDbType.Varchar2, ParameterDirection.Input)); paramArray.Add(Utilities.GetOraParam(":P_SUBMITTED_BY", objPratiuttar.SubmittedBy, OracleDbType.Double, ParameterDirection.Input)); paramArray.Add(Utilities.GetOraParam(":P_PRATIUTTAR_SUMMARY", objPratiuttar.PratiuttarSummary, OracleDbType.Varchar2, ParameterDirection.Input)); paramArray.Add(Utilities.GetOraParam(":P_ENTRY_BY", objPratiuttar.EntryBy, OracleDbType.Varchar2, ParameterDirection.Input)); if (objPratiuttar.PratiuttarID == 0) { SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, "SP_ADD_CASE_PRATIUTTAR", paramArray.ToArray()); } else if (objPratiuttar.PratiuttarID > 0) { SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, "SP_EDIT_CASE_PRATIUTTAR", paramArray.ToArray()); } objPratiuttar.PratiuttarID = int.Parse(paramArray[1].Value.ToString()); if (objPratiuttar.LstPratiuttarLitigants.Count > 0) { DLLPratiuttarLitigants.SavePratiuttarLitigants(objPratiuttar.LstPratiuttarLitigants, objPratiuttar.PratiuttarID, Tran); } if (objPratiuttar.LstPratiuttarEvidence.Count > 0) { DLLPratiuttarEvidence.SavePratiuttarEvidence(objPratiuttar.LstPratiuttarEvidence, objPratiuttar.PratiuttarID, Tran); } if (objPratiuttar.LstPratiuttarDocuments.Count > 0) { DLLPratiuttarDocuments.SavePratiuttarDocuments(objPratiuttar.LstPratiuttarDocuments, objPratiuttar.PratiuttarID, Tran); } Tran.Commit(); paramArray.Clear(); return(true); } catch (OracleException oex) { PCS.COREDL.OracleError oe = new PCS.COREDL.OracleError(); throw new ArgumentException(oe.GetOraError(oex.Number, oex.Message)); } catch (Exception ex) { Tran.Rollback(); throw ex; } finally { GetConn.CloseDbConn(); } }
/// <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); } } }
/************************************************************ * Function name : SetNoticeHitCnt * Purpose : 공지사항 읽은수 Update * Input : string[] rParams * Output : string *************************************************************/ public string SetNoticeHitCnt(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 { OracleParameter[] oOraParams = new OracleParameter[2]; oOraParams[0] = base.AddParam("p_not_no", OracleType.Number, Convert.ToInt32(rParams)); oOraParams[1] = base.AddParam("p_in_table", OracleType.VarChar, "T_NOTICE"); int j = base.Execute(db, CommandType.StoredProcedure, "pkg_lms_hitcount.lms_hitcount", oOraParams, xTransLMS); //string xSql = string.Empty; //xSql += " UPDATE t_notice SET hit_cnt = hit_cnt + 1 "; //xSql += string.Format(" WHERE not_no = {0} ", Convert.ToInt32(rParams)); //xCmdLMS.CommandText = xSql; //base.Execute("LMS", xCmdLMS, xTransLMS); xTransLMS.Commit(); // 트랜잭션 커밋 xRtn = Boolean.TrueString; } catch (Exception ex) { // 파일 제거 //if (xFilePath.Trim() != "") // File.Delete(xFilePath); xTransLMS.Rollback(); // Exception 발생시 롤백처리... throw ex; } finally { if (xCmdLMS != null) { xCmdLMS.Dispose(); } if (xTransLMS != null) { xTransLMS.Dispose(); } } } catch (Exception ex) { throw ex; } return(xRtn); }
/// <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); } } }
/************************************************************ * Function name : SetNoticeUpdate * Purpose : 공지사항 저장(기존 공지사항 저장 Update) * Input : string[] rParams * Output : String *************************************************************/ public string SetNoticeUpdate(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; string[] rMasterParams = (string[])rParams[0]; object[,] rDetailParams = (object[, ])rParams[1]; string[] rDeleteFile = (string[])rParams[2]; // 1. 기존 첨부파일의 삭제 // 2. 기존공지사항 Update // 3. 첨부파일 Insert try { string xSql = string.Empty; // 기존 첨부파일 삭제 for (int i = 0; i < rDeleteFile.Length; i++) { xSql = string.Empty; xSql += string.Format(" DELETE FROM t_notice_att WHERE not_no = {0} AND file_nm = '{1}' ", Convert.ToInt32(rMasterParams[7]), rDeleteFile[i]); xCmdLMS.CommandText = xSql; base.Execute(db, xCmdLMS, xTransLMS); } // 기존 공지사항 Update xSql = string.Empty; xSql += " UPDATE t_notice SET "; xSql += string.Format(" not_kind = '{0}', ", rMasterParams[0]); // 공지사항 타입 m_cd = '0055' , 000001: 일반공지 , 000002: 과정공지 xSql += string.Format(" open_course_id = '{0}', ", rMasterParams[1]); // 과정 ID (일반공지의 경우 Empty Value) xSql += string.Format(" notice_begin_dt = TO_DATE('{0}','YYYY.MM.DD'), ", rMasterParams[2]); // 공지 게시일자 xSql += string.Format(" notice_end_dt = TO_DATE('{0}','YYYY.MM.DD'), ", rMasterParams[3]); // 공지 종료일자 xSql += string.Format(" not_sub = '{0}', ", rMasterParams[4]); // 공지사항 제목 //xSql += string.Format(" not_content = '{0}', ", rMasterParams[5]); // 공지사항 내용 xSql += "not_content = :NOTCONTENT, "; xSql += string.Format(" upt_id = '{0}', ", rMasterParams[6]); // 수정자 ID xSql += string.Format(" send_flg = '{0}', ", rMasterParams[8]); // Sednf FLG 1 : 전송대상, 2 : 전송완료, 3 : 전송안함 xSql += " upt_dt = SYSDATE "; xSql += string.Format(" WHERE not_no = {0} ", Convert.ToInt32(rMasterParams[7])); OracleParameter NOTCONTENT = new OracleParameter(); NOTCONTENT.OracleType = OracleType.Clob; NOTCONTENT.ParameterName = "NOTCONTENT"; NOTCONTENT.Value = rMasterParams[5]; xCmdLMS.Parameters.Add(NOTCONTENT); xCmdLMS.CommandText = xSql; base.Execute(db, xCmdLMS, xTransLMS); for (int i = 0; i < rDetailParams.GetLength(0); i++) { byte[] xFileData = (byte[])rDetailParams[i, 0]; xSql = string.Empty; xSql += " INSERT INTO t_notice_att ( "; xSql += " NOT_NO, "; xSql += " FILE_NM, "; xSql += " att_file "; xSql += " ) "; xSql += " VALUES ( "; xSql += string.Format(" {0}, ", Convert.ToInt32(rMasterParams[7])); xSql += string.Format(" '{0}', ", rDetailParams[i, 1]); xSql += " :ATTFILE "; xSql += " ) "; OracleParameter ATTFILE = new OracleParameter(); ATTFILE.OracleType = OracleType.Blob; ATTFILE.ParameterName = "ATTFILE"; ATTFILE.Value = xFileData; xCmdLMS.Parameters.Add(ATTFILE); xCmdLMS.CommandText = xSql; base.Execute(db, xCmdLMS, xTransLMS); } if (rMasterParams[8] == "1") // 전송대상이면 { OracleParameter[] oOraParams = new OracleParameter[2]; oOraParams[0] = base.AddParam("p_in_table", OracleType.VarChar, "T_NOTICE"); oOraParams[1] = base.AddParam("p_out_table", OracleType.VarChar, "T_BULL"); int i = base.Execute(db, CommandType.StoredProcedure, "pkg_lms_datasync.lms_export", oOraParams, xTransLMS); } xTransLMS.Commit(); // 트랜잭션 커밋 xRtn = Boolean.TrueString; } catch (Exception ex) { // 파일 제거 if (xFilePath.Trim() != "") { File.Delete(xFilePath); } xTransLMS.Rollback(); // Exception 발생시 롤백처리... throw ex; } finally { if (xCmdLMS != null) { xCmdLMS.Dispose(); } if (xTransLMS != null) { xTransLMS.Dispose(); } } } catch (Exception ex) { throw ex; } return(xRtn); }
/// <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); } } }
/************************************************************ * Function name : SetStudyUpdate * Purpose : * Input : * Output : string *************************************************************/ #region public string SetStudyUpdate(object[] rParams) public string SetStudyUpdate(object[] rParams) { Database db = null; string xRtn = string.Empty; string xSql = string.Empty; try { db = base.GetDataBase("LMS"); //Database 생성 using (OracleConnection xCnnLMS = (OracleConnection)db.CreateConnection()) // base.CreateConnection("LMS"); { xCnnLMS.Open(); OracleTransaction xTransLMS = xCnnLMS.BeginTransaction(); // 트랜잭션 시작 OracleCommand xCmdLMS = null; try { xCmdLMS = base.GetSqlCommand(db); OracleParameter[] xPara = null; //전체 contents 수 정보 xSql = @" SELECT COUNT(*) AS TOTALCNT FROM T_COURSE_RESULT CR, T_OPEN_COURSE OC , T_COURSE_SUBJECT CS, T_SUBJECT_CONTENTS SC , T_SUBJECT S, T_CONTENTS C WHERE CR.OPEN_COURSE_ID = OC.OPEN_COURSE_ID AND OC.COURSE_ID = CS.COURSE_ID AND CS.SUBJECT_ID = SC.SUBJECT_ID AND SC.SUBJECT_ID = S.SUBJECT_ID AND SC.CONTENTS_ID = C.CONTENTS_ID AND CR.USER_ID = '{0}' AND OC.OPEN_COURSE_ID = '{1}' "; xSql = string.Format(xSql, rParams[0], rParams[1]); xCmdLMS.CommandText = xSql; object xTotalCnt = base.ExecuteScalar(db, xCmdLMS, xTransLMS); //최초 if (rParams[4].ToString() == "1") { xSql = @" UPDATE T_COURSE_RESULT SET USER_COURSE_BEGIN_DT = SYSDATE WHERE USER_ID = :USER_ID AND OPEN_COURSE_ID = :OPEN_COURSE_ID "; xPara = new OracleParameter[2]; xPara[0] = base.AddParam("USER_ID", OracleType.VarChar, rParams[0]); xPara[1] = base.AddParam("OPEN_COURSE_ID", OracleType.VarChar, rParams[1]); xCmdLMS.CommandText = xSql; base.Execute(db, xCmdLMS, xPara, xTransLMS); } //마지막 컨텐츠 if (rParams[4].ToString() == xTotalCnt.ToString()) { xSql = @" UPDATE T_COURSE_RESULT SET USER_COURSE_END_DT = SYSDATE WHERE USER_ID = :USER_ID AND OPEN_COURSE_ID = :OPEN_COURSE_ID "; xPara = new OracleParameter[2]; xPara[0] = base.AddParam("USER_ID", OracleType.VarChar, rParams[0]); xPara[1] = base.AddParam("OPEN_COURSE_ID", OracleType.VarChar, rParams[1]); xCmdLMS.CommandText = xSql; base.Execute(db, xCmdLMS, xPara, xTransLMS); } //마지막 들은 contents 정보 seq 보다 현재 들으려고 하는 seq가 클 경우 last 정보 update //사용자가 들은 마지막 컨텐츠 정보 xSql = @" SELECT LAST_SUBJECT_ID || '|' || LAST_CONTENTS_ID FROM T_COURSE_RESULT WHERE USER_ID = '{0}' AND OPEN_COURSE_ID = '{1}' "; xSql = string.Format(xSql, rParams[0], rParams[1]); xCmdLMS.CommandText = xSql; object xLast = base.ExecuteScalar(db, xCmdLMS, xTransLMS); if (xLast != null && xLast.ToString().Replace("|", "") != string.Empty) { xSql = @" SELECT OC.OPEN_COURSE_ID, S.SUBJECT_NM, S.SUBJECT_ID , C.CONTENTS_NM, C.CONTENTS_ID, C.CONTENTS_FILE_NM , CR.LAST_SUBJECT_ID, CR.LAST_CONTENTS_ID, SC.CONTENTS_SEQ /*마지막으로 수강한 컨텐츠의 SEQ 정보*/ , (SELECT CONTENTS_SEQ FROM T_SUBJECT_CONTENTS WHERE SUBJECT_ID = CR.LAST_SUBJECT_ID AND CONTENTS_ID = CR.LAST_CONTENTS_ID) AS LAST_CONTENTS_SEQ , S.SUBJECT_ID || '|' || C.CONTENTS_ID || '|' || C.CONTENTS_FILE_NM AS VINFO_T , S.SUBJECT_ID || '|' || C.CONTENTS_ID || '|' || C.CONTENTS_FILE_NM || '|' || ROW_NUMBER() OVER(ORDER BY CS.SUBJECT_SEQ, SC.CONTENTS_SEQ) AS VINFO , (SELECT COUNT(*) FROM T_SUBJECT_CONTENTS WHERE SUBJECT_ID = SC.SUBJECT_ID) AS TOTAL , ROW_NUMBER() OVER(ORDER BY CS.SUBJECT_SEQ, SC.CONTENTS_SEQ) AS ROW_SEQ FROM T_COURSE_RESULT CR, T_OPEN_COURSE OC , T_COURSE_SUBJECT CS, T_SUBJECT_CONTENTS SC , T_SUBJECT S, T_CONTENTS C WHERE CR.OPEN_COURSE_ID = OC.OPEN_COURSE_ID AND OC.COURSE_ID = CS.COURSE_ID AND CS.SUBJECT_ID = SC.SUBJECT_ID AND SC.SUBJECT_ID = S.SUBJECT_ID AND SC.CONTENTS_ID = C.CONTENTS_ID AND OC.OPEN_COURSE_ID = '{0}' AND CR.USER_ID = '{1}' ORDER BY CS.SUBJECT_SEQ, SC.CONTENTS_SEQ "; xSql = string.Format(xSql, rParams[1], rParams[0]); xCmdLMS.CommandText = xSql; DataSet xds = base.ExecuteDataSet(db, xCmdLMS, xTransLMS); string[] xLastKey = xLast.ToString().Split('|'); if (xds.Tables[0].Rows.Count > 0) { DataRow[] xdrarr = xds.Tables[0].Select("SUBJECT_ID = '" + xLastKey[0] + "' AND CONTENTS_ID = '" + xLastKey[1] + "' "); DataRow xdr = null; if (xdrarr.Length > 0) { xdr = xdrarr[0]; if (Convert.ToInt32(rParams[4]) > Convert.ToInt32(xdr["ROW_SEQ"].ToString())) { xTotalCnt = xTotalCnt == null || xTotalCnt.ToString() == string.Empty ? "1" : xTotalCnt.ToString(); double xProgress = (Convert.ToDouble(rParams[4]) / Convert.ToDouble(xTotalCnt)) * 100; xSql = @" UPDATE T_COURSE_RESULT SET PROGRESS_RATE = :PROGRESS_RATE , LAST_SUBJECT_ID = :LAST_SUBJECT_ID , LAST_CONTENTS_ID = :LAST_CONTENTS_ID WHERE USER_ID = :USER_ID AND OPEN_COURSE_ID = :OPEN_COURSE_ID "; xPara = new OracleParameter[5]; xPara[0] = base.AddParam("USER_ID", OracleType.VarChar, rParams[0]); xPara[1] = base.AddParam("OPEN_COURSE_ID", OracleType.VarChar, rParams[1]); xPara[2] = base.AddParam("PROGRESS_RATE", OracleType.VarChar, Convert.ToString(Math.Round(xProgress))); xPara[3] = base.AddParam("LAST_SUBJECT_ID", OracleType.VarChar, rParams[2]); xPara[4] = base.AddParam("LAST_CONTENTS_ID", OracleType.VarChar, rParams[3]); xCmdLMS.CommandText = xSql; base.Execute(db, xCmdLMS, xPara, xTransLMS); } } } } else { //최초이면 xTotalCnt = xTotalCnt == null || xTotalCnt.ToString() == string.Empty ? "1" : xTotalCnt.ToString(); double xProgress = (Convert.ToDouble(rParams[4]) / Convert.ToDouble(xTotalCnt)) * 100; xSql = @" UPDATE T_COURSE_RESULT SET PROGRESS_RATE = :PROGRESS_RATE , LAST_SUBJECT_ID = :LAST_SUBJECT_ID , LAST_CONTENTS_ID = :LAST_CONTENTS_ID WHERE USER_ID = :USER_ID AND OPEN_COURSE_ID = :OPEN_COURSE_ID "; xPara = new OracleParameter[5]; xPara[0] = base.AddParam("USER_ID", OracleType.VarChar, rParams[0]); xPara[1] = base.AddParam("OPEN_COURSE_ID", OracleType.VarChar, rParams[1]); xPara[2] = base.AddParam("PROGRESS_RATE", OracleType.VarChar, Convert.ToString(Math.Round(xProgress))); xPara[3] = base.AddParam("LAST_SUBJECT_ID", OracleType.VarChar, rParams[2]); xPara[4] = base.AddParam("LAST_CONTENTS_ID", OracleType.VarChar, rParams[3]); xCmdLMS.CommandText = xSql; base.Execute(db, xCmdLMS, xPara, xTransLMS); } xRtn = "Y"; xTransLMS.Commit(); //트렌잭션 커밋 } catch (Exception ex) { // 트랜잭션 롤백 xTransLMS.Rollback(); throw ex; } finally { if (xCmdLMS != null) { xCmdLMS.Dispose(); } if (xTransLMS != null) { xTransLMS.Dispose(); } if (xCnnLMS != null) { if (xCnnLMS.State == ConnectionState.Open) { xCnnLMS.Close(); } } } } } catch (Exception ex) { throw ex; } finally { db = null; } return(xRtn);//리터값 }
/// <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); } } }
/************************************************************ * Function name : SetExamSubmit * Purpose : * Input : * Output : string *************************************************************/ #region public string SetExamSubmit(string rParam) public string SetExamSubmit(string rParam) { Database db = null; string xRtn = string.Empty; string xSql = string.Empty; try { db = base.GetDataBase("LMS"); //Database 생성 using (OracleConnection xCnnLMS = (OracleConnection)db.CreateConnection()) // base.CreateConnection("LMS"); { xCnnLMS.Open(); OracleTransaction xTransLMS = xCnnLMS.BeginTransaction(); // 트랜잭션 시작 OracleCommand xCmdLMS = null; try { xCmdLMS = base.GetSqlCommand(db); OracleParameter[] xPara = null; ArrayList xarr = STR2MD2ArrayList(rParam); string[] xParam; string xUser = string.Empty; string xOpenId = string.Empty; string xSeq = string.Empty; for (int i = 0; i < xarr.Count; i++) { //xarr.Add(new string[] { dr["USER_ID"].ToString() // , dr["OPEN_COURSE_ID"].ToString() // , dr["COURSE_RESULT_SEQ"].ToString() // , dr["COURSE_ID"].ToString() // , dr["QUESTION_ID"].ToString() // , xAnswer // }); xParam = (string[])xarr[i]; if (xUser == string.Empty) { xUser = xParam[0]; xOpenId = xParam[1]; xSeq = xParam[2]; } //--USER_ID //--OPEN_COURSE_ID //--COURSE_RESULT_SEQ //--COURSE_ID //--QUESTION_ID xSql = " SELECT OPEN_COURSE_ID FROM T_ASSESS_RESULT "; xSql += " WHERE USER_ID = '" + xParam[0] + "' "; xSql += " AND OPEN_COURSE_ID = '" + xParam[1] + "' "; xSql += " AND COURSE_RESULT_SEQ = " + xParam[2] + " "; xSql += " AND COURSE_ID = '" + xParam[3] + "' "; xSql += " AND QUESTION_ID = '" + xParam[4] + "' "; xCmdLMS.CommandText = xSql; object xobj = base.ExecuteScalar(db, xCmdLMS, xTransLMS); if (xobj != null && xobj.ToString() != string.Empty) { //update xSql = @" UPDATE T_ASSESS_RESULT SET USER_ANSWER = :USER_ANSWER , UPT_ID = :USER_ID , UPT_DT = SYSDATE , ISRIGHT_FLG = :ISRIGHT_FLG WHERE USER_ID = :USER_ID AND OPEN_COURSE_ID = :OPEN_COURSE_ID AND COURSE_RESULT_SEQ = :COURSE_RESULT_SEQ AND COURSE_ID = :COURSE_ID AND QUESTION_ID = :QUESTION_ID "; xPara = new OracleParameter[7]; xPara[0] = base.AddParam("USER_ID", OracleType.VarChar, xParam[0]); xPara[1] = base.AddParam("OPEN_COURSE_ID", OracleType.VarChar, xParam[1]); xPara[2] = base.AddParam("COURSE_RESULT_SEQ", OracleType.Number, xParam[2]); xPara[3] = base.AddParam("COURSE_ID", OracleType.VarChar, xParam[3]); xPara[4] = base.AddParam("QUESTION_ID", OracleType.VarChar, xParam[4]); xPara[5] = base.AddParam("USER_ANSWER", OracleType.VarChar, xParam[5]); xPara[6] = base.AddParam("ISRIGHT_FLG", OracleType.VarChar, xParam[6]); xCmdLMS.CommandText = xSql; base.Execute(db, xCmdLMS, xPara, xTransLMS); } else { //insert xSql = @" INSERT INTO T_ASSESS_RESULT (USER_ID, OPEN_COURSE_ID, COURSE_RESULT_SEQ, COURSE_ID, QUESTION_ID , USER_ANSWER , INS_ID, INS_DT, UPT_ID, UPT_DT,ISRIGHT_FLG ) VALUES (:USER_ID, :OPEN_COURSE_ID, :COURSE_RESULT_SEQ, :COURSE_ID, :QUESTION_ID , :USER_ANSWER , :USER_ID, SYSDATE, :USER_ID, SYSDATE, :ISRIGHT_FLG) "; xPara = new OracleParameter[7]; xPara[0] = base.AddParam("USER_ID", OracleType.VarChar, xParam[0]); xPara[1] = base.AddParam("OPEN_COURSE_ID", OracleType.VarChar, xParam[1]); xPara[2] = base.AddParam("COURSE_RESULT_SEQ", OracleType.Number, xParam[2]); xPara[3] = base.AddParam("COURSE_ID", OracleType.VarChar, xParam[3]); xPara[4] = base.AddParam("QUESTION_ID", OracleType.VarChar, xParam[4]); xPara[5] = base.AddParam("USER_ANSWER", OracleType.VarChar, xParam[5]); xPara[6] = base.AddParam("ISRIGHT_FLG", OracleType.VarChar, xParam[6]); xCmdLMS.CommandText = xSql; base.Execute(db, xCmdLMS, xPara, xTransLMS); } } //T_COURSE_RESULT.ASSESS_SCORE에 점수 반영 xSql = @" SELECT NVL(SUM(AQ.QUESTION_SCORE), 0) AS TOTSUM FROM T_ASSESS_QUESTION AQ, T_ASSESS_RESULT AR WHERE AQ.QUESTION_ID = AR.QUESTION_ID AND AR.USER_ID = '{0}' AND AR.OPEN_COURSE_ID = '{1}' AND AR.COURSE_RESULT_SEQ = {2} AND AR.ISRIGHT_FLG = 'Y' "; xSql = string.Format(xSql, xUser, xOpenId, xSeq); xCmdLMS.CommandText = xSql; object xTotal = base.ExecuteScalar(db, xCmdLMS, xTransLMS); xSql = @" UPDATE T_COURSE_RESULT SET ASSESS_SCORE = :ASSESS_SCORE WHERE USER_ID = :USER_ID AND OPEN_COURSE_ID = :OPEN_COURSE_ID AND COURSE_RESULT_SEQ = :COURSE_RESULT_SEQ "; xPara = new OracleParameter[4]; xPara[0] = base.AddParam("USER_ID", OracleType.VarChar, xUser); xPara[1] = base.AddParam("OPEN_COURSE_ID", OracleType.VarChar, xOpenId); xPara[2] = base.AddParam("COURSE_RESULT_SEQ", OracleType.Number, xSeq); xPara[3] = base.AddParam("ASSESS_SCORE", OracleType.VarChar, xTotal.ToString()); xCmdLMS.CommandText = xSql; base.Execute(db, xCmdLMS, xPara, xTransLMS); /* * -. 총점(T_COURSE_RESULT.TOTAL_SCORE): 개설과정의 이수기준 PROGRESS RATE(T_OPEN_COURSE.STD_PROGRESS_RATE), FINAL TEST(T_OPEN_COURSE.STD_FINAL_EXAM) 항목의 각 %를 나의 강의실에 진도, 시험에 반영 하여 * 총점에 반영 하고, 개설과정의 수료점수(T_OPEN_COURSE.PASS_SCORE)에 총점이 만족되면 PASS_FLG를 수료로 UPDATE * 만족되지 못하면 PASS_FLG를 미수료로 UPDATE * * Ex) 예를들어 * 개설과정: Progress Rate=80%, Final Test=20%, 수료점수60점 * 나의강의실: 진도=80%, 시험=70점 => 개설과정 이수기준에 맞추면 진도=64점, 시험=14점 => 총점 78점 * 개설과정의 수료점수 60점에 만족 하므로 Pass~ */ xSql = @" SELECT CR.PROGRESS_RATE, CR.ASSESS_SCORE, CR.TOTAL_SCORE, CR.PASS_FLG , OC.STD_PROGRESS_RATE, OC.STD_FINAL_EXAM, OC.PASS_SCORE , TO_NUMBER(CR.PROGRESS_RATE) * (OC.STD_PROGRESS_RATE/100) AS PRO , TO_NUMBER(CR.ASSESS_SCORE) * (OC.STD_FINAL_EXAM/100) AS EXAM , TO_NUMBER(CR.PROGRESS_RATE) * (OC.STD_PROGRESS_RATE/100) + TO_NUMBER(CR.ASSESS_SCORE) * (OC.STD_FINAL_EXAM/100) AS TOT , CASE WHEN TO_NUMBER(CR.PROGRESS_RATE) * (OC.STD_PROGRESS_RATE/100) + TO_NUMBER(CR.ASSESS_SCORE) * (OC.STD_FINAL_EXAM/100) >= OC.PASS_SCORE THEN '000001' ELSE '000005' END PASS FROM T_COURSE_RESULT CR, T_OPEN_COURSE OC WHERE CR.OPEN_COURSE_ID = OC.OPEN_COURSE_ID AND CR.USER_ID = '{0}' AND CR.OPEN_COURSE_ID = '{1}' AND CR.COURSE_RESULT_SEQ = {2} "; xSql = string.Format(xSql, xUser, xOpenId, xSeq); xCmdLMS.CommandText = xSql; DataSet xdsResult = base.ExecuteDataSet(db, xCmdLMS, xTransLMS); if (xdsResult.Tables[0].Rows.Count > 0) { DataRow drResult = xdsResult.Tables[0].Rows[0]; xSql = @" UPDATE T_COURSE_RESULT SET TOTAL_SCORE = :TOTAL_SCORE , PASS_FLG = :PASS_FLG WHERE USER_ID = :USER_ID AND OPEN_COURSE_ID = :OPEN_COURSE_ID AND COURSE_RESULT_SEQ = :COURSE_RESULT_SEQ "; xPara = new OracleParameter[5]; xPara[0] = base.AddParam("USER_ID", OracleType.VarChar, xUser); xPara[1] = base.AddParam("OPEN_COURSE_ID", OracleType.VarChar, xOpenId); xPara[2] = base.AddParam("COURSE_RESULT_SEQ", OracleType.Number, xSeq); xPara[3] = base.AddParam("TOTAL_SCORE", OracleType.VarChar, drResult["TOT"].ToString()); xPara[4] = base.AddParam("PASS_FLG", OracleType.VarChar, drResult["PASS"].ToString()); xCmdLMS.CommandText = xSql; base.Execute(db, xCmdLMS, xPara, xTransLMS); } xRtn = "Y"; xTransLMS.Commit(); //트렌잭션 커밋 } catch (Exception ex) { // 트랜잭션 롤백 xTransLMS.Rollback(); throw ex; } finally { if (xCmdLMS != null) { xCmdLMS.Dispose(); } if (xTransLMS != null) { xTransLMS.Dispose(); } if (xCnnLMS != null) { if (xCnnLMS.State == ConnectionState.Open) { xCnnLMS.Close(); } } } } } catch (Exception ex) { throw ex; } finally { db = null; } return(xRtn);//리터값 }
//******************************************* 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); } } }
private void RollBackTrans() { Trans.Rollback(); }
// 执行insert,update,delete 动作,也可以使用事务 public static bool UpdateData(out string sError, string sSQL, string sConnStr = "", OracleParameter[] cmdParams = null, bool bUseTransaction = false) { int iResult = 0; sError = string.Empty; if (string.IsNullOrEmpty(sConnStr)) { sConnStr = _sConnString; } if (!bUseTransaction) { try { OracleConnection conn = new OracleConnection(sConnStr); if (conn.State != ConnectionState.Open) { conn.Open(); } OracleCommand cmd = new OracleCommand(); cmd.Connection = conn; cmd.CommandText = sSQL; if (cmdParams != null) { foreach (OracleParameter parm in cmdParams) { cmd.Parameters.Add(parm); } } iResult = cmd.ExecuteNonQuery(); } catch (Exception ex) { sError = ex.Message; iResult = -1; } } else // 使用事务 { OracleTransaction trans = null; try { OracleConnection conn = new OracleConnection(sConnStr); if (conn.State != ConnectionState.Open) { conn.Open(); } trans = conn.BeginTransaction(); OracleCommand cmd = new OracleCommand(); cmd.Connection = conn; cmd.CommandText = sSQL; if (cmdParams != null) { foreach (OracleParameter parm in cmdParams) { cmd.Parameters.Add(parm); } } cmd.Transaction = trans; iResult = cmd.ExecuteNonQuery(); trans.Commit(); } catch (Exception ex) { sError = ex.Message; iResult = -1; trans.Rollback(); } } return(iResult > 0); }
//Affiche FAMILLE JDE ajax (table) public OracleDataReader GetJDE(string matr) { DBConnectJDE c = new DBConnectJDE(); OracleConnection conn = c.GetConnectionJDE(); conn.Open(); OracleTransaction trans = conn.BeginTransaction(); nb = CompterAgentTemp(matr); if (nb == 0) { OracleCommand cmd = new OracleCommand { CommandText = "SELECT YAAN8, YAOEMP , YAALPH , YADSC1 , YASEX , YAALPH1, CASE YATRDJ WHEN 0 THEN '-' ELSE to_char(to_date(to_char(1900 + floor(YATRDJ/ 1000)),'YYYY') + mod(YATRDJ,1000) - 1, 'DD-MM-YYYY') END FROM F55EMPME WHERE YAAN8 = '" + matr + "' OR YAOEMP= '" + matr + "'", Connection = conn, CommandType = CommandType.Text }; try { // Exécution de la requête dr = cmd.ExecuteReader(); // On soumet la requête au serveur: tout s'est bien déroulé , la requête est exécutée trans.Commit(); } catch (Exception ex) { // Une erreur est survenue: on ne valide pas la requête trans.Rollback(); Console.WriteLine("<body><script >alert('Requête non effectuée !!\nErreur: '" + ex.Message + "'');</script></body>"); } finally { // Libération des ressources cmd.Dispose(); } } else if (nb == 1) { OracleCommand cmd = new OracleCommand { CommandText = "SELECT YAAN8, YAOEMP , YAALPH , YADSC1 , YASEX , YAALPH1, CASE YATRDJ WHEN 0 THEN '-' ELSE to_char(to_date(to_char(1900 + floor(YATRDJ/ 1000)),'YYYY') + mod(YATRDJ,1000) - 1, 'DD-MM-YYYY') END FROM F55EMPME WHERE YADSC1 <> YAALPH AND YAAN8 = '" + matr + "' OR YAOEMP= '" + matr + "'", Connection = conn, CommandType = CommandType.Text }; try { // Exécution de la requête dr = cmd.ExecuteReader(); // On soumet la requête au serveur: tout s'est bien déroulé , la requête est exécutée trans.Commit(); } catch (Exception ex) { // Une erreur est survenue: on ne valide pas la requête trans.Rollback(); Console.WriteLine("<body><script >alert('Requête non effectuée !!\nErreur: '" + ex.Message + "'');</script></body>"); } finally { // Libération des ressources cmd.Dispose(); } } return(dr); }
/// <summary> /// Atualiza a tabela de Custódia do sinacor dos clientes /// </summary> /// <param name="pListClientes">List<ClienteGradualPremiumInfo> pListClientes - Lista de Clientes a serem atualiados </param> /// <param name="pTiraCustodia">Booleano para retirar ou inserir custódia nos cliente (351 - tirar a custódia, 151 - insere a custódia)</param> public void AtualizaCustodiaSinacor(List <ClienteGradualPremiumInfo> pListClientes, bool pTiraCustodia) { OracleConnection lConn; OracleCommand lCommand; OracleTransaction lTrans = null; lConn = new OracleConnection(StringConexaoSinacor); try { lConn.Open(); lTrans = lConn.BeginTransaction(); lCommand = new OracleCommand(); lCommand.Connection = lConn; lCommand.Transaction = lTrans; lCommand.CommandType = CommandType.Text; string lQueryCus = string.Empty; foreach (ClienteGradualPremiumInfo lCliente in pListClientes) { if (pTiraCustodia) { lQueryCus = string.Format("UPDATE TSCCLICUS SET TP_CUSTODIA = 351 WHERE CD_CLIENTE = {0}", lCliente.CdCliente); logger.InfoFormat("Inativando Custódia do cliente : {0}", lCliente.CdCliente); } else { lQueryCus = string.Format("UPDATE TSCCLICUS SET TP_CUSTODIA = 151 WHERE CD_CLIENTE = {0}", lCliente.CdCliente); logger.InfoFormat("Ativando Custódia do cliente : {0}", lCliente.CdCliente); } lCommand.CommandText = string.Format(lQueryCus); lCommand.ExecuteNonQuery(); } lTrans.Commit(); logger.InfoFormat("! **************************************************************************************** !"); logger.InfoFormat("! ****************** Atualização de custodia efetuada com sucesso ************************ !"); logger.InfoFormat("! **************************************************************************************** !"); } catch (Exception ex) { lTrans.Rollback(); logger.ErrorFormat("Erro em AtualizaCustodiaSinacor - {0} - Stacktrace - {1}", ex.Message, ex.StackTrace); } finally { lConn.Close(); logger.InfoFormat("Fechando a conexão no método AtualizaCustodiaSinacor"); } }
public static void proceedToCheckout() { //Validasi Promo + Validasi Ada checked + kurir + payment //Validasi Ada barang, string message = validateProceedCheckout(); if (message == "Success") { App.openConnection(out _); using (OracleTransaction Transaction = App.connection.BeginTransaction()) { try { Dictionary <string, int> updateStok = new Dictionary <string, int>(); List <string> id_items = new List <string>(); H_Trans_ItemModel hti = new H_Trans_ItemModel(); DataRow rowHTI = hti.Table.NewRow(); ComboBoxItem cbiPembayaran = (ComboBoxItem)ViewComponent.cbPaymentMethod.SelectedItem; Metode_PembayaranModel mpm = new Metode_PembayaranModel(); DataRow dr = mpm.Table.Select($"NAMA = '{cbiPembayaran.Content}'").First(); rowHTI["ID"] = 0; rowHTI["ID_CUSTOMER"] = Session.User["ID"].ToString(); rowHTI["TANGGAL_TRANSAKSI"] = DateTime.Now; if (checkPromotion(promo, false)) { rowHTI["ID_PROMO"] = promo.ID; if (promo.JENIS_POTONGAN == "P") { diskon = Convert.ToInt32((Convert.ToDouble(promo.POTONGAN) * Convert.ToDouble(hargaSebelumOngkir)) / 100.0); diskon = Math.Min(promo.POTONGAN_MAX, diskon); } else { diskon = promo.POTONGAN; } } else { diskon = 0; } //grandtotal = hargasebelumongkir + ongkir - diskon rowHTI["GRANDTOTAL"] = grandTotal - diskon; rowHTI["SUBTOTAL"] = hargaSebelumOngkir; rowHTI["ONGKOS_KIRIM"] = ongkos_kirim; rowHTI["DISKON"] = diskon; rowHTI["ID_METODE_PEMBAYARAN"] = dr["ID"].ToString(); rowHTI["STATUS"] = "W"; hti.insert(rowHTI); D_Trans_ItemModel dti = new D_Trans_ItemModel(); int id_hti = generateID_H_Trans(); foreach (ShopCartComponent scc in list_shopcart) { foreach (CartComponent cc in scc.getCarts()) { DataRow rowDTI = dti.Table.NewRow(); rowDTI["ID"] = 0; rowDTI["ID_H_TRANS_ITEM"] = id_hti; rowDTI["ID_ITEM"] = cc.getItemID(); id_items.Add(cc.getItemID()); rowDTI["JUMLAH"] = cc.getQuantity(); rowDTI["ID_KURIR"] = scc.getIDKurir(); rowDTI["STATUS"] = "W"; if (updateStok.ContainsKey(cc.getItemID())) { updateStok[cc.getItemID()] += Convert.ToInt32(cc.getQuantity()); } else { updateStok.Add(cc.getItemID(), Convert.ToInt32(cc.getQuantity())); } dti.insert(rowDTI); } } Transaction.Commit(); //Update Stok foreach (var val in updateStok) { string id_item = val.Key; int qty = val.Value; // MessageBox.Show(id_item + " - " + qty); ItemModel im = new ItemModel(); DataRow item = im.Table.Select($"ID ='{id_item}'").FirstOrDefault(); if (item != null) { item["STOK"] = Convert.ToInt32(item["STOK"]) - qty; } im.update(); } //TODO tambah ke history trans :) foreach (string id_item in id_items) { deleteItemFromCart(Convert.ToInt32(id_item), false); } loadCartItem(); updateGrandTotal(); TransactionViewModel.initH_Trans(); CustomerViewModel.loadItems(); } catch (OracleException e) { Transaction.Rollback(); MessageBox.Show(e.Message); } }; App.closeConnection(out _); } else { MessageBox.Show(message); } }
//删除 private void barBtnDelete_ItemClick(object sender, ItemClickEventArgs e) { //记录操作日志 LogUtils.ReviewLogManager.ReviewLog(Properties.Settings.Default.LocalUserName, this.Text + "-" + this.barBtnDelete.Caption); 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(OracleHelper.conn, string.Format("DELETE FROM DB_CONTACTS 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(); }
public string SaveBomEntry(BOMModel objBOMModel) { string strMsg = ""; int x = objBOMModel.ItemDescription.Count(); for (int i = 0; i < x; i++) { var arrayItemName = objBOMModel.ItemName[i]; var arrayItemDescription = objBOMModel.ItemDescription[i]; var arrayModelCode = objBOMModel.ModelCode[i]; var arrayComponentName = objBOMModel.ComponentName[i]; var arrayColorId = objBOMModel.ColorId[i]; var arrayColorName = objBOMModel.ColorName[i]; var arrayFabricQuantity = objBOMModel.FabricQuantity[i]; var arrayUnitId = objBOMModel.UnitId[i]; var arrayRemarks = objBOMModel.Remarks[i]; var arrayTranId = objBOMModel.TranId[i]; OracleTransaction objOracleTransaction = null; OracleCommand objOracleCommand = new OracleCommand("pro_bill_of_material_save"); objOracleCommand.CommandType = CommandType.StoredProcedure; if (objBOMModel.StyleNo != "") { objOracleCommand.Parameters.Add("p_STYLE_NO", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = objBOMModel.StyleNo; } else { objOracleCommand.Parameters.Add("p_STYLE_NO", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = null; } if (objBOMModel.BuyerId != "") { objOracleCommand.Parameters.Add("p_BUYER_ID", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = objBOMModel.BuyerId; } else { objOracleCommand.Parameters.Add("p_BUYER_ID", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = null; } if (objBOMModel.SeasonYear != "") { objOracleCommand.Parameters.Add("p_SEASON_YEAR", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = objBOMModel.SeasonYear; } else { objOracleCommand.Parameters.Add("p_SEASON_YEAR", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = null; } if (objBOMModel.SeasonId != "") { objOracleCommand.Parameters.Add("p_SEASON_ID", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = objBOMModel.SeasonId; } else { objOracleCommand.Parameters.Add("p_SEASON_ID", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = null; } if (objBOMModel.R3Code != "") { objOracleCommand.Parameters.Add("p_R3_CODE", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = objBOMModel.R3Code; } else { objOracleCommand.Parameters.Add("p_R3_CODE", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = null; } if (objBOMModel.LastUpdateDate != "") { objOracleCommand.Parameters.Add("p_LAST_UPDATE_DATE", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = objBOMModel.LastUpdateDate; } else { objOracleCommand.Parameters.Add("p_LAST_UPDATE_DATE", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = null; } if (arrayItemName != "") { objOracleCommand.Parameters.Add("p_ITEM_NAME", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = arrayItemName; } else { objOracleCommand.Parameters.Add("p_ITEM_NAME", OracleDbType.Varchar2, ParameterDirection.InputOutput).Value = null; } if (arrayItemDescription != null) { objOracleCommand.Parameters.Add("p_ITEM_DESCRIPTION", OracleDbType.Varchar2, ParameterDirection.Input).Value = arrayItemDescription; } else { objOracleCommand.Parameters.Add("p_ITEM_DESCRIPTION", OracleDbType.Varchar2, ParameterDirection.Input).Value = null; } if (arrayModelCode != null) { objOracleCommand.Parameters.Add("p_MODEL_CODE", OracleDbType.Varchar2, ParameterDirection.Input).Value = arrayModelCode; } else { objOracleCommand.Parameters.Add("p_MODEL_CODE", OracleDbType.Varchar2, ParameterDirection.Input).Value = null; } if (arrayComponentName != null) { objOracleCommand.Parameters.Add("p_COMPONENT_NAME", OracleDbType.Varchar2, ParameterDirection.Input).Value = arrayComponentName; } else { objOracleCommand.Parameters.Add("p_COMPONENT_NAME", OracleDbType.Varchar2, ParameterDirection.Input).Value = null; } if (arrayColorId != null) { objOracleCommand.Parameters.Add("p_COLOR_CODE", OracleDbType.Varchar2, ParameterDirection.Input).Value = arrayColorId; } else { objOracleCommand.Parameters.Add("p_COLOR_CODE", OracleDbType.Varchar2, ParameterDirection.Input).Value = null; } if (arrayColorName != null) { objOracleCommand.Parameters.Add("p_COLOR_NAME", OracleDbType.Varchar2, ParameterDirection.Input).Value = arrayColorName; } else { objOracleCommand.Parameters.Add("p_COLOR_NAME", OracleDbType.Varchar2, ParameterDirection.Input).Value = null; } if (arrayFabricQuantity != null) { objOracleCommand.Parameters.Add("p_FABRIC_QUANTITY", OracleDbType.Varchar2, ParameterDirection.Input).Value = arrayFabricQuantity; } else { objOracleCommand.Parameters.Add("p_FABRIC_QUANTITY", OracleDbType.Varchar2, ParameterDirection.Input).Value = null; } if (arrayUnitId != null) { objOracleCommand.Parameters.Add("p_UNIT_ID", OracleDbType.Varchar2, ParameterDirection.Input).Value = arrayUnitId; } else { objOracleCommand.Parameters.Add("p_UNIT_ID", OracleDbType.Varchar2, ParameterDirection.Input).Value = null; } if (arrayRemarks != null) { objOracleCommand.Parameters.Add("p_REMARKS", OracleDbType.Varchar2, ParameterDirection.Input).Value = arrayRemarks; } else { objOracleCommand.Parameters.Add("p_REMARKS", OracleDbType.Varchar2, ParameterDirection.Input).Value = null; } if (arrayTranId != null) { objOracleCommand.Parameters.Add("p_TRAN_ID", OracleDbType.Varchar2, ParameterDirection.Input).Value = arrayTranId; } else { objOracleCommand.Parameters.Add("p_TRAN_ID", OracleDbType.Varchar2, ParameterDirection.Input).Value = null; } objOracleCommand.Parameters.Add("p_update_by", OracleDbType.Varchar2, ParameterDirection.Input).Value = objBOMModel.UpdateBy; objOracleCommand.Parameters.Add("p_head_office_id", OracleDbType.Varchar2, ParameterDirection.Input).Value = objBOMModel.HeadOfficeId; objOracleCommand.Parameters.Add("p_branch_office_id", OracleDbType.Varchar2, ParameterDirection.Input).Value = objBOMModel.BranchOfficeId; objOracleCommand.Parameters.Add("P_MESSAGE", OracleDbType.Varchar2, 500).Direction = ParameterDirection.Output; using (OracleConnection strConn = GetConnection()) { try { objOracleCommand.Connection = strConn; strConn.Open(); trans = strConn.BeginTransaction(); objOracleCommand.ExecuteNonQuery(); trans.Commit(); strConn.Close(); strMsg = objOracleCommand.Parameters["P_MESSAGE"].Value.ToString(); } catch (Exception ex) { throw new Exception("Error : " + ex.Message); objOracleTransaction.Rollback(); } finally { strConn.Close(); } } } return(strMsg); }
/// <summary> /// 事务回滚 /// </summary> public override void RollbackTrans() { _trans.Rollback(); _isInTransaction = false; }
//************************************************************ //** 저장 버튼 Click (여러 건의 DATA 추가입력/수정 후 저장) //************************************************************ public void BtnSave_Click() { if (MessageBox.Show("입력 및 수정중인 자료를 저장합니다.", "저장확인", MessageBoxButton.YesNo, MessageBoxImage.Question) == MessageBoxResult.No) { return; } if (!InputCheck()) { return; } OracleTransaction tran = null; try { con = Utility.SetOracleConnection(); tran = con.BeginTransaction(IsolationLevel.ReadCommitted); OracleCommand cmd = con.CreateCommand(); cmd.BindByName = true; cmd.Transaction = tran; for (int i = 0; i <= myViewModel.Count - 1; i++) { UcSubC05ViewModel vm = (UcSubC05ViewModel)myViewModel.ElementAt(i); if (vm.DataStatus.Equals("")) { continue; } if (vm.DataStatus.Equals("A")) { cmd.CommandText = SQLStatement.InsertSQL; } if (vm.DataStatus.Equals("U")) { cmd.CommandText = SQLStatement.UpdateSQL; cmd.Parameters.Add("key1", OracleDbType.Varchar2).Value = vm.Key1; cmd.Parameters.Add("key2", OracleDbType.Varchar2).Value = vm.Key2; } cmd.Parameters.Add("award_empno", OracleDbType.Varchar2).Value = vm.Award_empno; cmd.Parameters.Add("award_date", OracleDbType.Varchar2).Value = Utility.FormatDateR(vm.Award_date); cmd.Parameters.Add("award_type", OracleDbType.Varchar2).Value = vm.Award_type_Cd; cmd.Parameters.Add("award_no", OracleDbType.Varchar2).Value = vm.Award_no; cmd.Parameters.Add("award_kind", OracleDbType.Varchar2).Value = vm.Award_kind; cmd.Parameters.Add("award_organ", OracleDbType.Varchar2).Value = vm.Award_organ; cmd.Parameters.Add("award_content", OracleDbType.Varchar2).Value = vm.Award_content; cmd.Parameters.Add("award_inout", OracleDbType.Varchar2).Value = vm.Award_inout; cmd.Parameters.Add("award_pos", OracleDbType.Varchar2).Value = vm.Award_pos; cmd.Parameters.Add("award_dept", OracleDbType.Varchar2).Value = vm.Award_dept; cmd.Parameters.Add("datasys3", OracleDbType.Varchar2).Value = string.Concat(UserId, ":", UserNm); cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); //*----반드시 포함 } tran.Commit(); } catch (Exception ex) { tran.Rollback(); MessageBox.Show(ex.Message); return; } finally { if (con != null) { con.Close(); } } //**정상 저장 후 초기화******************************************************* for (int i = 0; i <= myViewModel.Count - 1; i++) { UcSubC05ViewModel vm = (UcSubC05ViewModel)myViewModel.ElementAt(i); if (vm.DataStatus.Equals("")) { continue; } vm.Key1 = vm.Award_empno; vm.Key2 = vm.Award_date; vm.DataStatus = ""; } dataGrid.RefreshData(); UserMessage.Text = "자료가 정상적으로 저장 되었습니다."; Utility.SetFuncBtn(MainBtn, "2"); }
public long Save(UserEntity entity) { string PREFERREDCURRENCY = ""; string PREFERREDCURRENCYCODE = ""; OracleConnection mFocisConnection; OracleTransaction mfocisOracleTransaction = null; using (mFocisConnection = new OracleConnection(GetConnectionString(configuration, DBConnectionMode.FOCiS))) { if (mFocisConnection.State == ConnectionState.Closed) { mFocisConnection.Open(); } mfocisOracleTransaction = mFocisConnection.BeginTransaction(); using (OracleCommand mCommand = mFocisConnection.CreateCommand()) { mCommand.BindByName = true; mCommand.Transaction = mfocisOracleTransaction; mCommand.CommandType = CommandType.Text; try { mCommand.CommandText = "SELECT CURRENCYNAME,CURRENCYCODE FROM FOCIS.CURRENCIES WHERE CURRENCYID IN (SELECT BASECURRENCYID FROM FOCIS.SMDM_COUNTRY WHERE countryid=" + entity.COUNTRYID + ")"; DataTable dt = ReturnDatatable(GetConnectionString(configuration, DBConnectionMode.FOCiS), mCommand.CommandText, null); if (dt.Rows.Count > 0) { PREFERREDCURRENCY = dt.Rows[0]["CURRENCYNAME"].ToString(); PREFERREDCURRENCYCODE = dt.Rows[0]["CURRENCYCODE"].ToString(); } else { PREFERREDCURRENCY = "US Dollar"; PREFERREDCURRENCYCODE = "USD"; } } catch (Exception ex) { mfocisOracleTransaction.Rollback(); // throw new ShipaDbException(ex.ToString()); } } } using (mConnection = new OracleConnection(GetConnectionString(configuration, DBConnectionMode.SSP))) { if (mConnection.State == ConnectionState.Closed) { mConnection.Open(); } mOracleTransaction = mConnection.BeginTransaction(); using (OracleCommand mCommand = mConnection.CreateCommand()) { mCommand.BindByName = true; mCommand.Transaction = mOracleTransaction; mCommand.CommandType = CommandType.Text; try { if (string.IsNullOrEmpty(entity.COUNTRYCODE)) { entity.COUNTRYCODE = ""; } mCommand.CommandText = @"INSERT INTO USERS (USERID,USERTYPE,EMAILID,PASSWORD,DATECREATED,FIRSTNAME,LASTNAME,COMPANYNAME, ISDID,ISDCODE,MOBILENUMBER,ISTERMSAGREED,COUNTRYID,ACCOUNTSTATUS,TIMEZONEID,USERCULTUREID,TOKEN,TOKENEXPIRY,COUNTRYCODE,COUNTRYNAME, PREFERREDCURRENCY,PREFERREDCURRENCYCODE,NOTIFICATIONSUBSCRIPTION,SHIPMENTPROCESS,SHIPPINGEXPERIENCE) VALUES (:USERID,:USERTYPE,:EMAILID,:PASSWORD,:DATECREATED,:FIRSTNAME,:LASTNAME,:COMPANYNAME,:ISDID,:ISDCODE,:MOBILENUMBER,:ISTERMSAGREED, :COUNTRYID,:ACCOUNTSTATUS,:TIMEZONEID,:USERCULTUREID,:TOKEN,:TOKENEXPIRY,:COUNTRYCODE,:COUNTRYNAME,:PREFERREDCURRENCY, :PREFERREDCURRENCYCODE,:NOTIFICATIONSUBSCRIPTION,:SHIPMENTPROCESS,:SHIPPINGEXPERIENCE)"; mCommand.Parameters.Add("USERID", entity.USERID.ToLower()); mCommand.Parameters.Add("USERTYPE", entity.USERTYPE); mCommand.Parameters.Add("EMAILID", entity.EMAILID); mCommand.Parameters.Add("PASSWORD", entity.PASSWORD); mCommand.Parameters.Add("DATECREATED", DateTime.Now); mCommand.Parameters.Add("FIRSTNAME", entity.FIRSTNAME); mCommand.Parameters.Add("LASTNAME", entity.LASTNAME); mCommand.Parameters.Add("COMPANYNAME", entity.COMPANYNAME); mCommand.Parameters.Add("ISDID", entity.ISDID); mCommand.Parameters.Add("ISDCODE", entity.ISDCODE); mCommand.Parameters.Add("MOBILENUMBER", entity.MOBILENUMBER); mCommand.Parameters.Add("ISTERMSAGREED", entity.ISTERMSAGREED); mCommand.Parameters.Add("COUNTRYID", entity.COUNTRYID); mCommand.Parameters.Add("ACCOUNTSTATUS", entity.ACCOUNTSTATUS); mCommand.Parameters.Add("TIMEZONEID", entity.TIMEZONEID); mCommand.Parameters.Add("USERCULTUREID", entity.USERCULTUREID); mCommand.Parameters.Add("TOKEN", entity.TOKEN); mCommand.Parameters.Add("TOKENEXPIRY", entity.TOKENEXPIRY); mCommand.Parameters.Add("COUNTRYCODE", entity.COUNTRYCODE.ToUpper()); mCommand.Parameters.Add("COUNTRYNAME", entity.COUNTRYNAME); mCommand.Parameters.Add("PREFERREDCURRENCY", PREFERREDCURRENCY); mCommand.Parameters.Add("PREFERREDCURRENCYCODE", PREFERREDCURRENCYCODE); mCommand.Parameters.Add("NOTIFICATIONSUBSCRIPTION", entity.NOTIFICATIONSUBSCRIPTION.Equals(true) ? 1 : 0); mCommand.Parameters.Add("SHIPMENTPROCESS", entity.SHIPMENTPROCESS); mCommand.Parameters.Add("SHIPPINGEXPERIENCE", entity.SHIPPINGEXPERIENCE); mCommand.ExecuteNonQuery(); mOracleTransaction.Commit(); } catch (Exception ex) { mOracleTransaction.Rollback(); // throw new ShipaDbException(ex.ToString()); } } //SSP NOTIFICATIONS ADDED BY SIVA if (mConnection.State == ConnectionState.Closed) { mConnection.Open(); } mOracleTransaction = mConnection.BeginTransaction(); using (OracleCommand mCommand = mConnection.CreateCommand()) { mCommand.BindByName = true; mCommand.Transaction = mOracleTransaction; mCommand.CommandType = CommandType.Text; try { mCommand.CommandText = "SELECT SSP_NOTIFICATIONS_SEQ.NEXTVAL FROM DUAL"; entity.NOTIFICATIONID = Convert.ToInt64(mCommand.ExecuteScalar()); mCommand.CommandText = "INSERT INTO SSPNOTIFICATIONS (NOTIFICATIONID,NOTIFICATIONTYPEID,USERID,DATECREATED,DATEREQUESTED,NOTIFICATIONCODE) VALUES (:NOTIFICATIONID,:NOTIFICATIONTYPEID,:USERID,:DATECREATED,:DATEREQUESTED,:NOTIFICATIONCODE)"; mCommand.Parameters.Add("NOTIFICATIONID", Convert.ToInt32(entity.NOTIFICATIONID)); mCommand.Parameters.Add("NOTIFICATIONTYPEID", 5111); mCommand.Parameters.Add("USERID", entity.USERID.ToUpper()); mCommand.Parameters.Add("DATECREATED", DateTime.Now); mCommand.Parameters.Add("DATEREQUESTED", DateTime.Now); mCommand.Parameters.Add("NOTIFICATIONCODE", "Profile Incomplete"); mCommand.ExecuteNonQuery(); mOracleTransaction.Commit(); } catch (Exception ex) { mOracleTransaction.Rollback(); // throw new ShipaDbException(ex.ToString()); } } } //--------------------Sales Force--------------- using (mConnection = new OracleConnection(GetConnectionString(configuration, DBConnectionMode.FOCiS))) { if (mConnection.State == ConnectionState.Closed) { mConnection.Open(); } mOracleTransaction = mConnection.BeginTransaction(); using (OracleCommand mCommand = mConnection.CreateCommand()) { mCommand.Parameters.Clear(); mCommand.BindByName = true; mCommand.Transaction = mOracleTransaction; mCommand.CommandType = CommandType.StoredProcedure; mCommand.CommandText = "USP_SSP_CEPREQUOTEPROFILE"; try { mCommand.Parameters.Add("PRM_GUESTNAME", Convert.ToString(entity.FIRSTNAME)); mCommand.Parameters.Add("PRM_GUESTCOUNTRYCODE", entity.COUNTRYCODE); mCommand.Parameters.Add("PRM_GUESTCOUNTRYNAME", entity.COUNTRYNAME); mCommand.Parameters.Add("PRM_GUESTCOMPANY", entity.COMPANYNAME); mCommand.Parameters.Add("PRM_GUESTEMAIL", entity.USERID); mCommand.Parameters.Add("PRM_TELEPHONENUMBER", Convert.ToInt64(entity.MOBILENUMBER)); mCommand.Parameters.Add("OP_RESULTSET", OracleDbType.RefCursor).Direction = ParameterDirection.Output; mCommand.ExecuteNonQuery(); mOracleTransaction.Commit(); } catch (Exception ex) { mOracleTransaction.Rollback(); // throw new ShipaDbException(ex.ToString()); } finally { mConnection.Dispose(); mCommand.Dispose(); mOracleTransaction.Dispose(); } } } //----------------------------------------------- return(1); }
//保存变更申请单 private void simpleButton1_Click(object sender, EventArgs e) { string applyType = string.Empty; if (INSERT_O.Checked) { applyType = INSERT_O.Text + "-"; } if (UPDATE.Checked) { applyType += UPDATE.Text + "-"; } if (DELETE.Checked) { applyType += DELETE.Text + "-"; } applyType = applyType.TrimEnd('-'); if (gridControl1.DataSource == null) { MessageBox.Show("没有要保存的数据", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } System.Data.DataView dv = ((System.Data.DataTable)gridControl1.DataSource).AsDataView(); if (dv == null || dv.Table.Select("VIN not is null or VIN <> ''").Length < 1) { MessageBox.Show("没有要保存的数据", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } using (OracleConnection con = new OracleConnection(OracleHelper.conn)) { con.Open(); OracleTransaction tra = con.BeginTransaction(); try { SplashScreenManager.ShowForm(typeof(DevWaitForm)); string delsql = string.Format("delete from DATA_CHANGE_BASE where ID='{0}'", guid); int val = OracleHelper.ExecuteNonQuery(tra, delsql, null); string sql = string.Format("insert into DATA_CHANGE_BASE values('{11}','{0}',to_date('{1}','yyyy-mm-dd hh24:mi:ss'),'{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}' )", this.QCSCQYMC.Text, this.APPLYDATA.Text, applyType, INSERT_O_RANGE.Text, UPDATE_RANGE.Text, DELETE_RANGE.Text, INSERT_O_SL.Text, UPDATE_SL.Text, DELETE_SL.Text, REASON.Text, REMARK.Text, guid); OracleHelper.ExecuteNonQuery(tra, sql, null); string delsqldata = string.Format("delete from DATA_CHANGE where DID='{0}'", guid); OracleHelper.ExecuteNonQuery(tra, delsqldata, null); foreach (DataRow r in dv.Table.Rows) { string sqlData = "insert into DATA_CHANGE values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}')"; string sqlDataStr = string.Format(sqlData, guid, r["VIN"], r["CLXH"], r["TYMC"], r["rllx"], r["CT_ZHGKRLXHL"], r["ZCZBZL"], r["CT_BSQXS"], r["ZWPS"], r["UPDATEFIELD"], r["FIELDOLD"], r["FIELDNEW"], r["APPLYTYPE"]); OracleHelper.ExecuteNonQuery(tra, sqlDataStr, null); } tra.Commit(); MessageBox.Show("操作成功,可以生成WORD或发送管理员", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch { tra.Rollback(); MessageBox.Show("操作失败", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } finally { SplashScreenManager.CloseForm(); } } }
/// <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); } } }