Transactions usage in BL for Oracle backend without escalating to DTC

[_TBS_]

Hi there,

I am really looking for an answer to this...

I am coming from Entity Framework world, where having some transactions in BL could cause escalation to DTC for Oracle
(links for reference: https://forums.oracle.com/thread/2356912 ; http://petermeinl.wordpress.com/2011/03/13/avoiding-unwanted-escalation-to-distributed-transactions/.

Eg for the same connection string:

using (var ts = new TransactionScope())
{
    // create order - make use of dbcontext, possibly to call SaveChanges here
    orderRepository.CreateOrder(order);
   // update inventory - make use of same dbcontext, possibly to call SaveChanges here
    inventoryRepository.UpdateInventory(inventory);
    ts.Complete();
}

I want to have similar code as above in my BL to work for both SQL Server and Oracle and to not be afraid about transactions escalation. Let's say, if I have to insert first an order, take the auto-generated id, then perform something else with this uid of the order... this should be possible without escalation neither in SQL Server 2008+ nor Oracle (again, same db, same conn string).

Is this possible using OpenAccess ? If yes, could you give me or point me to a concrete example/test app which works this way?

Thank you very much!

[Kaloyan Nikolov]

Hi Cornelia,

You are right escalating the Database Transaction to the Distributed Transaction Coordinator is always not wanted because it is hard to setup and it is costly operation performance wise. 
With Telerik OpenAccess ORM you can achieve this relatively easy.
The most simple way is to guarantee that all of your repository instances use one and the same OpenAccessContext instance and control the Database Transaction outside the repositories:

try
{
    using (var dbContext = new NorthwindModel())
    {
        var supplierRepository = new SupplierRepository(dbContext);
        var productsRepository = new ProductsRepository(dbContext);

        var supplier = new Supplier()
        {
            Address = "Address",
            City = "City",
            CompanyName = "Company",
            ContactName = "Contact",
        };
        supplierRepository.Add(supplier);

        var p = new Product()
        {
            ProductName = "Product1",
            Supplier = supplier
        };
        productsRepository.Add(p);

        throw new Exception("Gets a random error for some reason");

        //commit the transaction
        dbContext.SaveChanges();

    } //If an exception occurs the transaction will be rolled back at this point;
}
catch (Exception ex)
{
    Console.WriteLine(ex.ToString());
}

To get this approach working you should:
1. Inject single instance of the context to all repositories
2. Replace all SaveChanges() calls in you repository classes to FlushChanges(). This will persist all changes to the Database but will not commit the transaction. It will throw an exception if you have data integrity problem and the transaction will be rolled back when you leave the using statement or when you call the ClearChanges() method manually;
3. Call SaveChanges only once when you are ready with all operations. 
4. Always use the context in using statement to guarantee that if an exception is thrown the opened transaction will be rolled back or wrap you logic in try-catch-finally block and call the  ClearChanges() where it is appropriate. 
 
Please find attached a sample application.
You can read more about the transactions in OpenAccess ORM here.

I hope this helps. Please do not hesitate to get back to us if you have any additional questions. 


Regards,
Kaloyan Nikolov
Telerik