Roll Back Sql Server 2008 query

Question

I am inserting values into two tables using two stored procedure , and the data in both tables are linked to each other so i want if any error occurs in second stored procedure ,the data entered via 1st stored procedure should get roll backed.

I am using Sql server 2008 as my back end and ASP.net (c#) as front end

score 0 · Accepted Answer · answered Feb 01 '15 at 06:20

0

use need to use TransactionScope as below

using(var tran = new TransactionScope())
{
   //calling stored procedures here
   tran.Complete();
}

when an exception occurs the control will go out from the using and thus transaction will rollback

answered Feb 01 '15 at 06:20

Reza

18,865
13
88
163

Thnxx @reza , it really helped me – Akhil R J Feb 01 '15 at 13:42
@AkhilRJ You're welcome, you can upvote the post that help you by clicking on uparrow beside the question and you can mark the answer by clicking on Tickmark beside the question to help other people find the right answer in case they had same question – Reza Feb 01 '15 at 14:04

Nazmul Hossain · Answer 2 · 2015-02-01T06:46:25.850

if you are using entity framework you can use it.

using (var  dataContext = new SchoolMSDbContext())
{
  using (var trans = dataContext.Database.BeginTransaction(IsolationLevel.ReadCommitted))
  {
    try
    {
      // your query
      trans.Commit();
    }
    catch (Exception ex)
    {
      trans.Rollback();
      Console.WriteLine(ex.InnerException);
    }
  }
}

Or you can try this

 using (var dataContext = new SchoolMSDbContext())
                {
                    using (var transaction = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
                    {
                        try
                        {
                            //your query
                            transaction.Complete();

                        }
                        catch (Exception ex)
                        {
                            transaction.Dispose();
                            Console.WriteLine(ex.InnerException);
                        }
                    }
                }

for this you'll have to this references.

System.Transactions

for more information check this links https://msdn.microsoft.com/en-us/data/dn456843.aspx https://msdn.microsoft.com/en-us/library/2k2hy99x(v=vs.110).aspx

Could u plz explain why r we using 'TransactionScopeAsyncFlowOption.Enabled ' this parameter.. — Akhil R J, Feb 01 '15 at 13:41

Roll Back Sql Server 2008 query

2 Answers2