Scope with transaction vs. stored procedure with transaction

[Tomasz M.Lipiński]

Hi,

I'm using the "Old API". As I've learnt from this topic, the standard way of calling stored procedures makes such calls wrapped in a commited transaction. Therefore calling a stored procedure that contains transactions makes some exception raised (the suggested way of calling such procedures is using direct ADO.NET API). On the other hand, if this procedure doesn't contain transactions but raises some error, updates made by this procedure are commited by this wrapping transaction.

All that seems to make the following scenario impossible:
a. scope.Transaction.Begin()
b. some updates using the scope
c. a stored procedure call that makes its own updates
d. scope.Transaction.Rollback
We would like to have all updates rolled back but  as a result we have changes made in b. rolled back but changes made in c. still commited.
Is it true that mixing scope-level and sproc-level updates is - while using "Old API" - impossible (as far as using transactions is concerned)? Does the "New API" makes such a feature available?

Regads
Tomasz

[Damyan Bogoev]

Hello Tomasz,

Using the OpenAccess ADO API (i.e obtaining the OAConnection,OACommand, etc) is recommended when you want to execute SQL statements and want to commit these changes together with certain changes in the context. For example you have made some changes to persistent entities via the context instance and you also need to execute SQL that makes changes. You then commit/rollback both these changes together using context.SaveChanges() / ClearChanges(). 

Hope that helps.

Regards,
Damyan Bogoev
the Telerik team

[Tomasz M.Lipiński]

Hi,

Is this feature available using the Old API? If yes - how?

Regards
Tomasz

[Damyan Bogoev]

Hello Tomasz,

I have prepared a sample application which shows how to use the ADO API with OpenAccess Classic Model.

Hope that helps. If any other questions arise, do not hesitate to contact us back.

Kind regards,
Damyan Bogoev
the Telerik team

[Tomasz M.Lipiński]

Hi,

Thanks for the sample. Alas, I can't use it  - I still use VS 2008 (I haven't mentioned this, I'm sorry).
Could you just tip me, in which namespace / which assembly I can find IExtendedObjectScope, OAConnection and the others? As a reminder - I'm using OpenAccess Q1 2010.

Regards
Tomasz

[Damyan Bogoev]

Hello Tomasz,

Both IExtendedObjectScope and OAConnection are defined in the Telerik.OpenAccess assembly. You could find the IExtendedObjectScope interface in the Telerik.OpenAccess.SPI namespace and the OA classes in Telerik.OpenAccess.Data.Common.

Hope that helps. If any other questions arise, do not hesitate to contact us back.

Kind regards,
Damyan Bogoev
the Telerik team

[Tomasz M.Lipiński]

Hi,

Well, I'm pretty sure that they've started to be there after Q1 2010. Q1 2010, that I'm using, doesn't contain them.

Regards
Tomasz

[Damyan Bogoev]

Hello Tomasz,

 I am afraid that the ADO API was introduced in the product in the Q2 2011 release.

Kind regards,
Damyan Bogoev
the Telerik team

[Tomasz M.Lipiński]

Hi,

That is what I'm afraid too :-)  (or rather: :-( )

Regards
Tomasz

[IT-Als]

Hello Tomasz,

There's a second way of doing it in the classic API. Consider this pseudo code:

try
{
  // Get a scope
  IObjectScope scope = .....
   
  // Load an object into scope
  Model.Invoice pInvoice = .....

  scope.Transaction.Begin();
 
  // Perform some actions/changes on the loaded object
  pInvoice.CreatedDate = .....

  // Do a Flush to write changes to the database AND start a Database transaction that will be associated with the scope.
  scope.Transaction.Flush();

  // Perform the stored procedure
  IQuery query = scope.GetSqlQuery("dbo.sp_sequence_no_generator ?,?", null, "INTEGER ConsumerId, INTEGER SequenceNumberType");
 
  // Get results from the stored procedure and do something with int
  IQueryResult result = query.Execute(new object[] { consumerId, (int)type });

  // Do a Commit to write the last changes to the database  
  scope.Transaction.Commit();
}
catch (Exception)
{
   // Rollback if anything goes wrong... will rollback changes done by OpenAccess AND the SP because of the Flush
   scope.Transaction.Rollback();
}

The trick is that you use the same database transaction to do the SP that was used to do the changes on the objects in scope, but this is only possible because of the Flush() issued before the call the to SP.

Did this help achieve your goal..?


Regards

Henrik

[Tomasz M.Lipiński]

Hi,

Yes, it seems to fit my needs. Thanks a lot.

Regards
Tomasz