/// <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);
}