public void Execute(string name, Action<OracleConnection> a, bool useTransaction = false) { ConnectionString cs = Database.GetConnectionString(name); OracleConnectionStringBuilder builder = new OracleConnectionStringBuilder { DataSource = cs.DataSource, UserID = cs.UserID, Password = cs.Password, PersistSecurityInfo = cs.PersistSecurityInfo }; using (OracleConnection conn = new OracleConnection(builder.ConnectionString)) { conn.Open(); if (useTransaction) { using (OracleTransaction trans = conn.BeginTransaction()) { a(conn); trans.Commit(); } } else a(conn); conn.Close(); } }
public static OracleConnection GetConnection() { if (conn != null) { if (conn.State == System.Data.ConnectionState.Open) { return conn; } } string DBName = "RA00C000"; string DBUser = "******"; string DBPass = "******"; string[] args = Environment.GetCommandLineArgs(); if (args.Length > 3) { DBName = args[1]; DBUser = args[2]; DBPass = args[3]; } oradb = "Data Source=" + DBName + ";User Id=" + DBUser + ";Password="******";"; try { conn = new OracleConnection(oradb); conn.Open(); transaction = conn.BeginTransaction(); return conn; } catch (Exception) { return null; } }
/// <summary> /// Connects to the database. /// </summary> /// <param name="connDetails"></param> public SqlRunner(ConnectionDetails connDetails) { this.connDetails = connDetails; string connStr = GetConnectionString(); log.Info("connecting to [" + connDetails.Name + "] with connection string " + connStr); conn = new OracleConnection(connStr); conn.Open(); currentTransaction = conn.BeginTransaction(); }
public OWDB(string DataBaseName, string LoginUser, string LoginPassword) { OracleConnection1 = new Oracle.DataAccess.Client.OracleConnection(); // Sample Connectionstring 'User Id=owuser;Password=owuser;Data source=WHTRUNK' OracleConnection1.ConnectionString = "User Id=" + LoginUser + ";Password="******";Data source=" + DataBaseName + ""; OracleConnection1.Open(); OracleTransaction1 = OracleConnection1.BeginTransaction(); }
public bool Fill() { try { conn = new OracleConnection(GetConnectionString()); conn.Open(); transaction = conn.BeginTransaction(); } catch (Exception e) { MessageBox.Show("При подключении к базе данных произошла ошибка:\n\n" + e.Message); conn.Close(); conn.Dispose(); return false; } cmd = conn.CreateCommand(); cmd.CommandText = "SELECT TO_CHAR(id), l_name from akriko.cat_executors WHERE id in (SELECT distinct(ispolnitel_cik_id) FROM akriko.appeal) ORDER BY UPPER(l_name)"; try { dr = cmd.ExecuteReader(); while (dr.Read()) { if (dr.IsDBNull(0) || dr.IsDBNull(1)) { MessageBox.Show("При чтении данных из базы данных произошла ошибка:\n\n"); break; } else { UsersGrid.Rows.Add(); UsersGrid.Rows[UsersGrid.Rows.Count - 1].Cells["executor_id"].Value = dr.GetString(0); UsersGrid.Rows[UsersGrid.Rows.Count - 1].Cells["akriko"].Value = dr.GetString(1); } } dr.Close(); dr.Dispose(); } catch (Exception e) { MessageBox.Show("При чтении данных из базы данных произошла ошибка:\n\n" + e.Message); } if (UsersGrid.Rows.Count == 0) { MessageBox.Show("Отсутствуют неотконвертированные исполнители", "Конвертация исполнителей", MessageBoxButtons.OK, MessageBoxIcon.Information); ConvertButton.Enabled = false; } FillDeloDepartmentsColumn(); return true; }
public IDatabaseQuery StartQuery(bool withTransaction) { var connection = new OracleConnection(ConnectionString); OracleTransaction transaction = null; if (withTransaction) { try { connection.Open(); } catch (Exception ex) { TraceSource.TraceEvent(TraceEventType.Error, 5010, "{0}", ex); try { connection.Close(); } catch { } OracleConnection.ClearAllPools(); connection = new OracleConnection(ConnectionString); connection.Open(); } transaction = connection.BeginTransaction(); } IDatabaseQuery query; try { query = QueryFactory(connection, transaction); } catch (Exception ex) { TraceSource.TraceEvent(TraceEventType.Critical, 5101, "{0}", ex); TraceSource.TraceEvent(TraceEventType.Information, 5101, "Transactions: {0}, connections: {1}", OpenTransactions.Count, OpenConnections.Count); throw; } if (withTransaction) OpenTransactions.TryAdd(query, transaction); OpenConnections.TryAdd(query, connection); return query; }
/// <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 SingleStart() { Biz.CM.CMXmlAutoBizTx oBiz = null; Biz.CM.CMXmlAutoBiz oBiz2 = null; DataSet ds = null; int taskIdx = 0; try { #region 개별 SMS 발송 Function Func<object, int> action = (object oSms) => { int oraResult = 0; string insertSQL =""; // string insertValues = ""; CMXmlAutoBizTx bizTx = null; StringBuilder sbQuery = new StringBuilder(); string oraConn = ConfigurationManager.AppSettings["OraConnStr"]; int totRows = 0; int rowIdx = 0; string minId = ""; string maxId = ""; string dataRange = ""; // SMS TRAN TABLE string sc_tran_table = ConfigurationManager.AppSettings["SMS_TRAN_TABLE"]; // SMS SEQUENCE string sc_sequence = ConfigurationManager.AppSettings["SMS_SEQUENCE"]; DataTable dt = (DataTable)oSms; Hashtable ht = new Hashtable(); OracleConnection oCon = new OracleConnection(oraConn); OracleTransaction oTx = null; OracleCommand cmd = oCon.CreateCommand(); try { // 전체 대상 건수 totRows = dt.Rows.Count; // 시작ID maxId = dt.Rows[0]["SNGSMSLOGID"].ToString(); // 종료ID minId = dt.Rows[totRows - 1]["SNGSMSLOGID"].ToString(); // 작업 대상 ID dataRange = "SngSMSLogId> " + minId + " ~ " + maxId; try { // Connection Open. oCon.Open(); // 데이터 저장 Array 생성 /* TR_SENDDATE DATE * TR_PHONE VARCHAR2(20) * TR_CALLBACK VARCHAR2(20) * TR_MSG VARCHAR2(160) * TR_ETC3 VARCHAR2(160) */ DateTime[] sendDateVals = new DateTime[totRows]; string[] sendStatVals = new string[totRows]; string[] msgTypeVals = new string[totRows]; string[] phoneVals = new string[totRows]; string[] callBackVals = new string[totRows]; string[] msgVals = new string[totRows]; string[] etc1Vals = new string[totRows]; string[] etc2Vals = new string[totRows]; string[] etc3Vals = new string[totRows]; // 데이터 저장 foreach (DataRow dr in dt.Rows) { sendDateVals[rowIdx] = Convert.ToDateTime(dr["ReqDate"].ToString()); sendStatVals[rowIdx] = "0"; msgTypeVals[rowIdx] = "0"; phoneVals[rowIdx] = dr["Mobile"].ToString(); callBackVals[rowIdx] = dr["ReplyTelNo"].ToString(); msgVals[rowIdx] = dr["SendMessage"].ToString(); etc1Vals[rowIdx] = "TMS"; etc2Vals[rowIdx] = "Single"; etc3Vals[rowIdx] = dr["SngSMSLogId"].ToString(); rowIdx++; } // Parameter 생성 OracleParameter pSendDate = new OracleParameter(); pSendDate.OracleDbType = OracleDbType.Date; pSendDate.Value = sendDateVals; OracleParameter pSendStat = new OracleParameter(); pSendStat.OracleDbType = OracleDbType.Varchar2; pSendStat.Value = sendStatVals; OracleParameter pMsgType = new OracleParameter(); pMsgType.OracleDbType = OracleDbType.Varchar2; pMsgType.Value = msgTypeVals; OracleParameter pPhone = new OracleParameter(); pPhone.OracleDbType = OracleDbType.Varchar2; pPhone.Value = phoneVals; OracleParameter pCallBack = new OracleParameter(); pCallBack.OracleDbType = OracleDbType.Varchar2; pCallBack.Value = callBackVals; OracleParameter pMsg = new OracleParameter(); pMsg.OracleDbType = OracleDbType.Varchar2; pMsg.Value = msgVals; OracleParameter pEtc1 = new OracleParameter(); pEtc1.OracleDbType = OracleDbType.Varchar2; pEtc1.Value = etc1Vals; OracleParameter pEtc2 = new OracleParameter(); pEtc2.OracleDbType = OracleDbType.Varchar2; pEtc2.Value = etc2Vals; OracleParameter pEtc3 = new OracleParameter(); pEtc3.OracleDbType = OracleDbType.Varchar2; pEtc3.Value = etc3Vals; oTx = oCon.BeginTransaction(IsolationLevel.ReadCommitted); cmd.Transaction = oTx; cmd.CommandType = CommandType.Text; insertSQL = @"INSERT INTO " + sc_tran_table +" (TR_NUM" + ", TR_SENDDATE, TR_SENDSTAT, TR_MSGTYPE" + ", TR_PHONE, TR_CALLBACK, TR_MSG" + ", TR_ETC1, TR_ETC2, TR_ETC3)"; insertSQL = insertSQL + @" VALUES(" + sc_sequence +".NEXTVAL" + ", :1, :2, :3" + ", :4, :5, :6 " + ", :7, :8, :9)"; cmd.CommandText = insertSQL; cmd.ArrayBindCount = totRows; cmd.Parameters.Add(pSendDate); cmd.Parameters.Add(pSendStat); cmd.Parameters.Add(pMsgType); cmd.Parameters.Add(pPhone); cmd.Parameters.Add(pCallBack); cmd.Parameters.Add(pMsg); cmd.Parameters.Add(pEtc1); cmd.Parameters.Add(pEtc2); cmd.Parameters.Add(pEtc3); // Insert 실행 oraResult = cmd.ExecuteNonQuery(); if (oraResult > 0) { oTx.Commit(); log.Info(dataRange + " - 개별발송>SMS DB Insert> 성공"); ht.Clear(); ht.Add("SENDRESULT", "20"); ht.Add("MINID", minId); ht.Add("MAXID", maxId); bizTx = new CMXmlAutoBizTx(); if (bizTx.XmlNonExecute("uspSingleSMSLogSendAfter", ht) > 0) { log.Info(dataRange + " - 개별발송>발송결과(20) 갱신 성공"); } else { log.Error(dataRange + " - 개별발송>발송결과(20) 갱신 실패"); } } else { ht.Clear(); ht.Add("SENDRESULT", "40"); ht.Add("MINID", minId); ht.Add("MAXID", maxId); bizTx = new CMXmlAutoBizTx(); if (bizTx.XmlNonExecute("uspSingleSMSLogSendAfter", ht) > 0) { log.Error(dataRange + " - 개별발송>발송 실패, 발송결과(40) 갱신 성공"); } else { log.Error(dataRange + " - 개별발송>발송 실패, 발송결과(40) 갱신 실패"); } } } catch (Exception ex) { log.Error(dataRange + " - 개별발송>SMS DB Insert 실패>Task Sub" + ex.ToString()); oTx.Rollback(); ht.Clear(); ht.Add("SENDRESULT", "40"); ht.Add("MINID", minId); ht.Add("MAXID", maxId); bizTx = new CMXmlAutoBizTx(); if (bizTx.XmlNonExecute("uspSingleSMSLogSendAfter", ht) > 0) { log.Error(dataRange + " - 개별발송>발송 실패, 발송결과(40) 갱신 실패"); } else { log.Error(dataRange + " - 개별발송>발송 실패, 발송결과(40) 갱신 실패"); } } finally { oCon.Close(); cmd.Dispose(); } } catch (Exception ex) { log.Error(dataRange + " - 개별발송>SMS DB Insert 실패>Task Main" + ex.ToString()); ht.Clear(); ht.Add("SENDRESULT", "40"); ht.Add("MINID", minId); ht.Add("MAXID", maxId); bizTx = new CMXmlAutoBizTx(); if (bizTx.XmlNonExecute("uspSingleSMSLogSendAfter", ht) > 0) { log.Error(dataRange + " - 개별발송>발송 실패, 발송결과(40) 갱신 성공"); } else { log.Error(dataRange + " - 개별발송>발송 실패, 발송결과(40) 갱신 실패"); } } return oraResult; }; #endregion 개별 SMS 발송 Function #region 개별 SMS 로그 조회 Task bool sendCheck = false; int iResult=-1; Task<int>[] tasks = new Task<int>[1]; while(sendCheck == false) { oBiz = new CMXmlAutoBizTx(); iResult = oBiz.XmlNonExecute("uspSingleSMSLogStand"); if (iResult < 1) { sendCheck = true; break; } else { oBiz2 = new CMXmlAutoBiz(); ds = oBiz2.GetData("uspSingleSMSLogSend"); log.Info(taskIdx.ToString() + " - 개별 발송대상 데이터 건수> " + ds.Tables[0].Rows.Count.ToString()); if(ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { tasks[taskIdx] = Task<int>.Factory.StartNew(action, ds.Tables[0]); taskIdx++; Array.Resize(ref tasks, taskIdx+1); continue; } else { sendCheck = true; break; } } } if (tasks.Length > 0) { // 작업 종료할 때까지 대기 if (tasks.GetValue(0) != null) { if (taskIdx > 0) { Array.Resize(ref tasks, taskIdx ); } Task.WaitAll(tasks); } else { log.Info(taskIdx.ToString() + " - 개별 발송할 데이터 없음"); } } log.Info(taskIdx.ToString() + " - 개별 발송 작업 완료"); #endregion 개별 SMS 로그 조회 Task } catch (Exception ex) { log.Error(taskIdx.ToString() + " - 개별발송>발송 작업 실패>" + ex.ToString()); } finally { if (oBiz != null) oBiz = null; if (oBiz2 != null) oBiz2 = 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; }
public JsonResult UpdateRecords(string id, string records) { OutgoingNotice notice = repository.FindByNotCache(id); if (notice == null) { return Json(new { success = false, message = "未找到指定的发料通知单" }); } if (string.IsNullOrEmpty(records)) { return Json(new { success = false, message = "发料记录不能为空" }); } IPipeOutgoingRecordRepository porRepository = RepositoryFactory.GetRepository<IPipeOutgoingRecordRepository, PipeOutgoingRecord>(unitOfWork); IPipeRepository pipeRepository = RepositoryFactory.GetRepository<IPipeRepository, PDCPMS.Model.Common.Pipe>(unitOfWork); JavaScriptSerializer serializer = new JavaScriptSerializer(); Account onlineAccount = UserSession.OnlineAccount; IList<PDCPMS.DataContracts.Outgoing.PipeOutgoingRecord> recordContracts = serializer.Deserialize<IList<PDCPMS.DataContracts.Outgoing.PipeOutgoingRecord>>(records); IList<PipeOutgoingRecord> existsRecords = notice.Records; IList<long> pipeIds = new List<long>(); float totalWorkLength = 0f; float totalBusinessLength = 0f; float totalWeight = 0f; int count = 0; OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(System.Configuration.ConfigurationManager.ConnectionStrings["PDCPDMConnection"].ConnectionString); conn.Open(); OracleTransaction tran = conn.BeginTransaction(); try { foreach (PDCPMS.DataContracts.Outgoing.PipeOutgoingRecord rc in recordContracts) { PDCPMS.Model.Common.Pipe pipe = null; PipeOutgoingRecord record = null; if (string.IsNullOrEmpty(rc.Key)) { pipe = pipeRepository.FindBy(rc.PipeId); if (pipe == null || !PipePolicy.CanSentout(pipe) || pipeIds.Contains(rc.PipeId)) { continue; } if ((pipe.State & PipeState.SentingOut) != PipeState.SentingOut) { pipe.State |= PipeState.SentingOut; } //pipeRepository[pipe.Key] = pipe; pipeRepository.UpdatedPipe(pipe, conn, tran); record = new PipeOutgoingRecord(Guid.NewGuid().ToString("N"), notice, pipe, DateTime.Now); //porRepository.Add(record); porRepository.AddPipeOutgoingRecord(record, conn, tran); pipeIds.Add(rc.PipeId); } else { record = existsRecords.FirstOrDefault(r => r.Key.ToString() == rc.Key); if (record == null) { continue; } pipe = record.Pipe; pipeIds.Add((long)pipe.Key); existsRecords.Remove(record); } count += 1; totalBusinessLength += pipe.BusinessLength.HasValue ? pipe.BusinessLength.Value : 0; totalWorkLength += pipe.WorkLength.HasValue ? pipe.WorkLength.Value : 0; totalWeight += pipe.Weight.HasValue ? pipe.Weight.Value : 0; } // 已删除 foreach (PipeOutgoingRecord record in existsRecords) { PDCPMS.Model.Common.Pipe pipe = record.Pipe; if ((pipe.State & PipeState.SentingOut) == PipeState.SentingOut) { pipe.State ^= PipeState.SentingOut; } //pipeRepository[pipe.Key] = pipe; pipeRepository.UpdatedPipe(pipe, conn, tran); //porRepository.Remove(record); porRepository.RemovePipeOutgoingRecord(record, conn, tran); } notice.SentBusinessLength = totalBusinessLength; notice.SentCount = count; notice.SentWeight = totalWeight / 1000; notice.SentWorkLength = totalWorkLength; notice.Sender = onlineAccount; notice.DateFinished=DateTime.Now; //repository[notice.Key] = notice; repository.UpdatedItemOutgoingNotice(notice, conn, tran); tran.Commit(); } catch (Exception ex) { tran.Rollback(); return Json(new { success = false, message = "发料记录更新未成功"+ex }); } //unitOfWork.Commit(); //(unitOfWork as UnitOfWork).addedEntities[ return Json(new { success = true, message = "发料记录更新成功" }); }
/// <summary> /// 执行SQL语句,返回影响的记录数 /// </summary> /// <param name="SQLString">SQL语句</param> /// <returns>影响的记录数</returns> //public static int ExecuteSql(string SQLString) //{ // using (OracleConnection connection = new OracleConnection(connectionString)) // { // using (OracleCommand cmd = new OracleCommand(SQLString,connection)) // { // try // { // connection.Open(); // int rows=cmd.ExecuteNonQuery(); // return rows; // } // catch(System.Data.OracleClient.OracleException E) // { // connection.Close(); // throw new Exception(E.Message); // } // } // } //} /// <summary> /// 执行多条SQL语句,实现数据库事务。 /// </summary> /// <param name="SQLStringList">多条SQL语句</param> public static void ExecuteSqlTran(ArrayList SQLStringList) { using (OracleConnection conn = new OracleConnection(connectionString)) { conn.Open(); OracleCommand cmd = new OracleCommand(); cmd.Connection = conn; OracleTransaction tx = conn.BeginTransaction(); cmd.Transaction = tx; try { for (int n = 0; n < SQLStringList.Count; n++) { string strsql = SQLStringList[n].ToString(); if (strsql.Trim().Length > 1) { cmd.CommandText = strsql; cmd.ExecuteNonQuery(); } } tx.Commit(); } catch (OracleException E) { tx.Rollback(); throw new Exception(E.Message); } } }
/// <summary> /// Na Delete wordt het aantal verwijderde rijen teruggegeven. /// </summary> /// <param name="businessObject"></param> /// <returns></returns> public int Delete(BeheerContextEntity businessObject) { int rowsaffected = 0; using (OracleConnection conn = new OracleConnection(_oradb)) { conn.Open(); var trans = conn.BeginTransaction(); try { string sqlUpdate = @" DELETE FROM {0} WHERE {1} = '{2}' "; sqlUpdate = string.Format(sqlUpdate, businessObject.Tablename, businessObject.DataKeyName, businessObject.DataKeyValue); var cmd = new OracleCommand { Connection = conn, CommandText = sqlUpdate, CommandType = CommandType.Text }; cmd.Connection = conn; rowsaffected = cmd.ExecuteNonQuery(); trans.Commit(); } catch (Exception) { trans.Rollback(); throw; } } return rowsaffected; }
public string DataPublishTrans(int count, DataTable dt, string Company_code, string FielName, string User_ID, string type) { string retval = ""; string[] retCount = new string[count]; try { string[] arr_company_Code = new string[count]; string[] arr_FielName = new string[count]; string[] P_Trans_type = new string[count]; string[] P_Rowid = new string[count]; string[] p_bank_code = new string[count]; string[] p_branch_code = new string[count]; string[] P_Userid = new string[count]; string[] P_type = new string[count]; int[] arrSize = new int[count]; for (int i = 0; i < count; i++) { arrSize[i] = 1000; arr_company_Code[i] = Company_code; arr_FielName[i] = FielName; P_Trans_type[i] = dt.Rows[i]["TRANS_TYPE"].ToString(); P_Rowid[i] = dt.Rows[i]["ROWID"].ToString(); p_bank_code[i] = ""; p_branch_code[i] = ""; P_Userid[i] = User_ID; P_type[i] = type; } Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(ConnectionString); conn.Open(); Oracle.DataAccess.Client.OracleTransaction trans = conn.BeginTransaction(); Oracle.DataAccess.Client.OracleCommand cmd = conn.CreateCommand(); cmd.CommandText = "sp_Data_Publish"; cmd.CommandType = CommandType.StoredProcedure; cmd.BindByName = true; cmd.ArrayBindCount = count; cmd.Parameters.Add("P_company_code", OracleDbType.Varchar2, arr_company_Code, ParameterDirection.Input); cmd.Parameters.Add("P_FILE_NAME", OracleDbType.Varchar2, arr_FielName, ParameterDirection.Input); cmd.Parameters.Add("P_Trans_type", OracleDbType.Varchar2, P_Trans_type, ParameterDirection.Input); cmd.Parameters.Add("P_Rowid", OracleDbType.Varchar2, P_Rowid, ParameterDirection.Input); cmd.Parameters.Add("p_bank_code", OracleDbType.Varchar2, p_bank_code, ParameterDirection.Input); cmd.Parameters.Add("p_branch_code", OracleDbType.Varchar2, p_branch_code, ParameterDirection.Input); cmd.Parameters.Add("P_Userid", OracleDbType.Varchar2, P_Userid, ParameterDirection.Input); cmd.Parameters.Add("P_type", OracleDbType.Varchar2, P_type, ParameterDirection.Input); cmd.Parameters.Add("v_retval", OracleDbType.Varchar2, "", ParameterDirection.Output).ArrayBindSize = arrSize; #region int result = cmd.ExecuteNonQuery(); for (int i = 0; i < count; i++) { retCount[i] = ((Oracle.DataAccess.Types.OracleString[])(cmd.Parameters["v_retval"].Value))[i].ToString(); if (retCount[i].ToString().StartsWith("04") == false) { trans.Rollback(); conn.Close(); return(retCount[i]); } } string val = postLeadger(arr_company_Code[0], arr_FielName[0], P_Trans_type[0], P_Rowid[0], p_bank_code[0], p_branch_code[0], P_Userid[0], "14"); if (val.StartsWith("04")) { trans.Commit(); conn.Close(); //CoreBankingPoolCall(arr_company_Code[0], arr_FielName[0], P_Userid[0]); retval = retCount[0]; } else { trans.Rollback(); conn.Close(); return(val); } #endregion } catch (Exception ex) { retval = ex.Message; } return(retval); }
public IDatabaseQuery StartQuery(bool withTransaction) { var connection = new OracleConnection(ConnectionString); OracleTransaction transaction = null; if (withTransaction) { try { connection.Open(); } catch (Exception ex) { LogFactory.Create("Oracle database layer - start query").Error(ex.ToString()); try { connection.Close(); } catch { } OracleConnection.ClearAllPools(); connection = new OracleConnection(ConnectionString); connection.Open(); } transaction = connection.BeginTransaction(); } IDatabaseQuery query; try { query = QueryFactory(connection, transaction, LogFactory); } catch (Exception ex) { var log = LogFactory.Create("Oracle database layer - start query"); log.Error(ex.ToString()); log.Info("Transactions: {0}, connections: {1}".With(OpenTransactions.Count, OpenConnections.Count)); throw; } if (withTransaction) OpenTransactions.TryAdd(query, transaction); OpenConnections.TryAdd(query, connection); return query; }
public OracleTransaction BeginTransaction(OracleConnection conn) { if (conn.State == ConnectionState.Closed) { conn.Open(); } return conn.BeginTransaction(); }
public JsonResult Approve(string code, string ReportNO) { NormalCheckTask task = repository.FindByCode(code); Account onlineAccount = UserSession.OnlineAccount; if (task == null) { return Json(new { success = false, message = "未找到指定常检任务" }); } if (task.Approver != null) { return Json(new { success = false, message = string.Format("该批常检任务已完成批准,报告号为{0},请不要重复批准!", ReportNO) }); } Regex codePattern = new Regex(@"^[-|\.|a-z|A-Z|0-9]+$"); if (!codePattern.IsMatch(ReportNO)) { return Json(new { success = false, message = "报告号格式不正确" }); } if (null != repository.FindByReportNO(ReportNO,onlineAccount)) { return Json(new { success = false, message = string.Format("报告号{0}已经存在,请尝试使用其它报告号", ReportNO) }); } #region 回滚事务 OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(System.Configuration.ConfigurationManager.ConnectionStrings["PDCPDMConnection"].ConnectionString); conn.Open(); OracleTransaction tran = conn.BeginTransaction(); try { #endregion task.ReportNO = ReportNO; task.Approver = UserSession.OnlineAccount; task.DateApproved = DateTime.Now; //repository[task.Key] = task; repository.UpdatedTask(task, conn, tran); // Create Records INormalCheckRecordRepository recordRepository = RepositoryFactory.GetRepository<INormalCheckRecordRepository, NormalCheckRecord>(unitOfWork); TransferTask transferTask = task.TransferTask; int orederNO = 0; DateTime now = DateTime.Now; transferTask.Items.ForEach(transferItem => { transferItem.Records.ForEach(transferRecord => { orederNO++; NormalCheckRecord r = new NormalCheckRecord(Guid.NewGuid().ToString("N"), task, transferRecord.Pipe, now, orederNO); //recordRepository.Add(r); recordRepository.AddRecord(r, conn, tran); }); }); //unitOfWork.Commit(); #region 回滚事务 tran.Commit(); } catch (Exception ex) { tran.Rollback(); return Json(new { success = true, message = "没有成功批准检测任务,请重新批准!" + ex }); } #endregion return Json(new { success = true, message = "成功批准检测任务!" }); }
public JsonResult UpdateCheckResults(string code) { NormalCheckTask task = repository.FindByCode(code); if (task == null) { return Json(new { success = false, message = "未找到指定的常检任务" }); } if ((int)task.State < (int)NormalCheckTaskState.Received) { return Json(new { success = false, message = "当前常检任务沿不允许更新检测数据" }); } if (task.State == NormalCheckTaskState.Verified) { return Json(new { success = false, message = "当前常检任务已审核存档,不允许被编辑" }); } JavaScriptSerializer serializer = new JavaScriptSerializer(); IList<PDCPMS.DataContracts.NormalCheck.NormalCheckRecord> recordContracts = null; try { recordContracts = serializer.Deserialize<IList<PDCPMS.DataContracts.NormalCheck.NormalCheckRecord>>(Request.Form["Records"]); } catch { return Json(new { success = false, message = "请求数据有误" }); } INormalCheckRecordRepository recordRepository = RepositoryFactory.GetRepository<INormalCheckRecordRepository, NormalCheckRecord>(unitOfWork); IPipeRepository pipeRepository = RepositoryFactory.GetRepository<IPipeRepository, PDCPMS.Model.Common.Pipe>(unitOfWork); IList<String> codes = new List<string>(); #region 回滚事务 OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(System.Configuration.ConfigurationManager.ConnectionStrings["PDCPDMConnection"].ConnectionString); conn.Open(); OracleTransaction tran = conn.BeginTransaction(); try { #endregion foreach (PDCPMS.DataContracts.NormalCheck.NormalCheckRecord rc in recordContracts) { NormalCheckRecord record = recordRepository.FindBy(rc.Key); if (record == null) continue; record.IsBodyValid = rc.IsBodyValid; record.IsCheckBlindCrackValid = rc.IsCheckBlindCrackValid; record.IsCheckBodyCrackValid = rc.IsCheckBodyCrackValid; record.IsClosenessValid = rc.IsClosenessValid; record.IsEndValid = rc.IsEndValid; record.IsPressureValid = rc.IsPressureValid; record.IsScrewValid = rc.IsScrewValid; record.IsStraightValid = rc.IsStraightValid; record.IsThicknessValid = rc.IsThicknessValid; record.IsUnBlockValid = rc.IsUnBlockValid; record.IsValid = rc.IsValid; record.MaleP = rc.MaleP; record.FemaleA = rc.FemaleA; record.PressureDetectValue = rc.PressureDetectValue; record.PressureHoldValue = rc.PressureHoldValue; //2010-11-12 by huncherby record.OutDiameter = rc.OutDiameter; record.ThicknessValid = rc.ThicknessValid; record.CheckUnBlock = rc.CheckUnBlock; record.MinOutDiameter = rc.MinOutDiameter; record.Comment = rc.Comment; //recordRepository[record.Key] = record; recordRepository.UpdateRecord(record,conn,tran); if (!string.IsNullOrEmpty(rc.Code)) { rc.Code = rc.Code.ToUpper().Trim(); if (codes.Contains(rc.Code)) { return Json(new { success = false, message = "存在重复的工序号" }); } codes.Add(rc.Code); record.Pipe.Code = task.WorkNO + rc.Code; } else { record.Pipe.Code = string.Empty; } record.Pipe.WorkLength = rc.WorkLength; //pipeRepository[record.Pipe.Key] = record.Pipe; pipeRepository.UpdatedPipe(record.Pipe,conn,tran); } task.ReportComment = Request.Form["ReportComment"]; task.CheckConclusion = Request.Form["CheckConclusion"]; task.Finisher = UserSession.OnlineAccount; task.DateFinished = DateTime.Now; //repository[task.Key] = task; repository.UpdatedTask(task,conn,tran); //unitOfWork.Commit(); #region 回滚事务 tran.Commit(); } catch (Exception ex) { tran.Rollback(); return Json(new { success = true, message = "检测数据更新失败,请保存检测数据!" + ex }); } #endregion //改变保存数据的状态 2010-11-22 by hunherby pipeRepository.VerifyPipeBy(task); retory.updateisvaild(task); //更新结论 repository.updateCheckConclusion(task); return Json(new { success = true, message = "检测数据更新成功" }); }
/** * @brief Adds a user to the database * @param User user The user to add **/ public static int UpdateUserSettings(User user) { int result = -1; bool error = false; OracleConnection connection = new OracleConnection(ConfigurationBLL.GetConnectionStringByRole(Role.ROLES.CONNECTION_USER_ROLE)); OracleTransaction transaction = null; try { //Open the connection connection.Open(); //Start a local transaction transaction = connection.BeginTransaction(); //First we add the user to the database //Setup the command to run OracleCommand command = new OracleCommand(); command.Connection = connection; command.Transaction = transaction; if (user.User_Id > 0) { command.Parameters.Clear(); command.BindByName = true; if (user.Password != null && user.Password.Length > 0) { command.CommandText = UPDATE_USER_PASSWORD; command.Parameters.Add(":password", user.Password); } else { command.CommandText = UPDATE_USER_NO_PASSWORD; } //First we set the parameters command.Parameters.Add(":user_id", user.User_Id); command.Parameters.Add(":name", user.Name); command.Parameters.Add(":mobile", user.Mobile); command.Parameters.Add(":address", user.Address); command.Parameters.Add(":paypal_id", user.Paypal_Id); command.Parameters.Add(":betfair_id", user.Betfair_Id); Tools.printDebug(command.CommandText.ToString()); Tools.printDebug(command.Parameters.ToString()); //Run the command result = command.ExecuteNonQuery(); Tools.printDebug("UPDATE USER RESULT: " + result); } } catch (Exception exception) { error = true; Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception); throw new Exception(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception); } finally { if (transaction != null) { if (error) { transaction.Rollback(); } else { transaction.Commit(); } } //Closes the datareader and connection to the database if (connection != null) { connection.Close(); } } return result; }
/** * @brief Adds a user to the database * @param User user The user to add **/ public static int RegisterNewUser(User user) { int lastInsertedId = -1; bool error = false; OracleConnection connection = new OracleConnection(ConfigurationBLL.GetConnectionStringByRole(Role.ROLES.CONNECTION_USER_ROLE)); OracleTransaction transaction = null; try { //Open the connection connection.Open(); //Start a local transaction transaction = connection.BeginTransaction(); //First we add the user to the database //Setup the command to run OracleCommand command = new OracleCommand(); command.Connection = connection; command.Transaction = transaction; //Get the next id command.CommandText = DatabaseSchema.SELECT_USERS_NEXT_ID; int user_id = Convert.ToInt32(command.ExecuteScalar()); if (user_id > 0) { command.Parameters.Clear(); command.CommandText = INSERT_USER; //First we set the parameters command.Parameters.Add("user_id", user_id); command.Parameters.Add("password", user.Password); command.Parameters.Add("role_id", (int)user.Role_Id); command.Parameters.Add("name", user.Name); command.Parameters.Add("email", user.Email); command.Parameters.Add("registered", DatabaseSchema.USER_NOT_REGISTERED); command.Parameters.Add("mobile", user.Mobile); command.Parameters.Add("address", user.Address); command.Parameters.Add("paypal_id", user.Paypal_Id); command.Parameters.Add("betfair_id", user.Betfair_Id); //Run the command command.ExecuteNonQuery(); lastInsertedId = user_id; } } catch (Exception exception) { error = true; Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception); throw new Exception(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception); } finally { if (transaction != null) { if (error) { transaction.Rollback(); } else { transaction.Commit(); } } //Closes the datareader and connection to the database if (connection != null) { connection.Close(); } } return lastInsertedId; }
public static int ConfirmUserRegistration(String user_id, String email) { int result = -1; bool error = false; OracleConnection connection = new OracleConnection(ConfigurationBLL.GetConnectionStringByRole(Role.ROLES.CONNECTION_USER_ROLE)); OracleTransaction transaction = null; try { //Open the connection connection.Open(); //Start a local transaction transaction = connection.BeginTransaction(); //First we add the user to the database //Setup the command to run string strCommand = REGISTER_USER; strCommand = strCommand.Replace("@user_id", user_id); strCommand = strCommand.Replace("@email", email); strCommand = strCommand.Replace("@registered", "1"); Tools.printDebug(strCommand); OracleCommand command = new OracleCommand(strCommand); command.Connection = connection; command.Transaction = transaction; //Run the command result = command.ExecuteNonQuery(); } catch (Exception exception) { error = true; Tools.printDebug(new System.Diagnostics.StackFrame(), "", exception); throw new Exception(Resources.CANNOT_ACCESS_DATABASE_EXCEPTION_MESSAGE, exception); } finally { if (transaction != null) { if (error) { transaction.Rollback(); } else { transaction.Commit(); } } //Closes the datareader and connection to the database if (connection != null) { connection.Close(); } } return result; }
/// <summary> /// Na Update wordt het aantal bijgewerkte rijen teruggegeven. /// </summary> /// <param name="oldBusinessObject"></param> /// <param name="newBusinessObject"></param> /// <returns></returns> public int Update(BeheerContextEntity oldBusinessObject, BeheerContextEntity newBusinessObject) { int rowsAffected = 0; using (OracleConnection conn = new OracleConnection(_oradb)) { string sqlUpdate = "update {0} set {1} ='{2}' where {1} = '{3}'"; string tableName = oldBusinessObject.Tablename; string columnName = oldBusinessObject.DataKeyName; sqlUpdate = string.Format(sqlUpdate, tableName, columnName, newBusinessObject.DataKeyValue, oldBusinessObject.DataKeyValue); conn.Open(); var trans = conn.BeginTransaction(); try { var cmd = new OracleCommand { Connection = conn, CommandText = sqlUpdate, CommandType = CommandType.Text }; cmd.Connection = conn; rowsAffected = cmd.ExecuteNonQuery(); trans.Commit(); } catch (Exception) { trans.Rollback(); throw; } } return rowsAffected; }
public void InsertExample() { using (var connection = new OracleConnection(ConnectionString)) { connection.Open(); var id = new Random().Next(); var insertCommand = new OracleCommand(); insertCommand.Connection = connection; insertCommand.CommandType = CommandType.Text; insertCommand.CommandText = "Insert into \"MCC_User\" (\"Id\",\"Name\",\"SabreID\",\"Pcc\") Values (:Id,'Roman', '123123','3213')"; insertCommand.Parameters.Add(new OracleParameter(":Id", id)); insertCommand.ExecuteNonQuery(); var command = new OracleCommand("Select * from \"MCC_User\" where \"Name\" = 'Roman'"); command.Connection = connection; command.CommandType = System.Data.CommandType.Text; using (var reader = command.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { Console.WriteLine(reader["Id"] + ": " + reader["Name"]); } } } command.CommandText = "select count(*) from \"MCC_User\""; Console.WriteLine(command.ExecuteScalar()); var transaction = connection.BeginTransaction(); command.CommandText = "Delete from \"MCC_User\" where \"Name\" = 'Roman'"; command.ExecuteNonQuery(); transaction.Rollback(); } Console.ReadKey(); }
public int Insert(ParentKeyEntity masterForeignKey, BeheerContextEntity newBusinessObject) { int rowsAffected = 0; using (OracleConnection conn = new OracleConnection(_oradb)) { conn.Open(); var trans = conn.BeginTransaction(); try { string sqlUpdate = @"INSERT INTO {0} ({1}, {3}) VALUES ('{2}','{4}') "; sqlUpdate = string.Format(sqlUpdate, newBusinessObject.Tablename, newBusinessObject.DataKeyName, newBusinessObject.DataKeyValue, masterForeignKey.DataKeyName, masterForeignKey.DataKeyValue); var cmd = new OracleCommand { Connection = conn, CommandText = sqlUpdate, CommandType = CommandType.Text }; cmd.Connection = conn; rowsAffected = cmd.ExecuteNonQuery(); trans.Commit(); } catch (Exception) { trans.Rollback(); throw; } } return rowsAffected; }
private void SafeExecuteCommand(Action<OracleCommand> action) { using (var connection = new OracleConnection(_connectionString)) { OracleTransaction transaction = null; OracleCommand com; try { connection.Open(); OracleSqlUtility.SetSqlUserInfo(connection, _userInfo); transaction = connection.BeginTransaction(); com = connection.CreateCommand(); com.CommandTimeout = SqlUtility.SqlCommandTimeout; com.Transaction = transaction; } catch (OracleException ex) { if (transaction != null) transaction.Rollback(); var csb = new OracleConnectionStringBuilder(_connectionString); string msg = string.Format(CultureInfo.InvariantCulture, "Could not connect to data source '{0}', userID '{1}'.", csb.DataSource, csb.UserID); _logger.Error(msg); _logger.Error(ex.ToString()); throw new FrameworkException(msg, ex); } try { var setNationalLanguage = OracleSqlUtility.SetNationalLanguageQuery(); if (!string.IsNullOrEmpty(setNationalLanguage)) { _logger.Trace("Setting national language: {0}", SqlUtility.NationalLanguage); com.CommandText = setNationalLanguage; com.ExecuteNonQuery(); } action(com); transaction.Commit(); } catch (OracleException ex) { if (com != null && !string.IsNullOrWhiteSpace(com.CommandText)) _logger.Error("Unable to execute SQL query:\r\n" + com.CommandText); string msg = "OracleException has occurred:\r\n" + ReportSqlErrors(ex); if (ex.Number == 911) msg += "\r\nCheck that you are not using ';' at the end of the command's SQL query."; _logger.Error(msg); _logger.Error(ex.ToString()); throw new FrameworkException(msg, ex); } finally { TryRollback(transaction); } } }
public JsonResult Finish(string id) { TransferTask task = repository.FindByNotCache(id); if (task == null) { return Json(new { success = false, message = "无法找到指定的调度任务" }); } if (task.IsFinished) { return Json(new { success = false, message = "指定的调度任务已经完成,不能重复确认" }); } DateTime now = DateTime.Now; #region 回滚事务 OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(System.Configuration.ConfigurationManager.ConnectionStrings["PDCPDMConnection"].ConnectionString); conn.Open(); OracleTransaction tran = conn.BeginTransaction(); try { #endregion task.IsFinished = true; task.DateFinished = now; ITransferItemRepository transferItemRepository = RepositoryFactory.GetRepository<ITransferItemRepository, TransferItem>(unitOfWork); ITransferRecordRepository transferRecordRepository = RepositoryFactory.GetRepository<ITransferRecordRepository, TransferRecord>(unitOfWork); IPipeRepository pipeRepository = RepositoryFactory.GetRepository<IPipeRepository, PDCPMS.Model.Common.Pipe>(unitOfWork); // Li Zheng 2010.11.28 替换掉 for (int i = task.Items.Count - 1; i > -1; i--) { TransferItem item = task.Items[i]; for (int j = item.Records.Count - 1; j > -1; j--) { TransferRecord r = item.Records[j]; r.DateFinished = now; PDCPMS.Model.Common.Pipe pipe = r.Pipe; if ((pipe.State & PipeState.Transfering) == PipeState.Transfering) pipe.State ^= PipeState.Transfering; if (task.IsSystemTask) { pipe.State |= PipeState.NormalChecking; } pipe.PosLastChangeTime = now; pipe.PrevPosition = pipe.Position; pipe.Position = r.NewPos; //pipeRepository[pipe.Key] = pipe; pipeRepository.UpdatedPipe(pipe, conn, tran); } } // Li Zheng 2010.11.28 //pipeRepository.FinishTranUpdatePipe(id); if (task.IsSystemTask) { INormalCheckTaskRepository taskRepository = RepositoryFactory.GetRepository<INormalCheckTaskRepository, NormalCheckTask>(unitOfWork); NormalCheckTask normalCheckTask = taskRepository.FindBy(task); if (normalCheckTask != null) { normalCheckTask.Sender = UserSession.OnlineAccount; normalCheckTask.DateSent = now; normalCheckTask.State = NormalCheckTaskState.Transferd; //taskRepository[normalCheckTask.Key] = normalCheckTask; taskRepository.UpdatedTask(normalCheckTask, conn, tran); } } //repository[task.Key] = task; repository.UpdateTransfertask(task, conn, tran); //unitOfWork.Commit(); #region 回滚事务 tran.Commit(); } catch (Exception ex) { tran.Rollback(); return Json(new { success = true, message = "没有成功确认完成调度任务,请重新确认!"+ex }); } #endregion //TempData["HintMessage"] = new PDCPMS.Application.HintMessage { Content = "成功确认完成调度任务" }; return Json(new { success = true, message = "成功确认完成调度任务" }); }
/// <summary> /// 执行多条SQL语句,实现数据库事务。 /// </summary> /// <param name="SQLStringList">多条SQL语句</param> public static void ExecuteSqlTran(string conStr,List<String> SQLStringList) { using (OracleConnection conn = new OracleConnection(conStr)) { conn.Open(); OracleCommand cmd = new OracleCommand(); cmd.Connection = conn; OracleTransaction tx = conn.BeginTransaction(); cmd.Transaction = tx; try { foreach (string sql in SQLStringList) { if (!String.IsNullOrEmpty(sql)) { cmd.CommandText = sql; cmd.ExecuteNonQuery(); } } tx.Commit(); } catch (System.Data.OracleClient.OracleException E) { tx.Rollback(); throw new Exception(E.Message); } finally { if (conn.State != ConnectionState.Closed) { conn.Close(); } } } }
//public string CoreBankingPoolCall(string arr_company_code, string arr_file_name, string arr_userid) //{ // try // { // string[] p_company_code = new string[1]; // p_company_code[0] = arr_company_code; // string[] p_file_name = new string[1]; // p_file_name[0] = arr_file_name; // string[] p_userid = new string[1]; // p_userid[0] = arr_userid; // int[] arrSize = new int[1]; // arrSize[0] = 1000; // Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(ConnectionString); // conn.Open(); // Oracle.DataAccess.Client.OracleCommand cmd = conn.CreateCommand(); // cmd.CommandText = "corebank_system_sym_pub"; // cmd.CommandType = CommandType.StoredProcedure; // cmd.BindByName = true; // cmd.ArrayBindCount = 1; // cmd.Parameters.Add("p_company_code", OracleDbType.Varchar2, p_company_code, ParameterDirection.Input); // cmd.Parameters.Add("p_file_name", OracleDbType.Varchar2, p_file_name, ParameterDirection.Input); // cmd.Parameters.Add("p_userid", OracleDbType.Varchar2, p_userid, ParameterDirection.Input); // cmd.Parameters.Add("p_retval", OracleDbType.Varchar2, "", ParameterDirection.Output).ArrayBindSize = arrSize; // int result = cmd.ExecuteNonQuery(); // string p_return = ((Oracle.DataAccess.Types.OracleString[])(cmd.Parameters["p_retval"].Value))[0].ToString(); // } // catch (Exception ex) // { // return ex.Message; // } // return ""; //} public string force_updation(GridView gv, string User_ID, string type, int count, string company_code, string file_name) { string retval = ""; string[] retCount; try { int updateCount = 0; string[] arr = new string[count]; int m = 0; for (int i = 0; i < count; i++) { //if (((HiddenField)gv.Rows[i].FindControl("hf_BranchCode")).Value != "" && ((TextBox)gv.Rows[i].FindControl("txtaccountno_symbol")).Text != "") if (((TextBox)gv.Rows[i].FindControl("txtBranchCode")).Text != "Branch not found" && ((TextBox)gv.Rows[i].FindControl("txtBranchCode")).Text != "" && ((TextBox)gv.Rows[i].FindControl("txtaccountno_symbol")).Text != "" && //((TextBox)gv.Rows[i].FindControl("txtSymbolTitle")).Text != "Account Title not found." && ((TextBox)gv.Rows[i].FindControl("txtSymbolTitle")).Text != "Symbol Title" && ((TextBox)gv.Rows[i].FindControl("txtSymbolTitle")).Text != "") { arr[i] = m.ToString(); updateCount++; } else { arr[i] = "N"; } m++; } string[] p_Rowid = new string[updateCount]; string[] p_branch_code = new string[updateCount]; string[] p_Account_no = new string[updateCount]; string[] p_benename = new string[updateCount]; string[] P_Userid = new string[updateCount]; string[] P_type = new string[updateCount]; string[] P_company_code = new string[updateCount]; string[] p_file_name = new string[updateCount]; int[] arrSize = new int[updateCount]; retCount = new string[updateCount]; int u = 0; for (int i = 0; i < count; i++) { if (arr[i].ToString() != "N") { arrSize[u] = 1000; p_Rowid[u] = gv.Rows[i].Cells[0].Text; //p_branch_code[u] = (((HiddenField)gv.Rows[Convert.ToInt32(arr[i])].FindControl("hf_BranchCode")).Value == "" ? gv.Rows[i].Cells[3].Text : ((HiddenField)gv.Rows[i].FindControl("hf_BranchCode")).Value); p_branch_code[u] = (((TextBox)gv.Rows[Convert.ToInt32(arr[i])].FindControl("txtBranchCode")).Text == "" ? gv.Rows[i].Cells[3].Text : ((TextBox)gv.Rows[i].FindControl("txtBranchCode")).Text); p_Account_no[u] = (((TextBox)gv.Rows[Convert.ToInt32(arr[i])].FindControl("txtaccountno_symbol")).Text == "" ? gv.Rows[i].Cells[1].Text : ((TextBox)gv.Rows[i].FindControl("txtaccountno_symbol")).Text); p_benename[u] = ((TextBox)gv.Rows[Convert.ToInt32(arr[i])].FindControl("txtSymbolTitle")).Text; //(((TextBox)gv.Rows[Convert.ToInt32(arr[i])].FindControl("txtSymbolTitle")).Text == "" ? gv.Rows[i].Cells[1].Text : ((TextBox)gv.Rows[i].FindControl("txtaccountno_symbol")).Text); P_Userid[u] = User_ID; P_type[u] = type; P_company_code[u] = company_code; p_file_name[u] = file_name; u++; } } if (p_Rowid.Length > 0) { Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(ConnectionString); conn.Open(); Oracle.DataAccess.Client.OracleTransaction trans = conn.BeginTransaction(); Oracle.DataAccess.Client.OracleCommand cmd = conn.CreateCommand(); cmd.CommandText = "sp_force_Updation"; cmd.CommandType = CommandType.StoredProcedure; cmd.BindByName = true; cmd.ArrayBindCount = updateCount; cmd.Parameters.Add("p_rowid", OracleDbType.Varchar2, p_Rowid, ParameterDirection.Input); cmd.Parameters.Add("p_branch_code", OracleDbType.Varchar2, p_branch_code, ParameterDirection.Input); cmd.Parameters.Add("p_Account_no", OracleDbType.Varchar2, p_Account_no, ParameterDirection.Input); cmd.Parameters.Add("p_benename", OracleDbType.Varchar2, p_benename, ParameterDirection.Input); cmd.Parameters.Add("P_Userid", OracleDbType.Varchar2, P_Userid, ParameterDirection.Input); cmd.Parameters.Add("P_type", OracleDbType.Varchar2, P_type, ParameterDirection.Input); cmd.Parameters.Add("p_company_code", OracleDbType.Varchar2, P_company_code, ParameterDirection.Input); cmd.Parameters.Add("p_file_name", OracleDbType.Varchar2, p_file_name, ParameterDirection.Input); cmd.Parameters.Add("p_retval", OracleDbType.Varchar2, "", ParameterDirection.Output).ArrayBindSize = arrSize; int result = cmd.ExecuteNonQuery(); for (int i = 0; i < updateCount; i++) { retCount[i] = ((Oracle.DataAccess.Types.OracleString[])(cmd.Parameters["p_retval"].Value))[i].ToString(); if (retCount[i].ToString().StartsWith("0") == true) //Error { trans.Rollback(); conn.Close(); return(retCount[i]); } } trans.Commit(); conn.Close(); retval = retCount[0]; } } catch (Exception ex) { retval = "0;" + ex.Message; } return(retval); }
/// <summary> /// 通过事务执行SQL语句命令 /// </summary> /// <param name="connection"></param> /// <param name="SQLStringList"></param> public static void ExecuteSQLTransaction(OracleConnection connection, List<string> SQLStringList) { connection.Open(); OracleCommand command = new OracleCommand(); command.Connection = connection; OracleTransaction transaction = connection.BeginTransaction(); if (transaction != null) { //command.Transaction = transaction; transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted); } try { for (int i = 0; i < SQLStringList.Count; i++) { string str = SQLStringList[i].ToString(); if (str.Trim().Length > 1) { command.CommandText = str; command.ExecuteNonQuery(); } } transaction.Commit(); } catch (OracleException exception) { transaction.Rollback(); throw new Exception(exception.Message); } finally { connection.Close(); } }
/// <summary> /// OracleへUPDATE文を実行します。 /// 戻り値 boolean型 正常=true 異常=false /// </summary> /// <param name="sql">実行するSQL文です</param> public Boolean OracleUpdate(string sql) { Boolean rtncode = false; //戻り値用(初期値false) string connStr = GetConnectionString(); using (OracleCommand cmd = new OracleCommand()) { OracleConnection conn = new OracleConnection(); conn.ConnectionString = connStr; //トランザクション開始 conn.Open(); OracleTransaction transaction = conn.BeginTransaction(IsolationLevel.ReadCommitted); try { cmd.CommandText = sql; cmd.Connection = conn; cmd.Transaction = transaction; cmd.ExecuteNonQuery(); //トランザクションをコミットします。 transaction.Commit(); rtncode = true; } catch (Exception) { //トランザクションをロールバック transaction.Rollback(); rtncode = false; GetUser(); ErrorLogWrite(user_cd, "OracleUpdate", sql.Replace("'", "###")); } finally { conn.Close(); } } return rtncode; }
/// <summary> ///打开(如果必要的话),指定一个命令、连接、事务、参数类型和参数 /// </summary> /// <param name="command">OracleCommand</param> /// <param name="connection">OracleConnection</param> /// <param name="transaction">OracleTransaction 或 'null'</param> /// <param name="commandType"> CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">存储过程名称或SQL语句</param> /// <param name="commandParameters">OracleParameter[]</param> private static void PrepareCommand(OracleCommand command, OracleConnection connection, OracleTransaction transaction, CommandType commandType, string commandText, OracleParameter[] commandParameters) { if (connection.State != ConnectionState.Open) { connection.Open(); } command.Connection = connection; command.CommandText = commandText; if (transaction != null) { //command.Transaction = transaction; transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted); } command.CommandType = commandType; if (commandParameters != null) { AttachParameters(command, commandParameters); } return; }
/// <summary> /// 执行多条SQL语句,实现数据库事务。 /// </summary> /// <param name="SQLStringList">SQL语句的哈希表(key为sql语句,value是该语句的OracleParameter[])</param> public static void ExecuteSqlTran(Hashtable SQLStringList) { using (OracleConnection conn = new OracleConnection(connectionString)) { conn.Open(); using (OracleTransaction trans = conn.BeginTransaction()) { OracleCommand cmd = new OracleCommand(); try { //循环 foreach (DictionaryEntry myDE in SQLStringList) { string cmdText = myDE.Key.ToString(); OracleParameter[] cmdParms = (OracleParameter[])myDE.Value; PrepareCommand(cmd, conn, trans, cmdText, cmdParms); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); trans.Commit(); } } catch { trans.Rollback(); throw; } } } }
/// <summary> /// 演示 事务处理的部分。 /// </summary> /// <param name="conn"></param> private void Transaction(OracleConnection conn) { Console.WriteLine("开始事务处理操作!"); // 开始创建一个事务. OracleTransaction t = conn.BeginTransaction(); try { Console.WriteLine("模拟插入2条相同数据,造成主键冲突的异常!"); InsertData(conn, t); InsertData(conn, t); // 提交事务. t.Commit(); } catch (Exception ex) { Console.WriteLine(ex.Message); Console.WriteLine(ex.StackTrace); // 回滚事务. t.Rollback(); } if (ExistData(conn)) { Console.WriteLine("数据库中依然存在着主键为{0} 与 {1} 的的数据!", TEST_SALE_DATE, TEST_SALE_ITEM); } else { Console.WriteLine("数据库中已检索不到主键为{0} 与 {1} 的的数据!", TEST_SALE_DATE, TEST_SALE_ITEM); } }
/// <summary> /// Executes the command. /// </summary> /// <param name="dbCommand">The current sql command.</param> /// <param name="commandText">The command text to execute.</param> /// <param name="commandType">The command type.</param> /// <param name="connectionString">The connection string to use.</param> /// <param name="values">The collection of sql parameters to include.</param> /// <returns>-1 if command execution failed.</returns> public Int32 ExecuteCommand(ref DbCommand dbCommand, string commandText, CommandType commandType, string connectionString, params DbParameter[] values) { // Initial connection objects. dbCommand = null; Int32 returnValue = -1; OracleClient.OracleConnection orlConnection = null; OracleClient.OracleTransaction orlTransaction = null; try { // Create a new connection. using (orlConnection = new OracleClient.OracleConnection(connectionString)) { // Open the connection. orlConnection.Open(); // Start a new transaction. orlTransaction = orlConnection.BeginTransaction(); // Create the command and assign any parameters. dbCommand = new OracleClient.OracleCommand(DataTypeConversion.GetSqlConversionDataTypeNoContainer( ConnectionContext.ConnectionDataType.OracleDataType, commandText), orlConnection); dbCommand.CommandType = commandType; dbCommand.Transaction = orlTransaction; if (values != null) { foreach (OracleClient.OracleParameter sqlParameter in values) { dbCommand.Parameters.Add(sqlParameter); } } // Execute the command. returnValue = dbCommand.ExecuteNonQuery(); // Commit the transaction. orlTransaction.Commit(); // Close the database connection. orlConnection.Close(); } // Return true. return(returnValue); } catch (Exception ex) { try { // Attempt to roll back the transaction. if (orlTransaction != null) { orlTransaction.Rollback(); } } catch { } // Throw a general exception. throw new Exception(ex.Message, ex.InnerException); } finally { if (orlConnection != null) { orlConnection.Close(); } } }
public OracleTransaction BeginTransaction(OracleConnection conn, IsolationLevel isolationLevel) { if (conn.State == ConnectionState.Closed) { conn.Open(); } return conn.BeginTransaction(isolationLevel); }