Post
Ask Question

SqlTransaction In C#

Database transaction takes a 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 gets rolled back, then all of the data modifications are erased.
 

Properties of transaction

 
Atomicity
 
A transaction consists of many steps. When all the steps in a transaction get completed, it will get reflected in DB or if any step fails, all the transactions are rolled back.
 
Consistency
 
The database will move from one consistent state to another, if the transaction succeeds and remains in the original state, or if the transaction fails.
 
Isolation
 
Every transaction should operate as if it is the only transaction in the system.
 
Durability 
 
Once a transaction is 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,
  • COMMIT
    This command is used to save the changes invoked by the transaction.

  • ROLLBACK
    This command is used to undo the changes made by transaction.

  • SAVEPOINT
    With the help of this command you can roll the transaction back to a certain point without rolling back the entire transaction.

  • SET TRANSACTION
    This command is used to specify characteristics for the transaction. For example, you can specify a transaction to be read only, or read write it. Also helps set the name of transaction. 
Syntax

BEGIN { TRAN | TRANSACTION }
[ { transaction_name | @tran_name_variable }
[ WITH MARK [ 'description' ] ]
]
[ ; ]
 
Example 1
 
Simple Transaction,
  1. DECLARE @TranName VARCHAR(20);  
  2. SELECT @TranName = 'MyTransaction';  
  3.   
  4. BEGIN TRANSACTION @TranName;  
  5. USE AdventureWorks2012;  
  6. DELETE FROM AdventureWorks2012.HumanResources.JobCandidate  
  7.     WHERE JobCandidateID = 13;  
  8.   
  9. COMMIT TRANSACTION @TranName;  
  10. GO  
Example 2
 
Transaction with rollback.
  1. BEGIN TRAN   
  2.    UPDATE authors SET au_fname = 'John' WHERE au_id = '172-32-1176'   
  3.    UPDATE authors SET au_fname = 'JohnY' WHERE city = 'Lawrence'   
  4.    IF @@ROWCOUNT = 5   
  5.       COMMIT TRAN   
  6.    ELSE   
  7.       ROLLBACK   
Example 3
 
Transaction in Procedure.
  1. CREATE PROCEDURE TranTest2  
  2. AS  
  3. BEGIN TRAN  
  4.    INSERT INTO[authors]([au_id], [au_lname], [au_fname], [phone], [contract])  
  5.    VALUES('172-32-1176', 'Gates', 'Bill', '800-BUY-MSFT', 1)  
  6.    IF@@ ERROR < > 0  
  7. BEGIN  
  8.    ROLLBACK TRAN  
  9. END  
  10. UPDATE authors SET au_fname = 'Johnzzz'  
  11. WHERE au_id = '172-32-1176'  
  12. IF@@ ERROR < > 0  
  13. BEGIN  
  14.    ROLLBACK TRAN  
  15. END  
  16. COMMIT TRAN  
  17. GO  

Transaction with ADO.NET

 
The following example describes how SQL transaction is used with ADO.NET.
  1. SqlConnection sqlConnection db = new SqlConnection("ConnectionString");    
  2. SqlTransaction transaction;    
  3.   
  4. sqlConnection.Open();    
  5. transaction = sqlConnection.BeginTransaction();    
  6. try     
  7. {    
  8.    new SqlCommand("INSERT Qwery1", sqlConnection, transaction)    
  9.       .ExecuteNonQuery();    
  10.    new SqlCommand("INSERT Qwery2 ", sqlConnection, transaction)    
  11.       .ExecuteNonQuery();    
  12.    new SqlCommand("INSERT Qwery3 ", sqlConnection, transaction)    
  13.       .ExecuteNonQuery();    
  14.    transaction.Commit();    
  15. }     
  16. catch (SqlException sqlError)     
  17. {    
  18.    transaction.Rollback();    
  19. }    
  20. sqlConnection.Close();   
In the above example, we first opened a connection with SQL Database then created object of SqlTransaction class. Secondly, we 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.
 

Summary

 
I hope you got some helpful information about SqlTansaction with example in SQL and ADO.NET.


Similar Articles