/// <summary> /// 执行一个OracleCommand(返回一个结果OracleDataReader) /// </summary> /// <param name="connection">有效的 OracleConnection</param> /// <param name="transaction">有效的 OracleTransaction, or 'null'</param> /// <param name="commandType">命令类型 (stored procedure, text, etc.)</param> /// <param name="commandText">存储过程名称或 PL/SQL</param> /// <param name="commandParameters">以一个数组的形式返回OracleParameters </param> /// <param name="connectionOwnership">indicates whether the connection parameter was provided by the caller, or created by OracleProvider</param> /// <returns></returns> private static OracleDataReader ExecuteReader(OracleConnection connection, OracleTransaction transaction, CommandType commandType, string commandText, OracleParameter[] commandParameters, OracleConnectionOwnership connectionOwnership) { OracleCommand cmd = new OracleCommand(); PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters); OracleDataReader dr = null; try { if (connectionOwnership == OracleConnectionOwnership.External) { dr = cmd.ExecuteReader(); } else { // dr = cmd.ExecuteReader((CommandBehavior)((int)CommandBehavior.CloseConnection)); dr = cmd.ExecuteReader(); } if (transaction != null) { transaction.Commit(); } return (OracleDataReader)dr; } catch { if (transaction != null) { transaction.Rollback(); } throw; } }
/// <summary> ///执行一个命令,没有返回数据集,只返回影响记录数 /// </summary> /// <remarks> /// 例如: /// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", new OracleParameter("@prodid", 24)); /// </remarks> /// <param name="transaction">有效的 OracleTransaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">存储过程名称或 PL/SQL</param> /// <param name="commandParameters">OracleParameter[]</param> /// <returns>影响记录数</returns> public static int ExecuteNonQuery(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters) { OracleCommand cmd = new OracleCommand(); PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters); try { int returnInt = cmd.ExecuteNonQuery(); if (transaction != null) { transaction.Commit(); } return returnInt; } catch { if (transaction != null) { transaction.Rollback(); } return 0; } }
/// <summary> /// 回滚事务 /// </summary> public void RollbackTransaction(OracleConnection connObjection) { if (Transaction != null && Transaction.Connection == connObjection) { try { Transaction.Rollback(); Close(connObjection); Transaction = null; if (connObjection != null) { if (connObjection.State == ConnectionState.Open) { Close(connObjection); } } } catch (OracleException e) { Transaction.Rollback(); Close(connObjection); Transaction = null; LogHelper.WriteLog("MsOracle.RollbackTransaction(OracleConnection connObjection)", null, e); throw new System.Exception(e.Message); } } }
/// <summary> /// 执行一个OracleCommand命令并返回结果集 /// </summary> /// <remarks> /// 例如: /// DataTable ds = ExecuteDataTable(trans, CommandType.StoredProcedure, "GetOrders", new OracleParameter("@prodid", 24)); /// </remarks> /// <param name="transaction">有效的 OracleTransaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">存储过程名称或 PL/SQL</param> /// <param name="commandParameters">params OracleParameter[]</param> /// <returns>返回DataSet</returns> public static DataTable ExecuteTable(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters) { OracleCommand cmd = new OracleCommand(); PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters); OracleDataAdapter da; DataSet ds = new DataSet(); try { da = new OracleDataAdapter(cmd); da.Fill(ds); if (transaction != null) { transaction.Commit(); } } catch { if (transaction != null) { // Rollback the transaction transaction.Rollback(); } } //return the dataset return ds.Tables[0]; }
/// <summary> /// 执行一个OracleCommand命令并返回结果集;执行完成没有关闭OracleConnection连接,需要手动关闭 /// </summary> /// <remarks> /// 例如: /// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders", new OracleParameter("@prodid", 24)); /// </remarks> /// <param name="transaction">有效的 OracleTransaction</param> /// <param name="commandType">命令类型:SQL语句或存储过程</param> /// <param name="commandText">存储过程名称或 PL/SQL</param> /// <param name="commandParameters">params OracleParameter[]</param> /// <returns>返回DataSet</returns> public static DataSet ExecuteDataset(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters) { OracleCommand cmd = new OracleCommand(); PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters); OracleDataAdapter da; DataSet ds = new DataSet(); try { da = new OracleDataAdapter(cmd); da.Fill(ds); if (transaction != null) { transaction.Commit(); } } catch (System.Exception e) { if (transaction != null) { // Rollback the transaction transaction.Rollback(); } throw new System.Exception(e.Message, e); } //return the dataset return ds; }
/// <summary> ///返回结果集的第一行第一列 /// </summary> /// <remarks> /// 例如: /// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount", new OracleParameter("@prodid", 24)); /// </remarks> /// <param name="transaction">有效的 OracleTransaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">存储过程T-OleDb的名称或命令</param> /// <param name="commandParameters">params OracleParameter[]</param> /// <returns>返回结果集的第一行第一列</returns> public static object ExecuteScalar(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters) { OracleCommand cmd = new OracleCommand(); PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters); try { object obj = cmd.ExecuteScalar(); if (transaction != null) { transaction.Commit(); } return obj; } catch { if (transaction != null) { transaction.Rollback(); } return null; } }
/// <summary> /// 回滚事务 /// </summary> /// <param name="transaction">事务对象</param> /// <param name="connection">连接对象</param> public static void Rollback(OracleTransaction transaction, OracleConnection connection) { transaction.Rollback(); connection.Close(); }
public override void Rollback() { trx.Rollback(); }
/// <summary> /// Execute an OracleCommand (that returns a resultset) against the specified OracleTransaction /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders", new OracleParameter("@prodid", 24)); /// </remarks> /// <param name="transaction">有效的 OracleTransaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">存储过程名称或 PL/SQL</param> /// <param name="commandParameters">params OracleParameter[]</param> /// <returns>返回DataSet</returns> public static DataSet ExecuteDataset(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters) { //create a command and prepare it for execution OracleCommand cmd = new OracleCommand(); PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters); //create the DataAdapter & DataSet OracleDataAdapter da; DataSet ds = new DataSet(); try { da = new OracleDataAdapter(cmd); //fill the DataSet using default values for DataTable names, etc. da.Fill(ds); if (transaction != null) { transaction.Commit(); } } catch { if (transaction != null) { // Rollback the transaction transaction.Rollback(); } } //return the dataset return ds; }
/// <summary> ///返回结果集的第一行第一列 /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount", new OracleParameter("@prodid", 24)); /// </remarks> /// <param name="transaction">有效的 OracleTransaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-OleDb command</param> /// <param name="commandParameters">params OracleParameter[]</param> /// <returns>返回结果集的第一行第一列</returns> public static object ExecuteScalar(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters) { //create a command and prepare it for execution OracleCommand cmd = new OracleCommand(); PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters); try { //execute the command & return the results object obj = cmd.ExecuteScalar(); if (transaction != null) { transaction.Commit(); } return obj; } catch { if (transaction != null) { // Rollback the transaction transaction.Rollback(); } return null; } }
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"); }