Transactions are essential to ensure data consistency and integrity in operations that involve multiple database commands. In .NET, Dapper offers a simple and efficient way to manage transactions using the IDbTransaction interface. In this article, we will explore how to implement transactions with Dapper.
We have previously discussed how to work with transactions using Entity Framework in another article, but now we will focus on the approach with Dapper, which is a lighter and more performant alternative for accessing data directly in the database.
What Are Transactions?
Transactions are a set of operations executed as a single unit of work. They ensure that all changes to the database are successfully completed or, in case of failure, rolled back—maintaining data integrity.
Transactions follow the ACID principles:
Atomicity: Either all operations occur, or none occur.
Consistency: The database always transitions from one valid state to another.
Isolation: Concurrent transactions do not interfere with each other.
Durability: Committed changes are permanent.
Transactions are essential to avoid inconsistent data or integrity failures, especially in critical systems. They ensure that even in the event of failures, the database remains in a reliable state.
Why Use Them?
In real systems, many operations require multiple actions in the database to occur together. If one of these actions fails, the others shouldn’t remain. Without transactions, the database could end up in an inconsistent state, which is dangerous—especially in financial, inventory, or order control systems.
Imagine a sales system. When registering a new order, two actions are performed: inserting the order data into the database and updating the inventory to reduce the quantity of available items.
Now consider the following scenario without transactions:
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
// Step 1: Insert the order
connection.Execute("INSERT INTO Pedido (...) VALUES (...)");
// Step 2: Deduct item from stock
connection.Execute("UPDATE Produto SET Quantidade = Quantidade - 1 WHERE Id = @id", new { id = 1 });
}
If any failure occurs in the second operation—such as a non-existent item or insufficient stock—the order will have been recorded without the stock being updated. The database is now inconsistent: the order exists, but the product was not removed from inventory.
Creating Transactions with IDbTransaction
To avoid this situation, we can use the IDbTransaction interface in Dapper, which allows us to start, control, and complete transactions efficiently.
First, we need to open a connection to the database. In Dapper, this is done using an IDbConnection object configured with the appropriate connection string:
using (IDbConnection dbConnection = new SqlConnection(connectionString))
{
dbConnection.Open();
using (var transaction = dbConnection.BeginTransaction())
{
// Operations within the transaction
}
}
In this code, the connection is created within a using block, which means the instance will be automatically disposed at the end of the block. After that, we create an IDbConnection instance and provide the connection string. We then open the connection using the Open() method.
Once the connection is open, we start the transaction using the BeginTransaction method, which creates and returns an IDbTransaction object. This object will control the start and end of the transaction.
Now that the transaction is active, we can execute SQL commands within this transaction using Dapper. To ensure the operation is part of the transaction, we must pass the transaction object as a parameter to execution methods like Execute or Query.
// First operation: Insert an order
string queryInsertPedido = "INSERT INTO Pedidos (ClienteId, DataPedido) VALUES (@ClienteId, @DataPedido)";
dbConnection.Execute(queryInsertPedido, new { ClienteId = 1, DataPedido = DateTime.Now }, transaction: transaction);
// Second operation: Update product stock
string queryUpdateEstoque = "UPDATE Produtos SET Estoque = Estoque - @Quantidade WHERE Id = @ProdutoId";
dbConnection.Execute(queryUpdateEstoque, new { Quantidade = 1, ProdutoId = 758 }, transaction: transaction);
Here, we insert a new order into the “Pedidos” table and update the stock of a product in the “Produtos” table, subtracting 1 unit. Both actions must succeed for the changes to be saved. Otherwise, they will be rolled back.
After executing the commands, the next step is to decide whether to commit or rollback the changes. If everything succeeds, we use Commit() to persist the changes. If something goes wrong, we call Rollback() to undo the operations performed up to that point:
try
{
// Code above
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
throw;
}
In this example, if a failure occurs, an exception is thrown and the transaction is rolled back using Rollback(), ensuring the database is not altered inconsistently.
Best Practices with Transactions
When working with transactions, some best practices should be followed:
Keep transactions short: Long transactions can cause database locks and impact performance. Avoid keeping transactions open for extended periods.
Always use
using: Using theusingstatement ensures that connection and transaction objects are properly disposed of, preventing resource leaks. Withoutusing, you’d need to manually close the connection withawait connection.CloseAsync();.Catch specific exceptions: Avoid using generic
catchblocks. Capturing specific exceptions helps diagnose problems more quickly.
Conclusion
Transactions are essential for ensuring data integrity in systems that require atomic operations. Using Dapper gives us direct control over the transaction, making the process efficient and flexible. Following best practices and using transactions correctly is key to the success of any application that interacts with databases—whether with Dapper or other frameworks like Entity Framework, which we’ve also covered previously.