CODEWITHSUNDEEP
c#oracle-databasetransactionsoledbconnection-pooling
Dominic Cotton
Dominic Cotton

Reputation: 819

C# TransactionScope with OleDB and Oracle

My current application has all database operations in a giant Using statement with a connection to ensure that the transaction is committed or rolled back in full, currently if i have common methods they pass the current open OleDbConnection so that it can be used.

I would like to use TransactionScope in place of the outer using section. Please see my test code below:

private void Test() {        
    string _connectionString = "Provider=OraOLEDB.Oracle.1;Password=XXXXXXXX;Persist Security Info=True;User ID=XXXXXXXX;Data Source=XXXXXXX;min pool size=1;incr pool size=5;decr pool size=2;connection timeout=60;";
    using (TransactionScope _ts = new TransactionScope(TransactionScopeOption.Required))
    {
        using (OleDbConnection _cn = new OleDbConnection(_connectionString))
        {
            _cn.Open(); // Errors Here!
            using (OleDbCommand _cmd = new OleDbCommand())
            {
                _cmd.Connection = _cn;
                _cmd.CommandText = "insert into testtable (TEST) values ('FIRST')";
                _cmd.CommandType = CommandType.Text;
                _cmd.ExecuteNonQuery();
            }
        }

        using (OleDbConnection _cn = new OleDbConnection(_connectionString))
        {
            _cn.Open();
            using (OleDbCommand _cmd = new OleDbCommand())
            {
                _cmd.Connection = _cn;
                _cmd.CommandText = "insert into testtable (TEST) values ('SECOND')";
                _cmd.CommandType = CommandType.Text;
                _cmd.ExecuteNonQuery();
            }
        }
    }
}

The error i receive is "Unable to enlist in the transaction." I have read that Oracle doesn't like using a TransactionScope (Problems with TransactionScope and Oracle) , but it seems to fit with what i need to achieve. I have found very little information about how to bridge single transactions across connection pooled connections.

EDIT - 11th Feb

I switched from OleDB to ODP.Net and managed to get an official Oracle ORA error out... 

ORA-02048: attempt to begin distributed transaction without logging on

Sadly from what i can find i think its an Oracle bug? I have found forum posts which suggest that version 10.2.0.2 has this bug, but i am on 10.2.0.4?

Hoping somebody can help! Thanks

Upvotes: 1

Views: 2945

Answers (2)

Avi e
Avi e

Reputation: 31

To use with TransactionScope the connection string must contain "enlist=dynamic"

https://docs.oracle.com/database/121/ODPNT/InstallConfig.htm#r6c1-t14

Specifies whether the application enlists in distributed transactions explicitly after an OracleConnection.Open method invocation through EnlistTransaction() or EnlistDistributedTransaction(). To configure ODP.NET to enable dynamic enlistment programmatically, the connection string must contain "enlist=dynamic".

connection string for example: enlist=dynamic;User Id=USER;Password=pass;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=TestDB)));

Upvotes: 3

Dominic Cotton
Dominic Cotton

Reputation: 819

So, my answer was a couple of things:

Firstly I needed to install the OracleMTS service on my client. Secondly i changed from OleDB (via Oracle.ManagedDataAccess v12) to Oracle.DataAccess v11, and it works!

I discovered that v12 of the ODP.Net client and 10.2.0.4 have a bug where the distributed transaction fails, but version 11 works. Still puzzled why it doesn't work with OleDB but i have resolved it now. Hope this can help somebody else in my position!

Upvotes: 0

Related Questions