Oracle Transaction enlist problem

[HakanCicek]

Hi,
I am trying to use TransactionScope in my solution. I have a test application that has a wcf service hosted on windows service. What i want to do is just insert a record into db table within transaction.

UI button click event (Calls wcf service method)

Code: Select all

 private void uxAddCountryButton_Click(object sender, EventArgs e)
        {
            TransactionOptions _TransOps = new TransactionOptions();
            _TransOps.IsolationLevel= System.Transactions.IsolationLevel.ReadCommitted;
            _TransOps.Timeout = new TimeSpan(0, 2, 0);

            using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, _TransOps))
            {
                try
                {
                        _TransService.InsertCountry(int.Parse(uxCountryID.Text.Trim()), uxCountryName.Text.Trim());
                        scope.Complete();
                }
                catch (FaultException tex)
                {
                    MessageBox.Show(tex.Message + "\n\r" + tex.Detail.Message + "\n\r" + tex.Detail.Details);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message + "\n\r" + ex.StackTrace);
                }
            }
        }

WCF Service And Method

Code: Select all

 [ServiceBehavior(InstanceContextMode = InstanceContextMode.PerSession
                    , TransactionIsolationLevel = System.Transactions.IsolationLevel.ReadCommitted,
                    TransactionAutoCompleteOnSessionClose = false)]
    public class Service1 : IService1
    {
        string cs = "POOLING=FALSE;User Id=XXXX;Password=XXXX;Server=XXXXX;Direct=True;Sid=XXXX1;Persist Security Info=True;Transaction Scope Local=true;";

       [OperationBehavior(TransactionScopeRequired = true,   TransactionAutoComplete = false)]
        public void InsertUlke(int kod, string aciklama)
        {
            try
            {
                OracleConnection Conn = new OracleConnection(cs);
                OracleCommand comm = new OracleCommand("INSERT INTO TEST_COUNTRY (ID,DESC) VALUES (:ID,:DESC)", Conn);
                comm.Parameters.Add(":ID", OracleDbType.Number);
                comm.Parameters["ID"].Value = kod;
                comm.Parameters.Add(":DESC", OracleDbType.VarChar);
                comm.Parameters["DESC"].Value = aciklama;
                Conn.Open();
                comm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                   throw new FaultException(new GeneralFaultException(ex), new FaultReason(ex.Message));
            }
        }

}

When i execute this code with Transaction Scope Local=true parameter in connectionstring i get an exception that says cannot enlist transaction. Possibly single phase transaction was already used. (on Conn.Open(); line).

If i remove Transaction Scope Local=true from the connectionstring then it saysTransaction has aborted while disposing the transaction. I have downloaded version 6.50.237 of dotconnect for oracle but nothing changed. Dou you have any idea?

Note : i have tried to connect by oci but its same. Connectionstring was;
Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=XXXX)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XXXXX)));User Id=XXXX;Password=XXXXX;Transaction Scope Local=true;POOLING=FALSE

[Shalex]

Is there any inner exception (like ORA-01747)? DESC is Oracle key word. Try using a table without the DESC column and a command without the DESC parameter. Notify us about the results.

[HakanCicek]

Actually table name and column names are fake. Original names are in Turkish and i changed them not to make you confuse. And there are no inner exceptions

[Shalex]

We are investigating the issue. We will post here about the results.

[Shalex]

Your code works in our environment if TransactionAutoComplete is set to true (it is false in your sample). This attribute should be set to true to complete transaction.

If this doesn't help, please send us your small complete WCF Service project and the project that uses WCF Service.