DEV Community

Vishnu KN
Vishnu KN

Posted on

TIL: Transactions in .NET

When your app talks to a database, it might have to do multiple things at once. Sometimes, these things might be connected, and separating them can cause problems.

Consider an online store where the user purchases something from his balance. For a single purchase, we might want to

  • Deduct from the user's balance in one table

  • Add a record of the order to another.

If one of these actions 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 like a promise to the database: "Do all of these actions together. If they succeed, great! If even one of them fails, undo everything and pretend like nothing happened"

In other words, 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);
            }
        }
    }
}

Enter fullscreen mode Exit fullscreen mode

Top comments (0)