Problem with CSLA 4.3 on a SQL CE 3.5 SP2 data layer

Problem with CSLA 4.3 on a SQL CE 3.5 SP2 data layer

Old forum URL: forums.lhotka.net/forums/t/11708.aspx


mirq posted on Saturday, November 17, 2012

Hi,

Since there's no any search box here, I decided to make a new post.

I have a question: how can I use csla objects  with methods marked  with TransactionScope type attribute, when having as a data access layer implementation , a SQL CE 3.5 file ?

It seems that SQ CE doesn't allow TransactionScope when multiple connections have to be made (parent + children update)

"In a transaction scope, only one SqlCeConnection object can be enlisted that too if no other transaction manager is already enlisted into the transaction scope."

 

JonnyBee replied on Saturday, November 17, 2012

Look at the Csla.Data.ConnectionManager<T>. 

This support class will store the Connection object in Thread.LocalStore and allow you to "reuse" the same connection without having to pass it around to all objects as a parameter.

mirq replied on Saturday, November 17, 2012

Im am using the ConectionManager when I update every object. In my particular case, when I have one editabe root containing one or more editable children (an editable list), the root is updated through DataPortal_Update() wich will finally call a update method in the DAL implementation of the root object. That method use a connectionmanager :

 using (var ctx = ConnectionManager<SqlCeConnection>.GetManager("LocalDb"))

Then, back in the business object method DataPortal_Update of the root, there is a call to FieldManager.UpdateChildren(this) . I supose this call will lead finally to execution of the method Update() from each child of the root (in DAL implementation class). The problem is that this method has its own  using (var ctx = ConnectionManager<SqlCeConnection>.GetManager("LocalDb")) sentence.

It seems to me that this ctx object is not the same object the root created. The question is, how can I make the root to create a ctx (in the Thread.LocalStore as you said) and pass it then to all its children. I believe all the CRUD operations should be made through one single connection, in order to be accepted by SQL CE as a TransactionScope operation. But I am not very sure.

 

Thank you

 

 

JonnyBee replied on Saturday, November 17, 2012

You will use the same connection so long as it is in "scope".

IE: The DataPortal_XYZ  method must "OWN" the connection manager.

 

pecen replied on Wednesday, July 03, 2013

Hi,

I recently ended up with the same problem, and the code is very similar to the problem described earlier. Jonny, how can I make the DataPortal_XYZ method to own the connection manager?

Thanks,

/Peter

JonnyBee replied on Wednesday, July 03, 2013

    [Transactional(TransactionalTypes.TransactionScope)]
    protected override void DataPortal_Update()
    {
      using (var ctx = ConnectionManager<SqlCeConnection>.GetManager("LocalDb"))
      {
        // make sure  all the code that uses connetion to database is
        // called within this block
        // this block defines the scope in which you will always get the same 
        // connection 
        
        // run SQLs
        .....
 
        // propagate to children
        FieldManager.UpdateChildren(this.Id);
 
      }  // when execution leaves this scope the connection is closed and released.
 
      // If you should have code like this it will fail as it tries to 
      // create a new connection and enlist in transaction and SqlCE only supports
      // one connection to enlist in transaction
      using (var ctx = ConnectionManager<SqlCeConnection>.GetManager("LocalDb"))
      {
 
      }
    }

And also make sure to follow the pattern.
using (var ctx - uses reference counting to find out when it leaves the outermost code
block and only then will the connection be closed and released.

Failure to follow the pattern may lead to next database call reuse the connection that was used in the
previous call.

pecen replied on Thursday, July 04, 2013

I do exactly as you describe it, and it still fails. If I use a SQLite db I get the error message "Attempt to write to readonly database" which is not true since I in a step earlier write to the same db but to another table (and that works), and if I use Sql Ce instead I get the error message "The connection object can not be enlisted in transaction scope". 

Below you can see my code:

[

 

Transactional(TransactionalTypes.TransactionScope)]

 

 

protected override void DataPortal_Insert()

{

 

 

   using (var dalManager = DalFactory.GetManager())

   {

 

 

      var dal = dalManager.GetProvider<IFillUpDal>();

 

 

      using (BypassPropertyChecks)

      {

 

 

         var data = new FillUpDto { Id = Id, CarId = CarId, FillUpDate = FillUpDate, PartialFillUp = PartialFillup };

         dal.Insert(data);

         Id = data.Id;

      }

      FieldManager.UpdateChildren(

 

this);

   }

}

When the above code runs the code FieldManager.UpdateChildren(this), the application enters the following code, and the exception occurs on the first using clause:

 

 

public void Insert(LineItemDto data)

{

 

 

   using (var ctx = ConnectionManager<SqlCeConnection>.GetManager("FuelLogDb"))

   {

 

 

      using (var cm = ctx.Connection.CreateCommand())

      {

         ......

      }

   }

}

 

pecen replied on Thursday, July 04, 2013

Don't understand why my previous post formats so bad after I post (tried to fix it)

JonnyBee replied on Thursday, July 04, 2013

My entire point is that the DataPortal_Insert MUST OWN THE CONNECTIONMANAGER

Which means that

   using (var ctx = ConnectionManager<SqlCeConnection>.GetManager("FuelLogDb"))
   {
          // all updated must happen within this using block!!!!!!!!

   } 

should be placed in DataPortal_Insert and ALL updates to database must happen within that using block.

As-is your code will most likely create a new connection in each Child_Insert and that is NOT allowed when using SqlCe.  

pecen replied on Friday, July 05, 2013

Again, as I see it, my child updating occurs within the using-block in DataPortal_Insert through FieldManager.UpdateChildren(this). It's exactly as described in the EncapsulatedInvokeDto-example from the DataAccess-part in the e-book series.

If this is not possible (using FieldManager.UpdateChildren()) then I guess I'll have to move the code from the DAL to the Business class, or just remove the [Transactional...]-statement.

JonnyBee replied on Friday, July 05, 2013

NO - your DataPortal_Update/DataPortal_Insert does NOT own and define the lifetime of the connection. That is the entire point!!!!!

In SqlCe there can be only one connection that enlists into the transaction and YOUR code can only use that one connection on updates. Hence - the DataPortal_XYZ must define the scope of the connection instance so that all childs get the same connection object!!!!!!!!!!!

It is NOT FieldManger.UpdateChildren your are struggling with - it is understanding how to make your child objects use that SAME connection object rather than creating a new connection object every time.

Copyright (c) Marimer LLC