Oracle Transaction enlist problem

Oracle Transaction enlist problem

Postby HakanCicek » Tue 01 Nov 2011 15:50

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
HakanCicek
 
Posts: 2
Joined: Tue 01 Nov 2011 15:15

Postby Shalex » Fri 04 Nov 2011 17:24

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.
Shalex
Devart Team
 
Posts: 7530
Joined: Thu 14 Aug 2008 12:44

Postby HakanCicek » Mon 14 Nov 2011 13:32

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
HakanCicek
 
Posts: 2
Joined: Tue 01 Nov 2011 15:15

Postby Shalex » Mon 21 Nov 2011 16:37

We are investigating the issue. We will post here about the results.
Shalex
Devart Team
 
Posts: 7530
Joined: Thu 14 Aug 2008 12:44

Postby Shalex » Wed 23 Nov 2011 14:00

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.
Shalex
Devart Team
 
Posts: 7530
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle