Rollback a stored procedure call from inside a transaction using LINQ-to-SQL?

12,678

Another alternative to DbTransaction is TransactionScope - this provides a much simpler programming model, and is extensible to multiple simultaneous databases and other feeds (via DTC) - but at the cost of a small amount of overhead on the connection. It used to be more overhead, but under SQL2005 etc it will use the "LTM" until you start spanning multiple connections - so a single operation is usually very cheap:

using (TransactionScope tran = new TransactionScope())
using (FooDataContext ctx = new FooDataContext())
{
    // your work with ctx
    // ...
    // other work involving connections etc
    // ...
    tran.Complete();
}

Very simple ;-p You should also be able to make the transaction more granular (over just a few queries) ormore encompassing very simply. Most existing code will automatically enlist in the transaction scope, making it very easy to retro-fit into existing code.

For more info on TransactionScope (and general transactions in .NET), see here.

Share:
12,678

Related videos on Youtube

Admin
Author by

Admin

Updated on April 18, 2022

Comments

  • Admin
    Admin over 1 year

    I have a C#.net winform program which runs with a SQL Server database. I am using LINQ-to-SQL. Is it possible to rollback the call to one or more stored procedures inside a transaction within my program using LINQ-to-SQL?

    Initially I thought it would make sense to manage the transaction inside the stored procedure but if I need to rollback more than one stored procedure call as part of a single transaction it would need to be done in my C# program.

    Can someone point me to a code snippet on how to do this or provide some insight into an alternative?

  • aruno
    aruno almost 14 years
    just want to clarify one point if its not obvious : "The Complete method commits the transaction. If an exception has been thrown, complete is not called and the transaction is rolled back." (from first link above) So don't go looking for a 'rollback()' and don't have any code paths that do 'return' without Complete()