All geek questions in one place

Can TransactionScope roll back C # in stored procedures on SQL server?

My question is: is it possible to create logic on the C # side using (block and TransactionScope, execute more than one query without a query on the sql server and discard all stored procedures if this fails?

Limitations: I cannot make a large stored procedure to execute other stored procedures inside a TSQL transaction.

- Stored procedures do not contain "commit" in them, but only return 0; in the end.

Does this mean that they can be rolled back automatically if the transaction fails / time runs out / I did not get into the transaction .Complete ()? Do I need to embed a ROLLBACK in a saved process for this function?

I searched hard, but I can’t find the exact answer to this somewhat unique question. Thank you for your time. If anyone has any possible tests that I can run to try, I would be more than ready.

+4
source share
1 answer

Yes, TransactionScope is picked up by ADO.NET. If you catch any exceptions and call .Rollback (), everything will be rolled back.

There are small details in how it works (for example, MSDTC can participate), but what you offer is absolutely the right and right way to do what you want to achieve.

The same thing works for Oracle and other databases supporting TransactionScope. You can also transfer the transaction through the WCF service, where if more databases are used, they become part of the transaction, etc.

When doing some low-level work, you can even include NTFS operations in a transaction. The Windows API supports it, but not .NET without a shell, for NTFS.

+6
source

Source: https://habr.com/ru/post/1487970/

More articles:


All Articles