Menu
When my C#.net application updates records in more than one table, I use transactions so if anything should fail during the transaction I can rollback.
Which one is a better practice?
-Use stored procedure with BEGIN TRANSACTION/ROLLBACK/COMMIT TRANSACTION; -Use TransactionScope in the application as below:
using (TransactionScope ts = new TransactionScope())
{
}
624
Yes, a stored procedure can be run inside a transaction.
If we have more than one SQL statements in execute in the stored procedure and we want to rollback any changes done by any one of the SQL statements in case an error occurred because of one of the SQL statements, we can use transaction in stored procedure.
Parameter: Stored Procedures can take parameters as input but Triggers cannot take parameters as input. Return Values: Stored Procedures can return values but Triggers cannot return value. Transaction: Transaction statements such as begin transaction, commit transaction, and rollback inside a Stored Procedure.
Remember that if a BEGIN statement starts a transaction block before calling a stored procedure, the stored procedure always returns with an active transaction block, and you must issue a COMMIT or ROLLBACK statement to complete or abort that transaction.
This isn't a business logic issue, it's a data integrity issue and I feel that is ok to do in the stored procedure. I like to keep transaction logic as close to the operations as possible to shorten their duration.
135
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
