ADO.NET 2.0 introduced robust features for database development, with transaction processing among the most important. Transactions protect data integrity and consistency across multiple operations—either every step commits, or the whole set rolls back.
A transaction is a series of tasks that succeed or fail as a unit. You can implement transactions in stored procedures or in code using TransactionScope, which offers clear, concise syntax. Transactions may be local (single database) or distributed (multiple databases); TransactionScope can automatically promote a local transaction to a distributed one when needed.
A minimal example that deletes discontinued products:
using System.Transactions;
using System.Data.SqlClient;
public void SimpleTransaction()
{
string connectionString = "Server=localhost;Database=MyDB;Integrated Security=true;";
using (TransactionScope transScope = new TransactionScope())
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
string query = "DELETE FROM Products WHERE Discontinued = 1";
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
// Complete the transaction
transScope.Complete();
}
}
}
Call Complete() to commit; otherwise the transaction rolls back automatically when the scope is disposed.
Add structured error handling for reliability:
public void TransactionWithErrorHandling()
{
string connectionString = "Server=localhost;Database=MyDB;Integrated Security=true;";
using (TransactionScope transScope = new TransactionScope())
{
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
// First operation
SqlCommand cmd1 = new SqlCommand(
"UPDATE Products SET UnitPrice = UnitPrice * 1.1 WHERE CategoryID = 1",
conn);
int rowsAffected = cmd1.ExecuteNonQuery();
Console.WriteLine($"{rowsAffected} products updated");
// Second operation
SqlCommand cmd2 = new SqlCommand(
"INSERT INTO PriceHistory (ChangeDate, Description) VALUES (@Date, @Desc)",
conn);
cmd2.Parameters.AddWithValue("@Date", DateTime.Now);
cmd2.Parameters.AddWithValue("@Desc", "10% price increase for category 1");
cmd2.ExecuteNonQuery();
// If we got here, everything succeeded
transScope.Complete();
Console.WriteLine("Transaction committed successfully");
}
}
catch (SqlException ex)
{
Console.WriteLine($"Transaction failed: {ex.Message}");
// Automatic rollback on dispose
}
}
}When two databases must be updated atomically, place both operations in the same scope; promotion to a distributed transaction happens automatically.
public void DistributedTransaction()
{
string connString1 = "Server=Server1;Database=ProductsDB;Integrated Security=true;";
string connString2 = "Server=Server2;Database=InventoryDB;Integrated Security=true;";
using (TransactionScope transScope = new TransactionScope())
{
try
{
// First database operation
using (SqlConnection conn1 = new SqlConnection(connString1))
{
string query1 = "UPDATE Products SET Discontinued = 1 WHERE ProductID = @ID";
SqlCommand cmd1 = new SqlCommand(query1, conn1);
cmd1.Parameters.AddWithValue("@ID", 42);
conn1.Open();
cmd1.ExecuteNonQuery();
Console.WriteLine("Product marked as discontinued");
}
// Second database operation
using (SqlConnection conn2 = new SqlConnection(connString2))
{
string query2 = "DELETE FROM Inventory WHERE ProductID = @ID";
SqlCommand cmd2 = new SqlCommand(query2, conn2);
cmd2.Parameters.AddWithValue("@ID", 42);
conn2.Open();
cmd2.ExecuteNonQuery();
Console.WriteLine("Inventory record removed");
}
// Both operations succeeded
transScope.Complete();
Console.WriteLine("Distributed transaction completed successfully");
}
catch (SqlException ex)
{
Console.WriteLine($"Transaction failed and rolled back: {ex.Message}");
}
}
}A multi-step order workflow—create order, add items, update inventory—commits as a single unit.
public void ProcessOrder(int orderId, int customerId, List<OrderItem> items)
{
string connectionString = "Server=localhost;Database=SalesDB;Integrated Security=true;";
using (TransactionScope transScope = new TransactionScope())
{
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
// Create the order header
SqlCommand createOrder = new SqlCommand(
"INSERT INTO Orders (OrderID, CustomerID, OrderDate, Status) " +
"VALUES (@OrderID, @CustomerID, @OrderDate, @Status)",
conn);
createOrder.Parameters.AddWithValue("@OrderID", orderId);
createOrder.Parameters.AddWithValue("@CustomerID", customerId);
createOrder.Parameters.AddWithValue("@OrderDate", DateTime.Now);
createOrder.Parameters.AddWithValue("@Status", "Pending");
createOrder.ExecuteNonQuery();
// Add order items
foreach (var item in items)
{
SqlCommand addItem = new SqlCommand(
"INSERT INTO OrderDetails (OrderID, ProductID, Quantity, UnitPrice) " +
"VALUES (@OrderID, @ProductID, @Quantity, @UnitPrice)",
conn);
addItem.Parameters.AddWithValue("@OrderID", orderId);
addItem.Parameters.AddWithValue("@ProductID", item.ProductID);
addItem.Parameters.AddWithValue("@Quantity", item.Quantity);
addItem.Parameters.AddWithValue("@UnitPrice", item.UnitPrice);
addItem.ExecuteNonQuery();
// Update inventory
SqlCommand updateInventory = new SqlCommand(
"UPDATE Products SET UnitsInStock = UnitsInStock - @Quantity " +
"WHERE ProductID = @ProductID",
conn);
updateInventory.Parameters.AddWithValue("@Quantity", item.Quantity);
updateInventory.Parameters.AddWithValue("@ProductID", item.ProductID);
updateInventory.ExecuteNonQuery();
}
// All operations succeeded
transScope.Complete();
Console.WriteLine($"Order {orderId} processed successfully");
}
}
catch (SqlException ex)
{
Console.WriteLine($"Order processing failed: {ex.Message}");
// Automatic rollback
}
}
}
Control isolation level and timeout via TransactionOptions and TransactionScopeOption.
public void TransactionWithOptions()
{
string connectionString = "Server=localhost;Database=MyDB;Integrated Security=true;";
// Set transaction options
TransactionOptions options = new TransactionOptions
{
IsolationLevel = IsolationLevel.ReadCommitted,
Timeout = TimeSpan.FromSeconds(30)
};
using (TransactionScope transScope = new TransactionScope(
TransactionScopeOption.Required,
options))
{
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand(
"UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1",
conn);
cmd.ExecuteNonQuery();
cmd = new SqlCommand(
"UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2",
conn);
cmd.ExecuteNonQuery();
transScope.Complete();
}
}
catch (TransactionAbortedException ex)
{
Console.WriteLine($"Transaction aborted: {ex.Message}");
}
catch (SqlException ex)
{
Console.WriteLine($"Database error: {ex.Message}");
}
}
}Compose operations with inner scopes; failing an inner scope can abort the outer scope as needed.
public void NestedTransactions()
{
using (TransactionScope outerScope = new TransactionScope())
{
try
{
// First operation
UpdateCustomerInfo();
// Nested transaction
using (TransactionScope innerScope = new TransactionScope())
{
try
{
UpdateOrderStatus();
innerScope.Complete();
}
catch (Exception ex)
{
Console.WriteLine($"Inner transaction failed: {ex.Message}");
throw; // Re-throw to fail outer transaction
}
}
// If we got here, everything succeeded
outerScope.Complete();
}
catch (Exception ex)
{
Console.WriteLine($"Outer transaction failed: {ex.Message}");
}
}
}
private void UpdateCustomerInfo()
{
// Implementation here
}
private void UpdateOrderStatus()
{
// Implementation here
}For lengthy operations, set a custom timeout on the scope to avoid unintended aborts.
public void LongRunningTransaction()
{
// Set a 5-minute timeout
TransactionOptions options = new TransactionOptions
{
Timeout = TimeSpan.FromMinutes(5)
};
using (TransactionScope transScope = new TransactionScope(
TransactionScopeOption.Required,
options))
{
try
{
// Perform long-running operations
ProcessLargeDataSet();
transScope.Complete();
}
catch (TimeoutException ex)
{
Console.WriteLine($"Transaction timed out: {ex.Message}");
}
}
}
private void ProcessLargeDataSet()
{
// Implementation for processing large amounts of data
}
TransactionScope simplifies transaction processing in ADO.NET, automatically handling local vs. distributed behavior, encouraging clean, scoped syntax, and ensuring fail-safe rollbacks. Whether targeting a single database or coordinating across many, it provides a reliable path to maintain data integrity.
If you don’t call Complete(), the transaction rolls back automatically when the scope is disposed. This ensures commits occur only on explicit success.
Opening a second connection to a different database within the same scope promotes the transaction to a distributed one via DTC, transparently and without code changes.