This article describes about Sql transaction with example in SQL language as well as ADO.NET.
Database transaction takes database from one consistent state to another. At the end of the transaction the system must be in the prior state if the transaction fails or the status of the system should reflect the successful completion if the transaction goes through.
It is a single unit of work. If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database. If a transaction encounters errors or rolled back, then all of the data modifications are erased.
Properties of transaction
Once a transaction completed successfully, the updated rows/records must be available for all other transactions on a permanent basis.
Transaction commands
The transaction commands are only used in SQL DML language like INSERT, UPDATE and DELETE, you cannot use it with DDL or DCL language as these DDL and DCL languages are used to in creating structure and SQL security.
The transaction commands are given below:
Syntax BEGIN { TRAN | TRANSACTION } [ { transaction_name | @tran_name_variable } [ WITH MARK [ 'description' ] ] ] [ ; ]
Example 1: Simple Transaction
Example 2: Transaction with rollback.
Example 3: Transaction in Procedure.
Transaction with ADO.NET
The following example describes how SQL transaction is used with ADO.NET.
In the above example, we first opened connection with SQL Database then created object of SqlTransaction class. Secondly, kept the reference of SqlTransaction with this transaction object by calling SQL Begin Transaction method.
Within the try blockexecuted three SQL commands, if no error occurs the transaction will be committed other than the catch block rolled back the transaction. Finally, database connection is closed.
Basic SQL Queries