C# TransactionScope with OleDB and Oracle

Question

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

Answer 1

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!