Menu
Say a stored procedure on MSSQL Server uses an SQL transaction using BEGIN TRANSACTION/COMMIT TRANSACTION, how does that differ from beginning and commiting one using ADO.NET via SqlConnection.BeginTransaction()?
989
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. But, these statements cannot be used inside Trigger. Calling: Stored Procedure can be called inside a Trigger.
View is simple showcasing data stored in the database tables whereas a stored procedure is a group of statements that can be executed. A view is faster as it displays data from the tables referenced whereas a store procedure executes sql statements.
SQL Transaction gives you the “power to return back to a safe state if some error happens in the middle of your SQL Code”. For example, suppose in your Stored Procedure you are running an Insert statement followed by Update statement.
A stored procedure is an example of a batch. In a stored procedure, if one statement fails and there is error trapping (normally TRY/CATCH blocks) then the subsequent statements will not execute.
For ADO.NET, it's no difference. It's implicitly stated in MSDN where for SqlTransaction object the Commit method is said to "fail if the transaction has already been rolled back on the server."
Also, SQL Server Profiler shows "SET TRANSACTION ISOLATION LEVEL READ COMMITTED;BEGIN TRAN" as soon as you execute .BeginTransaction on a connection.
For ADO (not .NET), however, it's not. That used to allow nice scenarios with, effectively, nested transactions (server trans were nested inside client ones). Despite I have used that a lot, I can't define exactly what a "client transaction" was in that case.
182
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
