---
title: "How to use Transaction in ADO.NET?"  
description: "Here’s a full C# console application demonstrating ACID properties using ADO.NET with SQL Server."  
author: "ICSM Computer"  
published: 2025-02-12  
updated: 2025-02-12  
canonical: https://www.mindstick.com/blog/305252/how-to-use-transaction-in-ado-dot-net  
category: "ado.net"  
tags: ["c#", "ado.net"]  
reading_time: 3 minutes  

---

# How to use Transaction in ADO.NET?

Here’s a **full C# [console application](https://www.mindstick.com/forum/160510/how-to-fetch-data-from-the-database-in-the-dot-net-console-application-using-c-sharp)** demonstrating **[ACID properties](https://www.mindstick.com/articles/338513/acid-properties-in-database-transactions)** using ADO.NET with [SQL Server](https://www.mindstick.com/articles/12999/what-is-table-valued-function-in-sql-server). This example performs a **bank transfer** between two accounts while ensuring **Atomicity, Consistency, Isolation, and Durability**.

#### Steps in Code

1. **Atomicity**: Ensures either the transfer succeeds completely or rolls back.
2. **Consistency**: Checks sufficient balance before transferring.
3. **Isolation**: Uses `Serializable` isolation level to prevent concurrent modifications.
4. **Durability**: Uses `Commit()` to permanently save successful [transactions](https://www.mindstick.com/interview/864/explain-acid-rule-of-thumb-for-transactions).

#### SQL Table Setup

Before running the [C# code](https://www.mindstick.com/articles/334681/5-tips-to-writing-clean-c-sharp-code), create a `BankAccounts` [table in SQL](https://www.mindstick.com/forum/205/find-the-all-column-with-schema-for-any-table-in-sql-server) Server:

```plaintext
CREATE TABLE BankAccounts (
    AccountId INT PRIMARY KEY,
    AccountHolder NVARCHAR(100),
    Balance DECIMAL(10,2)
);

-- Insert Sample Data
INSERT INTO BankAccounts (AccountId, AccountHolder, Balance) VALUES (1, 'Alice', 5000);
INSERT INTO BankAccounts (AccountId, AccountHolder, Balance) VALUES (2, 'Bob', 3000);
```

## C# Code: ADO.NET Transaction with ACID

```plaintext
using System;
using System.Data;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = "your_connection_string_here"; // Update with your DB connection string

        int fromAccount = 1;  // Alice
        int toAccount = 2;    // Bob
        decimal transferAmount = 1000;

        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            conn.Open();

            // Begin Transaction with Serializable Isolation Level
            SqlTransaction transaction = conn.BeginTransaction(IsolationLevel.Serializable);

            try
            {
                // Check if fromAccount has enough balance
                decimal currentBalance = GetAccountBalance(conn, transaction, fromAccount);

                if (currentBalance < transferAmount)
                {
                    throw new Exception("Insufficient balance. Transaction cancelled.");
                }

                // Deduct amount from sender's account
                UpdateBalance(conn, transaction, fromAccount, -transferAmount);

                // Add amount to receiver's account
                UpdateBalance(conn, transaction, toAccount, transferAmount);

                // Commit transaction (Durability)
                transaction.Commit();
                Console.WriteLine("Transaction successful! Money transferred.");
            }
            catch (Exception ex)
            {
                // Rollback in case of failure (Atomicity)
                transaction.Rollback();
                Console.WriteLine("Transaction failed: " + ex.Message);
            }
        }
    }

    // Method to get account balance (Consistency)
    static decimal GetAccountBalance(SqlConnection conn, SqlTransaction transaction, int accountId)
    {
        using (SqlCommand cmd = new SqlCommand("SELECT Balance FROM BankAccounts WHERE AccountId = @AccountId", conn, transaction))
        {
            cmd.Parameters.AddWithValue("@AccountId", accountId);
            return (decimal)cmd.ExecuteScalar();
        }
    }

    // Method to update balance (Atomicity & Consistency)
    static void UpdateBalance(SqlConnection conn, SqlTransaction transaction, int accountId, decimal amount)
    {
        using (SqlCommand cmd = new SqlCommand("UPDATE BankAccounts SET Balance = Balance + @Amount WHERE AccountId = @AccountId", conn, transaction))
        {
            cmd.Parameters.AddWithValue("@AccountId", accountId);
            cmd.Parameters.AddWithValue("@Amount", amount);
            cmd.ExecuteNonQuery();
        }
    }
}
```

#### How This Code Follows ACID?

| ACID Property | [Implementation](https://www.mindstick.com/forum/33764/how-to-implementation-of-class-in-c-sharp) in Code |
| --- | --- |
| **Atomicity** | Uses `transaction.Rollback()` to ensure full execution or complete rollback. |
| **Consistency** | Checks balance before transferring money to prevent inconsistencies. |
| **Isolation** | Uses `IsolationLevel.Serializable` to prevent concurrent transactions from affecting data. |
| **Durability** | Uses `transaction.Commit()` to permanently save changes in the database. |

## Example Scenarios

**1.** If Alice has **5000** and sends **1000** to Bob, the transaction will succeed:

```plaintext
Transaction successful! Money transferred.
```

**2.** If Alice tries to send **6000**, which exceeds her balance, the transaction will roll back:

```plaintext
Transaction failed: Insufficient balance. Transaction cancelled.
```

#### Conclusion

This C# ADO.NET transaction **ensures ACID compliance** while handling a bank transfer. It prevents **[data corruption](https://answers.mindstick.com/qa/99813/how-to-fix-issues-with-file-or-data-corruption-in-computer-systems)**, ensures **consistency**, and guarantees **durability** of committed transactions.

---

Original Source: https://www.mindstick.com/blog/305252/how-to-use-transaction-in-ado-dot-net

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.