Does OA rollback if a stored procedure fails

2 posts, 0 answers
  1. Mike Prager
    Mike Prager avatar
    5 posts
    Member since:
    Sep 2005

    Posted 03 Apr 2013 Link to this post

    Hi Guys,

    If I have a stored procedure which does an insert, I know OA wraps this call in a transaction and so you need to call SaveChanges for this to commit. What happens if the sproc fails (for whatever reason) - Will this automatically rollback or do you need to explicity do this in the catch statement or does the using finaliser handle this? I want to know if there is a possibility that an open transaction is left?

    using (CoreContext dbContext = new CoreContext())
    {
       dbContext.MyStoredProcedure(parameters);
       dbContext.SaveChanges();
     }

    Many thanks

    Mike
  2. Kaloyan Nikolov
    Admin
    Kaloyan Nikolov avatar
    118 posts

    Posted 05 Apr 2013 Link to this post

    Hello Mike,

    If you call a stored procedure Telerik OpenAccess ORM doesn't commit / rollback the transaction automatically. You should do that manually, considering your business logic specifics.

    The transaction can be committed explicitly by calling the SaveChanges() method or to be rolled back by the ClearChanges() method. See the code example below.

    01.using (var dbContext = new EntitiesModel1())
    02.{
    03.    string s1 = "Title", s2 = "Author";
    04.    try
    05.    {
    06.        //At this statement the backend throws an exception.
    07.        dbContext.GetError(3, ref s1, ref s2);
    08. 
    09.        dbContext.Add(new Book() { Name = "Book1" });
    10. 
    11.        dbContext.SaveChanges();
    12.    }
    13.    catch(SqlException)
    14.    {
    15.        dbContext.ClearChanges();
    16.    }
    17.     
    18.}


    In your example the transaction will be committed or rolled back in case of an error from the using finalizer and no open transaction will be left. In some cases you might want to perform some additional operations after the stored procedure call with the same context or even operations with the rest of the application infrastructure. In that case you should rollback the transaction as fast as possible and not to wait the using finalizer. 

    NOTE: If you are going to use a new instance of the Context object for each database operation in your entire application this could lead to some performance drawbacks. You should consider sharing the context between different calls in order to minimize the cost for instantiating the Context object.

    Here you can find some approaches for managing OpenAccess Context in ASP.NET applications.  

    Please do not hesitate to contact us again if you have further questions.


    Greetings,
    Kaloyan Nikolov
    the Telerik team
    Using Encrypted Connection Strings with Telerik OpenAccess ORM. Read our latest blog article >>
  3. DevCraft banner
Back to Top