August 06 2025
CREATE TABLE Orders (
Id INT PRIMARY KEY,
Customer NVARCHAR(100),
Status NVARCHAR(50),
TotalAmount DECIMAL(18,2),
CreatedAt DATETIME2
);
CREATE TABLE OrderItems (
Id INT PRIMARY KEY,
OrderId INT FOREIGN KEY REFERENCES Orders(Id),
Product NVARCHAR(100),
Quantity INT,
Price DECIMAL(18,2)
);
public class Order
{
public int Id { get; set; }
public string Customer { get; set; } = default!;
public string Status { get; set; } = default!;
public decimal TotalAmount { get; set; }
public DateTime CreatedAt { get; set; }
public List<OrderItem> Items { get; set; } = new();
}
public class OrderItem
{
public int Id { get; set; }
public int OrderId { get; set; }
public string Product { get; set; } = default!;
public int Quantity { get; set; }
public decimal Price { get; set; }
}
List<Order> GenerateOrders(int count)
{
var rng = new Random();
var orders = new List<Order>();
for (int i = 1; i <= count; i++)
{
var order = new Order
{
Id = i,
Customer = $"Customer {i}",
Status = "Pending",
CreatedAt = DateTime.UtcNow,
Items = new List<OrderItem>()
};
int itemsCount = rng.Next(1, 5);
for (int j = 1; j <= itemsCount; j++)
{
order.Items.Add(new OrderItem
{
Id = i * 10 + j, // unique ID across orders
Product = $"Product {j}",
Quantity = rng.Next(1, 10),
Price = rng.Next(10, 100)
});
}
orders.Add(order);
}
return orders;
}
public async Task SyncOrdersWithClassicDapperAsync(List<Order> orders)
{
foreach (var order in orders)
{
// Recalculate total before insert
order.TotalAmount = order.Items.Sum(x => x.Price * x.Quantity);
// Try UPDATE Order first
var rowsAffected = await _connection.ExecuteAsync(
@"
UPDATE Orders
SET Customer = @Customer,
Status = @Status,
TotalAmount = @TotalAmount,
CreatedAt = @CreatedAt
WHERE Id = @Id", order);
// If not updated, INSERT Order
if (rowsAffected == 0)
{
await _connection.ExecuteAsync(
@"
INSERT INTO Orders (Id, Customer, Status, TotalAmount, CreatedAt)
VALUES (@Id, @Customer, @Status, @TotalAmount, @CreatedAt)", order);
}
// Delete existing OrderItems (optional if recreating)
await _connection.ExecuteAsync(
"DELETE FROM OrderItems WHERE OrderId = @OrderId",
new { OrderId = order.Id });
// Set OrderId and insert new items
foreach (var item in order.Items)
{
item.OrderId = order.Id;
await _connection.ExecuteAsync(
@"
INSERT INTO OrderItems (Id, OrderId, Product, Quantity, Price)
VALUES (@Id, @OrderId, @Product, @Quantity, @Price)", item);
}
}
}
public async Task SyncOrdersWithDapperPlusAsync(List<Order> orders)
{
DapperPlusManager.Entity<Order>().Table("Orders");
DapperPlusManager.Entity<OrderItem>().Table("OrderItems");
using var transaction = _connection.BeginTransaction();
foreach (var order in orders)
{
order.TotalAmount = order.Items.Sum(x => x.Price * x.Quantity);
foreach (var item in order.Items)
{
item.OrderId = order.Id; // Make sure FK is set
}
}
// Build all OrderItems (flattened)
var allItems = orders.SelectMany(o => o.Items).ToList();
// Build dummy OrderItems just to delete by OrderId
var itemsToDelete = orders.Select(o => new OrderItem { OrderId = o.Id }).ToList();
// Delete old OrderItems (must match key + FK mapping in config)
await transaction.BulkDeleteAsync(itemsToDelete);
// Insert new OrderItems
await transaction.BulkInsertAsync(allItems);
// Merge Orders
await transaction.BulkMergeAsync(orders);
transaction.Commit();
}
await connection.BulkUpdateAsync(orders, opts =>
opts.ColumnInputExpression = x => new { x.Status });
await connection.BulkInsertAsync(orders, opts => opts.BatchSize = 500);
DapperPlusManager.Entity<Order>().BeforeBulkAction = (e, t) =>
Console.WriteLine($"{t} - {((Order)e).Id}");
DapperPlusManager.Entity<YourType>().Key(x => new { x.TenantId, x.Code });
1. Design Patterns that Deliver
This isn’t just another design patterns book. Dive into real-world examples and practical solutions to real problems in real applications.Check out it here.
Go-to resource for understanding the core concepts of design patterns without the overwhelming complexity. In this concise and affordable ebook, I've distilled the essence of design patterns into an easy-to-digest format. It is a Beginner level. Check out it here.
Every Monday morning, I share 1 actionable tip on C#, .NET & Arcitecture topic, that you can use right away.
Join 17,150+ subscribers to improve your .NET Knowledge.
Subscribe to the TheCodeMan.net and be among the 17,150+ subscribers gaining practical tips and resources to enhance your .NET expertise.