This is a migrated thread and some comments may be shown as answers.
Scope with transaction vs. stored procedure with transaction
10 Answers 68 Views
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
Tomasz M.Lipiński
Top achievements
Rank 1
Tomasz M.Lipiński asked on 16 May 2012, 12:03 AM
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

10 Answers, 1 is accepted

Sort by
0
Damyan Bogoev
Telerik team
answered on 17 May 2012, 03:58 PM

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
Follow @OpenAccessORM Twitter channel to get first the latest updates on new releases, tips and tricks and sneak peeks at our product labs!
0
Tomasz M.Lipiński
Top achievements
Rank 1
answered on 17 May 2012, 06:02 PM
Hi,

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

Regards
Tomasz
0
Damyan Bogoev
Telerik team
answered on 18 May 2012, 04:56 PM

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
Follow @OpenAccessORM Twitter channel to be the first one to get the latest updates on new releases, tips and tricks and sneak peeks at our product labs!
0
Tomasz M.Lipiński
Top achievements
Rank 1
answered on 21 May 2012, 09:46 AM
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
0
Damyan Bogoev
Telerik team
answered on 21 May 2012, 03:55 PM

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
Follow @OpenAccessORM Twitter channel to be the first one to get the latest updates on new releases, tips and tricks and sneak peeks at our product labs!
0
Tomasz M.Lipiński
Top achievements
Rank 1
answered on 21 May 2012, 04:53 PM
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
0
Damyan Bogoev
Telerik team
answered on 22 May 2012, 03:58 PM

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
Follow @OpenAccessORM Twitter channel to be the first one to get the latest updates on new releases, tips and tricks and sneak peeks at our product labs!
0
Tomasz M.Lipiński
Top achievements
Rank 1
answered on 23 May 2012, 11:50 AM
Hi,

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

Regards
Tomasz
0
Accepted
IT-Als
Top achievements
Rank 1
answered on 24 May 2012, 07:04 AM
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
0
Tomasz M.Lipiński
Top achievements
Rank 1
answered on 24 May 2012, 11:40 PM
Hi,

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

Regards
Tomasz
Tags
General Discussions
Asked by
Tomasz M.Lipiński
Top achievements
Rank 1
Answers by
Damyan Bogoev
Telerik team
Tomasz M.Lipiński
Top achievements
Rank 1
IT-Als
Top achievements
Rank 1
Share this question
or