Esempio n. 1
0
        /// <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));
        }
Esempio n. 3
0
        private bool Enlist(OracleConnection connection)
        {
            var ambientTransaction = System.Transactions.Transaction.Current;

            if (ambientTransaction != null)
            {
                connection.EnlistTransaction(ambientTransaction);
                return(true);
            }
            else
            {
                return(false);
            }
        }
Esempio n. 4
0
        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...");
            }
        }
Esempio n. 5
0
        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("После закрытия блокировки");
        }