public void run() { Exception exp = null; OracleConnection con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString); OracleTransaction txn = null; try { BeginCase("BeginTransaction - connection close"); try { txn = con.BeginTransaction(); } catch (Exception ex) {exp = ex;} Compare(exp.GetType().FullName ,typeof(InvalidOperationException).FullName ); exp=null; } catch(Exception ex){exp = ex;} finally{EndCase(exp); exp = null;} con.Open(); try { BeginCase("BeginTransaction - connection close"); txn = con.BeginTransaction(); Compare(txn == null,false ); } catch(Exception ex){exp = ex;} finally{EndCase(exp); exp = null;} if (con.State == ConnectionState.Open) con.Close(); }
/// <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(System.Data.OracleClient.OracleException E) { tx.Rollback(); throw new Exception(E.Message); } } }
/// <summary></summary> /// 对于 UPDATE、INSERT 和 DELETE 语句,返回值为该命令所影响的行数。对于其他所有类型的语句,返回值为 -1 /// /// UPDATE、INSERT 和 DELETE 语句 public int ExecuteNonQuery(string sql) { using (oracleConnection = this.GetOracleConnection()) { if (oracleConnection == null) { return(-1); } int rv = -1; OracleTransaction oracleTransaction = null; try { if (oracleConnection.State == System.Data.ConnectionState.Closed) { oracleConnection.Open(); } oracleCommand = new OracleCommand(sql, oracleConnection); oracleTransaction = oracleConnection.BeginTransaction(); oracleCommand.Transaction = oracleTransaction; rv = oracleCommand.ExecuteNonQuery(); oracleTransaction.Commit(); } catch (Exception ex) { #if DEBUG System.Diagnostics.Debug.WriteLine(ex.ToString()); #endif oracleTransaction.Rollback(); rv = -1; } return(rv); } }
/// <summary> /// ������̺� /// </summary> /// <param name="heatno"></param> /// <param name="projectid"></param> /// <param name="spoolname"></param> /// <param name="materialname"></param> /// <param name="seqnum"></param> public static void AddHeatNo(string heatno, string projectid, string spoolname, string materialname, int seqnum) { OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr);//���conn���� conn.Open(); string queryString = "SP_AddHeatNo"; OracleTransaction trans = conn.BeginTransaction(); OracleCommand cmd = new OracleCommand(queryString, conn); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.Add("heatno", OracleType.VarChar).Value = heatno; cmd.Parameters.Add("pid", OracleType.VarChar).Value = projectid; cmd.Parameters.Add("spname", OracleType.VarChar).Value = spoolname; cmd.Parameters.Add("material", OracleType.VarChar).Value = materialname; cmd.Parameters.Add("seqnumber", OracleType.Number).Value = seqnum; cmd.Transaction = trans; try { //conn.Open(); cmd.ExecuteNonQuery(); trans.Commit(); } catch (OracleException ee) { trans.Rollback(); MessageBox.Show(ee.Message.ToString(), "����", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { conn.Close(); } }
public static int Save(Applicant applicant) { int applicantId; using (OracleConnection oraDbConn = new OracleConnection( ConnStringFactory.getConnString( ConnStringFactory.ConnStringType.Oracle))) { oraDbConn.Open(); using (OracleTransaction oraTrans = oraDbConn.BeginTransaction(IsolationLevel.ReadCommitted)) { try { applicantId = ApplicantDB.Save(applicant, oraDbConn, oraTrans); foreach (Loan loan in applicant.Loans) { loan.ApplicantId = applicantId; LoanManager.Save(loan, oraDbConn, oraTrans); } applicant.Id = applicantId; oraTrans.Commit(); } catch (OracleException oraEx) { oraTrans.Rollback(); throw oraEx; } } } return applicantId; }
public void run() { Exception exp = null; OracleConnection con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString); con.Open(); OracleTransaction txn = con.BeginTransaction(); OracleCommand cmd = new OracleCommand("Select * From Employees", con); try { BeginCase("check Transaction property - default"); Compare(cmd.Transaction==null , true); } catch(Exception ex){exp = ex;} finally{EndCase(exp); exp = null;} cmd.Transaction = txn; try { BeginCase("check Transaction property"); Compare(cmd.Transaction , txn); } catch(Exception ex){exp = ex;} finally{EndCase(exp); exp = null;} if (con.State == ConnectionState.Open) con.Close(); }
public static void UpdatePersonStatus( string queryString, string project, string drawing, string spool, string person, DateTime dt, string frmtxt) { OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr);//获得conn连接 conn.Open(); //string queryString = "SP_UpdateBlankingStatus"; OracleTransaction trans = conn.BeginTransaction(); OracleCommand cmd = new OracleCommand(queryString, conn); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.Add("project_in", OracleType.VarChar).Value = project; cmd.Parameters.Add("drawing_in", OracleType.VarChar).Value = drawing; cmd.Parameters.Add("spool_in", OracleType.VarChar).Value = spool; cmd.Parameters.Add("person_in", OracleType.VarChar).Value = person; cmd.Parameters.Add("datetime_in", OracleType.DateTime).Value = dt; cmd.Parameters.Add("frmtxt_in", OracleType.VarChar).Value = frmtxt; cmd.Transaction = trans; try { cmd.ExecuteNonQuery(); trans.Commit(); } catch (OracleException ee) { trans.Rollback(); MessageBox.Show(ee.Message.ToString(), "错误", MessageBoxButtons.OK,MessageBoxIcon.Error); } finally { conn.Close(); } }
public int ExecSql(string[] sqls) { using (OracleConnection conn = new OracleConnection(ConnString)) { OracleTransaction st; conn.Open(); st = conn.BeginTransaction(); try { foreach (string sql in sqls) { OracleCommand scc = new OracleCommand(sql, conn); scc.Transaction = st; scc.ExecuteNonQuery(); } st.Commit(); return 0; } catch { st.Rollback(); return -1; } } }
public void run() { OracleConnection con = null; OracleTransaction txn; Exception exp = null; try { BeginCase("OracleTransaction Rollback"); // //prepare data base.PrepareDataForTesting(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString); string Result = ""; con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString); con.Open(); txn = con.BeginTransaction(); OracleCommand cmd = new OracleCommand("Update Employees Set LastName = 'StamLastName' Where EmployeeID = 100", con, txn); cmd.ExecuteNonQuery(); txn.Rollback(); // // cmd = new OracleCommand("Select LastName From Employees Where EmployeeID = 100", con); Result = cmd.ExecuteScalar().ToString(); Compare(Result,"Last100" ); this.Log(Result); } catch(Exception ex){exp = ex;} finally{EndCase(exp); exp = null;} if (con.State == ConnectionState.Open) con.Close(); }
public static void MarkDeletedSpoolRecord(string projectid, string spname, string drawing, string username) { OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr);//获得conn连接 conn.Open(); string queryString = "SP_MarkDeleteSpoolRecord"; OracleTransaction trans = conn.BeginTransaction(); OracleCommand cmd = new OracleCommand(queryString, conn); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.Add("projectid_in", OracleType.VarChar).Value = projectid; cmd.Parameters.Add("spname_in", OracleType.VarChar).Value = spname; cmd.Parameters.Add("drawing_in", OracleType.VarChar).Value = drawing; cmd.Parameters.Add("username_in", OracleType.VarChar).Value = username; cmd.Transaction = trans; try { cmd.ExecuteNonQuery(); trans.Commit(); } catch (OracleException ee) { trans.Rollback(); MessageBox.Show(ee.Message.ToString(), "错误", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { conn.Close(); } }
public static string GetDrawingWithBlock(string projectid, string block) { OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr);//获得conn连接 conn.Open(); string queryString = "SP_GetDrawingWithBlock"; OracleTransaction trans = conn.BeginTransaction(); OracleCommand cmd = new OracleCommand(queryString, conn); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.Add("drawing_out", OracleType.VarChar,100); cmd.Parameters["drawing_out"].Direction = System.Data.ParameterDirection.Output; cmd.Parameters.Add("projectid_in", OracleType.VarChar).Value = projectid; cmd.Parameters.Add("block_in", OracleType.VarChar).Value = block; cmd.Transaction = trans; try { cmd.ExecuteNonQuery(); trans.Commit(); } catch (OracleException ee) { trans.Rollback(); MessageBox.Show(ee.Message.ToString(), "错误", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { conn.Close(); } return cmd.Parameters["drawing_out"].Value.ToString(); }
public static void AddTrayORClass(string queryString, string project, string drawing, string spool,string trayorclass) { OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr);//获得conn连接 conn.Open(); OracleTransaction trans = conn.BeginTransaction(); OracleCommand cmd = new OracleCommand(queryString, conn); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.Add("project_in", OracleType.VarChar).Value = project; cmd.Parameters.Add("drawing_in", OracleType.VarChar).Value = drawing; cmd.Parameters.Add("spool_in", OracleType.VarChar).Value = spool; cmd.Parameters.Add("trayorclass_in", OracleType.VarChar).Value = trayorclass; cmd.Transaction = trans; try { cmd.ExecuteNonQuery(); trans.Commit(); } catch (OracleException ee) { trans.Rollback(); MessageBox.Show(ee.Message.ToString(), "错误", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { conn.Close(); } }
private void btnConfirm_Click(object sender, EventArgs e) { if (selection.SelectedCount == 0) { MessageBox.Show("没有选择单据"); } else { using (OracleConnection connection = new OracleConnection(StrCon)) { connection.Open(); OracleCommand command = connection.CreateCommand(); OracleTransaction transaction; transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted); command.Transaction = transaction; try { for (int i = 0; i < selection.SelectedCount; ++i) { int RowIndex = selection.GetSelectedRowIndex(i); int RowHandle = gridView1.GetRowHandle(RowIndex); string strXSJSDID = gridView1.GetRowCellValue(RowHandle, colXSJSDID).ToString(); command.CommandText = "insert into Temp_Save_Id (tempid,id) Values (TEMP_SAVE_ID_SEQ.nextval,'" + strXSJSDID + "')"; command.ExecuteNonQuery(); } selection.ClearSelection(); command.CommandType = CommandType.StoredProcedure; command.CommandText = "JC_C_XSTSD_XD"; command.Parameters.Add("LS_XSTSDid", OracleType.VarChar).Value = this.btnConfirm.Tag.ToString(); command.Parameters.Add("DescErr", OracleType.VarChar, 255).Direction = ParameterDirection.Output; command.Parameters.Add("Message", OracleType.VarChar, 255).Direction = ParameterDirection.Output; command.ExecuteNonQuery(); transaction.Commit(); string mess = command.Parameters["Message"].Value.ToString(); string alarm = command.Parameters["DescErr"].Value.ToString(); MessageBox.Show(mess + alarm); } catch (OracleException ex) { transaction.Rollback(); MessageBox.Show(ex.Message); } finally { connection.Close(); this.DialogResult = DialogResult.OK; this.Close(); } } } }
public void get_cust_id_by_acct_id(int acct_id, out int p_cust_id, out int rc, out string msg) { rc = -1; msg = null; p_cust_id = -1; string connStr = getConnectionString(); try { log.Info(string.Format("get_cust_id_by_acct_id acct_id={0}", acct_id)); using (OracleConnection objConn = new OracleConnection(connStr)) { objConn.Open(); OracleTransaction transaction; transaction = objConn.BeginTransaction(); OracleCommand objCmd = new OracleCommand(schemaPrefixSap + "crmws_functions.get_cust_id_by_acct_id", objConn); objCmd.CommandType = CommandType.StoredProcedure; try { objCmd.Transaction = transaction; objCmd.Parameters.Clear(); objCmd.Parameters.Add("p_acct_id", OracleType.Number).Value = dbNlv(acct_id); objCmd.Parameters.Add("p_cust_id", OracleType.Number).Direction = ParameterDirection.Output; objCmd.Parameters.Add("p_rc", OracleType.Number).Direction = ParameterDirection.Output; objCmd.Parameters.Add("p_msg", OracleType.VarChar, 2048).Direction = ParameterDirection.Output; objCmd.ExecuteNonQuery(); rc = int.Parse(objCmd.Parameters["p_rc"].Value.ToString()); msg = objCmd.Parameters["p_msg"].Value.ToString(); p_cust_id = int.Parse(objCmd.Parameters["p_cust_id"].Value.ToString()); transaction.Commit(); } catch (Exception e) { transaction.Rollback(); rc = -3; log.Error("get_cust_id_by_acct_id", e); } finally { objConn.Close(); } } } catch (Exception e) { log.Error("get_cust_id_by_acct_id", e); rc = -1; } }
//Creating variables using parameters and assigning them to textboxes to insert into the customer table public void add_Customer(Customer_Entity_Class customer) { /* * Creating a connection to connect to the database calling Orc class that returns the connection string */ OracleConnection connection = new OracleConnection(OrcConnection.connection()); OracleCommand cmd = new OracleCommand(); try { connection.Open(); OracleTransaction transaction = connection.BeginTransaction(); cmd.Transaction = transaction; cmd.CommandType = CommandType.Text; cmd.CommandText = " INSERT INTO Customers Values(customer_seq.NEXTVAL,:forename,:surname,:address,:town,:county,:phoneNo,:custType)"; OracleParameter forename = new OracleParameter(":forename", OracleType.VarChar); forename.Value = customer.Forename; cmd.Parameters.Add(forename); OracleParameter surname = new OracleParameter(":surname", OracleType.VarChar); surname.Value = customer.Surname; cmd.Parameters.Add(surname); OracleParameter address = new OracleParameter(":address", OracleType.VarChar); address.Value = customer.Address; cmd.Parameters.Add(address); OracleParameter town = new OracleParameter(":town", OracleType.VarChar); town.Value = customer.Town; cmd.Parameters.Add(town); OracleParameter county = new OracleParameter(":county", OracleType.VarChar); county.Value = customer.County; cmd.Parameters.Add(county); OracleParameter phone_no = new OracleParameter(":phoneNo", OracleType.Number); phone_no.Value = customer.Phone_No; cmd.Parameters.Add(phone_no); OracleParameter cust_Type = new OracleParameter(":custType", OracleType.VarChar); cust_Type.Value = customer.Cust_Type; cmd.Parameters.Add(cust_Type); cmd.Connection = connection; cmd.ExecuteNonQuery(); transaction.Commit(); connection.Close(); MessageBox.Show("Insert complete successfully"); } catch (Exception) { MessageBox.Show("Insert did not complete Succesfully", "ERROR", MessageBoxButtons.OKCancel, MessageBoxIcon.Error); } }
public void GetAccountByCert( string CertValue, string CertType, out string AcctId, out int rc, out string msg ) { rc = -1; msg = null; AcctId = ""; try { log.Info(string.Format( "GetAccountByCert CertValue={0} CertType={1}", CertValue, CertType)); using (OracleConnection objConn = new OracleConnection(connStr)) { objConn.Open(); OracleTransaction transaction; transaction = objConn.BeginTransaction(); OracleCommand objCmd = new OracleCommand(schemaPrefixSap + "crmws_functions.get_account_by_cert", objConn); objCmd.CommandType = CommandType.StoredProcedure; objCmd.Transaction = transaction; objCmd.Parameters.Clear(); objCmd.Parameters.Add("p_cert_value", OracleType.VarChar).Value = dbNlv(CertValue); objCmd.Parameters.Add("p_cert_type", OracleType.VarChar).Value = dbNlv(CertType); objCmd.Parameters.Add("p_acct_id", OracleType.VarChar, 200).Direction = ParameterDirection.Output; objCmd.Parameters.Add("p_rc", OracleType.Number).Direction = ParameterDirection.Output; objCmd.Parameters.Add("p_msg", OracleType.VarChar, 500).Direction = ParameterDirection.Output; objCmd.ExecuteNonQuery(); rc = int.Parse(objCmd.Parameters["p_rc"].Value.ToString()); msg = objCmd.Parameters["p_msg"].Value.ToString(); AcctId = objCmd.Parameters["p_acct_id"].Value.ToString(); transaction.Commit(); objConn.Close(); } } catch (Exception e) { rc = -1; msg = e.Message; log.Error("GetAccountByCert exception", e); } log.Info(string.Format( "GetAccountByCert finished: rc={0}, msg={1}, AcctId={2}", rc, msg, AcctId)); }
private void btnSave_Click(object sender, EventArgs e) { if (string.IsNullOrEmpty(txtRoleName.Text.Trim())) { MessageBox.Show("请输入角色名称"); txtRoleName.Focus(); } else { using (OracleConnection connection = new OracleConnection(StrCon)) { connection.Open(); OracleCommand cmd = connection.CreateCommand(); OracleTransaction transaction; transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted); cmd.Transaction = transaction; try { cmd.CommandText = "insert into sys_role (role_id,role_name,super_id,description) values (seq_sys_role_role_id.nextval,'" + txtRoleName.Text.Trim() + "','0','" + txtRoleDirs.Text.Trim() + "')"; cmd.ExecuteNonQuery(); for (int i = 0; i < treeView2.Nodes.Count;i++ ) { if (treeView2.Nodes[i].Checked == true) { cmd.CommandText = "insert into SYS_ROLE_MODULE (ID,role_id,module_id) values (seq_SYS_ROLE_MODULE_id.nextval,seq_sys_role_role_id.currval,'" + treeView2.Nodes[i].Tag.ToString() + "')"; cmd.ExecuteNonQuery(); for (int j = 0; j < treeView2.Nodes[i].Nodes.Count;j++ ) { if (treeView2.Nodes[i].Nodes[j].Checked == true) { cmd.CommandText = "insert into SYS_ROLE_MODULE_ACTION t" + " (t.id, t.roleid, t.modeleid, t.actioncode)" + "values" + " (seq_action.nextval,seq_sys_role_role_id.currval, '" + treeView2.Nodes[i].Tag.ToString() + "', '" + treeView2.Nodes[i].Nodes[j].Tag.ToString() + "')"; cmd.ExecuteNonQuery(); } } } } transaction.Commit(); } catch (Exception ex) { transaction.Rollback(); MessageBox.Show(ex.Message); } finally { connection.Close(); this.DialogResult = DialogResult.OK; this.Close(); } } } }
/// <summary> ///保存一个文件 /// </summary> private void SaveSingle() { if (!ISBool) { MessageBox.Show("要修改的文件跟加载的文件不一致,请重新加载文件"); return; } if (FileList == null) { MessageBox.Show("请选择要保存的文件"); return; } if (!ValidState(FileList)) { return; } // OracleTransaction trans = Oracon.BeginTransaction(System.Data.IsolationLevel.ReadCommitted); mgr.SetTrans(trans); string strSql = "select seq_ftp_seq.nextval from dual"; foreach (FtpFile obj in FileList) { if (this.myEditType == EditType.Add) { obj.PrimaryID = mgr.ReturnOne(Oracon, strSql); } obj.LocalDirectory = this.txtFilePath.Text; if (SaveInfo(obj, trans) <= 0) { MessageBox.Show("保存失败"); return; } SaveHandle(obj); } trans.Commit(); if (cbContinue.Checked) { this.Clear(); // string strSql = "select seq_ftp_seq.nextval from dual"; this.lbID.Text = mgr.ReturnOne(Oracon, strSql); } else { this.Close(); } }
public void SetUp() { Exception exp = null; BeginCase("Setup"); try { con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString); con.Open(); tr = con.BeginTransaction(); cmd = new OracleCommand("", con, tr); dbServerType = ConnectedDataProvider.GetDbType(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString); Assert.AreEqual("Setup", "Setup"); } catch(Exception ex) {exp = ex;} finally {EndCase(exp); exp = null;} }
public void run() { Exception exp = null; OracleConnection con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString); con.Open(); /********************************************************* * OLEDB Provider for SQL Server does not allow nested transactions * http://support.microsoft.com/kb/177138/EN-US/ * http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q316872& */ if ((ConnectedDataProvider.GetDbType(con) == DataBaseServer.SQLServer) || (ConnectedDataProvider.GetDbType(con) == DataBaseServer.Oracle) || (ConnectedDataProvider.GetDbType(con) == DataBaseServer.PostgreSQL) || (ConnectedDataProvider.GetDbType(con) == DataBaseServer.DB2)) { Log(string.Format("Test skipped, nested transactions are not supported in {0}", ConnectedDataProvider.GetDbType(con))); return; } // How To Implement Nested Transactions with Oracle // http://support.microsoft.com/kb/187289/EN-US/ OracleTransaction txnOuter = null; OracleTransaction txnInner = null; try { BeginCase("Check Outer Transaction Isoloation Level"); txnOuter = con.BeginTransaction(); } catch(Exception ex){exp = ex;} finally{EndCase(exp); exp = null;} try { BeginCase("Check Inner Transaction Isoloation Level"); Compare(txnOuter.IsolationLevel,IsolationLevel.RepeatableRead); } catch(Exception ex){exp = ex;} finally{EndCase(exp); exp = null;} if (con.State == ConnectionState.Open) con.Close(); }
private void btnSaveContinue_Click(object sender, EventArgs e) { if (tbName.Text == "") { MessageBox.Show("请输入员工姓名"); tbName.Focus(); } else if (tbStaffNum.Text == "") { MessageBox.Show("请输入员工编号"); tbStaffNum.Focus(); } else { using (OracleConnection connection = new OracleConnection(FrmLogin.strDataCent)) { connection.Open(); OracleCommand command = connection.CreateCommand(); OracleTransaction transaction = connection.BeginTransaction(); command.Connection = connection; command.Transaction = transaction; try { if (this.Text == "增加员工") { vAddStaff(command, transaction); btnClear_Click(sender, e); } else if (this.Text == "修改员工") { vUpdateStaff(command, transaction); btnClear_Click(sender, e); } } catch (Exception exception) { transaction.Rollback(); MessageBox.Show(exception.ToString()); } finally { connection.Close(); } } } }
public void IsolationLevelChaos() { Exception exp = null; MonoTests.System.Data.Utils.DataBaseServer dbServer = ConnectedDataProvider.GetDbType(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString); OracleConnection con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString); OracleTransaction txn = null; //supported only in SQLServer if (dbServer == MonoTests.System.Data.Utils.DataBaseServer.SQLServer) { try { BeginCase("IsolationLevel = Chaos"); con.Open(); txn=con.BeginTransaction(IsolationLevel.Chaos); Compare(txn.IsolationLevel,IsolationLevel.Chaos); } catch(Exception ex){exp = ex;} finally{EndCase(exp); exp = null; if (con.State == ConnectionState.Open) con.Close();} } }
public void SetUp() { Exception exp = null; BeginCase("Setup"); try { // prepare data base.PrepareDataForTesting(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString); con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString); con.Open(); // transaction use was add for PostgreSQL tr = con.BeginTransaction(); cmd = new OracleCommand("", con, tr); Compare("Setup" ,"Setup"); } catch(Exception ex) {exp = ex;} finally {EndCase(exp); exp = null;} }
/// <summary> /// 分段施工计划 /// </summary> /// <param name="projectid"></param> /// <param name="block"></param> /// <param name="drawing"></param> /// <param name="plan_pre_start"></param> /// <param name="plan_pre_finish"></param> /// <param name="act_pre_start"></param> /// <param name="act_pre_finish"></param> /// <param name="plan_ass_start"></param> /// <param name="plan_ass_finish"></param> /// <param name="act_ass_start"></param> /// <param name="act_ass_finish"></param> /// <param name="username"></param> /// <param name="remark"></param> public static void BlockConstructionTab(string projectid, string block,string drawing,DateTime plan_pre_start,DateTime plan_pre_finish, DateTime act_pre_start,DateTime act_pre_finish, DateTime plan_ass_start, DateTime plan_ass_finish, DateTime act_ass_start, DateTime act_ass_finish,string username,string remark) { OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr);//获得conn连接 conn.Open(); string queryString = "SP_BlockConstructionTab"; OracleTransaction trans = conn.BeginTransaction(); OracleCommand cmd = new OracleCommand(queryString, conn); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.Add("projectid_in",OracleType.VarChar).Value = projectid; cmd.Parameters.Add("block_in", OracleType.VarChar).Value = block; cmd.Parameters.Add("drawing_in", OracleType.VarChar).Value = drawing; cmd.Parameters.Add("plan_pre_start_in", OracleType.DateTime).Value = plan_pre_start; cmd.Parameters.Add("plan_pre_finish_in", OracleType.DateTime).Value = plan_pre_finish; cmd.Parameters.Add("act_pre_start_in", OracleType.DateTime).Value = act_pre_start; cmd.Parameters.Add("act_pre_finish_in",OracleType.DateTime).Value = act_pre_finish; cmd.Parameters.Add("plan_ass_start_in",OracleType.DateTime).Value = plan_ass_start; cmd.Parameters.Add("plan_ass_finish_in",OracleType.DateTime).Value = plan_ass_finish; cmd.Parameters.Add("act_ass_start_in",OracleType.DateTime).Value = act_ass_start; cmd.Parameters.Add("act_ass_finish_in",OracleType.DateTime).Value = act_ass_finish; cmd.Parameters.Add("username_in",OracleType.VarChar).Value = username; cmd.Parameters.Add("remark_in",OracleType.VarChar).Value = remark; cmd.Transaction = trans; try { cmd.ExecuteNonQuery(); trans.Commit(); } catch (OracleException ee) { trans.Rollback(); MessageBox.Show(ee.Message.ToString(), "错误", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { conn.Close(); } }
public void run() { Exception exp = null; OracleConnection con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString); con.Open(); OracleTransaction txn = con.BeginTransaction(); try { BeginCase("check connection"); Compare(txn.Connection,con); } catch(Exception ex){exp = ex;} finally{EndCase(exp); exp = null;} //check exception - using a command with the same connection as the transaction OracleCommand cmd = new OracleCommand("Select * from Employees",con); //Execute requires the command to have a transaction object when the connection assigned to the command is in a pending local transaction. //The Transaction property of the command has not been initialized. try { BeginCase("Command that uses the transaction connection - exception"); try { cmd.ExecuteNonQuery(); } catch (Exception ex){exp=ex;} Compare(exp.GetType().FullName ,typeof(InvalidOperationException).FullName); exp = null; } catch(Exception ex) {exp = ex;} finally {EndCase(exp); exp = null;} if (con.State == ConnectionState.Open) con.Close(); }
/* * Setting up parameters and inserting values into the car_class if a new rate needs to be added * to the car class table */ public void addCar_Rate(Car_Rate_Entity_Class rate_Class) { /* * Creating a connection to connect to the database calling Orc class that returns the connection string */ OracleConnection connection = new OracleConnection(OrcConnection.connection()); OracleCommand cmd = new OracleCommand(); try { connection.Open(); OracleTransaction transaction = connection.BeginTransaction(); cmd.Transaction = transaction; cmd.CommandType = CommandType.Text; cmd.CommandText = "INSERT INTO Car_Class VALUES(car_rate_seq.NEXTVAL,:description,:car_rate)"; var description = new OracleParameter(":description", OracleType.VarChar); description.Value = rate_Class.Description; cmd.Parameters.Add(description); var car_rate = new OracleParameter(":car_rate", OracleType.Number); car_rate.Value = rate_Class.Car_rate; cmd.Parameters.Add(car_rate); cmd.Connection = connection; cmd.ExecuteNonQuery(); transaction.Commit(); connection.Close(); MessageBox.Show("Insert complete successfully"); } catch (Exception) { MessageBox.Show("Insert Did not complete successfully"); } }
public static bool BuildBatchPackage(string str1, string str2) { using (OracleConnection connection = new OracleConnection(SQLHelper.ConnectionString)) { connection.Open(); OracleCommand command = connection.CreateCommand(); OracleTransaction transaction; // Start a local transaction transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted); // Assign transaction object for a pending local transaction command.Transaction = transaction; try { command.CommandText = new StringBuilder() .Append("INSERT INTO FX_PACKAGECHECK(PACKAGEID, OPERATOR, MODIFYTIME, DELTF, STATE, NOREG, PACKAGENUM) ") .Append("VALUES (" + str1 + ")").ToString(); command.ExecuteNonQuery(); command.CommandText = new StringBuilder() .Append("INSERT INTO FX_PACKAGECHECKLIST (ID, PACKAGEID, KWID, BOOKID, SCALE, ORNAMENT) ") .Append("VALUES ('" + generate() + "', " + str2 + ")").ToString(); command.ExecuteNonQuery(); transaction.Commit(); return true; } catch (Exception ex) { transaction.Rollback(); logger.ErrorException("保存包件信息引发异常", ex); return false; } } }
private void btnDeleteCase_Click(object sender, EventArgs e) { bool fgcheckJD = false; if (this.txtTSDH.Text.ToString() == "") { fgcheckJD = false; MessageBox.Show("您还没有保存结算单,请保存后再进行选单减单操作"); } else if (selection.SelectedCount == 0) { fgcheckJD = false; MessageBox.Show("选择要减去的单据"); } else { fgcheckJD = true; } if (fgcheckJD == true) { using (OracleConnection connection = new OracleConnection(StrCon)) { OracleCommand cmd = connection.CreateCommand(); OracleTransaction transaction; connection.Open(); transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted); cmd.Transaction = transaction; try { for (int i = 0; i < selection.SelectedCount; ++i) { int RowIndex = selection.GetSelectedRowIndex(i); int RowHandle = gridView1.GetRowHandle(RowIndex); string strXSTSDMXID = gridView1.GetRowCellDisplayText(RowHandle, colXSTSDMXID).ToString(); cmd.CommandText = "INSERT INTO TEMP_SAVE_ID (TEMPID, ID) Values (temp_save_id_seq.nextval, '" + strXSTSDMXID + "')"; cmd.ExecuteNonQuery(); } selection.ClearSelection(); SelectCountClear(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "Jt_c_XSTSD_JD"; cmd.Parameters.Add("LS_XSTSDid", OracleType.VarChar).Value = this.txtTSDH.Tag.ToString().Trim(); cmd.Parameters.Add("Descerr", OracleType.VarChar, 255).Direction = ParameterDirection.Output; cmd.Parameters.Add("Message", OracleType.VarChar, 255).Direction = ParameterDirection.Output; cmd.ExecuteNonQuery(); transaction.Commit(); unitOfWork1.DropIdentityMap(); xpServerCollectionSource1.Reload(); MessageBox.Show(cmd.Parameters["Message"].Value.ToString()); } catch (Exception ex) { transaction.Rollback(); MessageBox.Show(ex.Message); } finally { connection.Close(); } } } }
protected override DbTransaction BeginDbTransaction(IsolationLevel isolationLevel) { return(new OracleTransaction(con.BeginTransaction(), this)); }
private void btnConfirm_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e) { string strXSHZDID = ""; if (selection.SelectedCount == 0) { MessageBox.Show("请先选择需要确认的记录"); } else { for (int i = 0; i < selection.SelectedCount; ++i) { int RowIndex = selection.GetSelectedRowIndex(i); int RowHandle = gridView1.GetRowHandle(RowIndex); string strZT = gridView1.GetRowCellValue(RowHandle, colZT).ToString(); if (strZT == "02") { string strTemp = gridView1.GetRowCellDisplayText(RowHandle, colXSHZDID); strXSHZDID += "\'" + strTemp + "\', "; } else { strXSHZDID = ""; MessageBox.Show("所选记录已被确认,请重新选择!"); break; } } selection.ClearSelection(); vClearSelectSummary(); if (!String.IsNullOrEmpty(strXSHZDID)) { using (OracleConnection connection = new OracleConnection(FrmLogin.strDataCent)) { connection.Open(); OracleCommand command = connection.CreateCommand(); OracleTransaction transaction = connection.BeginTransaction(); command.Connection = connection; command.Transaction = transaction; int index = strXSHZDID.LastIndexOf("'"); strXSHZDID = strXSHZDID.Substring(0, index + 1).Trim(); try { string strSQL = "update jc_c_xshzd set zt = '15', czrq = sysdate where XSHZDID in (" + strXSHZDID + ")"; command.CommandText = strSQL; command.ExecuteNonQuery(); transaction.Commit(); unitOfWork1.DropIdentityMap(); xpServerCollectionSource1.Reload(); MessageBox.Show("所选记录已确认成功"); } catch (Exception exception) { transaction.Rollback(); MessageBox.Show(exception.ToString()); } finally { connection.Close(); } } } } }
/// <summary> /// 开始事务 /// </summary> /// <param name="conn">OracleConnection连接对象</param> public static void BeginTransaction(OracleConnection connObjection) { if (Transaction == null||Transaction.Connection != connObjection) { Transaction = connObjection.BeginTransaction(IsolationLevel.ReadCommitted); } }
/// <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); } else { if (command.Parameters.Count > 0) command.Parameters.Clear(); } return; }
/// <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 e) { transaction.Rollback(); string stringlist = ""; foreach (var str in SQLStringList) { stringlist += str.ToString() + "\r\n"; } LogHelper.WriteLog("MsOracle.ExecuteSQLTransaction", stringlist, e); throw new System.Exception(e.Message); } finally { connection.Close(); } }
//public ActionResult Search() //{ // return View("Index"); //} //[HttpPost] public ActionResult Search(string BondLicenseNo) { if (!(System.Web.HttpContext.Current.User.IsInRole("Bonder"))) { var bonderslno = (from b in db.BONDERs where b.BONDLICENSENO == BondLicenseNo select b.BONDERSLNO).SingleOrDefault(); System.Data.OracleClient.OracleCommand cmd = new System.Data.OracleClient.OracleCommand(); System.Data.OracleClient.OracleConnection conn = ConnectBMS.Connection(); cmd.Connection = conn; System.Data.OracleClient.OracleTransaction bmsTransaction = conn.BeginTransaction(); cmd.Transaction = bmsTransaction; cmd.CommandText = "select STATUS,BSDATE,SUBMITTEDBYNM,REMARKS from BONDSTATUS where BONDERSLNO=:BONDERSLNO "; cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("BONDERSLNO", bonderslno); System.Data.OracleClient.OracleDataReader drBondStatus = cmd.ExecuteReader(); if (drBondStatus.HasRows) { drBondStatus.Read(); ViewBag.STATUS = drBondStatus.GetValue(0); ViewBag.BSDATE = drBondStatus.GetValue(1); ViewBag.SUBMITTEDBYNM = drBondStatus.GetValue(2); ViewBag.REMARKS = drBondStatus.GetValue(3); } try { return(View(getRenewalInfo(bonderslno))); } catch (DbEntityValidationException dbEx) { foreach (var validationErrors in dbEx.EntityValidationErrors) { foreach (var validationError in validationErrors.ValidationErrors) { System.Console.WriteLine("Property: {0} Error: {1}", validationError.PropertyName, validationError.ErrorMessage); } } ViewBag.Message = "Insertion Failed"; return(View("RenewalForm")); } return(RedirectToAction("Index")); } else { USERPERMISSION permission = session.getStoredUserPermission(); var bonderName = permission.BONDER.BONDERNAME; var bonderslno = (from b in db.BONDERs where b.BONDLICENSENO == BondLicenseNo && b.BONDERNAME == bonderName select b.BONDERSLNO).SingleOrDefault(); System.Data.OracleClient.OracleCommand cmd = new System.Data.OracleClient.OracleCommand(); System.Data.OracleClient.OracleConnection conn = ConnectBMS.Connection(); cmd.Connection = conn; System.Data.OracleClient.OracleTransaction bmsTransaction = conn.BeginTransaction(); cmd.Transaction = bmsTransaction; cmd.CommandText = "select STATUS,BSDATE,SUBMITTEDBYNM,REMARKS from BONDSTATUS where BONDERSLNO=:BONDERSLNO"; cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("BONDERSLNO", bonderslno); System.Data.OracleClient.OracleDataReader drBondStatus = cmd.ExecuteReader(); if (drBondStatus.HasRows) { drBondStatus.Read(); ViewBag.STATUS = drBondStatus.GetValue(0); ViewBag.BSDATE = drBondStatus.GetValue(1); ViewBag.SUBMITTEDBYNM = drBondStatus.GetValue(2); ViewBag.REMARKS = drBondStatus.GetValue(3); } try { return(View(getRenewalInfo(bonderslno))); } catch (DbEntityValidationException dbEx) { foreach (var validationErrors in dbEx.EntityValidationErrors) { foreach (var validationError in validationErrors.ValidationErrors) { System.Console.WriteLine("Property: {0} Error: {1}", validationError.PropertyName, validationError.ErrorMessage); } } ViewBag.Message = "Insertion Failed"; return(View("RenewalForm")); } return(RedirectToAction("Index")); } }
public ActionResult Renewinfo(RenewalViewModel rvm, string[] doc, string[] rgno, string[] issuedate, string[] expdate, HttpPostedFileBase[] files) { System.Data.OracleClient.OracleCommand cmd = new System.Data.OracleClient.OracleCommand(); System.Data.OracleClient.OracleConnection conn = ConnectBMS.Connection(); cmd.Connection = conn; System.Data.OracleClient.OracleTransaction bmsTransaction = conn.BeginTransaction(); cmd.Transaction = bmsTransaction; cmd.CommandText = "update BONDSTATUS set STATUS=:STATUS,BSDATE=:BSDATE,SUBMITTEDBYNM=:SUBMITTEDBYNM,REMARKS=:REMARKS" + " where BONDERSLNO=:BONDERSLNO"; cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("STATUS", Request["STATUS"]); if (!String.IsNullOrEmpty(Request["ApplicationSubmissionDate"])) { cmd.Parameters.Add(new OracleParameter(":BSDATE", OracleType.DateTime)).Value = Request["ApplicationSubmissionDate"]; } else { cmd.Parameters.Add(new OracleParameter(":BSDATE", OracleType.DateTime)).Value = DBNull.Value; } cmd.Parameters.AddWithValue("SUBMITTEDBYNM", Request["SubmittedBy"]); cmd.Parameters.AddWithValue("REMARKS", Request["Remarks"]); cmd.Parameters.AddWithValue("BONDERSLNO", BondInfo.bondSlNoToRenew); try { try { bmsTransaction = conn.BeginTransaction(); } catch { } cmd.Transaction = bmsTransaction; cmd.ExecuteNonQuery(); bmsTransaction.Commit(); } catch { bmsTransaction.Rollback(); } if (Request["STATUS"] == "Cm") { cmd.CommandText = "update BONDAPPLICATIONPROGRESS set READYFORAPP=:READYFORAPP where BONDERSLNO=:BONDERSLNO and BSNO=:BSNO"; cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("BONDERSLNO", BondInfo.bondSlNoToRenew); cmd.Parameters.AddWithValue("BSNO", BondInfo.BSNoToRenew); cmd.Parameters.AddWithValue("READYFORAPP", "Y"); try { try { bmsTransaction = conn.BeginTransaction(); } catch { } cmd.Transaction = bmsTransaction; cmd.ExecuteNonQuery(); bmsTransaction.Commit(); } catch { bmsTransaction.Rollback(); } } int p = 1; if (doc != null) { try { for (int i = 0; i < doc.Length; i++) { if (doc[i] != null && doc[i] != "") { DOCUMENTATTACHMENT D = new DOCUMENTATTACHMENT(); var path = ""; D.ATTCHSLNO = (Int16)p; //foreach (var outitem in rvm) //{ // foreach (var item in outitem.Bonder) // { D.BONDERSLNO = BondInfo.bondSlNoToRenew; //} //foreach (var item in outitem.Bondstatus) //{ D.BSNO = BondInfo.BSNoToRenew; // } //} if (files[i] != null) { var filename = Path.GetFileName(files[i].FileName); path = Path.Combine(Server.MapPath(Url.Content("~/Uploads/")), filename); files[i].SaveAs(path); D.ATTACHFILENM = path; } D.DOCHEADINGNAME = doc[i]; D.RGATTCHNAME = rgno[i]; //D.ISSUEDATE = Convert.ToDateTime(issuedate[i], CultureInfo.CurrentCulture); //D.EXPDATE = Convert.ToDateTime(expdate[i], CultureInfo.CurrentCulture); if (!String.IsNullOrEmpty(issuedate[i])) { D.ISSUEDATE = DateTime.ParseExact(issuedate[i], "dd/MM/yyyy", null); } if (!String.IsNullOrEmpty(expdate[i])) { D.EXPDATE = DateTime.ParseExact(expdate[i], "dd/MM/yyyy", null); } db.DOCUMENTATTACHMENTs.Add(D); p++; db.SaveChanges(); } } ViewBag.Message = "Successfully Inserted"; //return View(renewalviewmodel); //return Search(BIMS.CommonAppSet.BondInfo.bondLicenseNoToRenew); //return View("Search",rvm); //return View("RenewalForm"); } catch (DbEntityValidationException dbEx) { foreach (var validationErrors in dbEx.EntityValidationErrors) { foreach (var validationError in validationErrors.ValidationErrors) { System.Console.WriteLine("Property: {0} Error: {1}", validationError.PropertyName, validationError.ErrorMessage); } } ViewBag.Message = "Insertion Failed"; return(View("RenewalForm")); } } cmd.CommandText = "select STATUS,BSDATE,SUBMITTEDBYNM,REMARKS from BONDSTATUS where BONDERSLNO=:BONDERSLNO"; cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("BONDERSLNO", BondInfo.bondSlNoToRenew); System.Data.OracleClient.OracleDataReader drBondStatus = cmd.ExecuteReader(); if (drBondStatus.HasRows) { drBondStatus.Read(); ViewBag.STATUS = drBondStatus.GetValue(0); ViewBag.BSDATE = drBondStatus.GetValue(1); ViewBag.SUBMITTEDBYNM = drBondStatus.GetValue(2); ViewBag.REMARKS = drBondStatus.GetValue(3); } return(View("Search", getRenewalInfo(BondInfo.bondSlNoToRenew))); //return View("RenewalForm"); }