Hi. I have a problem with transaction using SQL Compact when using BatchProccess.

Has we all know that SQL Compact does not support nested transaction, but the BatchProcess always creates a new transaction making it impossible to set transaction on the first level.

Is there any way to tell the BatchProcess or the DataAccessAdapter that there exists a transaction, and in return do not create a transaction of there own ??

This is what I like to do, where the services use database components descried below.

using (TransactionScope scope = new TransactionScope())
{
       myFirstService.Save(listofStuffToSave);
       mySecondService.Save(anotherListOfStuffToSave);
       scope.Complete();
}

Here is our code that does the commit to the database from within a abstract base database component.

       /// <summary>
        /// Commit save list and delete list.
        /// </summary>
        /// <param name="saveList">List of entites to save</param>
        /// <param name="deleteList">List of entites to delete</param>
        /// <returns></returns>
        public int Save(EntityCollection<CommonEntityBase> saveList, EntityCollection<CommonEntityBase> deleteList)
        {
            Logger.Debug("Save");
            int numberChanged = 0;

            try
            {
                using (DataAccessAdapter adapter = new DataAccessAdapter())
                {
                    BatchProcess batchProcess = new BatchProcess();
                    batchProcess.CollectionsToSave = saveList;
                    batchProcess.CollectionsToDelete = deleteList;
                    numberChanged = batchProcess.Commit(adapter);
                } 
            }
            catch (Exception ex)
            {
                Logger.Error("Save: Exception", ex);
                IExceptionMapping exMapping = ExceptionMappingFactory.GetMapper<IExceptionMapping>();
                exMapping.HandleDalException(ex);
            }
            return numberChanged;
        }

if we use this code for SQL Compact scenario we get 'The connection object can not be enlisted in transaction scope.' .

It seams that the adapter already knows that there exists a System Transaction from the property InSystemTransaction is set to true, when transaction have been created.

We are setting SqlServerDQECompatibilityLevel = 4 from the app.config.

I thought that that would be enough, but the DataAccessAdapter still has the CompatibilityLevel as null.

We are using the same components for Oracle and SQL Server (Compact as well), just be switching out the DalDBSpecific assemblies, works like a charm, except for transaction scenarios for SQL Compact.

llbgen Version: ORMSupportClasses.NET20 3.1.11.0518
DQE.SqlServer.NET20 3.1.11.0221

So in short, Is there a way to force the BatchProcess not to create a transaction when in System Transaction , so that we don't get nested transactions ??

solution is needed !!

best regards thorri.

What's a "BatchProcess"? Do you mean a UnitOfWork?

This is SQL CE Desktop version not the Compact Framework version, right?

Hi. Thanks for quick replay during the christmas traffic.

we are using SQL Compact Framework version 3.5 using SD.LLBLGen.Pro.DQE.SqlServer.NET20. setting the SqlServerDQECompatibilityLevel to 4.

Well a BatchProcess is a wrapper at our side, thought at first that this was part of llbgen .. my bad there ..

it just a simple wrapper,it walks on the entities in the list and saves or deletes them.. ( see code below) ..

I have been playing with this and still get the same scope exception, it seems that the DataAccessAdapter always creates a new transaction Scope.

So when saving entities with two different data adapters within a a transaction scope that is the cause.

it does not matter if I disable adapter.StartTransaction in the code below. or just save a single entity.

Our batch process.

        public int Commit(IDataAccessAdapter adapter)
        {

                if (!adapter.IsTransactionInProgress)
                   adapter.StartTransaction(System.Data.IsolationLevel.ReadCommitted, "bp");

            int totalAmountAffected = 0;
            try
            {
                for (int i = _collectionsToDelete.Count - 1; i >= 0; i--)
                {
                    EntityBase2 entity = (EntityBase2)_collectionsToDelete[i];

                    if (adapter.DeleteEntity(entity))
                        totalAmountAffected++;
                }

                if (_collectionsToSave.Count > 0)
                {
                    _logger.Debug("Commit: Save Collection");
                    totalAmountAffected += adapter.SaveEntityCollection(_collectionsToSave);
                }

               adapter.Commit();
            }
            catch (Exception ex)
            {
                adapter.Rollback();
                throw ex;
            }
            return totalAmountAffected;
        }

best regards.

hi. I am unable to create a new instance a DataAccessAdapter.

DataAccessAdapter adapter = new DataAccessAdapter()

You should use normal transactions. You can make your BatchProcess receives the IDataAccessAdapter instance and work with it inside. Outside you create the transaction and commit.

using (var adapter = new DataAccessAdapter())
{
     adapter.StartTransaction(IsolationLevel.Serializable, "MyTx");

     myFirstService.Save(listofStuffToSave, adapter);
     mySecondService.Save(anotherListOfStuffToSave, adapter);
    
     adapter.Commit();
}

Hi. if it would be this easy, then I would have solved this way long time ago. .

I have no access to the DataAccessAdatpers from the business logic, this code runs as well on oracle and SQL server. And by the way it is not a good practice to have data-access component in the logic.

Here is the stack trace if helps in any way.

 at System.Data.SqlServerCe.SqlCeConnection.Enlist(Transaction tx)
   at System.Data.SqlServerCe.SqlCeConnection.Open()
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.OpenConnection() in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 1089
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.StartTransaction(IsolationLevel isolationLevelToUse, String name) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 881
   at SD.LLBLGen.Pro.ORMSupportClasses.UnitOfWork2.Commit(IDataAccessAdapter adapterToUse, Boolean autoCommit) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\UnitOfWork\UnitOfWork2.cs:line 879
   at SD.LLBLGen.Pro.ORMSupportClasses.UnitOfWork2.Commit(IDataAccessAdapter adapterToUse) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\UnitOfWork\UnitOfWork2.cs:line 706
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.SaveEntityCollection(IEntityCollection2 collectionToSave, Boolean refetchSavedEntitiesAfterSave, Boolean recurse) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 2513
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.SaveEntityCollection(IEntityCollection2 collectionToSave) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 2491
   at MyCompany.MyProduct.Components.BatchProcess.Commit(IDataAccessAdapter adapter) 

this clearly shows that the DataAccessAdpater is starting a new Transaction, so is there any way turn that feature off. is there any template settings or switches that I am not aware of in the code generation that will stop the DataAccessAdpater to start a new transaction.

best regards thorri.

When you develop applications to run on SQL Server Compact 3.5, note the following differences between SQL Server Compact 3.5 and Microsoft SQL Server 2008 R2:

SQL Server Compact 3.5 does not support nesting of transactions but it does support parallel transactions in ADO.NET.

In SQL Server Compact 3.5, if a cursor is opened within a transaction, the cursor exists within the scope of that transaction. If the transaction is aborted, the cursor ceases to exist. To continue using a cursor after a canceled transaction, create the cursor outside the scope of the transaction. Within the context of OLE DB for SQL Server Compact 3.5, this means that the recordset will not be valid, and must be closed. If the transaction commits, the cursor still exists and is fully functional.

SQL Server Compact 3.5 does not support distributed transactions.

SQL Server Compact 3.5 does not support save points. Save points permit an application to roll back part of a transaction if a minor error is encountered. The application must still commit or roll back the full transaction when it is complete.

ref: http://msdn.microsoft.com/en-us/library/ms172400(v=SQL.105).aspx

Hi. So, I can not use two different DataAccessAdapters within the same system transaction ??

like so, where in the real case the adapters are buried deep in our database components.

using (TransactionScope scope = new TransactionScope())
{
     IDataAccessAdapter adapter1 = new DataAccessAdapter(); 
     //Do some CRUD...  

     IDataAccessAdapter adapter2 = new DataAccessAdapter(); 
     //Do some CRUD...  

   scope.Complete();

}

Do you have any other suggestions other than the workaround suggested be daelmo ??

thanks for the help.

I don't have any other suggestion.

(edit, removed wrong assumptions). We have to look into whether this is related to our code, or whether it's an issue with SQL CE specifically. We think our code should work with multiple adapters in the same distributed transaction, however if this is a limitation of CE there's not much we can do.

We'll look into fixing this, if it's related to our code and whether it's doable, as an entity should participate in just 1 transaction if it's saved to two or more databases as the transaction's commit / rollback should result in just 1 commit/rollback on the entity itself, not one per db.

As it's a holiday week this week, we hope to have a fix for this first week of January 2012. I hope that's not too late for you

Btw, saving the same list of entities twice isn't going to work: after the first save returns, the entities are no longer marked 'dirty', so the second save won't result in the same save actions. Is there a reason why you want to save the elements to two different databases at the same time and not use some form of replication system?

(there's a workaround though, however it involves entity cloning, and save the originals to db1, and the clones to db2)

Hi Otis. Thanks for your effort during the holiday season.

we are not saving a list or entity twice, there are two separated lists delete and save/update. This particular application is using one SQL compact database so we are not worried about that the moment..

regards thorri.

Ok, how things work: A transaction scope manages a distributed transaction. Inside the scope, code creates ado.net connections and over those connections ado.net transactions. Each time this happens, the transaction is enlisted with the scope's manager. When the ado.net transaction is committed/rolled back, the manager is notified.

This means that multiple connections and transactions simply work. This has been the case for many years already. So why does your code fail? Well... See: http://msdn.microsoft.com/en-us/library/bb896149%28SQL.100%29.aspx

under 'Limitations of System.Transactions':

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.

This means that with CE, you can only have 1 ado.net connection and 1 ado.net transaction in 1 scope. As you create 2 adapters, you have 2 ado.net connections and 2 ado.net transactions. Normally this is fine (that's why you'd use transactionscope!) but in this case, it's not: you need 2 connections as you start 2 adapters, and as the scope can enlist just 1 CE connection, this will never work.

You need to share the adapter for both methods. In theory we could add code to share the live connection among adapters, but that makes little sense. Adapters are light-weight objects which control their own stuff, not some other object's contents. So it's a limitation in CE.