SQL transactions are used in ADO.NET - this is a powerful feature that ensures data integrity during various database operations.

A transaction is a sequence of database operations that must be executed as a unit. It follows the ACID principles:

• Atomicity: all operations succeed or none
• Consistency: The database remains consistent
• Isolation: Transactions are independent
• Durability: changes persist even after a crash

SQL Transaction Commands in ADO.NET

ADO.NET uses the following commands to manage transactions,

Basic Example of Using Transaction in ADO.NET

Let's say you want to transfer money between two bank accounts,

using System;
using System.Data;
using System.Data.SqlClient;
class TransactionExample
{
   static void Main()
   {
       string connectionString = "your_connection_string_here";
       using (SqlConnection conn = new SqlConnection(connectionString))
       {
           conn.Open();
           // Start a local transaction
           SqlTransaction transaction = conn.BeginTransaction();
           try
           {
               // Command 1: Deduct from Account A
               SqlCommand cmd1 = new SqlCommand("UPDATE Accounts SET Balance = Balance - 500 WHERE AccountID = 1", conn, transaction);
               cmd1.ExecuteNonQuery();
               // Command 2: Add to Account B
               SqlCommand cmd2 = new SqlCommand("UPDATE Accounts SET Balance = Balance + 500 WHERE AccountID = 2", conn, transaction);
               cmd2.ExecuteNonQuery();
               // If both succeed, commit
               transaction.Commit();
               Console.WriteLine("Transaction committed successfully.");
           }
           catch (Exception ex)
           {
               // If any command fails, roll back the transaction
               transaction.Rollback();
               Console.WriteLine("Transaction rolled back. Error: " + ex.Message);
           }
       }
   }
}

Explanation:

• conn.BeginTransaction(): Begins a SQL transaction.
• new SqlCommand(..., conn, transaction): Associates command with the transaction.
• transaction.Commit(): Saves changes to the Database.
• transaction.Rollback(): Undoes the changes if any occurred.

Important Points While using Transaction

• Always use try-catch during transactions
• Always commit or rollback to avoid open transactions
• Keep transactions small to avoid locking resources
• Use using statements to ensure the connection is closed.

Also, read: ACID Properties in Database Transactions