/// <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("После закрытия блокировки");
}