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, Aug 22 '11 at 06:53
If you have SP, you can handle the transaction in SP itself. I guess this is the better way. — Naveen, Dec 23 '13 at 12:53

score 2 · Answer 1 · answered Aug 22 '11 at 05:14

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 '11 at 05:14

Eranga

32,181
5
97
96

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? – Riz Aug 22 '11 at 05:42
@eFriend your SP should throw an exception if it fails. if any one of the operations fail all will be reverted. – Eranga Aug 22 '11 at 06:02

score 0 · Answer 2 · edited May 23 '17 at 12:26

0

Check this out

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

edited May 23 '17 at 12:26

Community

1
1

answered Aug 22 '11 at 05:19

Vlad Bezden

83,883
25
248
179

I have gone through that question already, but it doesn't answer my question. – Riz Aug 22 '11 at 05:46

Entity Framework - SQL Server - Transactions handling

2 Answers2