Problem with TransactionScope

[marcc]

Hello,

I seem to have a problem when doing inserts within the context of a transaction, using TransactionScope.

I'm using a simple model with 2 tables: View en ViewLayout. There's a foreign key from ViewLayout to View.

This is the code to do an insert into View & ViewLayout within a transaction (DB is the datacontext):

Code: Select all

using (var scope = new TransactionScope())
{
    using (var db = new DB())
    {
       var v = new View();
       v.ViewID = Guid.NewGuid();
       v.Name = "V1";

       db.Views.InsertOnSubmit(v);
       db.SubmitChanges();
         
       var vl = new ViewLayout();
       vl.ViewLayoutID = Guid.NewGuid();
       vl.ViewID = v.ViewID;
       vl.Name = "VL1";

       db.ViewLayouts.InsertOnSubmit(vl);
       db.SubmitChanges();
    }

    scope.Complete();
}

The problem is that a second connection is used for the second insert.
Since the transaction is not committed yet, that insert fails because the
result of the first insert is not visible yet.

This is wat dbmonitor is showing:


7/09/2010 17:21:42 n/a dotConnect for Oracle monitoring is started Complete
7/09/2010 17:21:42 0,016 Creating pool manager Complete
7/09/2010 17:21:42 0,000 Creating pool with connections string: "User Id=x;Password=y;Server=voracle01;Direct=True;Sid=WICTEST;Persist Security Info=True;" Complete
7/09/2010 17:21:42 0,000 Creating object Complete
7/09/2010 17:21:42 0,594 Open connection: "User Id=x;Password=y;Server=voracle01;Direct=True;Sid=WICTEST;Persist Security Info=True;" Complete
7/09/2010 17:21:42 0,531 Connect: "User Id=x;Password=y;Server=voracle01;Direct=True;Sid=WICTEST;Persist Security Info=True;" Complete
7/09/2010 17:21:43 0,000 Creating object Complete
7/09/2010 17:21:43 0,000 Prepare: INSERT INTO SRG.KPD_VIEW (VIEWID, NAME) VALUES (:p1, :p2) Complete
7/09/2010 17:21:43 0,031 Execute: INSERT INTO SRG.KPD_VIEW (VIEWID, NAME) VALUES (:p1, :p2) Complete
7/09/2010 17:21:43 0,281 Open connection: "User Id=x;Password=y;Server=voracle01;Direct=True;Sid=WICTEST;Persist Security Info=True;" Complete
7/09/2010 17:21:43 0,265 Connect: "User Id=x;Password=y;Server=voracle01;Direct=True;Sid=WICTEST;Persist Security Info=True;" Complete
7/09/2010 17:21:43 0,000 Creating object Complete
7/09/2010 17:21:43 0,000 Prepare: INSERT INTO SRG.KPD_VIEWLAYOUT (VIEWLAYOUTID, VIEWID, DESCRIPTION, XML) VALUES (:p1, :p2, :p3, :p4) Complete
7/09/2010 17:21:43 58,609 Execute: INSERT INTO SRG.KPD_VIEWLAYOUT (VIEWLAYOUTID, VIEWID, DESCRIPTION, XML) VALUES (:p1, :p2, :p3, :p4) Complete
7/09/2010 17:22:42 0,000 Connection is returned to pool. Pool has 2 connection(s). Error
7/09/2010 17:22:46 0,016 Close connection Complete
7/09/2010 17:22:46 0,016 Connection is returned to pool. Pool has 2 connection(s). Complete


I wasn't expecting a second connection to be used.

I have to a that I'm using direct mode.

Can you please help me with this?

Thanks,

Marc

[AndreyR]

There is a number of approaches.
The first is to use one SubmitChanfges call, like in the following code sample (I presume that there is an association in the model for the View and ViewLayout entities, and the View entitiy is the parent for the ViewLayout):

Code: Select all

using (var scope = new TransactionScope()) 
{ 
    using (var db = new DB()) 
    { 
       var v = new View(); 
       v.ViewID = Guid.NewGuid(); 
       v.Name = "V1"; 

       var vl = new ViewLayout(); 
       vl.ViewLayoutID = Guid.NewGuid(); 
       vl.View = v; 
       vl.Name = "VL1"; 

       db.Views.InsertOnSubmit(v); 
       db.SubmitChanges(); 
    } 

    scope.Complete(); 
}

The second approach is to open the context.Connection explicitly. This will let the context know that you plan to use only one connection instance:

Code: Select all

using (var scope = new TransactionScope()) 
{ 
    using (var db = new DB()) 
    { 
       db.Connection.Open();       

       var v = new View(); 
       v.ViewID = Guid.NewGuid(); 
       v.Name = "V1"; 

       db.Views.InsertOnSubmit(v); 
       db.SubmitChanges(); 
          
       var vl = new ViewLayout(); 
       vl.ViewLayoutID = Guid.NewGuid(); 
       vl.ViewID = v.ViewID; 
       vl.Name = "VL1"; 

       db.ViewLayouts.InsertOnSubmit(vl); 
       db.SubmitChanges(); 
    } 

    scope.Complete(); 
}

[marcc]

Andrey,

thanks for your reply.

For a number of reasons, the first approach won't work for me.

The second one might do it, but I'm wondering:

I've tried the original approach with SQL Server, this seems to work fine.
Is this because of differences in how SQL Server and Oracle work,
or is it because of how DotConnect is implemented?

Wich leads me to the next question: Is it possible to have DotConnect
behave in the same way as Microsoft Linq to SQL?

Thanks,

Marc

[StanislavK]

Please try the Beta 6.0 build of dotConnect for Oracle:
http://www.devart.com/dotconnect/oracle/download.html

If the problem persists, please specify the error that occurs when the connection is closed (you can check it with dbMonitor: select the Error tab when the corresponding line is highlighted) and, if possible, send us a small test project.

[marcc]

After installing the Beta, I get 'Sorry, your trial period has expired'.
(This is on Windows7 64 bit).

Regards,

Marc

[StanislavK]

We've answered you by mail.