Does OA rollback if a stored procedure fails

[Mike Prager]

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

[Kaloyan Nikolov]

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