Running a stored procedure in a transaction

Hi Anatol,
thank you, it works perfect,
Stepan

You are welcome!

Hello Anatol,
I found this question when searching on how to run SP within the context of ObjectSpace saving transaction with XPO
the question up is saying *save object and execute stored procedure must be in transaction* , and you replied to use ObjectSpace.Committing to run the stored procedure , questions are :

1. will stored procedure then run and find data saved by ObjectSpace but transaction is still pending ? or data not yet saved? I want to run it after the data is saved but before the transaction is committed
2. will I be able to RollBack the transaction if any database error is returned after running the procedure
   I used to do that in XPO like that , it was very convenient
   UOW.ExplicitBeginTransaction()
   Try
   UOW.CommitChanges()
   UOW.ExecuteSprocParametrized("psp_Account_AfterSave", SPParam.ToArray())
   UOW.ExplicitCommitTransaction()
   Catch ex As Exception
   UOW.ExplicitRollbackTransaction()
   End Try
   I need a way to run a procedure within the ObjectSpace transaction and be able to roll back the transaction if I want , I read and know and understand arguments about XAF concept like Dennis is saying here
   https://www.devexpress.com/Support/Center/Question/Details/T148956
   I read all that and understand it and agree with it and want to use XAF to its potential but I just want to run one procedure within the saving transaction of an invoice which will do many things at database that can not be practically brought to client like updating moving average cost of inventory for 20,000 rows , adjusting many other tables after saving the invoice , I don't want to interfere with XAF saving the invoice itself CRUD, its working perfect its own way with its Invoice header and detail classes defined in the BO , but run an outside SP which will not interfere with data at hand , this is so important in financial applications like accounting ERP
   Kindly advice , I hope very much you don't tell me there is no way around it because I really want to use XAF
   Thank you
   Bassam

The answer to both of your questions is no. To execute a stored procedure and saving operation within the same database transaction using XPO, you need to use an explicit transaction, as this is done in your code. Note that only one explicit transaction can be active at a time, so they should be short - you cannot start a transaction, wait until a user enters data and clicks Save and then commit it. In my tests, the following code works:

C#
public class ExplicitTransactionController : ObjectViewController<DetailView, Contact> {  
    protected override void OnActivated() {  
        base.OnActivated();  
        ObjectSpace.Committing += ObjectSpace_Committing;  
    }  
    void ObjectSpace_Committing(object sender, System.ComponentModel.CancelEventArgs e) {  
        if (!e.Cancel) {  
            e.Cancel = true;  
            ObjectSpace.Committing -= ObjectSpace_Committing;  
            try {  
                ((XPObjectSpace)ObjectSpace).Session.ExplicitBeginTransaction();  
                ObjectSpace.CommitChanges();  
                ((XPObjectSpace)ObjectSpace).Session.ExecuteNonQuery("select asd from Contact where dsa = '10'");  
                ((XPObjectSpace)ObjectSpace).Session.ExplicitCommitTransaction();  
            } catch {  
                ((XPObjectSpace)ObjectSpace).Session.ExplicitRollbackTransaction();  
                ObjectSpace.Refresh();  
                throw;  
            } finally {  
                ObjectSpace.Committing += ObjectSpace_Committing;  
            }  
        }  
    }  
    protected override void OnDeactivated() {  
        base.OnDeactivated();  
        ObjectSpace.Committing -= ObjectSpace_Committing;  
    }  
}  

However, since XAF was not designed for this scenario, we cannot guarantee that all functionality will work correctly with this solution. In addition, this solution cannot be used with the Middle-Tier and Integrated security modes, since these modes do not allow sending database queries bypassing the Security System.