Here’s a full C# console application demonstrating ACID properties using ADO.NET with 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.

SQL Table Setup

Before running the C# code, create a BankAccounts table in SQL Server:

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

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?

Example Scenarios

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

Transaction successful! Money transferred.

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

Transaction failed: Insufficient balance. Transaction cancelled.

Conclusion

This C# ADO.NET transaction ensures ACID compliance while handling a bank transfer. It prevents data corruption, ensures consistency, and guarantees durability of committed transactions.