Using TransactionScope returns no data or gives exception

[freakshow]

I am using dotConnect for Oracle 5.0.22.0. This scenario does not work in either direct or non-direct mode.

Background: Want to do integration tests

Scenario: Create a new record, save changes and verify the result (read it back).

The scenario works as expected when I am not using TransactionScope.

In direct mode I get no record back.
In non-direct mode I get an ORA-01453 error message (SET TRANSACTION must be first statement of transaction)

Code: Select all

using (TransactionScope trans = new TransactionScope())
{
	using (Entities entities = new Entities())
	{
	    var qry = from e in entities.Table1
		      select e;
	    foreach (Table1 record in qry)
	    {
		Console.WriteLine(record.Id);
	    }
	    Console.WriteLine("-----------------------");

	    Table1 newRecord = Table1.CreateTable1("SOMEID");
	    entities.AddToTable1(newRecord);
	    entities.SaveChanges();

	    var qry2 = from e in entities.Table1
		      select e;
	    
	    foreach (Table1 record in qry)
	    {
	    	Console.WriteLine(record.Id);
	    }
	    Console.WriteLine("-----------------------");
	}
}

[AndreyR]

This problem is associated with transactions Isolation level.
It is already fixed in the recent Beta of dotConnect.
As a workaround you can try creating TransactionScope in the following way:

Code: Select all

     TransactionOptions to = new TransactionOptions();
      to.IsolationLevel = IsolationLevel.ReadCommitted;
      using (TransactionScope trans = new TransactionScope(TransactionScopeOption.Required, to))

[freakshow]

Thanks, but this does not work in Direct mode (it works in non-direct mode).

[AndreyR]

We are investigating the Direct mode problem.

[freakshow]

Any updates for this?

[AndreyR]

Please try to reproduce the problem using the latest dotConnect for Oracle 5.20.27 Beta build. We have added some fixes to it.
If the problem persists, please send us (support * devart * com, subject "Oracle Transaction Scope Direct problem") a small test project illustrating the error.