Entity Framework - SQL Server - Transactions handling

Question

I am using ASP.Net Web Forms, .Net Framework 3.5, Entity Framework In my application I am doing inserts using entity model and then calling SaveChanges(), I know in this case Entity model handles the transactions and if any query fails, everything will be reverted.

But in few cases, I use a SQL-Server SP to insert data in different tables. This SP has 4 to 5 insert queries. I want to know that if any one query in SP fails, will Entity model revert the other queries or not? I don't think entity model will handle that - right? Is there any workaround or I'll have to use Entity model to insert data for handling transactions?

Try changing your sp to fail on purpose halfway through and see if any changes persist. — alun
– alun, Commented Aug 22, 2011 at 6:53
If you have SP, you can handle the transaction in SP itself. I guess this is the better way. — Naveen
– Naveen, Commented Dec 23, 2013 at 12:53

Eranga · Accepted Answer · 2011-08-22 05:14:44Z

2

You can use the TransactionScope class. This will ensure an Atomic transaction

using (TransactionScope scope = new TransactionScope())
{

     mySP.Insert();

     context.SaveChanges();

     scope.Complete();
}

answered Aug 22, 2011 at 5:14

Eranga

32.5k5 gold badges100 silver badges98 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Riz Over a year ago

So if any insert/update or delete query in my SP fails, executed queries will be reverted too? Do I need to write something specific in my SP?

Eranga Over a year ago

@eFriend your SP should throw an exception if it fails. if any one of the operations fail all will be reverted.

Community · Accepted Answer · 2017-05-23 12:26:54Z

-1

Check this out

Entity Framework - Using Transactions or SaveChanges(false) and AcceptAllChanges()?

edited May 23, 2017 at 12:26

CommunityBot

11 silver badge

answered Aug 22, 2011 at 5:19

Vlad Bezden

90.7k27 gold badges261 silver badges190 bronze badges

1 Comment

Riz Over a year ago

I have gone through that question already, but it doesn't answer my question.

Collectives™ on Stack Overflow

Entity Framework - SQL Server - Transactions handling

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related