.NET Transactions
Introduction
In database operations, reliability is critical. Imagine transferring money from one bank account to another - either both operations (withdrawal and deposit) must succeed, or neither should happen at all. This is where transactions come in.
A transaction is a sequence of operations performed as a single logical unit of work. Transactions ensure that database operations follow the ACID principles:
- Atomicity: All operations complete successfully, or none of them do
- Consistency: Data remains in a consistent state before and after the transaction
- Isolation: Transactions operate independently without interference
- Durability: Once committed, changes are permanent
In this guide, we'll learn how to implement transactions in .NET applications using different approaches, from low-level ADO.NET to the more abstract Entity Framework Core.
Understanding Database Transactions in .NET
.NET provides several ways to work with database transactions:
- ADO.NET transactions (using
SqlTransaction,OleDbTransaction, etc.) - Transaction Scope (System.Transactions namespace)
- Entity Framework transactions
Let's explore each approach.
ADO.NET Transactions
ADO.NET provides explicit transaction control through connection objects. This is the most basic and direct way to manage database transactions.
Basic ADO.NET Transaction Example
csharp
using System;
using System.Data;
using System.Data.SqlClient;
public void TransferMoney(int fromAccountId, int toAccountId, decimal amount)
{
string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
// Begin a transaction
SqlTransaction transaction = connection.BeginTransaction();
try
{
// Create command objects that will execute against the same transaction
using (SqlCommand withdrawCommand = new SqlCommand())
using (SqlCommand depositCommand = new SqlCommand())
{
// Set up withdraw command
withdrawCommand.Connection = connection;
withdrawCommand.Transaction = transaction;
withdrawCommand.CommandText =
"UPDATE Accounts SET Balance = Balance - @Amount WHERE AccountId = @AccountId";
withdrawCommand.Parameters.AddWithValue("@Amount", amount);
withdrawCommand.Parameters.AddWithValue("@AccountId", fromAccountId);
// Set up deposit command
depositCommand.Connection = connection;
depositCommand.Transaction = transaction;
depositCommand.CommandText =
"UPDATE Accounts SET Balance = Balance + @Amount WHERE AccountId = @AccountId";
depositCommand.Parameters.AddWithValue("@Amount", amount);
depositCommand.Parameters.AddWithValue("@AccountId", toAccountId);
// Execute commands
int rowsAffectedWithdraw = withdrawCommand.ExecuteNonQuery();
int rowsAffectedDeposit = depositCommand.ExecuteNonQuery();
// Verify that both operations affected exactly one row each
if (rowsAffectedWithdraw != 1 || rowsAffectedDeposit != 1)
{
// Something went wrong, roll back the transaction
transaction.Rollback();
Console.WriteLine("Transaction failed: incorrect number of rows affected.");
return;
}
// If we get here, commit the transaction
transaction.Commit();
Console.WriteLine("Transaction committed successfully.");
}
}
catch (Exception ex)
{
// An error occurred, roll back the transaction
Console.WriteLine($"Transaction error: {ex.Message}");
try
{
transaction.Rollback();
}
catch (Exception rollbackEx)
{
Console.WriteLine($"Rollback error: {rollbackEx.Message}");
}
}
}
}
How It Works
- We create a connection to our database
- We begin a transaction using
connection.BeginTransaction() - We create and execute commands that are associated with this transaction
- If everything succeeds, we call
transaction.Commit()to make the changes permanent - If anything fails, we call
transaction.Rollback()to cancel all changes
TransactionScope
TransactionScope provides a higher-level abstraction for working with transactions. It's simpler to use and can even coordinate transactions across multiple connections or resources (distributed transactions).
Basic TransactionScope Example
csharp
using System;
using System.Data;
using System.Data.SqlClient;
using System.Transactions;
public void TransferMoneyWithTransactionScope(int fromAccountId, int toAccountId, decimal amount)
{
string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";
// Create a TransactionScope
using (TransactionScope scope = new TransactionScope())
{
try
{
// Perform the withdraw operation
using (SqlConnection connection1 = new SqlConnection(connectionString))
{
connection1.Open();
using (SqlCommand withdrawCommand = new SqlCommand())
{
withdrawCommand.Connection = connection1;
withdrawCommand.CommandText =
"UPDATE Accounts SET Balance = Balance - @Amount WHERE AccountId = @AccountId";
withdrawCommand.Parameters.AddWithValue("@Amount", amount);
withdrawCommand.Parameters.AddWithValue("@AccountId", fromAccountId);
int rowsAffected = withdrawCommand.ExecuteNonQuery();
if (rowsAffected != 1)
{
throw new Exception("Failed to withdraw money from account.");
}
}
}
// Perform the deposit operation
using (SqlConnection connection2 = new SqlConnection(connectionString))
{
connection2.Open();
using (SqlCommand depositCommand = new SqlCommand())
{
depositCommand.Connection = connection2;
depositCommand.CommandText =
"UPDATE Accounts SET Balance = Balance + @Amount WHERE AccountId = @AccountId";
depositCommand.Parameters.AddWithValue("@Amount", amount);
depositCommand.Parameters.AddWithValue("@AccountId", toAccountId);
int rowsAffected = depositCommand.ExecuteNonQuery();
if (rowsAffected != 1)
{
throw new Exception("Failed to deposit money to account.");
}
}
}
// If we get here without exceptions, commit the transaction
scope.Complete();
Console.WriteLine("Transaction completed successfully.");
}
catch (Exception ex)
{
// If any exception occurs, the transaction is automatically rolled back
Console.WriteLine($"Transaction failed: {ex.Message}");
// The TransactionScope will automatically be disposed and the transaction rolled back
}
}
}
How TransactionScope Works
- We create a
TransactionScopeusing theusingstatement - We perform our operations within this scope
- If all operations succeed, we call
scope.Complete()to commit the transaction - If any exception is thrown, or if
scope.Complete()is not called, the transaction is automatically rolled back
Entity Framework Core Transactions
Entity Framework Core handles transactions automatically for most operations. When you call SaveChanges(), EF Core wraps that operation in a transaction automatically. However, for multiple operations that must succeed or fail as a unit, you can use explicit transactions.
Implicit Transactions
csharp
using System;
using Microsoft.EntityFrameworkCore;
public class BankContext : DbContext
{
public DbSet<Account> Accounts { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer("Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;");
}
}
public class Account
{
public int AccountId { get; set; }
public decimal Balance { get; set; }
public string Owner { get; set; }
}
public void UpdateAccount(int accountId, decimal newAmount)
{
using (var context = new BankContext())
{
var account = context.Accounts.Find(accountId);
account.Balance = newAmount;
// This automatically uses a transaction
context.SaveChanges();
Console.WriteLine("Account updated successfully.");
}
}
Explicit EF Core Transactions
For multiple operations that need to be part of the same transaction:
csharp
using System;
using Microsoft.EntityFrameworkCore;
public void TransferMoneyWithEF(int fromAccountId, int toAccountId, decimal amount)
{
using (var context = new BankContext())
{
using (var transaction = context.Database.BeginTransaction())
{
try
{
var fromAccount = context.Accounts.Find(fromAccountId);
var toAccount = context.Accounts.Find(toAccountId);
if (fromAccount == null || toAccount == null)
{
throw new Exception("One or both accounts not found");
}
if (fromAccount.Balance < amount)
{
throw new Exception("Insufficient funds");
}
fromAccount.Balance -= amount;
toAccount.Balance += amount;
context.SaveChanges();
// Add an audit record
context.TransactionLogs.Add(new TransactionLog
{
TransactionDate = DateTime.Now,
FromAccountId = fromAccountId,
ToAccountId = toAccountId,
Amount = amount
});
context.SaveChanges();
// Commit the transaction
transaction.Commit();
Console.WriteLine("Transfer completed successfully.");
}
catch (Exception ex)
{
// Roll back on any exception
transaction.Rollback();
Console.WriteLine($"Transfer failed: {ex.Message}");
}
}
}
}
How EF Core Transactions Work
- Begin a transaction with
context.Database.BeginTransaction() - Perform your database operations
- Call
SaveChanges()to persist the operations (still within the transaction) - Call
transaction.Commit()to finalize the transaction - If an error occurs, call
transaction.Rollback()to undo all changes
Transaction Isolation Levels
In real-world scenarios with many users accessing a database simultaneously, you need to control how transactions interact with each other. This is done using isolation levels.
csharp
using System;
using System.Data;
using System.Data.SqlClient;
public void TransactionWithIsolationLevel()
{
string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
// Begin a transaction with a specific isolation level
SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.Serializable);
try
{
// Your transaction code here...
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
Console.WriteLine($"Error: {ex.Message}");
}
}
}
Common Isolation Levels
- ReadUncommitted: Lowest isolation level; allows dirty reads (seeing uncommitted changes from other transactions)
- ReadCommitted: Prevents dirty reads; allows non-repeatable reads and phantom reads
- RepeatableRead: Prevents dirty and non-repeatable reads; allows phantom reads
- Serializable: Highest isolation level; prevents all concurrency side effects but has the lowest performance
Best Practices for Transactions
- Keep Transactions Short: Long-running transactions can cause locking and performance issues
- Only Include What's Necessary: Don't include operations that don't need to be part of the transaction
- Handle Exceptions Properly: Always include proper exception handling with rollback
- Choose the Right Isolation Level: Higher isolation levels provide more consistency but less concurrency
- Consider Connection Pooling: Be mindful that open transactions hold database connections
- Test Concurrency Scenarios: Make sure your application handles multiple simultaneous transactions correctly
Real-World Example: Order Processing System
Here's a more complex example involving multiple tables in an order processing system:
csharp
using System;
using System.Data;
using System.Data.SqlClient;
using System.Transactions;
public class OrderProcessor
{
private readonly string _connectionString;
public OrderProcessor(string connectionString)
{
_connectionString = connectionString;
}
public int ProcessOrder(int customerId, List<OrderItem> items, string shippingAddress)
{
// Use a TransactionScope to ensure all operations are atomic
using (var scope = new TransactionScope())
{
try
{
int orderId;
decimal totalAmount = items.Sum(i => i.Price * i.Quantity);
using (var connection = new SqlConnection(_connectionString))
{
connection.Open();
// 1. Create the order
using (var cmd = new SqlCommand(
"INSERT INTO Orders (CustomerId, OrderDate, TotalAmount, ShippingAddress, Status) " +
"VALUES (@CustomerId, @OrderDate, @TotalAmount, @ShippingAddress, @Status); " +
"SELECT SCOPE_IDENTITY();", connection))
{
cmd.Parameters.AddWithValue("@CustomerId", customerId);
cmd.Parameters.AddWithValue("@OrderDate", DateTime.Now);
cmd.Parameters.AddWithValue("@TotalAmount", totalAmount);
cmd.Parameters.AddWithValue("@ShippingAddress", shippingAddress);
cmd.Parameters.AddWithValue("@Status", "Pending");
// Get the new order ID
orderId = Convert.ToInt32(cmd.ExecuteScalar());
}
// 2. Create order items
foreach (var item in items)
{
using (var cmd = new SqlCommand(
"INSERT INTO OrderItems (OrderId, ProductId, Quantity, UnitPrice) " +
"VALUES (@OrderId, @ProductId, @Quantity, @UnitPrice)", connection))
{
cmd.Parameters.AddWithValue("@OrderId", orderId);
cmd.Parameters.AddWithValue("@ProductId", item.ProductId);
cmd.Parameters.AddWithValue("@Quantity", item.Quantity);
cmd.Parameters.AddWithValue("@UnitPrice", item.Price);
cmd.ExecuteNonQuery();
}
}
// 3. Update inventory
foreach (var item in items)
{
using (var cmd = new SqlCommand(
"UPDATE Products SET StockQuantity = StockQuantity - @Quantity " +
"WHERE ProductId = @ProductId AND StockQuantity >= @Quantity", connection))
{
cmd.Parameters.AddWithValue("@ProductId", item.ProductId);
cmd.Parameters.AddWithValue("@Quantity", item.Quantity);
int rowsAffected = cmd.ExecuteNonQuery();
if (rowsAffected == 0)
{
// This means there wasn't enough inventory
throw new Exception($"Insufficient inventory for product {item.ProductId}");
}
}
}
// 4. Create customer notification record
using (var cmd = new SqlCommand(
"INSERT INTO CustomerNotifications (CustomerId, OrderId, NotificationType, Status) " +
"VALUES (@CustomerId, @OrderId, @NotificationType, @Status)", connection))
{
cmd.Parameters.AddWithValue("@CustomerId", customerId);
cmd.Parameters.AddWithValue("@OrderId", orderId);
cmd.Parameters.AddWithValue("@NotificationType", "Email");
cmd.Parameters.AddWithValue("@Status", "Pending");
cmd.ExecuteNonQuery();
}
}
// Complete the transaction if everything succeeded
scope.Complete();
Console.WriteLine($"Order {orderId} processed successfully.");
return orderId;
}
catch (Exception ex)
{
// The transaction will automatically be rolled back
Console.WriteLine($"Order processing failed: {ex.Message}");
throw; // Re-throw the exception for higher-level handling
}
}
}
}
public class OrderItem
{
public int ProductId { get; set; }
public int Quantity { get; set; }
public decimal Price { get; set; }
}
Summary
Transactions are essential for maintaining data integrity in any database application. In .NET, you have several options for implementing transactions:
- ADO.NET transactions: Low-level, direct control with explicit commit and rollback
- TransactionScope: Higher-level abstraction that can coordinate across multiple resources
- Entity Framework transactions: Built-in automatic transactions with explicit control when needed
Remember the key principles of transactions:
- Atomicity: All or nothing
- Consistency: Maintain data integrity
- Isolation: Protect transactions from each other
- Durability: Once committed, changes are permanent
By properly implementing transactions, you ensure your application's data operations are reliable, even in the face of system failures or concurrent access.
Additional Resources
- Microsoft Documentation: ADO.NET Transactions
- Microsoft Documentation: System.Transactions
- Entity Framework Core Transactions
Exercises
- Create a banking application that uses transactions to transfer money between accounts.
- Implement a transaction that spans multiple database operations (e.g., create an order and update inventory).
- Experiment with different isolation levels and observe the behavior when multiple users are accessing the same data.
- Create a transaction that involves both database access and file system operations using TransactionScope.
- Implement a retry mechanism for transactions that fail due to deadlocks.
If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)