TransactionScope and Transactions
- by Mike
In my C# code I am using TransactionScope because I was told not to rely that my sql programmers will always use transactions and we are responsible and yada yada.
Having said that
It looks like TransactionScope object Rolls back before the SqlTransaction? Is that possible and if so what is the correct methodology for wrapping a TransactionScope in a transaction.
Here is the sql test
CREATE PROC ThrowError
AS
BEGIN TRANSACTION --SqlTransaction
SELECT 1/0
IF @@ERROR<> 0
BEGIN
ROLLBACK TRANSACTION --SqlTransaction
RETURN -1
END
ELSE
BEGIN
COMMIT TRANSACTION --SqlTransaction
RETURN 0
END
go
DECLARE @RESULT INT
EXEC @RESULT = ThrowError
SELECT @RESULT
And if I run this I get just the divide by 0 and return -1
Call from the C# code I get an extra error message
Divide by zero error encountered.
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION tatement is missing. Previous count = 1, current count = 0.
If I give the sql transaction a name then
Cannot roll back SqlTransaction.
No transaction or savepoint of that name was found.
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
TRANSACTION statement is missing. Previous count = 1, current count = 2.
some times it seems the count goes up, until the app completely exits
The c# is just
using (TransactionScope scope = new TransactionScope())
{
... Execute Sql
scope.Commit()
}
