C# Transactions
In database operations, ensuring data integrity is crucial. Imagine transferring money between bank accounts—you need to ensure that the money is both deducted from one account and added to another, with no possibility of only one operation succeeding. This is where transactions come in.
What are Transactions?
A transaction is a sequence of operations performed as a single logical unit of work. A transaction has four key properties, often referred to as ACID properties:
- Atomicity: All operations within the transaction either complete successfully or fail altogether.
- Consistency: The transaction brings the database from one valid state to another.
- Isolation: Transactions execute in isolation from one another.
- Durability: Once a transaction is committed, it remains so, even in the event of a system failure.
Why Use Transactions?
Consider a scenario where you're updating multiple related records in a database:
- Update customer billing information
- Create an order
- Reduce inventory counts
If any one of these steps fails, you'd want all operations to be rolled back to maintain data integrity. Transactions provide exactly this functionality.
Working with Transactions in ADO.NET
Let's see how to implement transactions using ADO.NET:
csharp
using System;
using System.Data;
using System.Data.SqlClient;
public class TransactionExample
{
public static void PerformTransaction()
{
string connectionString = "Data Source=ServerName;Initial Catalog=DatabaseName;Integrated Security=True";
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
// Create a transaction
SqlTransaction transaction = connection.BeginTransaction();
try
{
// Create command with transaction and connection
SqlCommand command = connection.CreateCommand();
command.Transaction = transaction;
// First operation
command.CommandText = "UPDATE Accounts SET Balance = Balance - 100 WHERE AccountId = 1";
command.ExecuteNonQuery();
// Second operation
command.CommandText = "UPDATE Accounts SET Balance = Balance + 100 WHERE AccountId = 2";
command.ExecuteNonQuery();
// If everything is successful, commit the transaction
transaction.Commit();
Console.WriteLine("Transaction committed successfully!");
}
catch (Exception ex)
{
// If an error occurs, roll back the transaction
Console.WriteLine($"Error occurred: {ex.Message}");
try
{
transaction.Rollback();
Console.WriteLine("Transaction rolled back successfully!");
}
catch (Exception rollbackEx)
{
Console.WriteLine($"Rollback error: {rollbackEx.Message}");
}
}
}
}
}
This example shows a typical banking transaction where money is transferred from one account to another.
Transaction Isolation Levels
SQL Server and other database systems support different isolation levels, which control how transactions interact with each other:
csharp
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
// Create a transaction with specified isolation level
SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
// Use the transaction as before
}
Common isolation levels include:
- ReadUncommitted: Allows dirty reads (seeing uncommitted changes from other transactions)
- ReadCommitted: Prevents dirty reads but allows non-repeatable reads
- RepeatableRead: Prevents dirty and non-repeatable reads
- Serializable: The highest isolation level, prevents all concurrency side effects
Transactions in Entity Framework
Entity Framework makes transactions even easier to use:
Entity Framework 6
csharp
using System;
using System.Data.Entity;
public class EF6TransactionExample
{
public static void PerformTransaction()
{
using (var context = new YourDbContext())
{
using (var dbContextTransaction = context.Database.BeginTransaction())
{
try
{
// Make your changes
var account1 = context.Accounts.Find(1);
account1.Balance -= 100;
var account2 = context.Accounts.Find(2);
account2.Balance += 100;
// Save changes to the database
context.SaveChanges();
// Commit transaction
dbContextTransaction.Commit();
Console.WriteLine("Transaction committed");
}
catch (Exception ex)
{
// Roll back if there was an exception
dbContextTransaction.Rollback();
Console.WriteLine($"Transaction rolled back: {ex.Message}");
}
}
}
}
}
Entity Framework Core
csharp
using System;
using Microsoft.EntityFrameworkCore;
public class EFCoreTransactionExample
{
public static void PerformTransaction()
{
using (var context = new YourDbContext())
{
using (var transaction = context.Database.BeginTransaction())
{
try
{
// Make your changes
var account1 = context.Accounts.Find(1);
account1.Balance -= 100;
var account2 = context.Accounts.Find(2);
account2.Balance += 100;
// Save changes to the database
context.SaveChanges();
// Commit transaction
transaction.Commit();
Console.WriteLine("Transaction committed");
}
catch (Exception ex)
{
// Roll back if there was an exception
transaction.Rollback();
Console.WriteLine($"Transaction rolled back: {ex.Message}");
}
}
}
}
}
Implicit Transactions in Entity Framework
Entity Framework wraps each SaveChanges() call in a transaction automatically, but you'll need explicit transactions when multiple SaveChanges() calls need to be treated as a single operation.
csharp
// This is implicitly wrapped in a transaction
context.Accounts.Add(new Account { Name = "New Account", Balance = 1000 });
context.SaveChanges(); // Automatic transaction happens here
Real-World Example: Order Processing System
Let's look at a more comprehensive example of using transactions in an order processing system:
csharp
using System;
using System.Data.SqlClient;
public class OrderProcessor
{
private readonly string _connectionString;
public OrderProcessor(string connectionString)
{
_connectionString = connectionString;
}
public void ProcessOrder(int customerId, int productId, int quantity)
{
using (SqlConnection connection = new SqlConnection(_connectionString))
{
connection.Open();
SqlTransaction transaction = connection.BeginTransaction();
try
{
// Check if we have enough inventory
SqlCommand checkInventory = connection.CreateCommand();
checkInventory.Transaction = transaction;
checkInventory.CommandText = "SELECT StockQuantity FROM Products WHERE ProductId = @ProductId";
checkInventory.Parameters.AddWithValue("@ProductId", productId);
int stockQuantity = Convert.ToInt32(checkInventory.ExecuteScalar());
if (stockQuantity < quantity)
{
throw new InvalidOperationException("Not enough inventory");
}
// Get product price
SqlCommand getPrice = connection.CreateCommand();
getPrice.Transaction = transaction;
getPrice.CommandText = "SELECT Price FROM Products WHERE ProductId = @ProductId";
getPrice.Parameters.AddWithValue("@ProductId", productId);
decimal price = Convert.ToDecimal(getPrice.ExecuteScalar());
decimal totalAmount = price * quantity;
// Create order
SqlCommand createOrder = connection.CreateCommand();
createOrder.Transaction = transaction;
createOrder.CommandText = @"
INSERT INTO Orders (CustomerId, OrderDate, TotalAmount)
VALUES (@CustomerId, @OrderDate, @TotalAmount);
SELECT SCOPE_IDENTITY();";
createOrder.Parameters.AddWithValue("@CustomerId", customerId);
createOrder.Parameters.AddWithValue("@OrderDate", DateTime.Now);
createOrder.Parameters.AddWithValue("@TotalAmount", totalAmount);
int orderId = Convert.ToInt32(createOrder.ExecuteScalar());
// Create order details
SqlCommand createOrderDetails = connection.CreateCommand();
createOrderDetails.Transaction = transaction;
createOrderDetails.CommandText = @"
INSERT INTO OrderDetails (OrderId, ProductId, Quantity, UnitPrice)
VALUES (@OrderId, @ProductId, @Quantity, @UnitPrice)";
createOrderDetails.Parameters.AddWithValue("@OrderId", orderId);
createOrderDetails.Parameters.AddWithValue("@ProductId", productId);
createOrderDetails.Parameters.AddWithValue("@Quantity", quantity);
createOrderDetails.Parameters.AddWithValue("@UnitPrice", price);
createOrderDetails.ExecuteNonQuery();
// Update inventory
SqlCommand updateInventory = connection.CreateCommand();
updateInventory.Transaction = transaction;
updateInventory.CommandText = @"
UPDATE Products
SET StockQuantity = StockQuantity - @Quantity
WHERE ProductId = @ProductId";
updateInventory.Parameters.AddWithValue("@ProductId", productId);
updateInventory.Parameters.AddWithValue("@Quantity", quantity);
updateInventory.ExecuteNonQuery();
// Commit the transaction
transaction.Commit();
Console.WriteLine($"Order {orderId} processed successfully");
}
catch (Exception ex)
{
transaction.Rollback();
Console.WriteLine($"Order processing failed: {ex.Message}");
throw; // Re-throw the exception for higher-level handling
}
}
}
}
// Usage
// var orderProcessor = new OrderProcessor("connection string here");
// orderProcessor.ProcessOrder(1, 100, 5); // Customer 1 orders 5 units of product 100
This example demonstrates a realistic order processing system that:
- Checks inventory levels
- Creates an order record
- Creates order detail records
- Updates inventory levels
All these operations are wrapped in a transaction to ensure data integrity.
Best Practices for Using Transactions
- Keep transactions short: Long-running transactions can lock database resources.
- Only include necessary operations: Don't wrap operations that don't need to be transactional.
- Handle exceptions properly: Always include proper exception handling with rollbacks.
- Choose the appropriate isolation level: Higher isolation levels provide more data consistency but reduce concurrency.
- Consider distributed transactions: For operations spanning multiple databases, consider using
System.Transactions.TransactionScope.
Distributed Transactions
When you need to work with multiple databases or resource managers, you might need distributed transactions:
csharp
using System;
using System.Data.SqlClient;
using System.Transactions;
public class DistributedTransactionExample
{
public static void PerformDistributedTransaction()
{
using (var scope = new TransactionScope())
{
try
{
// Work with the first database
using (var connection1 = new SqlConnection("connection string 1"))
{
connection1.Open();
var command1 = connection1.CreateCommand();
command1.CommandText = "UPDATE Accounts SET Balance = Balance - 100 WHERE AccountId = 1";
command1.ExecuteNonQuery();
}
// Work with the second database
using (var connection2 = new SqlConnection("connection string 2"))
{
connection2.Open();
var command2 = connection2.CreateCommand();
command2.CommandText = "UPDATE AccountMirror SET Balance = Balance - 100 WHERE AccountId = 1";
command2.ExecuteNonQuery();
}
// Complete the transaction
scope.Complete();
Console.WriteLine("Distributed transaction committed");
}
catch (Exception ex)
{
// Transaction will automatically abort if not completed
Console.WriteLine($"Transaction aborted: {ex.Message}");
}
}
}
}
Summary
Transactions are a crucial part of database programming, ensuring that your data remains consistent even when things go wrong. In C#, you can use transactions with ADO.NET for direct control or rely on Entity Framework's transaction management for a higher-level approach.
Key takeaways:
- Transactions maintain data integrity through ACID properties
- ADO.NET provides direct transaction control with
BeginTransaction,Commit, andRollback - Entity Framework wraps operations in implicit transactions and supports explicit transactions
- For cross-database operations, use distributed transactions with
TransactionScope
By properly implementing transactions in your database operations, you can ensure your applications maintain data integrity even in the face of errors or system failures.
Exercises
- Create a banking application that transfers funds between accounts using transactions.
- Implement a simple inventory management system that uses transactions to update stock levels and create orders.
- Modify the order processing example to include multiple products in a single order.
- Create an example that demonstrates the differences between transaction isolation levels.
- Implement a transaction with a savepoint that allows partial rollbacks.
Additional Resources
If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)