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);
}
}
}
}
Top comments (0)