When developing an ASP.NET Core Web API, we often need to perform multiple database operations as part of a single business operation.
For example, suppose we are creating an order. We may need to:
- Insert the order.
- Insert order items.
- Reduce product stock.
- Save payment information.
What happens if the order is inserted successfully but updating the product stock fails?
Now the database contains incomplete data.
This is where database transactions become important.
In this article, we will learn how to use transactions with Dapper in ASP.NET Core Web API from the beginning, assuming you have little or no previous experience with transactions.
What is Dapper?
Dapper is a lightweight Object-Relational Mapper (ORM) for .NET.
It allows us to execute SQL queries while reducing the amount of database-related code we need to write.
For example, without Dapper, you may need to write a lot of code to execute SQL commands and convert database results into C# objects.
With Dapper, we can write:
var users = await connection.QueryAsync<User>(
"SELECT * FROM Users"
);
Dapper executes the SQL query and maps the result to the User class.
Dapper does not hide SQL from you. Instead, it gives you a simple and fast way to work with SQL.
What is a Database Transaction?
A transaction is a group of database operations that should be treated as one unit of work.
The basic idea is:
Operation 1
+
Operation 2
+
Operation 3
=
One Transaction
Either all operations succeed or all operations are rolled back.
For example:
Create Order
|
v
Create Order Items
|
v
Update Product Stock
|
v
Commit
If something fails:
Create Order
|
v
Create Order Items
|
v
Update Product Stock
|
X Error
|
v
Rollback
The database returns to the state it had before the transaction started.
Why Do We Need Transactions?
Imagine an e-commerce application.
A customer purchases:
Product: Laptop
Quantity: 1
Price: ₹50,000
Your API performs three operations:
INSERT INTO Orders
Then:
INSERT INTO OrderItems
Then:
UPDATE Products
SET Stock = Stock - 1
Suppose the first two queries succeed but the third query fails.
You would have:
Order created Yes
Order item created Yes
Stock updated No
This creates inconsistent data.
A transaction prevents this situation.
With a transaction:
Start Transaction
Create Order
|
Create Order Item
|
Update Stock
|
Success
|
COMMIT
If any operation fails:
Start Transaction
Create Order
|
Create Order Item
|
Update Stock
|
Error
|
ROLLBACK
ACID Properties
Database transactions are generally described using four important properties called ACID.
1. Atomicity
All operations should succeed or all should fail.
For example:
Insert Order
Insert Order Item
Update Stock
If stock update fails, the order and order item should also be rolled back.
2. Consistency
The database should remain in a valid state before and after the transaction.
3. Isolation
One transaction should not improperly interfere with another transaction.
For example, two customers should not be able to purchase the last available product simultaneously.
4. Durability
Once a transaction is committed, the changes should remain saved even if the application restarts.
How Transactions Work with Dapper
Dapper itself does not provide a completely separate transaction system.
Instead, Dapper works with the transaction functionality provided by the database connection.
For SQL Server, we can use:
var transaction = connection.BeginTransaction();
Then pass the transaction to Dapper:
await connection.ExecuteAsync(
sql,
parameters,
transaction
);
At the end:
transaction.Commit();
If something goes wrong:
transaction.Rollback();
Creating an ASP.NET Core Web API Project
Let's create a simple ASP.NET Core Web API.
You can create the project using:
dotnet new webapi -n DapperTransactionApi
Move into the project:
cd DapperTransactionApi
Install Dapper:
dotnet add package Dapper
For SQL Server, install:
dotnet add package Microsoft.Data.SqlClient
Database Setup
For this example, we will create two tables:
Orders
OrderItems
Our SQL database can contain the following tables.
Orders Table
CREATE TABLE Orders
(
Id INT IDENTITY(1,1) PRIMARY KEY,
CustomerName NVARCHAR(100) NOT NULL,
TotalAmount DECIMAL(18,2) NOT NULL,
CreatedAt DATETIME2 NOT NULL
);
OrderItems Table
CREATE TABLE OrderItems
(
Id INT IDENTITY(1,1) PRIMARY KEY,
OrderId INT NOT NULL,
ProductName NVARCHAR(200) NOT NULL,
Quantity INT NOT NULL,
Price DECIMAL(18,2) NOT NULL,
CONSTRAINT FK_OrderItems_Orders
FOREIGN KEY (OrderId)
REFERENCES Orders(Id)
);
Create the Connection String
Open:
appsettings.json
Add your SQL Server connection string:
{
"ConnectionStrings": {
"DefaultConnection": "Server=localhost;Database=ShopDb;Trusted_Connection=True;TrustServerCertificate=True;"
}
}
If you are using SQL Server authentication:
{
"ConnectionStrings": {
"DefaultConnection": "Server=localhost;Database=ShopDb;User Id=sa;Password=YourPassword;TrustServerCertificate=True;"
}
}
Never hard-code database passwords directly into your source code in a production application.
Create Order Models
Create a folder:
Models
Create:
Order.cs
namespace DapperTransactionApi.Models;
public class Order
{
public int Id { get; set; }
public string CustomerName { get; set; } = string.Empty;
public decimal TotalAmount { get; set; }
public DateTime CreatedAt { get; set; }
}
Now create:
OrderItem.cs
namespace DapperTransactionApi.Models;
public class OrderItem
{
public int Id { get; set; }
public int OrderId { get; set; }
public string ProductName { get; set; } = string.Empty;
public int Quantity { get; set; }
public decimal Price { get; set; }
}
Create Request Models
For creating an order, we can create:
CreateOrderRequest.cs
namespace DapperTransactionApi.Models;
public class CreateOrderRequest
{
public string CustomerName { get; set; } = string.Empty;
public decimal TotalAmount { get; set; }
public List<CreateOrderItemRequest> Items { get; set; } = [];
}
Create:
CreateOrderItemRequest.cs
namespace DapperTransactionApi.Models;
public class CreateOrderItemRequest
{
public string ProductName { get; set; } = string.Empty;
public int Quantity { get; set; }
public decimal Price { get; set; }
}
Create Database Connection
Create a folder:
Data
Then create:
DbConnectionFactory.cs
using Microsoft.Data.SqlClient;
using System.Data;
namespace DapperTransactionApi.Data;
public class DbConnectionFactory
{
private readonly IConfiguration _configuration;
public DbConnectionFactory(IConfiguration configuration)
{
_configuration = configuration;
}
public IDbConnection CreateConnection()
{
return new SqlConnection(
_configuration.GetConnectionString("DefaultConnection")
);
}
}
Register the Connection Factory
Open:
Program.cs
Add:
builder.Services.AddScoped<DbConnectionFactory>();
Your basic setup can look like:
var builder = WebApplication.CreateBuilder(args);
builder.Services.AddControllers();
builder.Services.AddScoped<DbConnectionFactory>();
var app = builder.Build();
app.MapControllers();
app.Run();
Creating an Order with a Transaction
Now comes the important part.
Create:
Controllers/OrdersController.cs
using Dapper;
using DapperTransactionApi.Data;
using DapperTransactionApi.Models;
using Microsoft.AspNetCore.Mvc;
namespace DapperTransactionApi.Controllers;
[ApiController]
[Route("api/[controller]")]
public class OrdersController : ControllerBase
{
private readonly DbConnectionFactory _connectionFactory;
public OrdersController(DbConnectionFactory connectionFactory)
{
_connectionFactory = connectionFactory;
}
[HttpPost]
public async Task<IActionResult> CreateOrder(
CreateOrderRequest request)
{
using var connection = _connectionFactory.CreateConnection();
await connection.OpenAsync();
using var transaction = connection.BeginTransaction();
try
{
var orderSql = """
INSERT INTO Orders
(
CustomerName,
TotalAmount,
CreatedAt
)
OUTPUT INSERTED.Id
VALUES
(
@CustomerName,
@TotalAmount,
@CreatedAt
);
""";
var orderId = await connection.ExecuteScalarAsync<int>(
orderSql,
new
{
request.CustomerName,
request.TotalAmount,
CreatedAt = DateTime.UtcNow
},
transaction
);
var itemSql = """
INSERT INTO OrderItems
(
OrderId,
ProductName,
Quantity,
Price
)
VALUES
(
@OrderId,
@ProductName,
@Quantity,
@Price
);
""";
foreach (var item in request.Items)
{
await connection.ExecuteAsync(
itemSql,
new
{
OrderId = orderId,
item.ProductName,
item.Quantity,
item.Price
},
transaction
);
}
transaction.Commit();
return Ok(new
{
message = "Order created successfully",
orderId
});
}
catch (Exception ex)
{
transaction.Rollback();
return StatusCode(500, new
{
message = "Order creation failed",
error = ex.Message
});
}
}
}
Understanding the Code Step by Step
Let's understand what is happening.
Step 1: Create Database Connection
using var connection = _connectionFactory.CreateConnection();
This creates a connection to SQL Server.
Step 2: Open the Connection
await connection.OpenAsync();
The connection is now ready for database operations.
Step 3: Start Transaction
using var transaction = connection.BeginTransaction();
This is the most important line.
From this point, database operations can participate in the transaction.
Step 4: Insert the Order
We execute:
INSERT INTO Orders
using Dapper:
var orderId = await connection.ExecuteScalarAsync<int>(
orderSql,
parameters,
transaction
);
Notice this:
transaction
We pass the transaction to Dapper.
This means the query belongs to our transaction.
Why Does ExecuteScalarAsync Return the Order ID?
Our SQL contains:
OUTPUT INSERTED.Id
Suppose the database generates:
Order ID = 101
Then:
ExecuteScalarAsync<int>()
returns:
101
We can then use this ID when inserting order items.
Step 5: Insert Order Items
We loop through all items:
foreach (var item in request.Items)
Then insert each item:
await connection.ExecuteAsync(
itemSql,
parameters,
transaction
);
Again, we pass:
transaction
Therefore, these inserts belong to the same transaction.
Step 6: Commit the Transaction
If everything works:
transaction.Commit();
The database permanently saves all changes.
The flow becomes:
Create Order
|
v
Create Order Item 1
|
v
Create Order Item 2
|
v
Create Order Item 3
|
v
Commit
Step 7: Rollback on Error
Suppose the second order item fails.
The code enters:
catch
Then:
transaction.Rollback();
This cancels all changes made by the transaction.
For example:
Order Inserted
|
Item 1 Inserted
|
Item 2 Failed
|
Rollback
After rollback:
Order Inserted = No
Item 1 Inserted = No
Item 2 Inserted = No
This is the main purpose of a transaction.
Important: Every Query Must Use the Transaction
This is a common beginner mistake.
Correct:
await connection.ExecuteAsync(
sql,
parameters,
transaction
);
Incorrect:
await connection.ExecuteAsync(
sql,
parameters
);
If you forget to pass the transaction, that query may not participate in your transaction.
The safe pattern is:
connection
+
transaction
+
Dapper query
Example API Request
We can call:
POST /api/orders
with:
{
"customerName": "Shyam",
"totalAmount": 1500,
"items": [
{
"productName": "T-Shirt",
"quantity": 2,
"price": 500
},
{
"productName": "Jeans",
"quantity": 1,
"price": 500
}
]
}
The API performs:
BEGIN TRANSACTION
Insert Order
|
v
Get Order ID
|
v
Insert T-Shirt
|
v
Insert Jeans
|
v
COMMIT
What Happens If an Error Occurs?
Suppose the Jeans insertion fails.
Without a transaction:
Order -> Created
T-Shirt -> Created
Jeans -> Failed
This is bad because the database contains partial data.
With a transaction:
Order -> Created
T-Shirt -> Created
Jeans -> Failed
|
v
Rollback
Final database state:
Order -> Not Created
T-Shirt -> Not Created
Jeans -> Not Created
The database remains consistent.
Transaction with Product Stock
A real e-commerce application usually needs more than two queries.
For example:
Create Order
|
Create Order Items
|
Reduce Product Stock
|
Create Payment Record
|
Commit
All of these operations can be placed inside one transaction.
For example:
using var transaction = connection.BeginTransaction();
try
{
// Create order
// Create order items
// Update product stock
// Create payment record
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
This is a common pattern in real-world applications.
Transaction Isolation Levels
SQL Server supports different transaction isolation levels.
For example:
using System.Data;
using var transaction =
connection.BeginTransaction(
IsolationLevel.ReadCommitted
);
Some common isolation levels are:
ReadUncommitted
ReadCommitted
RepeatableRead
Serializable
Snapshot
For beginners, ReadCommitted is usually a good starting point to understand.
The correct isolation level depends on your application's concurrency requirements.
Using TransactionScope
Another option in .NET is:
TransactionScope
For example:
using var scope = new TransactionScope(
TransactionScopeAsyncFlowOption.Enabled
);
try
{
// Database operations
scope.Complete();
}
catch
{
// Transaction automatically rolls back
}
However, when using Dapper with a single database connection, explicitly using:
connection.BeginTransaction()
is often easier to understand and control.
Should We Put Transactions in Controllers?
For small learning projects, putting transaction code in a controller is understandable.
However, in a production application, it is better to separate responsibilities.
A common architecture is:
Controller
|
v
Service
|
v
Repository
|
v
Dapper
|
v
SQL Server
For example:
OrdersController
|
v
OrderService
|
v
OrderRepository
|
v
SQL Server
The service layer can control the business transaction.
Recommended Production Structure
A larger ASP.NET Core project could look like:
DapperTransactionApi
│
├── Controllers
│ └── OrdersController.cs
│
├── Services
│ └── OrderService.cs
│
├── Repositories
│ └── OrderRepository.cs
│
├── Models
│ ├── Order.cs
│ ├── OrderItem.cs
│ └── CreateOrderRequest.cs
│
├── Data
│ └── DbConnectionFactory.cs
│
├── appsettings.json
└── Program.cs
This makes the application easier to maintain.
Common Mistakes Beginners Make
1. Forgetting to Commit
If you start a transaction:
var transaction = connection.BeginTransaction();
you need to commit it:
transaction.Commit();
Otherwise, your changes may not become permanent.
2. Forgetting Rollback
Always handle exceptions:
try
{
// operations
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
3. Not Passing the Transaction to Dapper
Wrong:
await connection.ExecuteAsync(
sql,
parameters
);
Correct:
await connection.ExecuteAsync(
sql,
parameters,
transaction
);
4. Opening Multiple Connections
Avoid doing this unnecessarily:
Connection 1 -> Insert Order
Connection 2 -> Insert Order Item
A transaction belongs to a particular database connection.
For a simple transaction, use the same connection:
Connection
|
+--- Transaction
|
+--- Query 1
+--- Query 2
+--- Query 3
5. Keeping Transactions Open for Too Long
Transactions consume database resources.
Avoid:
Begin Transaction
Wait for external API
Wait for user
Perform calculations
Wait for another service
Commit
Instead, keep the transaction as short as reasonably possible.
Begin Transaction
|
Database operations
|
Commit
Transaction vs Normal Database Operations
Without transaction:
Query 1 -> Success
Query 2 -> Success
Query 3 -> Failed
The first two changes may remain in the database.
With transaction:
Query 1 -> Success
Query 2 -> Success
Query 3 -> Failed
|
v
Rollback
Everything is reverted.
When Should You Use a Transaction?
Use transactions when multiple database operations must succeed together.
Common examples include:
E-commerce
Create Order
Create Order Items
Update Stock
Create Payment
Banking
Debit Account A
Credit Account B
Create Transaction Record
Wallet
Deduct User Wallet
Add Merchant Wallet
Create Payment Record
Booking System
Create Booking
Reserve Seat
Create Payment Record
Inventory
Create Purchase
Add Stock
Create Stock History
When Do You Not Need a Transaction?
You usually don't need an explicit transaction for a single simple database operation.
For example:
SELECT * FROM Products
or:
UPDATE Products
SET Name = @Name
WHERE Id = @Id
If there is only one independent operation, an explicit transaction may add unnecessary complexity.
Dapper Transaction Best Practices
For production applications, follow these practices:
1. Use parameterized queries
Good:
var sql = """
SELECT *
FROM Products
WHERE Id = @Id
""";
await connection.QueryAsync<Product>(
sql,
new { Id = productId }
);
Avoid building SQL using string concatenation:
var sql =
"SELECT * FROM Products WHERE Id = " + productId;
Parameterized queries help protect against SQL injection.
2. Keep transactions short
Do only the required database operations inside the transaction.
3. Use async APIs
For ASP.NET Core applications:
await connection.ExecuteAsync(...);
is preferable to blocking database calls.
4. Handle exceptions properly
Use:
try
{
// transaction operations
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
5. Keep business logic outside the controller
For larger applications, use:
Controller
Service
Repository
Database
instead of putting everything into the controller.
Complete Transaction Flow
The complete flow can be visualized as:
HTTP Request
|
v
OrdersController
|
v
OrderService
|
v
Open Connection
|
v
Begin Transaction
|
+----------+----------+
| | |
v v v
Insert Insert Update
Order Items Stock
| | |
+----------+----------+
|
v
Everything OK?
/ \
Yes No
| |
v v
COMMIT ROLLBACK
| |
+-----+----+
|
v
API Response
This is the basic concept you should remember.
Final Example to Remember
If you are new to transactions, remember this simple pattern:
using var connection = _connectionFactory.CreateConnection();
await connection.OpenAsync();
using var transaction = connection.BeginTransaction();
try
{
await connection.ExecuteAsync(
sql1,
parameters1,
transaction
);
await connection.ExecuteAsync(
sql2,
parameters2,
transaction
);
await connection.ExecuteAsync(
sql3,
parameters3,
transaction
);
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
Think of it like this:
BEGIN
|
+-- Query 1
|
+-- Query 2
|
+-- Query 3
|
+-- Success --> COMMIT
|
+-- Error --> ROLLBACK
Once you understand this pattern, you can use Dapper transactions for orders, payments, wallets, bookings, inventory, and many other business operations.
Conclusion
Transactions are an important part of building reliable ASP.NET Core Web APIs.
When multiple database operations belong to the same business operation, a transaction ensures that the database does not end up with partially completed data.
With Dapper, the basic transaction pattern is straightforward:
Open Connection
↓
Begin Transaction
↓
Execute Dapper Queries
↓
If Successful → Commit
↓
If Failed → Rollback
The most important thing to remember is that every Dapper query that should participate in the transaction must use the same database connection and transaction object.
Once this concept is clear, you can move toward a cleaner architecture using:
Controller
↓
Service
↓
Repository
↓
Dapper
↓
SQL Server
This approach is particularly useful when building production-level ASP.NET Core APIs such as e-commerce, payment, inventory, booking, and wallet systems.
Top comments (0)