/// <summary> /// Audita la transaccion en la tabla de auditoria /// </summary> /// <param name="NumeroSecuencia">Numero de secuencia de la transaccion</param> /// <param name="TipoMensaje">E: Enviado - R: Recibido</param> /// <param name="CampoDato">Detalle de la transaccion</param> public void AuditarTransaccion(string NumeroSecuencia, string TipoMensaje, string CampoDato) { DB2Transaction _trans = _myConn.BeginTransaction(); DB2Command _myDB2Command = _myConn.CreateCommand(); string _myQuery = "INSERT INTO CCVAUD VALUES(V7FECH, V7HORA, V7TMSJ, V7NSEQ, V7BUFF) "; _myQuery += "(?, ?, '?', ?, '?')"; _myDB2Command.CommandText = _myQuery; _myDB2Command.Parameters.Add("@FechaEnvio", DB2Type.Decimal); _myDB2Command.Parameters.Add("@HoraEnvio", DB2Type.Decimal); _myDB2Command.Parameters.Add("@Tipo", DB2Type.VarChar); _myDB2Command.Parameters.Add("@Secuencia", DB2Type.Decimal); _myDB2Command.Parameters.Add("@Mensaje", DB2Type.VarChar); _myDB2Command.Parameters["@FechaEnvio"].Value = Convert.ToDecimal(string.Format("{0:yyyyMMdd}", DateTime.Now)); _myDB2Command.Parameters["@HoraEnvio"].Value = Convert.ToDecimal(string.Format("{0:HHmmss}", DateTime.Now)); _myDB2Command.Parameters["@Tipo"].Value = TipoMensaje; _myDB2Command.Parameters["@Secuencia"].Value = Convert.ToDecimal(NumeroSecuencia); _myDB2Command.Parameters["@Mensaje"].Value = CampoDato; _myDB2Command.Transaction = _trans; _myDB2Command.ExecuteNonQuery(); _trans.Commit(); }
private bool ExecuteNonQuery(DBRequest request) { //Controllo if (!registered.Contains(request.Controller)) { throw new Exception("Il Controller non è registrato all'inizio dell'esecuzione"); } //Inizio DB2Transaction transaction = databaseConnection.GetTransaction(); DB2Command command = databaseConnection.GetCommand(); command.CommandText = request.Command; command.Transaction = transaction; //Esecuzione try { command.ExecuteNonQuery(); } catch (Exception e) { throw new Exception("Errore: " + e.Message); } //Controllo if (!registered.Contains(request.Controller)) { transaction.Rollback(); throw new Exception("Il Controller non è registrato alla fine dell'esecuzione"); } //Fine transaction.Commit(); return(true); }
/// <summary> /// 执行多条SQL语句,实现数据库事务。 /// </summary> /// <param name="SQLStringList">ArrayList</param> public static void ExecuteSqlTran(ArrayList sqlList, string ConString) { bool mustCloseConnection = false; using (DB2Connection conn = new DB2Connection(ConString)) { conn.Open(); using (DB2Transaction trans = conn.BeginTransaction()) { DB2Command cmd = new DB2Command(); try { for (int i = 0; i < sqlList.Count; i++) { string cmdText = sqlList[i].ToString(); PrepareCommand(cmd, conn, trans, CommandType.Text, cmdText, null, out mustCloseConnection); int val = cmd.ExecuteNonQuery(); } trans.Commit(); } catch { trans.Rollback(); throw; } finally { conn.Close(); cmd.Dispose(); } } } }
/// <summary> /// Inserir Veículo no Estacionamento /// </summary> public void EntrarVeiculo([FromBody] EstacionamentoModel obj) { if (!conexao.IsOpen) { conexao.Open(); } DB2Transaction trans = conexao.BeginTransaction(); try { new EstacionamentoDao(conexao, trans).EntrarVeiculo(obj); trans.Commit(); } catch (Exception ex) { trans.Rollback(); throw new Exception(ex.Message); } finally { if (conexao.IsOpen) { conexao.Close(); } } }
/// <summary> /// Obtiene los datos necesarios para poder enviar la transaccion a SENEBI /// </summary> /// <returns>Clase Transaccion, array de string</returns> public Transaccion ObtenerProximoMensaje() { DB2Transaction _trans = _myConn.BeginTransaction(); DB2Command _myDB2Command = _myConn.CreateCommand(); DB2DataReader _reader; Transaccion _transaccion = new Transaccion(); // Obtiene 1 transaccion del dia en estado pendiente ordenada por fecha y hora de alta // Query de version anterior: SELECT V4CTRX, V4IRED, V4NSEQ, V4DUSU, V4CLAV, V4DATO, V4ESTA, V4MENS, V4FALT, V4HALT, V4IUSR, V4FENV, V4HENV, V4FRTA, V4HRTA string _myQuery = "SELECT V4CTRX, V4NSEQ, V4DATO, V4ESTA, V4FALT, V4HALT"; _myQuery += "FROM CCVREQ "; _myQuery += "WHERE (V4FALT = " + Convert.ToDecimal(string.Format("{0:yyyyMMdd}", DateTime.Now)).ToString() + ") AND (V4ESTA = 'P') "; _myQuery += "ORDER BY V4FALT, V4HALT FETCH FIRST 1 ROW ONLY"; _myDB2Command.CommandText = _myQuery; _myDB2Command.Transaction = _trans; _reader = _myDB2Command.ExecuteReader(); if (_reader.Read()) { _transaccion.CodigoTransaccion = _reader.GetString(0); _transaccion.NumeroSecuencia = _reader.GetString(1); _transaccion.TramaMensaje = _reader.GetString(2); _transaccion.EstadoMensaje = _reader.GetString(3); _transaccion.FechaAlta = _reader.GetString(4); _transaccion.HoraAlta = _reader.GetString(5); } _reader.Close(); _trans.Commit(); return(_transaccion); }
public void CommitTransaction() { try { Trans.Commit(); } catch (Exception) { throw; } }
private void HistorialSeguimientoTabla(String tablaSeguir, String sqlRevision) { sqlRevision = sqlRevision.Replace("'", ""); sqlRevision = sqlRevision.ToUpper(); tablaSeguir = tablaSeguir.ToUpper(); RegexOptions options = RegexOptions.None; Regex regex = new Regex(@"[ ]{2,}", options); sqlRevision = regex.Replace(sqlRevision, @" "); if ((sqlRevision.Contains("UPDATE " + tablaSeguir) == true || sqlRevision.Contains("INSERT INTO " + tablaSeguir) == true || sqlRevision.Contains("DELETE FROM " + tablaSeguir) == true) && sqlRevision.Contains("MHISTORIAL_CAMBIOS") == false) { string usuario = HttpContext.Current.User.Identity.Name.ToLower(); string operacion = ""; if (sqlRevision.Contains("UPDATE")) { operacion = "U"; } else if (sqlRevision.Contains("DELETE")) { operacion = "D"; } else if (sqlRevision.Contains("INSERT")) { operacion = "I"; } DB2Command command = new DB2Command(); DB2Connection con = OpenConnection(); command.Connection = con; DB2Transaction trans = con.BeginTransaction(); command.Transaction = trans; string sqlHistorial = "INSERT INTO MHISTORIAL_CAMBIOS VALUES (DEFAULT,'" + tablaSeguir + "','" + operacion + "','" + sqlRevision + "','" + usuario + "','" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "');"; AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Actividad, sqlHistorial, null, string.Empty); command.CommandText = sqlHistorial; try { command.ExecuteNonQuery(); trans.Commit(); exceptions += "ejecutando: " + tablaSeguir + cambioLinea; } catch (Exception ex) { exceptions += String.Format("error ejecutando: {0} \n {1} \n", tablaSeguir, ex.Message); } } }
public bool DoCommit(ref string mess) { bool done = false; try { for (int j = 0; j < ol_cnt; j++) { OrderLine a = ols[j]; s_quantity = a.s_quantity - a.ol_quantity; if (s_quantity < 10) { s_quantity += 91; } var cmd = db.CreateCommand(); cmd.Transaction = tr; cmd.CommandText = "update STOCK set S_QUANTITY=" + s_quantity + " where S_I_ID=" + a.oliid + " and S_W_ID=" + a.ol_supply_w_id; Form1.RecordRequest(cmd, fid, tid); cmd.ExecuteNonQuery(); cmd.CommandText = "insert into ORDER_LINE(OL_O_ID,OL_D_ID,OL_W_ID,OL_NUMBER,OL_I_ID,OL_SUPPLY_W_ID,OL_QUANTITY,OL_AMOUNT)values(" + o_id + "," + did + "," + wid + "," + (j + 1) + "," + a.oliid + "," + a.ol_supply_w_id + "," + a.ol_quantity + "," + a.ol_amount + ")"; Form1.RecordRequest(cmd, fid, tid); cmd.ExecuteNonQuery(); } mess = "OKAY"; int rbk = util.random(1, 100); if (rbk == 1) { tr.Rollback(); tr = null; done = true; } else { tr.Commit(); Form1.commits++; tr = null; } // Phase 3 display the results Set(130, "OKAY"); done = true; } catch (Exception ex) { Set(130, ex.Message); Form1.RecordResponse(ex, fid, tid); tr.Rollback(); Form1.wconflicts++; } return(done); }
public void Single() { PutBlanks(); did = util.random(1, 10); int y = util.random(1, 100); if (y <= 60) { clast = enc.GetString(util.NextLast(util.NURandCLast())); } else { cid = util.NURandCID(); } bool done = false; int count = 0; string mess = ""; while (!done && count++ < 1000) { if (tr == null) { tr = db.BeginTransaction(System.Data.IsolationLevel.Serializable); } tid = ++Form1._tid; if (cid > 0) { if (FetchCustFromId(ref mess)) { goto bad; } } else { if (FetchCustFromLast(ref mess)) { goto bad; } } DoDisplay(ref mess); Invalidate(true); done = true; tr.Commit(); tr = null; } bad :; }
public bool update(string sql) { try { DB2Transaction trans = m_connect.BeginTransaction(); DB2Command cmd = m_connect.CreateCommand(); cmd.CommandText = sql; cmd.Transaction = trans; cmd.ExecuteNonQuery(); trans.Commit(); return(true); } catch (Exception e) { return(false); } }
/// <summary> /// Actualiza operacion luego del alta recibida desde WS. /// </summary> /// <param name="NumeroSecuencia">Numero de secuencia de la transaccion</param> /// <param name="CodigoOperacion">Codigo de operacion recibido por CV</param> /// <param name="Plazo">Plazo actualizado</param> /// <param name="MensajeError">Para alta fallida: descripcion</param> /// <param name="Resultado">Resultado del proceso con CV, para saber que update realizar</param> public void ActualizarOperacion(string NumeroSecuencia, string CodigoOperacion, string Plazo, string MensajeError, bool Resultado) { DB2Transaction _trans = _myConn.BeginTransaction(); DB2Command _myDB2Command = _myConn.CreateCommand(); string _myQuery = "UPDATE CCVOPE "; if (Resultado) { _myQuery += "SET V3IOPE = ?, V3PLAZ = ? "; } else { _myQuery += "SET V3MENS = '?' "; } _myQuery += "WHERE V3NSEQ = ?"; _myDB2Command.CommandText = _myQuery; _myDB2Command.Parameters.Add("@Secuencia", DB2Type.Decimal); if (Resultado) { _myDB2Command.Parameters.Add("@Plazo", DB2Type.Decimal); _myDB2Command.Parameters.Add("@Operacion", DB2Type.Decimal); } else { _myDB2Command.Parameters.Add("@Mensaje", DB2Type.VarChar); } _myDB2Command.Parameters["@Secuencia"].Value = Convert.ToDecimal(NumeroSecuencia); if (Resultado) { _myDB2Command.Parameters["@Plazo"].Value = Convert.ToDecimal(Plazo); _myDB2Command.Parameters["@Operacion"].Value = Convert.ToDecimal(CodigoOperacion); } else { _myDB2Command.Parameters["@Mensaje"].Value = MensajeError; } _myDB2Command.Transaction = _trans; _myDB2Command.ExecuteNonQuery(); _trans.Commit(); }
public bool CommitTransaction() { try { trans.Commit(); return(true); } catch (Exception ex) { LastError = ex.Message; Logger.WriteLogExcept(LogTitle, ex); return(false); } finally { trans = null; } }
public bool Transaction(ArrayList sql) { bool commit = true; String exceptions = ""; DB2Command command = new DB2Command(); DB2Transaction trans = connection.BeginTransaction(); command.Connection = connection; command.Transaction = trans; for (int i = 0; i < sql.Count; i++) { command.CommandText = sql[i].ToString(); try { command.ExecuteNonQuery(); } catch { exceptions += String.Format("error ejecutando: {0}\n", sql[i]); commit = false; } } if (commit) { trans.Commit(); } else { trans.Rollback(); } Exceptions = exceptions; return(commit); }
/// <summary> /// Actualiza estado y mensaje de una operacion obtenida /// </summary> /// <param name="NumeroSecuencia">Numero de secuencia de la operacion</param> /// <param name="Resultado">P:Procesada - R:Error</param> /// <param name="MensajeError">Descripcion del error en caso que corresponda</param> public void ActualizarTransaccion(string NumeroSecuencia, bool Resultado, string MensajeError) { string _estado = string.Empty; if (Resultado) { _estado = "P"; } else { _estado = "R"; } DB2Transaction _trans = _myConn.BeginTransaction(); DB2Command _myDB2Command = _myConn.CreateCommand(); string _myQuery = "UPDATE CCVREQ "; _myQuery += "SET V4FENV = ?, V4HENV = ?, V4ESTA = '?', V4MENS = '?' "; _myQuery += "WHERE V4NSEQ = ?"; _myDB2Command.CommandText = _myQuery; _myDB2Command.Parameters.Add("@FechaEnvio", DB2Type.Decimal); _myDB2Command.Parameters.Add("@HoraEnvio", DB2Type.Decimal); _myDB2Command.Parameters.Add("@Estado", DB2Type.VarChar); _myDB2Command.Parameters.Add("@Mensaje", DB2Type.VarChar); _myDB2Command.Parameters.Add("@Secuencia", DB2Type.Decimal); _myDB2Command.Parameters["@FechaEnvio"].Value = Convert.ToDecimal(string.Format("{0:yyyyMMdd}", DateTime.Now)); _myDB2Command.Parameters["@HoraEnvio"].Value = Convert.ToDecimal(string.Format("{0:HHmmss}", DateTime.Now)); _myDB2Command.Parameters["@Estado"].Value = _estado; _myDB2Command.Parameters["@Mensaje"].Value = MensajeError; _myDB2Command.Parameters["@Secuencia"].Value = Convert.ToDecimal(NumeroSecuencia); _myDB2Command.Transaction = _trans; _myDB2Command.ExecuteNonQuery(); _trans.Commit(); }
/// <summary> /// Retirar Veículo do Estacionamento /// </summary> public double SairVeiculo(int codigo) { try { if (!conexao.IsOpen) { conexao.Open(); } if (transacao == null) { transacao = conexao.BeginTransaction(); } DateTime dataEntrada = new EstacionamentoDao(conexao, transacao).BuscarDataEntradaVeiculo(codigo); DateTime dataSaida = DateTime.Now; TimeSpan duracao = new TimeSpan(dataSaida.Ticks - dataEntrada.Ticks); new EstacionamentoDao(conexao, transacao).SairVeiculo(codigo); transacao.Commit(); return(0); } catch (Exception ex) { transacao.Rollback(); throw new Exception(ex.Message); } finally { if (conexao.IsOpen) { conexao.Close(); } } }
/// <summary> /// 导入 /// </summary> /// <param name="recc"></param> /// <param name="connectStr"></param> /// <param name="sql"></param> /// <param name="allDB2Para"></param> /// <returns></returns> public static bool Import(int recc, string connectStr, string sql, IList <DB2Parameter> allDB2Para) { //设置一个数据库的连接串 using (DB2Connection conn = new DB2Connection(connectStr)) { conn.Open(); DB2Transaction transaction = conn.BeginTransaction(); try { DB2Command command = new DB2Command(); command.Connection = conn; command.CommandType = CommandType.Text; command.Transaction = transaction; //到此为止,还都是我们熟悉的代码,下面就要开始喽 //这个参数需要指定每次批插入的记录数 command.ArrayBindCount = recc; //在这个命令行中,用到了参数,参数我们很熟悉,但是这个参数在传值的时候 //用到的是数组,而不是单个的值,这就是它独特的地方 command.CommandText = sql; //下面定义几个数组,分别表示三个字段,数组的长度由参数直接给出 foreach (var t in allDB2Para) { command.Parameters.Add(t); } //这个调用将把参数数组传进SQL,同时写入数据库 command.ExecuteNonQuery(); transaction.Commit();//提交事务 } catch (Exception e) { transaction.Rollback();//事务回滚 throw e; } } return(true); }
private DBReaderController ExecuteQuery(DBRequest request) { //Controllo if (!registered.Contains(request.Controller)) { throw new Exception("Il Controller non è registrato all'inizio dell'esecuzione"); } //Inizio DBReaderController result; DB2DataReader reader; DB2Transaction transaction = databaseConnection.GetTransaction(); DB2Command command = databaseConnection.GetCommand(); command.CommandText = request.Command; command.Transaction = transaction; //Esecuzione try { reader = command.ExecuteReader(); } catch (Exception e) { throw new Exception("Errore in lettura: " + e.Message); } //Controllo if (!registered.Contains(request.Controller)) { transaction.Rollback(); throw new Exception("Il Controller non è registrato alla fine dell'esecuzione"); } //Fine transaction.Commit(); return(result = new DBReaderController(reader)); }
public void Single() { PutBlanks(); did = util.random(1, 10); if (FetchDistrict()) { goto bad; } cdid = did; cid = -1; clast = ""; /* if (activewh>1) * { * int x = util.random(1,100); * if (x>85) * { * cdid = util.random(1,10); * cwid = util.random(1,activewh,wid); * } * } */ int y = util.random(1, 100); if (y <= 60) // select by random last name { clast = enc.GetString(util.NextLast(9999)); } else { cid = util.NURandCID(); } c_amount = decimal.Parse(util.NextNString(1, 500000, 2).ToString()); bool done = false; string mess = ""; while (!done && count++ < 1000) { tr = db.BeginTransaction(System.Data.IsolationLevel.Serializable); tid = ++Form1._tid; try { if (cid < 0) { FetchCustFromLast(ref mess); } else { FetchCustFromId(ref mess); } DoPayment(ref mess); Invalidate(true); tr.Commit(); done = true; } catch (Exception ex) { Form1.RecordResponse(ex, fid, tid); Form1.wconflicts++; } } return; bad :; }
/// <summary> /// 执行事务 /// </summary> public override void CommitTrans() { _trans.Commit(); _isInTransaction = false; }
public void commit() { trans.Commit(); }
public bool Transaction(ArrayList sql) { int numQueries = 0, i = 0; bool status = false; DB2Command command = new DB2Command(); DB2Connection con = OpenConnection(); command.Connection = con; DB2Transaction trans = con.BeginTransaction(); command.Transaction = trans; for (i = 0; i < sql.Count; i++) { AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Actividad, (string)sql[i], null, string.Empty); command.CommandText = sql[i].ToString(); try { command.ExecuteNonQuery(); exceptions += "ejecutando: " + sql[i] + cambioLinea; numQueries++; HistorialSeguimientoTabla(TABLA_SEGUIR, sql[i].ToString()); //Almacenamiento historial de seguimiento a tabla. } catch (Exception ex) { exceptions += String.Format("error ejecutando: {0} \n {1} \n", sql[i], ex.Message); status = false; } } if (numQueries == sql.Count) { try { trans.Commit(); exceptions += "ejecutando Commit -- " + cambioLinea; status = true; } catch (Exception e) { exceptions += "Error ejecutando Commit: " + e.ToString(); AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Error, string.Empty, e, exceptions); status = false; } } else { try { trans.Rollback(); } catch (Exception e) { exceptions += "Error ejecutando RollBack: " + e.ToString(); AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Error, string.Empty, e, exceptions); } numQueries = 0; } CloseConnection(con); return(status); }