How to rollback 3 statements if one fails?

Question

In C#, I have three statements in a method that insert data into a database. The first statement inserts and I get an ID from it. This ID is used in the next two statements.

I have two cases to handle:

1.) The second statement can return false if certain input conditions aren't met. In that case, I'd like to rollback the first statement.
2.) If either of the statements generate an error, they should all rollback.

I thought to wrap all three statements in a transaction scope but it will only rollback on exceptions, which only covers the second case. What is the best way handle both cases?

I'm using DataContext via a DBML with LINQ 2 SQL

Answer 1

Take a look at the link I posted. Its pretty simple to manually roll back a transaction

How to Commit and Rollback Transaction in .NET

The jist of it is that after you have your transaction object, you can call RollBack() on it.

For Linq-To-Sql take a look at How to: Bracket Data Submissions by Using Transactions (LINQ to SQL)

Answer 2

With TransactionScope() you need to call Complete() method to commit the changes. Othewise all pending changes will be rolled back, so your (1) is also covered

Answer 3

You can use the RAISERROR statement, with the right severity level (18 I believe should do it) to "throw an exception" and cause your transaction to rollback under user-checked conditions.

Obviously though, this will only rollback anything within the same transaction scope.