/// <summary> /// 更新类对象到数据库中 /// </summary> /// <typeparam name="T">类对象ID段类型</typeparam> /// <param name="objTable">类对象</param> /// <param name="tr">事务,null表示不启用事务</param> /// <returns>更新记录成功,返回记录ID号,否则返回默认值(0,或者空串)</returns> public T Update <T>(ITable objTable, Transaction tr) { Type type = typeof(object); string pkField = objTable.GetPrimayField(ref type); string hashKey = this.mDataSource + ":table:" + objTable.GetTableName() + ":update"; string sqlstr = ""; string strParamList = ""; if (paramCache[hashKey] == null) { string str1 = ""; foreach (string strkey in objTable.GetFieldList().Split(new char[] { ',' })) { if (pkField != strkey) { str1 += "," + strkey + "=" + BuildParamFlag(strkey); strParamList += "," + strkey; } } str1 = str1.Remove(0, 1); sqlstr = "UPDATE " + objTable.GetTableName() + " set " + str1 + " where " + pkField + "=" + BuildParamFlag(pkField); strParamList = strParamList.Remove(0, 1) + "," + pkField; paramCache.Add(hashKey, sqlstr); paramCache.Add(hashKey + ":param", strParamList); } sqlstr = paramCache[hashKey].ToString(); strParamList = paramCache[hashKey + ":param"].ToString(); Hashtable htParam = objTable.GetFields(); return(Execute(objTable.GetTableName(), "update", sqlstr, htParam, strParamList, tr) ? (T)htParam[pkField] : default(T)); }
public void TestBeginTransactionChaos() { DataBaseServer dbType = ConnectedDataProvider.GetDbType(con); // not supported on DB2 and Oracle and Sybase if (dbType != DataBaseServer.Oracle && dbType != DataBaseServer.DB2 && dbType != DataBaseServer.Sybase) { con.Close(); con.Open(); try { BeginCase("BeginTransaction - IsolationLevel Chaos"); tran = con.BeginTransaction(IsolationLevel.Chaos); Compare(tran == null, false); } catch(Exception ex){exp = ex;} finally{EndCase(exp); exp = null;} } /* not supported by MSSQL,DB2,Oracle con.Close(); con.Open(); try { BeginCase("BeginTransaction - IsolationLevel Unspecified"); tran = con.BeginTransaction(IsolationLevel.Unspecified ); Compare(tran == null, false); } catch(Exception ex){exp = ex;} finally{EndCase(exp); exp = null;} */ }
public bool UpdateQCResult(double loid, DateTime duedate, double qcqty1, double qcqty2, double qcqty3, string qcresult, string qcremark, string userID, string status, OracleTransaction zTrans) { string sql = "UPDATE PDPRODUCT SET QCRESULT = '" + qcresult + "', "; sql += "QCQTY1 = " + qcqty1 + ", "; sql += "QCQTY2 = " + qcqty2 + ", "; sql += "QCQTY3 = " + qcqty3 + ", "; sql += "QCREMARK = '" + qcremark + "', "; sql += "PRODSTATUS = '" + status + "', "; sql += "QCDUEDATE = " + OracleDB.QRDateTime(duedate) + ", "; sql += "UPDATEBY = '" + userID + "', "; sql += "UPDATEON = " + OracleDB.QRDateTime() + " "; sql += "WHERE LOID = " + loid + " "; //+ (status == Constz.Requisition.Status.Approved.Code ? "AND STATUS = '" + Constz.Requisition.Status.Waiting.Code + "' " : (status == Constz.Requisition.Status.Void.Code ? "AND STATUS = '" + Constz.Requisition.Status.Approved.Code + "' " : "")); bool ret = true; try { ret = (OracleDB.ExecNonQueryCmd(sql, zTrans) > 0); if (!ret) throw new ApplicationException(OracleDB.Err_NoUpdate); } catch (Exception ex) { ret = false; _error = ex.Message; } return ret; }
public bool CreateTransaction() { bool ret = true; try { if (_Conn == null) { _Conn = OracleDB.GetConnection(); } _Trans = _Conn.BeginTransaction(IsolationLevel.ReadCommitted); } catch (Exception ex) { ret = false; _error = ex.Message; } if (!ret) { try { if (_Conn != null) { _Conn.Close(); } _Conn.Open(); _Trans = _Conn.BeginTransaction(IsolationLevel.ReadCommitted); ret = true; } catch (Exception ex) { ret = false; _error = ex.Message; } } return ret; }
/// <summary> /// This method opens (if necessary) and assigns a connection, transaction, command type and parameters /// to the provided command. /// </summary> /// <param name="command">the OracleCommand to be prepared</param> /// <param name="connection">a valid OracleConnection, on which to execute this command</param> /// <param name="transaction">a valid OracleTransaction, or 'null'</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or PL/SQL command</param> /// <param name="commandParameters">an array of OracleParameters to be associated with the command or 'null' if no parameters are required</param> private static void PrepareCommand(OracleCommand command, OracleConnection connection, OracleTransaction transaction, CommandType commandType, string commandText, OracleParameter[] commandParameters) { //if the provided connection is not open, we will open it if (connection.State != ConnectionState.Open) { connection.Open(); } //associate the connection with the command command.Connection = connection; //set the command text (stored procedure name or Oracle statement) command.CommandText = commandText; command.CommandTimeout = 200000; //if we were provided a transaction, assign it. if (transaction != null) { command.Transaction = transaction; } //set the command type command.CommandType = commandType; //attach the command parameters if they are provided if (commandParameters != null) { AttachParameters(command, commandParameters); } return; }
public static int Save(Loan loan, OracleConnection oraDbConn, OracleTransaction oraTrans) { if(loan.Id == null){ return Insert(loan, oraDbConn, oraTrans); } else { return Update(loan); } }
public static int Save(Applicant applicant, OracleConnection oraDbConn, OracleTransaction oraTrans) { if (applicant.Id == null) { return Insert(applicant, oraDbConn, oraTrans); } else { return Update(applicant); } }
public OracleCommandSet(OracleConnection connection, OracleTransaction transaction) { this._usedParameterNames = new Hashtable(StringComparer.OrdinalIgnoreCase); this._commandList = new ArrayList(); this._batchCommand = new OracleCommand(); this.Connection = connection; this.Transaction = transaction; }
/// <summary> /// Execute an OracleCommand (that returns no resultset) against an existing database transaction /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24)); /// </remarks> /// <param name="trans">an existing database transaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or PL/SQL command</param> /// <param name="commandParameters">an array of OracleParamters used to execute the command</param> /// <returns>an int representing the number of rows affected by the command</returns> public static int ExecuteNonQuery(OracleTransaction trans, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters) { OracleCommand cmd = new OracleCommand(); PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; }
/// <summary> /// Get Data List of This Table /// </summary> /// <param name="whereCause"></param> /// <param name="zTrans">Transaction, set to null if no transaction provided</param> /// <returns></returns> public DataTable GetDataList(string whereCause, OracleTransaction zTrans) { string sql = "SELECT PT.* , "; sql += "CASE PT.TYPE WHEN '" + Constz.ProductType.Type.FG.Code + "' THEN '" + Constz.ProductType.Type.FG.Rank + "' "; sql += "WHEN '" + Constz.ProductType.Type.WH.Code + "' THEN '" + Constz.ProductType.Type.WH.Rank + "' "; sql += "WHEN '" + Constz.ProductType.Type.Others.Code + "' THEN '" + Constz.ProductType.Type.Others.Rank + "' "; sql += "ELSE '' END AS RANK FROM PRODUCTTYPE PT ORDER BY RANK,PT.CODE"; return OracleDB.ExecListCmd(sql); }
public override void Commit() { try { ts.Commit(); } finally { ts = null; } }
public OracleCommand (string commandText, OracleConnection connection, OracleTransaction tx) { moreResults = -1; preparedStatement = null; CommandText = commandText; Connection = connection; Transaction = tx; CommandType = CommandType.Text; UpdatedRowSource = UpdateRowSource.Both; DesignTimeVisible = true; parameters = new OracleParameterCollection (); }
public void BeginTrans() { if (_conn != null) { if (_trans != null) { throw new Exception("Transition already began! Please commit it before begin a new one."); } _trans = _conn.BeginTransaction(); _isInTransaction = true; } }
public FrmSupplierCorrection(OracleConnection Conn, OracleTransaction Trans, string strGYSMC) { string strSQL = "select DWID, DWMC, DWBH, ZJM from JT_J_DWXX"; OracleDataAdapter ada = new OracleDataAdapter(strSQL, Conn); ada.SelectCommand.Transaction = Trans; DataSet ds = new DataSet(); ada.Fill(ds, "JT_J_DWXX"); InitializeComponent(); jTJDWXXBindingSource.DataSource = ds; jTJDWXXBindingSource.DataMember = "JT_J_DWXX"; teOldSupplier.Text = strGYSMC; }
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 bool UpdateQCStockinItem(double stockin, string userID, string status, OracleTransaction zTrans) { string sql = "UPDATE STOCKINITEM "; sql += "SET STATUS = '" + status + "', "; sql += "UPDATEBY = '" + userID + "', "; sql += "UPDATEON = " + OracleDB.QRDateTime() + " "; sql += "WHERE STOCKIN = " + stockin + " "; bool ret = true; try { ret = (OracleDB.ExecNonQueryCmd(sql, zTrans) > 0); if (!ret) throw new ApplicationException(OracleDB.Err_NoUpdate); } catch (Exception ex) { ret = false; _error = ex.Message; } return ret; }
public bool UpdateQCStockin(double loid, string anacode, DateTime anadate, string userID, string status, OracleTransaction zTrans) { string sql = "UPDATE STOCKIN SET ANACODE = '" + anacode + "', "; sql += "STATUS = '" + status + "', "; sql += "ANADATE = " + OracleDB.QRDateTime(anadate) + ", "; sql += "UPDATEBY = '" + userID + "', "; sql += "UPDATEON = " + OracleDB.QRDateTime() + " "; sql += "WHERE LOID = " + loid + " "; bool ret = true; try { ret = (OracleDB.ExecNonQueryCmd(sql, zTrans) > 0); if (!ret) throw new ApplicationException(OracleDB.Err_NoUpdate); } catch (Exception ex) { ret = false; _error = ex.Message; } return ret; }
public bool UpdatePDOrder(double loid, string userID, string status, OracleTransaction zTrans) { string sql = "UPDATE PDORDER SET "; sql += "STATUS = '" + status + "', "; sql += "UPDATEBY = '" + userID + "', "; sql += "UPDATEON = " + OracleDB.QRDateTime() + " "; sql += "WHERE LOID = " + loid + " "; //+ (status == Constz.Requisition.Status.Approved.Code ? "AND STATUS = '" + Constz.Requisition.Status.Waiting.Code + "' " : (status == Constz.Requisition.Status.Void.Code ? "AND STATUS = '" + Constz.Requisition.Status.Approved.Code + "' " : "")); bool ret = true; try { ret = (OracleDB.ExecNonQueryCmd(sql, zTrans) > 0); if (!ret) throw new ApplicationException(OracleDB.Err_NoUpdate); } catch (Exception ex) { ret = false; _error = ex.Message; } return ret; }
internal static bool ExecuteNonQuery(string sql, OracleTransaction trans) { bool ret = false; try { OracleCommand cmd = new OracleCommand(); cmd.Connection = trans.Connection; cmd.Transaction = trans; cmd.CommandText = sql; cmd.CommandType = CommandType.Text; cmd.CommandTimeout = 240; cmd.ExecuteNonQuery(); ret = true; } catch (Exception ex) { ret = false; } return ret; }
public static int ExecNonQueryCmd(string sqlz, OracleTransaction zTrans) { OracleCommand zCommand = new OracleCommand(); int retval; if (zTrans != null) { BuildzCommand(zCommand, zTrans.Connection, zTrans, CommandType.Text, sqlz, null); retval = zCommand.ExecuteNonQuery(); } else { using (OracleConnection zConn = new OracleConnection(ConnectionString)) { BuildzCommand(zCommand, zConn, zTrans, CommandType.Text, sqlz, null); retval = zCommand.ExecuteNonQuery(); } } return retval; }
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> /// 执行SQL语句 /// </summary> /// <param name="sqlstr">sql语句</param> /// <param name="tr">事务,null表示不启用事务</param> /// <returns>是否执行成功</returns> public bool ExecSQL(string sqlstr, Transaction tr) { DbCommand dbCommand = new DbCommand(); dbCommand.Connection = mConn; dbCommand.CommandText = sqlstr; dbCommand.Transaction = tr; try { if (mConn.State == ConnectionState.Closed) { mConn.Open(); } dbCommand.ExecuteNonQuery(); return(true); } catch (Exception e) { WriteLogInfo("errorn on " + this.GetType().Name + " ExecSQL:" + sqlstr + "\r\n" + e.Message); return(false); } }
private static int Insert(Loan loan, OracleConnection oraDbConn, OracleTransaction oraTrans) { int LoanId = 0; using (OracleCommand insertLoanCommand = new OracleCommand()) { insertLoanCommand.CommandType = CommandType.StoredProcedure; insertLoanCommand.CommandText = "LoansPKG.insertLoan"; insertLoanCommand.Connection = oraDbConn; insertLoanCommand.Transaction = oraTrans; insertLoanCommand.Parameters.AddWithValue("AppId", loan.ApplicantId); insertLoanCommand.Parameters.AddWithValue("LoanType", loan.Type); insertLoanCommand.Parameters.AddWithValue("LoanAmount", loan.Amount); OracleParameter outputLoanId = new OracleParameter("LoanId", OracleType.Number); outputLoanId.Direction = ParameterDirection.Output; insertLoanCommand.Parameters.Add(outputLoanId); insertLoanCommand.ExecuteNonQuery(); LoanId = Convert.ToInt32(outputLoanId.Value); } return LoanId; }
/// <summary> /// 插入类对象到数据库中 /// </summary> /// <typeparam name="T">类对象ID段类型</typeparam> /// <param name="objTable">类对象</param> /// <param name="CreateNewId">是否生成新的ID号</param> /// <param name="tr">事务,null表示不启用事务</param> /// <returns>插入记录成功,返回记录ID号,否则返回默认值(0,或者空串)</returns> public T Insert <T>(ITable objTable, bool CreateNewId, Transaction tr) { Type type = typeof(object); string pkField = objTable.GetPrimayField(ref type); string hashKey = this.mDataSource + ":table:" + objTable.GetTableName() + ":insert"; string sqlstr = ""; string strParamList = ""; if (paramCache[hashKey] == null) { string str1 = ""; string str2 = ""; foreach (string strkey in objTable.GetFieldList().Split(new char[] { ',' })) { str1 += "," + strkey; str2 += "," + BuildParamFlag(strkey); } str1 = str1.Remove(0, 1); str2 = str2.Remove(0, 1); sqlstr = "INSERT INTO " + objTable.GetTableName() + " (" + str1 + ")VALUES(" + str2 + ")"; strParamList = str1; paramCache.Add(hashKey, sqlstr); paramCache.Add(hashKey + ":param", strParamList); } sqlstr = paramCache[hashKey].ToString(); strParamList = paramCache[hashKey + ":param"].ToString(); Hashtable htParam = objTable.GetFields(); if (CreateNewId) { htParam[pkField] = GetSerialNo <T>(objTable.GetTableName(), pkField); } return(Execute(objTable.GetTableName(), "insert", sqlstr, htParam, strParamList, tr) ? (T)htParam[pkField] : default(T)); }
internal static DataTable ExecuteTable(string sql, OracleTransaction trans) { OracleCommand cmd = new OracleCommand(); OracleDataAdapter adapter = new OracleDataAdapter(); adapter.SelectCommand = cmd; DataTable dt = new DataTable(); try { cmd.Connection = trans.Connection; cmd.Transaction = trans; cmd.CommandText = sql; cmd.CommandType = CommandType.Text; cmd.CommandTimeout = 240; adapter.Fill(dt); adapter.Dispose(); } catch (Exception ex) { dt = new DataTable(); } return dt; }
/// <summary> /// 删除数据库中指定ID的数据记录 /// </summary> /// <typeparam name="T">类对象ID段类型</typeparam> /// <param name="objTable">类对象</param> /// <param name="id">待删除的记录ID</param> /// <param name="tr">事务,null表示不启用事务</param> /// <returns>更新删除成功,返回记录ID号,否则返回默认值(0,或者空串)</returns> public T Delete <T>(ITable objTable, T id, Transaction tr) { Type type = typeof(object); string pkField = objTable.GetPrimayField(ref type); string hashKey = this.mDataSource + ":table:" + objTable.GetTableName() + ":delete"; string sqlstr = ""; string strParamList = ""; if (paramCache[hashKey] == null) { sqlstr = "DELETE FROM " + objTable.GetTableName() + " where " + pkField + "=" + BuildParamFlag(pkField); strParamList = pkField; paramCache.Add(hashKey, sqlstr); paramCache.Add(hashKey + ":param", strParamList); } sqlstr = paramCache[hashKey].ToString(); strParamList = paramCache[hashKey + ":param"].ToString(); Hashtable htParam = objTable.GetFields(); htParam[pkField] = id; return(Execute(objTable.GetTableName(), "delete", sqlstr, htParam, strParamList, tr) ? (T)htParam[pkField] : default(T)); }
/// <summary> /// 执行一个存储过程,没有返回数据集,只返回影响记录数 /// 这个方法并没有提供存取到输出参数或存储过程的返回值参数。 /// </summary> /// <remarks> /// 这种方法并没有提供存取到输出参数或存储过程的返回值参数 /// /// 例如: /// int result = ExecuteNonQuery(conn, trans, "PublishOrders", 24, 36); /// </remarks> /// <param name="transaction">有效的 OracleTransaction</param> /// <param name="spName">存储过程名称</param> /// <param name="parameterValues"> params object[]</param> /// <returns>影响记录数</returns> public static int ExecuteNonQuery(OracleTransaction transaction, string spName, params object[] parameterValues) { if ((parameterValues != null) && (parameterValues.Length > 0)) { OracleParameter[] commandParameters = MsOracleProviderParameterCache.GetSpParameterSet(transaction.Connection.ConnectionString, spName); AssignParameterValues(commandParameters, parameterValues); return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, commandParameters); } else { return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName); } }
/// <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> /// <remarks> /// 例如: /// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders"); /// </remarks> /// <param name="transaction">有效的 OracleTransaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">存储过程名称或 PL/SQL</param> /// <returns>影响记录数</returns> public static int ExecuteNonQuery(OracleTransaction transaction, CommandType commandType, string commandText) { return ExecuteNonQuery(transaction, commandType, commandText, (OracleParameter[])null); }
/// <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; }
public OracleCommand(string commandText, OracleConnection connection, OracleTransaction tx) : this() { this.CommandText = commandText; this.Connection = connection; this.Transaction = tx; }
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"); }
/// <summary> /// 提交事务 /// </summary> public static void CommitTransaction() { try { if (Transaction != null) { Transaction.Commit(); if (conn != null) { if (conn.State == ConnectionState.Open) { conn.Close(); conn.Dispose(); } conn = null; comm = null; adpter = null; Transaction = null; } } } catch (OracleException e) { Transaction.Rollback(); Close(); Transaction = null; LogHelper.WriteLog("MsOracle.CommitTransaction",null, e); throw new System.Exception(e.Message); } finally { Close(); Transaction = null; } }
/** * Initializes a new instance of the OracleCommand class with the text of the query, a SqlConnection, and the Transaction. * @param cmdText The text of the query. * @param connection A SqlConnection that represents the connection to an instance of SQL Server. * @param transaction The SqlTransaction in which the OracleCommand executes. */ public OracleCommand( String cmdText, OracleConnection connection, OracleTransaction transaction) : base(cmdText, connection, transaction) { }
public OracleTransaction(System.Data.OracleClient.OracleTransaction trx, OracleConnection con) { this.trx = trx; this.con = con; }
/// <summary> /// 开始事务 /// </summary> /// <param name="conn">OracleConnection连接对象</param> public static void BeginTransaction(OracleConnection connObjection) { if (Transaction == null||Transaction.Connection != connObjection) { Transaction = connObjection.BeginTransaction(IsolationLevel.ReadCommitted); } }
//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")); } }
/// <summary> /// 开始事务 /// </summary> public static void BeginTransaction() { if (conn == null) { conn = GetOracleConnection(ConnectionString); } if (Transaction.Connection != conn) { Transaction = conn.BeginTransaction(IsolationLevel.ReadCommitted); } }
//private Transaction CreateTransaction() //{ // Transaction tr=mConn.BeginTransaction(); // tr.Commit(); //} #endregion #endregion #region 公开方法:存贮过程相关 #region CallProc public bool CallProc(IStoredProc objProc, Transaction tr) { #region 创建命令 DbCommand dbCommand = new DbCommand(); dbCommand.Connection = mConn; dbCommand.CommandType = CommandType.StoredProcedure; dbCommand.CommandText = objProc.GetProcName(); dbCommand.Transaction = tr; #endregion #region 参数赋值 #if DT_ORACLE DbParameter[] dbparams = this.DeriveParameters(objProc.GetProcName(), false); #elif DT_MSSQL DbParameter[] dbparams = this.DeriveParameters(objProc.GetProcName(), true); #elif DT_OLEDB DbParameter[] dbparams = new DbParameter[0]; this.WriteLogInfo(this.GetType().Name + "OLEDB方式不支持取存贮过程参数"); return(false); #endif Hashtable htParam = objProc.GetFields(); foreach (DbParameter dbparam in dbparams) { if (dbparam.Direction == ParameterDirection.Input || dbparam.Direction == ParameterDirection.InputOutput) { dbparam.Value = htParam[dbparam.ParameterName.ToUpper()]; } else { dbparam.Value = DBNull.Value; } dbCommand.Parameters.Add(dbparam); } #endregion #region 执行 DataSet mDataSet = new DataSet(); using (DataAdapter mDataAdapter = new DataAdapter(dbCommand)) { try { mDataAdapter.Fill(mDataSet); } catch (Exception e) { WriteLogInfo("error on " + this.GetType().Name + " " + objProc.GetProcName() + ".excute:\r\n" + e.Message); return(false); } } #endregion #region 读取参数 foreach (DbParameter dbparam in dbparams) { if (dbparam.Direction == ParameterDirection.Output || dbparam.Direction == ParameterDirection.InputOutput) { htParam[dbparam.ParameterName.ToUpper()] = dbparam.Value; } } if (objProc.GetCursorFieldList() != "") { string[] cursorFields = objProc.GetCursorFieldList().Split(new char[] { ',' }); if (mDataSet.Tables.Count != cursorFields.Length) { WriteLogInfo("error on " + this.GetType().Name + " " + objProc.GetProcName() + ".excute:\r\n 返回的游标数量与参数不匹配:" + "\r\n\t游标参数:" + objProc.GetCursorFieldList() + "\r\n\t游标返回数:" + mDataSet.Tables.Count); return(false); } int idx = 0; foreach (string cursorField in cursorFields) { htParam[cursorField.ToUpper()] = mDataSet.Tables[idx++]; } } #endregion return(true); }
/// <summary> /// 回滚事务 /// </summary> public static void RollbackTransaction(OracleConnection connObjection) { try { if (Transaction != null && Transaction.Connection != null) { Transaction.Rollback(); Close(connObjection); Transaction = null; if (connObjection != null) { if (connObjection.State == ConnectionState.Open) { Close(connObjection); } } } else { 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> /// 执行SQL语句 /// </summary> /// <param name="tablename"></param> /// <param name="optype"></param> /// <param name="sqlstr"></param> /// <param name="htParam"></param> /// <param name="strParamList"></param> /// <param name="tr"></param> /// <returns></returns> private bool Execute(string tablename, string optype, string sqlstr, Hashtable htParam, string strParamList, Transaction tr) { #region 创建命令 DbCommand dbCommand = new DbCommand(); dbCommand.Connection = mConn; dbCommand.CommandText = sqlstr; dbCommand.Transaction = tr; #endregion #region 设置参数(对于OLEDB方式而言,参数的顺序必须与SQL语句中一致) foreach (string strkey in strParamList.Split(new char[] { ',' })) { #if DT_OLEDB if (htParam[strkey].GetType() != typeof(DateTime)) { dbCommand.Parameters.Add(new DbParameter(strkey, htParam[strkey])); } else { //OleDB连Access时,这个日期类型必须特别指定;连Oracle不需要;SQL没测过 dbCommand.Parameters.Add(new DbParameter(strkey, System.Data.OleDb.OleDbType.Date)).Value = htParam[strkey]; } #else dbCommand.Parameters.Add(new DbParameter(strkey, htParam[strkey])); #endif } #endregion #region 提交命令 try { if (mConn.State == ConnectionState.Closed) { mConn.Open(); } dbCommand.ExecuteNonQuery(); //DisConnection(); return(true); } catch (Exception e) { this.WriteLogInfo("error on " + this.GetType().Name + " " + tablename + ".Execute." + optype + ":\r\nsql:" + sqlstr + "\r\n" + e.Message); return(false); } #endregion }