I'm using TransactionScope in my C# code. I will try to simplify as much as I can .
public void ProcessStrings(...)
{
...
using (TransactionScope transactionScope = new TransactionScope())
{
...
if(someCondition)
{
try
{
DeleteSomeStrings(...);
}
catch(Exception ex)
{
//log error
...
throw;
}
}
...
try
{
UpdateSomeStrings(...);
}
catch(Exception ex)
{
//log error
...
throw;
}
...
transactionScope.Complete();
}
...
}
The DeleteSomeStrings and UpdateSomeStrings methods call two stored procedures through ADO.NET. I need to use explicit transaction handling into these stored procedures as well because they are also called from other contexts:
CREATE PROCEDURE [dbo].[DeleteSomeStrings]
...
--parameter list
...
AS
BEGIN TRY
BEGIN TRAN
...
COMMIT TRAN
END TRY
BEGIN CATCH
...
ROLLBACK
...
RAISERROR(...)
END CATCH
and the second one has a similar structure:
CREATE PROCEDURE [dbo].[UpdateSomeStrings]
...
--parameter list
...
AS
BEGIN TRY
BEGIN TRAN
...
COMMIT TRAN
END TRY
BEGIN CATCH
...
ROLLBACK
...
RAISERROR(...)
END CATCH
The problem is when DeleteSomeStrings succeeds and UpdateSomeStrings fails, the data that was processed in DeleteSomeStrings still gets committed. It looks like the TransactionScope is ignored in this scenario. I would expect everything to be rolled back. Is this the normal behavior and if so why?
