/// <summary> /// 明確式交易 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void button15_Click(object sender, EventArgs e) { string connectionString = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.18.196.18)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=PTWSFDEV)));Persist Security Info=False;User ID=sdd1test;Password=PEGATRON;Min Pool Size=2;Max Pool Size=5;Connection Timeout=10"; try { CommittableTransaction transaction = new CommittableTransaction(); //必須參考 System.Transactions OracleConnection cn = new OracleConnection(connectionString); cn.Open(); cn.EnlistTransaction(transaction); // 從這裡就可以看出,可以跨連線是他的優點 OracleCommand cmd = new OracleCommand(); cmd.Connection = cn; cmd.CommandType = CommandType.Text; cmd.CommandText = "INSERT INTO T1 (F1,F2) VALUES('SDD1','Jetq')"; int count = cmd.ExecuteNonQuery(); transaction.Rollback(); // 還原 //transaction.Commit(); // 確認 cn.Close(); } catch (OracleException ex) { MessageBox.Show(ex.Message); } }
private OracleDbConnection CreateOracleDbConnectionInAPossiblyAmbientTransaction(OracleConnection connection) { var ambientTransaction = System.Transactions.Transaction.Current; if (ambientTransaction != null) { connection.EnlistTransaction(ambientTransaction); return(new OracleDbConnection(connection, null)); } var transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted); return(new OracleDbConnection(connection, transaction)); }
private bool Enlist(OracleConnection connection) { var ambientTransaction = System.Transactions.Transaction.Current; if (ambientTransaction != null) { connection.EnlistTransaction(ambientTransaction); return(true); } else { return(false); } }
private void button5_Click(object sender, EventArgs e) { CommittableTransaction _cmtTran = new CommittableTransaction(); try { //Connect to the first database instance and create a new record in //the Invoice table string _connstring = "Data Source=localhost/NEWDB;User Id=EDZEHOO;Password=PASS123;"; OracleConnection _connObj = new OracleConnection(_connstring); _connObj.Open(); OracleCommand _cmdObj = _connObj.CreateCommand(); //Clear the appropriate tables _cmdObj.CommandText = "DELETE FROM Invoice WHERE InvID='B01'"; _cmdObj.ExecuteNonQuery(); _connObj.EnlistTransaction(_cmtTran); _cmdObj.CommandText = "INSERT INTO Invoice(InvID, InvDate, Remarks) VALUES(:InvID, SYSDATE, :Remarks)"; _cmdObj.Parameters.Add(new OracleParameter("InvID", "B01")); _cmdObj.Parameters.Add(new OracleParameter("Remarks", "Sample invoice")); _cmdObj.ExecuteNonQuery(); MessageBox.Show("Invoice record inserted successfully"); _connObj.Close(); _connObj.Dispose(); _connObj = null; //Connect to the second database instance and create a new record in //the Receipt table _connstring = "Data Source=localhost/SECONDDB;User Id=SYSTEM;Password=admin;"; _connObj = new OracleConnection(_connstring); _connObj.Open(); _connObj.EnlistTransaction(_cmtTran); _cmdObj = _connObj.CreateCommand(); _cmdObj.CommandText = "INSERT INTO Receipt(ReceiptID, ReceiptDate, Remarks) VALUES(:ReceiptID, SYSDATE, :Remarks)"; _cmdObj.Parameters.Add(new OracleParameter("ReceiptID", "R01")); _cmdObj.Parameters.Add(new OracleParameter("Remarks", "Sample receipt")); _cmdObj.ExecuteNonQuery(); _connObj.Close(); _connObj.Dispose(); _connObj = null; _cmtTran.Commit(); } catch (Exception ex) { _cmtTran.Rollback(); MessageBox.Show("Uh oh, rollback initiated..."); } }
static void Main2(string[] args) { //TestNpgsql(); string pooling = "Data Source=rac1; User Id=TEST; Password=TEST; Enlist=true; Pooling=true; Min Pool Size=1; Max Pool Size=1;Metadata Pooling=false;"; string noPooling = "Data Source=rac1; User Id=TEST; Password=TEST; Enlist=true; Pooling=false;Metadata Pooling=false;"; //Stopwatch sw = Stopwatch.StartNew(); //for (int i = 0; i < 100; i++) //{ // using (OracleConnection conn = new OracleConnection(pooling)) // { // conn.Open(); // } //} //sw.Stop(); //Console.WriteLine(sw.Elapsed); //sw.Restart(); //for (int i = 0; i < 100; i++) //{ // using (OracleConnection conn = new OracleConnection(noPooling)) // { // conn.Open(); // } //} //sw.Stop(); //Console.WriteLine(sw.Elapsed); using (OracleConnection conn = new OracleConnection(noPooling)) { conn.Open(); using (OracleCommand cmd = new OracleCommand("UPDATE t1 SET id = 0", conn)) { cmd.ExecuteNonQuery(); } using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { Timeout = TimeSpan.Zero, IsolationLevel = IsolationLevel.ReadCommitted })) { conn.EnlistTransaction(Transaction.Current); conn.IsEnlisted(); using (OracleCommand cmd = new OracleCommand("UPDATE t1 SET id = 2", conn)) { cmd.ExecuteNonQuery(); } using (TransactionScope ts2 = new TransactionScope(TransactionScopeOption.Suppress)) { using (OracleCommand cmd = new OracleCommand("UPDATE t1 SET id = 5", conn)) { cmd.ExecuteNonQuery(); } } using (OracleCommand cmd = new OracleCommand("UPDATE t1 SET id = 4", conn)) { cmd.ExecuteNonQuery(); } } conn.IsEnlisted(); using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { Timeout = TimeSpan.Zero, IsolationLevel = IsolationLevel.ReadCommitted })) { conn.EnlistTransaction(Transaction.Current); using (OracleCommand cmd = new OracleCommand("UPDATE t1 SET id = 3", conn)) { cmd.ExecuteNonQuery(); } ts.Complete(); } } //ReadLine("До открытия соединения"); //using (OracleConnection conn = new OracleConnection(noPooling)) //{ // conn.Open(); // ReadLine("После открытия соединения"); // SignPatchLocker.Lock(1, conn); // ReadLine("После наложения блокировки"); //} //ReadLine("После закрытия блокировки"); //using (OracleConnection conn = new OracleConnection(noPooling)) //{ // conn.Open(); // ReadLine("После открытия соединения"); // SignPatchLocker.Lock(1, conn); // ReadLine("После наложения блокировки"); //} //ReadLine("После закрытия блокировки"); }