/// <summary>トランザクション開始</summary>
/// <param name="iso">
/// 分離レベル
/// 1. NoTransaction:トランザクションを開始しない。
/// 2. DefaultTransaction:規定の分離レベルでトランザクションを開始する。
/// 3. ReadUncommitted:非コミット読み取りの分離レベルでトランザクションを開始する。
/// 4. ReadCommitted:コミット済み読み取りの分離レベルでトランザクションを開始する。
/// 5. RepeatableRead:反復可能読み取りの分離レベルでトランザクションを開始する。
/// 6. Serializable:直列化可能の分離レベルでトランザクションを開始する。
/// 7. Snapshot:エラー（DB2.NETでは無効な分離レベル）
/// </param>
/// <remarks>
/// DB2は、SQL Serverと同様にロック法方式のDBMSであるため、方利用方法は、SQLServerとほぼ同様
/// 本メソッドの実装も、スナップ ショット分離レベルの実装を除き、SQLServerと同様とした。
/// 詳細は、DB2のマニュアルなどを参照のこと。
/// 必要に応じて利用する。
/// </remarks>
public override void BeginTransaction(DbEnum.IsolationLevelEnum iso)
{
// 分離レベル設定のチェック
if (iso == DbEnum.IsolationLevelEnum.NoTransaction)
{
// トランザクションを開始しない（nullのまま）。
}
else if (iso == DbEnum.IsolationLevelEnum.DefaultTransaction)
{
// 規定の分離レベルでトランザクションを開始する。
this._tx = this._cnn.BeginTransaction();
}
else if (iso == DbEnum.IsolationLevelEnum.ReadUncommitted)
{
// 非コミット読み取りの分離レベルでトランザクションを開始する。
this._tx = this._cnn.BeginTransaction(IsolationLevel.ReadUncommitted);
}
else if (iso == DbEnum.IsolationLevelEnum.ReadCommitted)
{
// コミット済み読み取りの分離レベルでトランザクションを開始する。
this._tx = this._cnn.BeginTransaction(IsolationLevel.ReadCommitted);
}
else if (iso == DbEnum.IsolationLevelEnum.RepeatableRead)
{
// 反復可能読み取りの分離レベルでトランザクションを開始する。
this._tx = this._cnn.BeginTransaction(IsolationLevel.RepeatableRead);
}
else if (iso == DbEnum.IsolationLevelEnum.Serializable)
{
// 直列化可能の分離レベルでトランザクションを開始する。
this._tx = this._cnn.BeginTransaction(IsolationLevel.Serializable);
}
else if (iso == DbEnum.IsolationLevelEnum.Snapshot)
{
// 無効な分離レベル（スナップ ショット分離レベル）。
throw new ArgumentException(String.Format(
PublicExceptionMessage.DB_ISO_LEVEL_PARAM_ERROR_SS, "DB2"));
}
else if (iso == DbEnum.IsolationLevelEnum.User)
{
// 無効な分離レベル（ユーザ指定）。
throw new ArgumentException(
PublicExceptionMessage.DB_ISO_LEVEL_PARAM_ERROR_USR);
}
else if (iso == DbEnum.IsolationLevelEnum.NotConnect)
{
// 2009/03/29 -- 追加したNotConnectの対応（このコードブロック）。
// 無効な分離レベル（NotConnect指定）。
throw new ArgumentException(
PublicExceptionMessage.DB_ISO_LEVEL_PARAM_ERROR_NC);
}
else
{
// 通らない予定
}
// 分離レベル（iso）をメンバ変数に保存
_iso = iso;
}
public void Single()
{
status.Text = "";
// Phase 1 generate the "terminal input"
PutBlanks();
Set(0, wid);
GetData();
count = 0;
mess = "OKAY";
if (tr == null)
{
tr = db.BeginTransaction(System.Data.IsolationLevel.Serializable);
}
tid = ++Form1._tid;
// Invalidate(true);
// Thread.Sleep(1000);
// Phase 2 (re)start the transaction
while (count++ < 1000)
{
if (FetchDistrict(ref mess))
{
goto bad;
}
if (FetchCustomer(ref mess))
{
goto bad;
}
if (DoOLCount(ref mess))
{
goto bad;
}
total = 0.0M;
for (int j = 0; j < ol_cnt; j++)
{
if (FetchItemData(j, ref mess))
{
goto bad;
}
if (DoOLQuantity(j, ref mess))
{
goto bad;
}
}
DoTotal();
if (DoCommit(ref mess))
{
break;
}
bad:
tr.Rollback();
tr = null;
Set(130, mess);
Invalidate(true);
}
Invalidate(true);
if (btn != null)
{
btn.Enabled = true;
}
}
/// <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();
}
}
}
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)
{
bool mustCloseConnection = false;
string ConString = System.Configuration.ConfigurationSettings.AppSettings["conInsertDB2"].ToString();
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();
}
}
}
}
public async Task <User> GetUser(string userId, CancellationToken ct = default)
{
User returnUser = new User();
try
{
if (!_conn.IsOpen)
{
_conn.Open();
}
DB2Transaction trans = _conn.BeginTransaction();
DB2Command cmd = _conn.CreateCommand();
try
{
cmd.Transaction = trans;
cmd.CommandType = CommandType.Text;
cmd.CommandText = $"SELECT * FROM USER_TABLE WHERE ID = '{userId}'";
using (DB2DataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
await reader.ReadAsync();
returnUser.FirstName = reader[0].ToString().Trim();
returnUser.MiddleName = reader[1].ToString().Trim();
returnUser.LastName = reader[2].ToString().Trim();
returnUser.UserId = reader[3].ToString().Trim().Replace(".", "");
}
else
{
return(new User());
}
}
cmd.Transaction.Commit();
return(returnUser);
}
catch (Exception ex)
{
cmd.Transaction.Rollback();
throw ex;
}
}
catch (Exception ex)
{
if (_conn.IsOpen)
{
_conn.Close();
}
throw ex;
}
finally
{
_conn.Close();
}
}
/// <summary>
/// 开始事务
/// </summary>
public override void BeginTrans()
{
if (_isInTransaction)
{
throw new Exception("当前事务尚未提交!");
}
_trans = _conn.BeginTransaction();
_isInTransaction = true;
}
public void CreateTransaction()
{
try
{
Trans = Conn.BeginTransaction();
}
catch (Exception)
{
throw;
}
}
public void CreateTransaction(IsolationLevel isolationLevel)
{
try
{
Trans = Conn.BeginTransaction(isolationLevel);
}
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 override void BeginTransaction()
//{
// if (conn != null)
// {
// this.tran = this.conn.BeginTransaction();
// this._s = DBStatus.Begin_Trans;
// }
// else
// {
// isneedopentrans_in_open = true;
// }
//}
public override void BeginTransaction(IsolationLevel level)
{
if (conn != null)
{
this.tran = this.conn.BeginTransaction(level);
this._s = DBStatus.Begin_Trans;
}
else
{
isneedopentrans_in_open = true;
}
}
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 bool BeginTransaction()
{
try
{
trans = conn.BeginTransaction();
return(true);
}
catch (Exception ex)
{
LastError = ex.Message;
Logger.WriteLogExcept(LogTitle, ex);
return(false);
}
}
/// <summary>トランザクションのコミット</summary>
/// <remarks>必要に応じて利用する。</remarks>
public override void CommitTransaction()
{
// Txオブジェクトの存在チェック
if (this._tx == null)
{
// nullのためなにもしない。
}
else
{
// トランザクションのコミット
this._tx.Commit();
// nullクリア
this._tx = null;
}
}
/// <summary>トランザクションのロールバック</summary>
/// <remarks>必要に応じて利用する。</remarks>
public override void RollbackTransaction()
{
// Txオブジェクトの存在チェック
if (this._tx == null)
{
// nullのためなにもしない。
}
else
{
// トランザクションのロールバック
this._tx.Rollback();
// nullクリア
this._tx = null;
}
}
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();
}
bool Schedule(int did, int carid, DB2Transaction tr)
{
int oid = 0;
int ocid = 0;
var cmd = db.CreateCommand();
cmd.Transaction = tr;
cmd.CommandText = "select NO_O_ID from NEW_ORDER where NO_W_ID=" + wid + " and NO_D_ID=" + did;
Form1.RecordRequest(cmd, fid, tid);
var s = cmd.ExecuteReader();
if (!s.Read())
{
s.Close();
return(false);
}
oid = (int)s[0];
s.Close();
cmd.CommandText = "delete NEW_ORDER where NO_W_ID=" + wid + " and NO_D_ID=" + did + " and NO_O_ID=" + oid;
Form1.RecordRequest(cmd, fid, tid);
cmd.ExecuteNonQuery();
cmd.CommandText = "select O_C_ID from ORDER where O_W_ID=" + wid + " and O_D_ID=" + did + " and O_ID=" + oid;
Form1.RecordRequest(cmd, fid, tid);
s = cmd.ExecuteReader();
s.Read();
ocid = (int)s[0];
s.Close();
cmd.CommandText = "update \"ORDER\" where O_W_ID=" + wid + " and O_D_ID=" + did + " and O_ID=" + oid + " set O_CARRIER_ID = " + carid;
Form1.RecordRequest(cmd, fid, tid);
cmd.ExecuteNonQuery();
cmd.CommandText = "update ORDER_LINE where OL_W_ID=" + wid + " and OL_D_ID=" + did + " and OL_O_ID=" + oid + " set OL_DELIVERY_DATE='" + DateTime.Now.ToString("o") + "'";
Form1.RecordRequest(cmd, fid, tid);
cmd.ExecuteNonQuery();
decimal amount = 0.0M;
cmd.CommandText = "select sum(OL_AMOUNT) from ORDER_LINE where OL_W_ID=" + wid + " and OL_D_ID=" + did + " and OL_O_ID=" + oid;
s = cmd.ExecuteReader();
s.Read();
amount = util.GetDecimal(s[0]);
s.Close();
cmd.CommandText = "update CUSTOMER where C_W_ID=" + wid + " and C_D_ID=" + did + " and C_ID=" + ocid + " set C_BALANCE =C_BALANCE+" + amount + ",C_DELIVERY_CNT=C_DELIVERY_CNT+1";
Form1.RecordRequest(cmd, fid, tid);
s = cmd.ExecuteReader();
return(true);
}
public bool RollbackTransaction()
{
try
{
trans.Rollback();
return(true);
}
catch (Exception ex)
{
LastError = ex.Message;
Logger.WriteLogExcept(LogTitle, ex);
return(false);
}
finally
{
trans = null;
}
}
public override void Open(string connString)
{
if (conn == null ||
this.conn.State == ConnectionState.Closed)
{
this.conn = new DB2Connection(connString);
}
if (this.conn.State != ConnectionState.Open)
{
this.conn.Open();
}
if (isneedopentrans_in_open)
{
this.tran = conn.BeginTransaction();
isneedopentrans_in_open = false;
this._s = DBStatus.Begin_Trans;
}
else
{
this._s = DBStatus.Open;
}
}
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();
}
}
}
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));
}
/// <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);
}
/// <summary>
/// 预处理用户提供的命令,数据库连接/事务/命令类型/参数
/// </summary>
/// <param name="command">要处理的db2Command</param>
/// <param name="connection">数据库连接</param>
/// <param name="transaction">一个有效的事务或者是null值</param>
/// <param name="commandType">命令类型 (存储过程,命令文本, 其它.)</param>
/// <param name="commandText">存储过程名或都T-SQL命令文本</param>
/// <param name="commandParameters">和命令相关联的db2Parameter参数数组,如果没有参数为'null'</param>
/// <param name="mustCloseConnection"><c>true</c> 如果连接是打开的,则为true,其它情况下为false.</param>
private static void PrepareCommand(DB2Command command, DB2Connection connection, DB2Transaction transaction, CommandType commandType, string commandText, DB2Parameter[] commandParameters, out bool mustCloseConnection)
{
if (command == null)
{
throw new ArgumentNullException("command");
}
if (commandText == null || commandText.Length == 0)
{
throw new ArgumentNullException("commandText");
}
// If the provided connection is not open, we will open it
if (connection.State != ConnectionState.Open)
{
mustCloseConnection = true;
connection.Open();
}
else
{
mustCloseConnection = false;
}
// 给命令分配一个数据库连接.
command.Connection = connection;
// 设置命令文本(存储过程名或SQL语句)
command.CommandText = commandText;
// 分配事务
if (transaction != null)
{
if (transaction.Connection == null)
{
throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
}
command.Transaction = transaction;
}
// 设置命令类型.
command.CommandType = commandType;
// 分配命令参数
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}
return;
}
public EstacionamentoDao(DB2Connection conexao, DB2Transaction transacao = null)
{
this.conexao = conexao;
this.transacao = transacao;
}
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);
}
private static void Update <T>(DbConnection connection, string configKey, T configSection, DB2Transaction transaction = null)
{
var serializer = new XmlSerializer(typeof(T));
var outStream = new StringWriter(CultureInfo.InvariantCulture);
serializer.Serialize(outStream, configSection);
var content = outStream.ToString();
using (var command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
if (transaction != null)
{
command.Transaction = transaction;
}
command.CommandText = "UPDATE NICK_TOOLS.CONFIGURATION SET XMLVALUE = ? WHERE KEY = ?";
command.Parameters.Add(new DB2Parameter {
Value = content
});
command.Parameters.Add(new DB2Parameter {
Value = configKey
});
var result = command.ExecuteNonQuery();
if (result == 0)
{
command.CommandText = "INSERT INTO NICK_TOOLS.CONFIGURATION(KEY, VALUE, XMLVALUE) VALUES (?, 'See XMLVALUE', ?)";
command.Parameters.Clear();
command.Parameters.Add(new DB2Parameter {
Value = configKey
});
command.Parameters.Add(new DB2Parameter {
Value = content
});
command.ExecuteNonQuery();
}
}
}
