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

  1. 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.
  2. 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.
  3. Isolation: Every transaction should operate as if it is the only transaction in the system.
  4. 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 languages like INSERT, UPDATE, and DELETE, you cannot use it with DDL or DCL language as these DDL and DCL languages are used 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 the 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 a transaction.

Syntax

BEGIN TRANSACTION
[ { transaction_name | @tran_name_variable }
[ WITH MARK [ 'description' ] ]
]
[ ; ]

Example 1. Simple Transaction

DECLARE @TranName VARCHAR(20);  
SELECT @TranName = 'MyTransaction';  
BEGIN TRANSACTION @TranName;  
    USE AdventureWorks2012;  
    DELETE FROM AdventureWorks2012.HumanResources.JobCandidate  
    WHERE JobCandidateID = 13;  
COMMIT TRANSACTION @TranName;  
GO

Example 2. Transaction with rollback

BEGIN TRAN   
   UPDATE authors SET au_fname = 'John' WHERE au_id = '172-32-1176';   
   UPDATE authors SET au_fname = 'JohnY' WHERE city = 'Lawrence';   
   IF @@ROWCOUNT = 5   
      COMMIT TRAN;   
   ELSE   
      ROLLBACK;

Example 3. Transaction in Procedure

CREATE PROCEDURE TranTest2  
AS  
BEGIN  
    BEGIN TRAN;       
    INSERT INTO authors (au_id, au_lname, au_fname, phone, contract)  
    VALUES ('172-32-1176', 'Gates', 'Bill', '800-BUY-MSFT', 1);  
    IF @@ERROR <> 0  
    BEGIN  
        ROLLBACK TRAN;  
    END   
    UPDATE authors SET au_fname = 'Johnzzz'  
    WHERE au_id = '172-32-1176';  
    
    IF @@ERROR <> 0  
    BEGIN  
        ROLLBACK TRAN;  
    END   
    COMMIT TRAN;  
END  
GO

Transaction with ADO.NET

The following example describes how SQL transaction is used with ADO.NET.

SqlConnection sqlConnection = new SqlConnection("ConnectionString");    
SqlTransaction transaction;    
sqlConnection.Open();    
transaction = sqlConnection.BeginTransaction();    
try     
{    
    new SqlCommand("INSERT Qwery1", sqlConnection, transaction).ExecuteNonQuery();    
    new SqlCommand("INSERT Qwery2", sqlConnection, transaction).ExecuteNonQuery();    
    new SqlCommand("INSERT Qwery3", sqlConnection, transaction).ExecuteNonQuery();    
    transaction.Commit();    
}     
catch (SqlException sqlError)     
{    
    transaction.Rollback();    
}    
sqlConnection.Close();

In the above example, we first opened a connection with the SQL Database and then created an object of the SqlTransaction class. Secondly, we kept the reference of SqlTransaction with this transaction object by calling the SQL Begin Transaction method.

Within the try block executes three SQL commands, if no error occurs the transaction will be committed other than the catch block rolled back the transaction. Finally, a database connection is closed.

Summary

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


Similar Articles