Scope with transaction vs. stored procedure with transaction

11 posts, 1 answers
  1. Tomasz M.Lipiński
    Tomasz M.Lipiński avatar
    71 posts
    Member since:
    Nov 2009

    Posted 15 May 2012 Link to this post

    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
  2. Damyan Bogoev
    Admin
    Damyan Bogoev avatar
    581 posts

    Posted 17 May 2012 Link to this post

    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!
  3. DevCraft banner
  4. Tomasz M.Lipiński
    Tomasz M.Lipiński avatar
    71 posts
    Member since:
    Nov 2009

    Posted 17 May 2012 Link to this post

    Hi,

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

    Regards
    Tomasz
  5. Damyan Bogoev
    Admin
    Damyan Bogoev avatar
    581 posts

    Posted 18 May 2012 Link to this post

    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!
  6. Tomasz M.Lipiński
    Tomasz M.Lipiński avatar
    71 posts
    Member since:
    Nov 2009

    Posted 21 May 2012 Link to this post

    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
  7. Damyan Bogoev
    Admin
    Damyan Bogoev avatar
    581 posts

    Posted 21 May 2012 Link to this post

    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!
  8. Tomasz M.Lipiński
    Tomasz M.Lipiński avatar
    71 posts
    Member since:
    Nov 2009

    Posted 21 May 2012 Link to this post

    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
  9. Damyan Bogoev
    Admin
    Damyan Bogoev avatar
    581 posts

    Posted 22 May 2012 Link to this post

    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!
  10. Tomasz M.Lipiński
    Tomasz M.Lipiński avatar
    71 posts
    Member since:
    Nov 2009

    Posted 23 May 2012 Link to this post

    Hi,

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

    Regards
    Tomasz
  11. Answer
    TSE
    TSE avatar
    381 posts
    Member since:
    Sep 2008

    Posted 24 May 2012 Link to this post

    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
  12. Tomasz M.Lipiński
    Tomasz M.Lipiński avatar
    71 posts
    Member since:
    Nov 2009

    Posted 24 May 2012 Link to this post

    Hi,

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

    Regards
    Tomasz
Back to Top
DevCraft banner