How to use TransactionScope with OleDB in .NET?

811368

We are developing a .NET application using an oracle database and OleDB.
However, whenever we try to execute a command within a "TransactionScope" we run into an exception when opening the connection.

I have included a simple sample to demonstrate this.
This same code runs successfully if we comment out the TransactionScope lines.


Does anyone else have experience with this?
Any help is appreciated.

Thanks,

-Joel


connectionString = "Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=theHost)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=theServiceName)));User Id=theID;Password=thePassword;";

query = "update test_table set test_column = 8 where test_id = 4";


private static int OleTest(string connectionString, string query)
{
int results;
using (TransactionScope scope = new TransactionScope())
{
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
using (OleDbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = query;
conn.Open();//<-- throws the exception here
results = cmd.ExecuteNonQuery();
conn.Close();
}
}
scope.Complete();
}
return results;
}

Exception thrown:

System.ArgumentException

Value does not fall within the expected range.

at System.Data.Common.NativeMethods.ITransactionJoin.JoinTransaction(Object punkTransactionCoord, Int32 isoLevel, Int32 isoFlags, IntPtr pOtherOptions)
at System.Data.OleDb.OleDbConnectionInternal.EnlistTransactionInternal(Transaction transaction, Boolean forcedAutomatic)
at System.Data.OleDb.OleDbConnection.Open()
at OleDBTransactionScope.Program.OleTest(String connectionString, String query) in C:\SvnRepository\OleDBTransactionScope\OleDBTransactionScope\Program.cs:line 35
at OleDBTransactionScope.Program.Main(String[] args) in C:\SvnRepository\OleDBTransactionScope\OleDBTransactionScope\Program.cs:line 16

814760

Hello Dear,

This is actuall connection string. Hope this will help you.

<add name="ConnectionString" connectionString="Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=199.166.166.166)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=test)));User ID=test;Password=test;Unicode=True;Omit Oracle Connection Name=False" providerName="System.Data.OracleClient"/>
</connectionStrings>

Best of luck

Chouhan