TIL: Transactions in .NET – ██FR█████ █INTELL███████████

This content originally appeared on DEV Community and was authored by Vishnu KN

While performing multiple database operations, sometimes it becomes essential that for a bunch of operations that they either succeed as a whole or fail as a whole.

For example, for a single purchase, we might want to deduct the user’s balance in one table and add a record to the order history in the other. If one of these operations fail, it does not make sense to proceed with the other since it leads to data inconsistencies and confusion. In such cases, transactions come in handy.

A transaction is a set of database operations, either performed on one table or across multiple tables, which are designed to either succeed as a whole or to fail as a whole. If it succeeds it is ‘committed’ (the changes are saved) , if it fails it is ‘rolled back’ (the changes are undone).

Code example:

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = "your-connection-string-here";

        int userId = 1;
        decimal purchaseAmount = 100;

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

            // Start the transaction
            SqlTransaction transaction = conn.BeginTransaction();

            try
            {
                // Step 1: Deduct from User Balance
                SqlCommand deductCmd = new SqlCommand(@"
                    UPDATE Users
                    SET Balance = Balance - @Amount
                    WHERE UserId = @UserId", conn, transaction);
                deductCmd.Parameters.AddWithValue("@Amount", purchaseAmount);
                deductCmd.Parameters.AddWithValue("@UserId", userId);
                deductCmd.ExecuteNonQuery();

                // Step 2: Add to OrderHistory
                SqlCommand insertOrderCmd = new SqlCommand(@"
                    INSERT INTO OrderHistory (UserId, Amount, OrderDate)
                    VALUES (@UserId, @Amount, @OrderDate)", conn, transaction);
                insertOrderCmd.Parameters.AddWithValue("@UserId", userId);
                insertOrderCmd.Parameters.AddWithValue("@Amount", purchaseAmount);
                insertOrderCmd.Parameters.AddWithValue("@OrderDate", DateTime.Now);
                insertOrderCmd.ExecuteNonQuery();

                // Everything succeeded, commit the transaction
                transaction.Commit();
                Console.WriteLine("Purchase completed successfully.");
            }
            catch (Exception ex)
            {
                // Something went wrong, roll back everything
                transaction.Rollback();
                Console.WriteLine("Purchase failed. Transaction rolled back.");
                Console.WriteLine("Error: " + ex.Message);
            }
        }
    }
}

This content originally appeared on DEV Community and was authored by Vishnu KN