Error Handling and Transactions with Dapper in .NET Core
Error Handling and Transactions
with Dapper in .NET Core
Dapper is a lightweight and
high-performance micro-ORM for .NET applications, allowing efficient
interaction with databases. While Dapper simplifies database operations, proper
error handling and transaction management are crucial to maintaining data
consistency and ensuring robustness in applications. This article explores
effective error handling and transaction management strategies when using
Dapper in .NET Core.
Error Handling in Dapper
Handling database errors properly
helps prevent unexpected failures and ensures system reliability. Some common
error-handling techniques include:
1. Using Try-Catch Blocks
When executing SQL commands, wrap
them in a try-catch block to catch and handle database exceptions gracefully.
using (var connection = new
SqlConnection(connectionString))
{
try
{
connection.Open();
var result = connection.Execute("INSERT INTO Users (Name, Email) VALUES
(@Name, @Email)", new { Name = "John Doe", Email =
"john@example.com" });
Console.WriteLine($"Rows affected: {result}");
}
catch
(SqlException ex)
{
Console.WriteLine($"Database error: {ex.Message}");
}
catch
(Exception ex)
{
Console.WriteLine($"Unexpected error: {ex.Message}");
}
}
2. Logging Errors
It's important to log errors for
debugging and monitoring purposes. You can integrate a logging framework like
Serilog or NLog to capture error details.
catch (SqlException ex)
{
logger.LogError(ex, "SQL Error occurred while executing a query");
}
catch (Exception ex)
{
logger.LogError(ex, "An unexpected error occurred");
}
3. Handling Connection Issues
If a database connection fails,
implement a retry policy using Polly to automatically retry failed queries.
var retryPolicy = Policy
.Handle<SqlException>()
.WaitAndRetry(3, retryAttempt => TimeSpan.FromSeconds(retryAttempt));
retryPolicy.Execute(() =>
{
using (var
connection = new SqlConnection(connectionString))
{
connection.Open();
connection.Execute("DELETE FROM Users WHERE Id = @Id", new { Id = 1
});
}
});
Using Transactions in Dapper
Transactions ensure consistency in
the database by allowing multiple operations to be executed atomically. If any
operation fails, all changes are rolled back.
1. Implementing Transactions with
BeginTransaction()
Dapper supports transactions using
BeginTransaction() to execute multiple SQL statements within a single
transaction.
using (var connection = new
SqlConnection(connectionString))
{
connection.Open();
using (var
transaction = connection.BeginTransaction())
{
try
{
// First operation
connection.Execute("INSERT INTO Orders (UserId, TotalAmount) VALUES
(@UserId, @TotalAmount)",
new { UserId = 1, TotalAmount = 100.00 }, transaction);
// Second operation
connection.Execute("INSERT INTO OrderDetails (OrderId, ProductId,
Quantity) VALUES (@OrderId, @ProductId, @Quantity)",
new { OrderId = 1, ProductId = 10, Quantity = 2 }, transaction);
// Commit transaction if all operations succeed
transaction.Commit();
}
catch (Exception ex)
{
Console.WriteLine($"Transaction failed: {ex.Message}");
transaction.Rollback(); // Rollback on error
}
}
}
2. Ensuring Consistency with
IsolationLevel
Specify IsolationLevel to control
how transactions interact with other transactions. Example:
using (var connection = new
SqlConnection(connectionString))
{
connection.Open();
using (var
transaction = connection.BeginTransaction(IsolationLevel.Serializable))
{
try
{
connection.Execute("UPDATE Accounts SET Balance = Balance - @Amount WHERE
UserId = @UserId",
new { Amount = 500, UserId = 1 }, transaction);
connection.Execute("UPDATE Accounts SET Balance = Balance + @Amount WHERE
UserId = @RecipientId",
new { Amount = 500, RecipientId = 2 }, transaction);
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
}
}
3. Handling Transactions in a
Repository Pattern
If you're using a repository
pattern, encapsulate transaction logic to improve code maintainability.
public async Task<bool> TransferFunds(int
senderId, int recipientId, decimal amount)
{
using (var
connection = new SqlConnection(connectionString))
{
await connection.OpenAsync();
using (var transaction = connection.BeginTransaction())
{
try
{
await connection.ExecuteAsync("UPDATE Accounts SET Balance = Balance -
@Amount WHERE UserId = @UserId",
new { Amount = amount, UserId = senderId }, transaction);
await connection.ExecuteAsync("UPDATE Accounts SET Balance = Balance +
@Amount WHERE UserId = @RecipientId",
new { Amount = amount, RecipientId = recipientId }, transaction);
transaction.Commit();
return true;
}
catch
{
transaction.Rollback();
return false;
}
}
}
}
Conclusion
Proper error handling and
transaction management in Dapper are essential for building reliable .NET Core
applications. By implementing structured error handling, logging, retry
mechanisms, and atomic transactions, you can improve data integrity and ensure a
smooth user experience.
Using try-catch, logging frameworks, and Polly for retries enhances robustness, while transactions with BeginTransaction() ensure consistency across multiple operations. These techniques will help you build a resilient database layer in your .NET Core application with Dapper.
Comments
Post a Comment