Back to: ADO.NET Core Tutorial For Beginners and Professionals
ADO.NET Core Implicit vs Explicit Transactions
In this article, I will discuss ADO.NET Core Implicit vs Explicit Transactions with Examples. Please read our previous article discussing how to implement different Transaction Isolation Levels using ADO.NET Core.
ADO.NET Core Implicit vs. Explicit Transactions
Transactions are fundamental in ensuring data integrity and consistency by treating a set of operations as a single atomic unit. In ADO.NET Core, we can manage transactions in two ways: implicitly (where SQL Server and the framework automatically manage the transaction boundaries) and explicitly (where we have full control over when to start, commit, or rollback a transaction). The choice to use Implicit vs Explicit Transactions depends on the complexity of the operations you need to perform. Let us proceed and understand each approach with real-time examples.
Database Setup in SQL Server
The following SQL script creates a database in SQL Server with two related tables: Employee and Address. In the Employee table, we store employee details, and in the Address table, we store address information associated with each employee. We also enforce referential integrity by defining a foreign key with an ON DELETE CASCADE constraint. This ensures that all corresponding addresses are automatically removed when an employee is deleted. Please execute the following script in SQL Server Management Studio (SSMS) or any SQL client tool connected to your SQL Server instance.
-- Create the EmployeesDB database
CREATE DATABASE EmployeesDB;
GO
-- Use the EmployeesDB database
USE EmployeesDB;
GO
-- Create Employee Table with realistic columns
CREATE TABLE Employee
(
EmployeeId INT IDENTITY(1,1) PRIMARY KEY, -- Primary key
Name NVARCHAR(100) NOT NULL, -- Employee Name
Position NVARCHAR(100) NOT NULL, -- Job Position
Email NVARCHAR(200), -- Employee Email
Salary DECIMAL(10, 2), -- Employee Salary
HireDate DATETIME NOT NULL DEFAULT GETDATE() -- Hire date defaults to current time
);
GO
-- Create Address Table with additional details and referential integrity
CREATE TABLE Address
(
AddressId INT IDENTITY(1,1) PRIMARY KEY, -- Primary key
EmployeeId INT NOT NULL, -- Foreign key to Employee
Street NVARCHAR(200) NOT NULL, -- Street address
City NVARCHAR(100) NOT NULL, -- City name
State NVARCHAR(100) NOT NULL, -- State name
ZipCode NVARCHAR(10) NULL, -- Optional zip code
FOREIGN KEY (EmployeeId)
REFERENCES Employee(EmployeeId)
ON DELETE CASCADE -- Cascading delete to maintain referential integrity
);
GO
Script Explanation
- Employee Table: This table contains an auto-incrementing primary key (EmployeeId), basic employee information (name, Email, Salary, and position), and a HireDate column that defaults to the current date/time.
- Address Table: This table uses an auto-incrementing AddressId and includes address details along with an EmployeeId that links every address to an existing employee. The ON DELETE CASCADE clause ensures that all associated addresses are automatically removed if an employee record is deleted.
What are Implicit Transactions in ADO.NET Core?
Implicit transactions are automatically managed by the framework. With the help of the TransactionScope class, all operations within the scope are automatically part of a transaction. If all operations succeed and you call Complete(), the transaction is committed; if an exception occurs (or Complete() is not called), the transaction is rolled back automatically. You do not manually call BeginTransaction, Commit, or Rollback methods. This method is ideal for simple, self-contained operations.
When Should We Use Implicit Transactions in ADO.NET Core?
We need to use Implicit transactions in the below scenarios:
- Simple Operations: If you have a relatively small set of data operations that either all succeed or all fail, implicit transactions can simplify your code.
- Cleaner Code: When the scope of your transactional operations is limited to a single method or code block, TransactionScope can be very readable.
- Automatic Management: When you want the framework to automatically rollback any changes if an error occurs.
How Do We Implement Implicit Transactions in ADO.NET Core?
Using the TransactionScope class, we wrap a block of code so that all operations within are automatically part of a transaction. So, we need to follow the below steps:
- Create a TransactionScope object within a using block.
- Perform all your ADO.NET operations (inserts, updates, deletes) inside that block.
- If everything succeeds, call scope.Complete() to commit.
- If an exception occurs or scope.Complete() isn’t called; the transaction automatically rolls back when the TransactionScope is disposed.
The syntax to use Implicit Transactions in ADO.NET Core is given below:
Understanding TransactionScope in ADO.NET Core
- TransactionScope allows us to wrap a series of database operations in a transaction without manually creating or committing the transaction object.
- If all operations succeed, we need to call scope.Complete() to commit the transaction.
- If any operation fails (throws an exception) or if we never call scope.Complete(), the transaction is rolled back automatically.
This approach keeps the code clear and ensures transaction boundaries are automatically respected.
Example to Understand Implicit Transactions in ADO.NET Core
The following .NET Core Console Application demonstrates how to use implicit transactions via TransactionScope. This example inserts a new employee and their address into the database. If both insertions succeed, the transaction is committed; otherwise, it is automatically rolled back. The example code is self-explained, so please read the comment lines for a better understanding.
using Microsoft.Data.SqlClient;
using System.Transactions;
namespace ADODOTNETCoreDemo
{
internal class Program
{
static void Main(string[] args)
{
// Define the connection string to connect to the EmployeesDB database.
string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EmployeesDB;Trusted_Connection=True;TrustServerCertificate=True;";
try
{
// Begin a TransactionScope block to automatically manage the transaction.
using (TransactionScope scope = new TransactionScope())
{
// Create and open a SQL connection within the transaction scope.
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
Console.WriteLine("Database connection opened.");
// ------------ Insert a new Employee ------------
// Parameterized query for security and clarity
string insertEmployeeQuery = @"
INSERT INTO Employee (Name, Position, Email, Salary)
VALUES (@Name, @Position, @Email, @Salary);
SELECT SCOPE_IDENTITY();
";
using (SqlCommand command1 = new SqlCommand(insertEmployeeQuery, connection))
{
// Add parameters to prevent SQL injection and supply values.
command1.Parameters.AddWithValue("@Name", "John Doe");
command1.Parameters.AddWithValue("@Position", "Software Developer");
command1.Parameters.AddWithValue("@Email", "john.doe@example.com");
command1.Parameters.AddWithValue("@Salary", 75000);
// Execute the insert command and retrieve the newly inserted EmployeeId.
int employeeId = Convert.ToInt32(command1.ExecuteScalar());
Console.WriteLine($"Employee inserted with EmployeeId: {employeeId}");
// ------------ Insert the Address for the new Employee ------------
string insertAddressQuery = @"
INSERT INTO Address (EmployeeId, Street, City, State, ZipCode)
VALUES (@EmployeeId, @Street, @City, @State, @ZipCode);
SELECT SCOPE_IDENTITY();
";
using (SqlCommand command2 = new SqlCommand(insertAddressQuery, connection))
{
// Add parameters for the address insertion.
command2.Parameters.AddWithValue("@EmployeeId", employeeId);
command2.Parameters.AddWithValue("@Street", "123 Main St");
command2.Parameters.AddWithValue("@City", "Anytown");
command2.Parameters.AddWithValue("@State", "Anystate");
command2.Parameters.AddWithValue("@ZipCode", "12345");
// Execute the command and capture the new AddressId.
int addressId = Convert.ToInt32(command2.ExecuteScalar());
Console.WriteLine($"Address inserted with AddressId: {addressId}");
}
}
}
// If everything is successful, call Complete() to commit the transaction
scope.Complete();
Console.WriteLine("Transaction completed successfully.");
}
}
catch (Exception ex)
{
// In case of an error, the transaction is automatically rolled back when the TransactionScope is disposed.
Console.WriteLine($"An error occurred: {ex.Message}");
}
}
}
}
Code Explanation
- Connection Setup: The connection string connects to the EmployeesDB database. The connection is opened inside the TransactionScope to ensure that all operations run within the same transaction context.
- TransactionScope: The using (TransactionScope scope = new TransactionScope()) block automatically manages the transaction. If scope.Complete() is called; all changes are committed; if not, they are rolled back.
- Employee Insertion: The first SQL command inserts a new employee. The parameterized query prevents SQL injection, and SCOPE_IDENTITY() retrieves the newly generated EmployeeId.
- Address Insertion: The second SQL command inserts an address using the EmployeeId retrieved from the previous step.
- Automatic Rollback: If any exception occurs during the execution of the commands, the absence of a call to scope.Complete() will cause the transaction to automatically roll back upon disposal of the TransactionScope.
Output:
TransactionScope in Asynchronous Scenarios:
To use TransactionScope in asynchronous scenarios (when using asynchronous methods such as OpenAsync(), ExecuteScalarAsync(), ExecuteNonQueryAsync(), etc.), we need to enable the asynchronous flow explicitly. This can be done by using the overload constructor that accepts a TransactionScopeAsyncFlowOption parameter. The syntax is given below:
using (TransactionScope scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
// your code here...
// Ensure you call scope.Complete() after all operations succeed.
scope.Complete();
}
This will tell the .NET framework that the TransactionScope can now be used in an asynchronous environment. For a better understanding, we are converting the previous example to use asynchronous programming, and this time, we are using TransactionScope with TransactionScopeAsyncFlowOption.Enabled parameter. The following code is self-explained, so please read the comment lines for a better understanding.
using Microsoft.Data.SqlClient;
using System.Transactions;
namespace ADODOTNETCoreDemo
{
public class Program
{
static async Task Main(string[] args)
{
// Define the connection string to connect to the EmployeesDB database.
string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EmployeesDB;Trusted_Connection=True;TrustServerCertificate=True;";
try
{
// Begin a TransactionScope block to automatically manage the transaction.
// Enable async flow for TransactionScope
using (TransactionScope scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
// Create and open a SQL connection within the transaction scope.
using (SqlConnection connection = new SqlConnection(connectionString))
{
//Open the Connection Asynchronously
await connection.OpenAsync();
Console.WriteLine("Database connection opened.");
// ------------ Insert a new Employee ------------
// Parameterized query for security and clarity
string insertEmployeeQuery = @"
INSERT INTO Employee (Name, Position, Email, Salary)
VALUES (@Name, @Position, @Email, @Salary);
SELECT SCOPE_IDENTITY();
";
using (SqlCommand command1 = new SqlCommand(insertEmployeeQuery, connection))
{
// Add parameters to prevent SQL injection and supply values.
command1.Parameters.AddWithValue("@Name", "John Doe");
command1.Parameters.AddWithValue("@Position", "Software Developer");
command1.Parameters.AddWithValue("@Email", "john.doe@example.com");
command1.Parameters.AddWithValue("@Salary", 75000);
// Execute the insert command Asynchronously and retrieve the newly inserted EmployeeId.
int employeeId = Convert.ToInt32(await command1.ExecuteScalarAsync());
Console.WriteLine($"Employee inserted with EmployeeId: {employeeId}");
// ------------ Insert the Address for the new Employee ------------
string insertAddressQuery = @"
INSERT INTO Address (EmployeeId, Street, City, State, ZipCode)
VALUES (@EmployeeId, @Street, @City, @State, @ZipCode);
SELECT SCOPE_IDENTITY();
";
using (SqlCommand command2 = new SqlCommand(insertAddressQuery, connection))
{
// Add parameters for the address insertion.
command2.Parameters.AddWithValue("@EmployeeId", employeeId);
command2.Parameters.AddWithValue("@Street", "123 Main St");
command2.Parameters.AddWithValue("@City", "Anytown");
command2.Parameters.AddWithValue("@State", "Anystate");
command2.Parameters.AddWithValue("@ZipCode", "12345");
// Execute the command Asynchronously and capture the new AddressId .
int addressId = Convert.ToInt32(await command2.ExecuteScalarAsync());
Console.WriteLine($"Address inserted with AddressId: {addressId}");
}
}
}
// If everything is successful, call Complete() to commit the transaction
scope.Complete();
Console.WriteLine("Transaction completed successfully.");
}
}
catch (Exception ex)
{
// In case of an error, the transaction is automatically rolled back when the TransactionScope is disposed.
Console.WriteLine($"An error occurred: {ex.Message}");
}
}
}
}
Key Points:
- Async Flow Option: When dealing with asynchronous operations, use Enabled to ensure that the scope can be correctly disposed even if the continuation occurs on a different thread.
- Calling Complete: Remember to call scope.Complete() when your operations are successful so that the transaction is committed. If an exception occurs and you don’t call Complete(), the transaction will roll back.
What are Explicit Transactions in ADO.NET Core?
Explicit transactions provide full control over transaction management by manually defining the transaction boundaries. Using the BeginTransaction method on a SqlConnection, you can group multiple operations under a single transaction and explicitly call Commit() or Rollback() based on whether all operations succeed. This approach is ideal for complex, multi-step operations that must be committed or rolled back as a single unit of work.
When to Use Explicit Transactions?
We need to use Explicit transactions in the below scenarios:
- Complex Multi-Step Operations: When your business logic involves multiple interdependent operations where a failure in one should undo all changes.
- More Control: When you need to decide exactly when to commit or rollback transactions.
- Spanning Multiple Methods/Classes: When transaction management needs to be handled manually across a broader scope.
How Do We Implement Explicit Transactions in ADO.NET Core?
To manage explicit transactions, you need to:
- Open a database connection.
- Start a transaction using SqlTransaction transaction = connection.BeginTransaction();.
- Associate each SqlCommand with this transaction by setting the command.Transaction = transaction;.
- Commit the transaction using transaction.Commit() if every operation succeeds, or roll it back with transaction.Rollback() if any operation fails.
The syntax to use Explicit Transactions in ADO.NET Core is given below:
Example to Understand Explicit Transactions in ADO.NET Core
The following example performs the same operations (inserting an Employee and an Address) but manages the transaction explicitly. This gives us explicit control over when the transaction is committed or rolled back. The example code is self-explained, so please read the comment lines for a better understanding.
using Microsoft.Data.SqlClient;
namespace ADODOTNETCoreDemo
{
internal class Program
{
static void Main(string[] args)
{
// Define the connection string to access the EmployeesDB database.
string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EmployeesDB;Trusted_Connection=True;TrustServerCertificate=True;";
try
{
// Create and open the SQL connection.
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
Console.WriteLine("Database connection opened.");
// Begin an explicit transaction
SqlTransaction transaction = connection.BeginTransaction();
Console.WriteLine("Explicit transaction started.");
try
{
// Insert a new Employee Record
string insertEmployeeQuery = @"
INSERT INTO Employee (Name, Position, Email, Salary)
VALUES (@Name, @Position, @Email, @Salary);
SELECT SCOPE_IDENTITY();
";
using (SqlCommand command1 = new SqlCommand(insertEmployeeQuery, connection, transaction))
{
// Add parameters to the query.
command1.Parameters.AddWithValue("@Name", "Jane Doe");
command1.Parameters.AddWithValue("@Position", "Project Manager");
command1.Parameters.AddWithValue("@Email", "jane.doe@example.com");
command1.Parameters.AddWithValue("@Salary", 90000);
// Execute the insert command and retrieve the newly inserted EmployeeId.
int employeeId = Convert.ToInt32(command1.ExecuteScalar());
Console.WriteLine($"Employee inserted with EmployeeId: {employeeId}");
// Insert an address record linked to the newly inserted employee.
string insertAddressQuery = @"
INSERT INTO Address (EmployeeId, Street, City, State, ZipCode)
VALUES (@EmployeeId, @Street, @City, @State, @ZipCode);
SELECT SCOPE_IDENTITY();
";
using (SqlCommand command2 = new SqlCommand(insertAddressQuery, connection, transaction))
{
// Supply parameters for the address insertion.
command2.Parameters.AddWithValue("@EmployeeId", employeeId);
command2.Parameters.AddWithValue("@Street", "456 Elm St");
command2.Parameters.AddWithValue("@City", "Othertown");
command2.Parameters.AddWithValue("@State", "Otherstate");
command2.Parameters.AddWithValue("@ZipCode", "54321");
// Execute the command and capture the new AddressId.
int addressId = Convert.ToInt32(command2.ExecuteScalar());
Console.WriteLine($"Address inserted with AddressId: {addressId}");
}
}
// If all commands execute successfully, commit the transaction.
transaction.Commit();
Console.WriteLine("Transaction committed successfully.");
}
catch (Exception innerEx)
{
// If any error occurs during the transaction, attempt to roll back.
try
{
transaction.Rollback();
Console.WriteLine("Transaction rolled back due to an error.");
}
catch (Exception rollbackEx)
{
Console.WriteLine($"Error during rollback: {rollbackEx.Message}");
}
Console.WriteLine($"An error occurred during transaction processing: {innerEx.Message}");
}
}
}
catch (Exception ex)
{
// Handle any errors related to the SQL connection or outer block.
Console.WriteLine($"A connection error occurred: {ex.Message}");
}
}
}
}
Code Explanation
- Manual Transaction Control: After opening the connection, the code begins an explicit transaction using connection.BeginTransaction(). All subsequent commands use this transaction object.
- Employee Insertion: The first command inserts a new employee record. The query is parameterized and uses SCOPE_IDENTITY() to obtain the new EmployeeId.
- Address Insertion: The second command inserts the corresponding address and uses the same transaction to ensure atomicity.
- Commit/Rollback Logic: If both operations succeed, transaction.Commit() is called to save the changes; if any operation fails, the inner catch block rolls back the transaction to maintain database integrity.
Output:
When to Use One Over Another in Real-Time Applications?
ADO.NET Core offers two transaction management approaches:
- Implicit Transactions (using TransactionScope) simplify the coding process by automatically beginning and ending transactions with minimal manual control. They are best for simple and self-contained operations.
- Explicit Transactions (using BeginTransaction()) give you complete control over the transaction lifecycle, making them indispensable for complex, multi-step processes where atomicity is paramount.
Please have a look at the following image for a better understanding.
Implicit Transactions:
- Great for simple operations within a single method or a well-defined code block.
- Useful when you want minimal code and a straightforward approach to ensure atomicity (all-or-nothing) for a small set of statements.
Explicit Transactions:
- It is best suited for complex or multi-step processes where you may need to handle conditional logic before committing or rolling back.
- Provides complete control, especially in enterprise scenarios where multiple classes or methods might participate in a larger transaction.
Choose the approach that best fits your application’s complexity and requirements. Implicit transactions may be enough for straightforward data operations. Explicit transactions are typically preferred for advanced scenarios involving multiple operations or complex error handling.
In the next article, I will discuss ADO.NET Core Distributed Transaction with Examples. In this article, I explain ADO.NET Core Implicit vs. Explicit Transactions with Examples. I would like your feedback. Please post your feedback, questions, or comments about this article.
About the Author: Pranaya Rout
Pranaya Rout has published more than 3,000 articles in his 11-year career. Pranaya Rout has very good experience with Microsoft Technologies, Including C#, VB, ASP.NET MVC, ASP.NET Web API, EF, EF Core, ADO.NET, LINQ, SQL Server, MYSQL, Oracle, ASP.NET Core, Cloud Computing, Microservices, Design Patterns and still learning new technologies.