![Dapper Deep Dive Part 8: Ultra-Fast Querying & Mapping Secrets for ASP.NET Core Pros - FreeLearning365.com]( "Dapper Deep Dive Part 8: Ultra-Fast Querying & Mapping Secrets for ASP.NET Core Pros - FreeLearning365.com")
Table of Contents
Dapper Fundamentals and Setup
Basic Query Operations and Mapping
Advanced Query Techniques
Performance Optimization Strategies
Multi-Mapping and Complex Relationships
Stored Procedures and Dynamic Parameters
Bulk Operations and Performance
Real-World Enterprise Scenarios
Integration with ASP.NET Core
Best Practices and Advanced Patterns
1. Dapper Fundamentals and Setup
What is Dapper and Why Use It?
Dapper is a simple object mapper for .NET that extends IDbConnection with useful methods for executing queries and mapping results to objects. It's known for its exceptional performance and minimal overhead.
Key Advantages:
Nearly as fast as raw ADO.NET
Easy to use and learn
Minimal configuration required
Flexible mapping capabilities
Supports complex object graphs
Project Setup and Configuration
Program.cs
using Dapper;
using Microsoft.Data.SqlClient;
using System.Data;
var builder = WebApplication.CreateBuilder(args);
// Add services to container
builder.Services.AddControllers();
builder.Services.AddScoped<IDbConnection>(provider =>
{
var connectionString = builder.Configuration.GetConnectionString("DefaultConnection");
return new SqlConnection(connectionString);
});
builder.Services.AddScoped<IProductRepository, ProductRepository>();
builder.Services.AddScoped<IUserRepository, UserRepository>();
builder.Services.AddScoped<IOrderRepository, OrderRepository>();
var app = builder.Build();
// Configure pipeline
if (app.Environment.IsDevelopment())
{
app.UseDeveloperExceptionPage();
}
app.UseRouting();
app.MapControllers();
app.Run();
appsettings.json
{
"ConnectionStrings": {
"DefaultConnection": "Server=.;Database=DapperDemo;Trusted_Connection=true;TrustServerCertificate=true;"
},
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft.AspNetCore": "Warning"
}
}
}
Database Setup and Initialization
DatabaseInitializer.cs
using Dapper;
using Microsoft.Data.SqlClient;
using System.Data;
namespace DapperDeepDive.Data
{
public class DatabaseInitializer
{
private readonly string _connectionString;
public DatabaseInitializer(string connectionString)
{
_connectionString = connectionString;
}
public async Task InitializeDatabaseAsync()
{
using var connection = new SqlConnection(_connectionString);
// Create database if not exists
var createDbSql = @"
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'DapperDemo')
BEGIN
CREATE DATABASE DapperDemo;
END";
await connection.ExecuteAsync(createDbSql);
// Switch to DapperDemo database
connection.ConnectionString += "Database=DapperDemo;";
await connection.OpenAsync();
// Create tables
await CreateTablesAsync(connection);
await SeedSampleDataAsync(connection);
}
private async Task CreateTablesAsync(IDbConnection connection)
{
// Users table
var createUsersTable = @"
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='Users' AND xtype='U')
CREATE TABLE Users (
Id INT IDENTITY PRIMARY KEY,
Username NVARCHAR(50) NOT NULL UNIQUE,
Email NVARCHAR(100) NOT NULL UNIQUE,
FirstName NVARCHAR(50) NOT NULL,
LastName NVARCHAR(50) NOT NULL,
DateOfBirth DATE NULL,
CreatedAt DATETIME2 DEFAULT GETUTCDATE(),
IsActive BIT DEFAULT 1
)";
// Products table
var createProductsTable = @"
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='Products' AND xtype='U')
CREATE TABLE Products (
Id INT IDENTITY PRIMARY KEY,
Name NVARCHAR(100) NOT NULL,
Description NVARCHAR(500) NULL,
Price DECIMAL(18,2) NOT NULL,
StockQuantity INT NOT NULL DEFAULT 0,
Category NVARCHAR(50) NULL,
CreatedAt DATETIME2 DEFAULT GETUTCDATE(),
IsActive BIT DEFAULT 1
)";
// Orders table
var createOrdersTable = @"
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='Orders' AND xtype='U')
CREATE TABLE Orders (
Id INT IDENTITY PRIMARY KEY,
UserId INT NOT NULL FOREIGN KEY REFERENCES Users(Id),
OrderDate DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
TotalAmount DECIMAL(18,2) NOT NULL,
Status NVARCHAR(20) NOT NULL DEFAULT 'Pending',
ShippingAddress NVARCHAR(200) NULL,
CreatedAt DATETIME2 DEFAULT GETUTCDATE()
)";
// OrderItems table
var createOrderItemsTable = @"
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='OrderItems' AND xtype='U')
CREATE TABLE OrderItems (
Id INT IDENTITY PRIMARY KEY,
OrderId INT NOT NULL FOREIGN KEY REFERENCES Orders(Id),
ProductId INT NOT NULL FOREIGN KEY REFERENCES Products(Id),
Quantity INT NOT NULL,
UnitPrice DECIMAL(18,2) NOT NULL,
TotalPrice DECIMAL(18,2) NOT NULL
)";
await connection.ExecuteAsync(createUsersTable);
await connection.ExecuteAsync(createProductsTable);
await connection.ExecuteAsync(createOrdersTable);
await connection.ExecuteAsync(createOrderItemsTable);
}
private async Task SeedSampleDataAsync(IDbConnection connection)
{
// Check if data already exists
var userCount = await connection.ExecuteScalarAsync<int>("SELECT COUNT(*) FROM Users");
if (userCount > 0) return;
// Insert sample users
var insertUsers = @"
INSERT INTO Users (Username, Email, FirstName, LastName, DateOfBirth) VALUES
('john_doe', '[email protected]', 'John', 'Doe', '1985-05-15'),
('jane_smith', '[email protected]', 'Jane', 'Smith', '1990-08-22'),
('bob_wilson', '[email protected]', 'Bob', 'Wilson', '1988-12-10'),
('alice_brown', '[email protected]', 'Alice', 'Brown', '1992-03-30')";
// Insert sample products
var insertProducts = @"
INSERT INTO Products (Name, Description, Price, StockQuantity, Category) VALUES
('Laptop', 'High-performance laptop', 999.99, 50, 'Electronics'),
('Smartphone', 'Latest smartphone model', 699.99, 100, 'Electronics'),
('Headphones', 'Noise-cancelling headphones', 199.99, 75, 'Electronics'),
('Book', 'Programming guide', 39.99, 200, 'Books'),
('Desk Chair', 'Ergonomic office chair', 299.99, 30, 'Furniture')";
await connection.ExecuteAsync(insertUsers);
await connection.ExecuteAsync(insertProducts);
}
}
}
2. Basic Query Operations and Mapping
Entity Models
Models/User.cs
namespace DapperDeepDive.Models
{
public class User
{
public int Id { get; set; }
public string Username { get; set; } = string.Empty;
public string Email { get; set; } = string.Empty;
public string FirstName { get; set; } = string.Empty;
public string LastName { get; set; } = string.Empty;
public DateTime? DateOfBirth { get; set; }
public DateTime CreatedAt { get; set; }
public bool IsActive { get; set; }
// Navigation properties
public List<Order> Orders { get; set; } = new List<Order>();
}
public class Product
{
public int Id { get; set; }
public string Name { get; set; } = string.Empty;
public string Description { get; set; } = string.Empty;
public decimal Price { get; set; }
public int StockQuantity { get; set; }
public string Category { get; set; } = string.Empty;
public DateTime CreatedAt { get; set; }
public bool IsActive { get; set; }
}
public class Order
{
public int Id { get; set; }
public int UserId { get; set; }
public DateTime OrderDate { get; set; }
public decimal TotalAmount { get; set; }
public string Status { get; set; } = string.Empty;
public string ShippingAddress { get; set; } = string.Empty;
public DateTime CreatedAt { get; set; }
// Navigation properties
public User User { get; set; } = new User();
public List<OrderItem> OrderItems { get; set; } = new List<OrderItem>();
}
public class OrderItem
{
public int Id { get; set; }
public int OrderId { get; set; }
public int ProductId { get; set; }
public int Quantity { get; set; }
public decimal UnitPrice { get; set; }
public decimal TotalPrice { get; set; }
// Navigation properties
public Product Product { get; set; } = new Product();
}
}
Basic Repository Implementation
Repositories/IUserRepository.cs
using DapperDeepDive.Models;
namespace DapperDeepDive.Repositories
{
public interface IUserRepository
{
Task<User?> GetByIdAsync(int id);
Task<IEnumerable<User>> GetAllAsync();
Task<User?> GetByUsernameAsync(string username);
Task<User?> GetByEmailAsync(string email);
Task<int> CreateAsync(User user);
Task<bool> UpdateAsync(User user);
Task<bool> DeleteAsync(int id);
Task<bool> ExistsAsync(int id);
Task<int> GetCountAsync();
}
}
Repositories/UserRepository.cs
using Dapper;
using DapperDeepDive.Models;
using System.Data;
namespace DapperDeepDive.Repositories
{
public class UserRepository : IUserRepository
{
private readonly IDbConnection _connection;
public UserRepository(IDbConnection connection)
{
_connection = connection;
}
public async Task<User?> GetByIdAsync(int id)
{
const string sql = @"
SELECT Id, Username, Email, FirstName, LastName,
DateOfBirth, CreatedAt, IsActive
FROM Users
WHERE Id = @Id AND IsActive = 1";
return await _connection.QueryFirstOrDefaultAsync<User>(sql, new { Id = id });
}
public async Task<IEnumerable<User>> GetAllAsync()
{
const string sql = @"
SELECT Id, Username, Email, FirstName, LastName,
DateOfBirth, CreatedAt, IsActive
FROM Users
WHERE IsActive = 1
ORDER BY CreatedAt DESC";
return await _connection.QueryAsync<User>(sql);
}
public async Task<User?> GetByUsernameAsync(string username)
{
const string sql = @"
SELECT Id, Username, Email, FirstName, LastName,
DateOfBirth, CreatedAt, IsActive
FROM Users
WHERE Username = @Username AND IsActive = 1";
return await _connection.QueryFirstOrDefaultAsync<User>(sql, new { Username = username });
}
public async Task<User?> GetByEmailAsync(string email)
{
const string sql = @"
SELECT Id, Username, Email, FirstName, LastName,
DateOfBirth, CreatedAt, IsActive
FROM Users
WHERE Email = @Email AND IsActive = 1";
return await _connection.QueryFirstOrDefaultAsync<User>(sql, new { Email = email });
}
public async Task<int> CreateAsync(User user)
{
const string sql = @"
INSERT INTO Users (Username, Email, FirstName, LastName, DateOfBirth)
OUTPUT INSERTED.Id
VALUES (@Username, @Email, @FirstName, @LastName, @DateOfBirth)";
return await _connection.ExecuteScalarAsync<int>(sql, user);
}
public async Task<bool> UpdateAsync(User user)
{
const string sql = @"
UPDATE Users
SET Username = @Username, Email = @Email,
FirstName = @FirstName, LastName = @LastName,
DateOfBirth = @DateOfBirth
WHERE Id = @Id AND IsActive = 1";
var affectedRows = await _connection.ExecuteAsync(sql, user);
return affectedRows > 0;
}
public async Task<bool> DeleteAsync(int id)
{
const string sql = @"
UPDATE Users
SET IsActive = 0
WHERE Id = @Id";
var affectedRows = await _connection.ExecuteAsync(sql, new { Id = id });
return affectedRows > 0;
}
public async Task<bool> ExistsAsync(int id)
{
const string sql = "SELECT 1 FROM Users WHERE Id = @Id AND IsActive = 1";
var result = await _connection.ExecuteScalarAsync<int?>(sql, new { Id = id });
return result.HasValue;
}
public async Task<int> GetCountAsync()
{
const string sql = "SELECT COUNT(*) FROM Users WHERE IsActive = 1";
return await _connection.ExecuteScalarAsync<int>(sql);
}
}
}
Product Repository with Advanced Features
Repositories/ProductRepository.cs
using Dapper;
using DapperDeepDive.Models;
using System.Data;
namespace DapperDeepDive.Repositories
{
public interface IProductRepository
{
Task<Product?> GetByIdAsync(int id);
Task<IEnumerable<Product>> GetAllAsync();
Task<IEnumerable<Product>> GetByCategoryAsync(string category);
Task<IEnumerable<Product>> GetActiveProductsAsync();
Task<IEnumerable<Product>> SearchProductsAsync(string searchTerm);
Task<decimal> GetTotalInventoryValueAsync();
Task<Dictionary<string, int>> GetProductCountByCategoryAsync();
Task<int> CreateAsync(Product product);
Task<bool> UpdateAsync(Product product);
Task<bool> UpdateStockAsync(int productId, int newStockQuantity);
Task<bool> DeleteAsync(int id);
}
public class ProductRepository : IProductRepository
{
private readonly IDbConnection _connection;
public ProductRepository(IDbConnection connection)
{
_connection = connection;
}
public async Task<Product?> GetByIdAsync(int id)
{
const string sql = @"
SELECT Id, Name, Description, Price, StockQuantity,
Category, CreatedAt, IsActive
FROM Products
WHERE Id = @Id";
return await _connection.QueryFirstOrDefaultAsync<Product>(sql, new { Id = id });
}
public async Task<IEnumerable<Product>> GetAllAsync()
{
const string sql = @"
SELECT Id, Name, Description, Price, StockQuantity,
Category, CreatedAt, IsActive
FROM Products
ORDER BY CreatedAt DESC";
return await _connection.QueryAsync<Product>(sql);
}
public async Task<IEnumerable<Product>> GetByCategoryAsync(string category)
{
const string sql = @"
SELECT Id, Name, Description, Price, StockQuantity,
Category, CreatedAt, IsActive
FROM Products
WHERE Category = @Category AND IsActive = 1
ORDER BY Name";
return await _connection.QueryAsync<Product>(sql, new { Category = category });
}
public async Task<IEnumerable<Product>> GetActiveProductsAsync()
{
const string sql = @"
SELECT Id, Name, Description, Price, StockQuantity,
Category, CreatedAt, IsActive
FROM Products
WHERE IsActive = 1 AND StockQuantity > 0
ORDER BY Price DESC";
return await _connection.QueryAsync<Product>(sql);
}
public async Task<IEnumerable<Product>> SearchProductsAsync(string searchTerm)
{
const string sql = @"
SELECT Id, Name, Description, Price, StockQuantity,
Category, CreatedAt, IsActive
FROM Products
WHERE (Name LIKE '%' + @SearchTerm + '%'
OR Description LIKE '%' + @SearchTerm + '%')
AND IsActive = 1
ORDER BY
CASE
WHEN Name LIKE @SearchTerm + '%' THEN 1
WHEN Name LIKE '%' + @SearchTerm + '%' THEN 2
ELSE 3
END,
Name";
return await _connection.QueryAsync<Product>(sql, new { SearchTerm = searchTerm });
}
public async Task<decimal> GetTotalInventoryValueAsync()
{
const string sql = @"
SELECT SUM(Price * StockQuantity)
FROM Products
WHERE IsActive = 1";
return await _connection.ExecuteScalarAsync<decimal>(sql);
}
public async Task<Dictionary<string, int>> GetProductCountByCategoryAsync()
{
const string sql = @"
SELECT Category, COUNT(*) as Count
FROM Products
WHERE IsActive = 1
GROUP BY Category
ORDER BY Count DESC";
var results = await _connection.QueryAsync<(string Category, int Count)>(sql);
return results.ToDictionary(x => x.Category, x => x.Count);
}
public async Task<int> CreateAsync(Product product)
{
const string sql = @"
INSERT INTO Products (Name, Description, Price, StockQuantity, Category)
OUTPUT INSERTED.Id
VALUES (@Name, @Description, @Price, @StockQuantity, @Category)";
return await _connection.ExecuteScalarAsync<int>(sql, product);
}
public async Task<bool> UpdateAsync(Product product)
{
const string sql = @"
UPDATE Products
SET Name = @Name, Description = @Description,
Price = @Price, StockQuantity = @StockQuantity,
Category = @Category, IsActive = @IsActive
WHERE Id = @Id";
var affectedRows = await _connection.ExecuteAsync(sql, product);
return affectedRows > 0;
}
public async Task<bool> UpdateStockAsync(int productId, int newStockQuantity)
{
const string sql = @"
UPDATE Products
SET StockQuantity = @StockQuantity
WHERE Id = @ProductId";
var affectedRows = await _connection.ExecuteAsync(sql,
new { ProductId = productId, StockQuantity = newStockQuantity });
return affectedRows > 0;
}
public async Task<bool> DeleteAsync(int id)
{
const string sql = "DELETE FROM Products WHERE Id = @Id";
var affectedRows = await _connection.ExecuteAsync(sql, new { Id = id });
return affectedRows > 0;
}
}
}
3. Advanced Query Techniques
Multiple Result Sets
Repositories/AdvancedUserRepository.cs
using Dapper;
using DapperDeepDive.Models;
using System.Data;
namespace DapperDeepDive.Repositories
{
public interface IAdvancedUserRepository
{
Task<(User User, IEnumerable<Order> Orders)> GetUserWithOrdersAsync(int userId);
Task<UserStatistics> GetUserStatisticsAsync(int userId);
Task<PaginatedResult<User>> GetUsersPaginatedAsync(int pageNumber, int pageSize);
Task<IEnumerable<User>> GetUsersWithOrdersAsync();
Task<dynamic> GetUserDynamicAsync(int userId);
}
public class AdvancedUserRepository : IAdvancedUserRepository
{
private readonly IDbConnection _connection;
public AdvancedUserRepository(IDbConnection connection)
{
_connection = connection;
}
public async Task<(User User, IEnumerable<Order> Orders)> GetUserWithOrdersAsync(int userId)
{
const string sql = @"
SELECT * FROM Users WHERE Id = @UserId;
SELECT * FROM Orders WHERE UserId = @UserId ORDER BY OrderDate DESC;";
using var multi = await _connection.QueryMultipleAsync(sql, new { UserId = userId });
var user = await multi.ReadFirstOrDefaultAsync<User>();
var orders = await multi.ReadAsync<Order>();
return (user, orders);
}
public async Task<UserStatistics> GetUserStatisticsAsync(int userId)
{
const string sql = @"
SELECT
COUNT(o.Id) as TotalOrders,
SUM(o.TotalAmount) as TotalSpent,
AVG(o.TotalAmount) as AverageOrderValue,
MAX(o.OrderDate) as LastOrderDate,
MIN(o.OrderDate) as FirstOrderDate
FROM Users u
LEFT JOIN Orders o ON u.Id = o.UserId
WHERE u.Id = @UserId
GROUP BY u.Id, u.Username";
return await _connection.QueryFirstOrDefaultAsync<UserStatistics>(sql, new { UserId = userId });
}
public async Task<PaginatedResult<User>> GetUsersPaginatedAsync(int pageNumber, int pageSize)
{
var offset = (pageNumber - 1) * pageSize;
const string sql = @"
SELECT
Id, Username, Email, FirstName, LastName,
DateOfBirth, CreatedAt, IsActive
FROM Users
WHERE IsActive = 1
ORDER BY CreatedAt DESC
OFFSET @Offset ROWS
FETCH NEXT @PageSize ROWS ONLY;
SELECT COUNT(*)
FROM Users
WHERE IsActive = 1;";
using var multi = await _connection.QueryMultipleAsync(sql,
new { Offset = offset, PageSize = pageSize });
var users = await multi.ReadAsync<User>();
var totalCount = await multi.ReadFirstAsync<int>();
return new PaginatedResult<User>(users, totalCount, pageNumber, pageSize);
}
public async Task<IEnumerable<User>> GetUsersWithOrdersAsync()
{
const string sql = @"
SELECT
u.*,
o.Id, o.UserId, o.OrderDate, o.TotalAmount, o.Status, o.ShippingAddress
FROM Users u
LEFT JOIN Orders o ON u.Id = o.UserId
WHERE u.IsActive = 1
ORDER BY u.CreatedAt DESC, o.OrderDate DESC";
var userDict = new Dictionary<int, User>();
var users = await _connection.QueryAsync<User, Order, User>(
sql,
(user, order) =>
{
if (!userDict.TryGetValue(user.Id, out var userEntry))
{
userEntry = user;
userEntry.Orders = new List<Order>();
userDict.Add(userEntry.Id, userEntry);
}
if (order != null)
{
userEntry.Orders.Add(order);
}
return userEntry;
},
splitOn: "Id");
return userDict.Values;
}
public async Task<dynamic> GetUserDynamicAsync(int userId)
{
const string sql = @"
SELECT
u.Id, u.Username, u.Email,
COUNT(o.Id) as OrderCount,
SUM(o.TotalAmount) as TotalSpent
FROM Users u
LEFT JOIN Orders o ON u.Id = o.UserId
WHERE u.Id = @UserId
GROUP BY u.Id, u.Username, u.Email";
return await _connection.QueryFirstOrDefaultAsync<dynamic>(sql, new { UserId = userId });
}
}
public class UserStatistics
{
public int TotalOrders { get; set; }
public decimal TotalSpent { get; set; }
public decimal AverageOrderValue { get; set; }
public DateTime? LastOrderDate { get; set; }
public DateTime? FirstOrderDate { get; set; }
}
public class PaginatedResult<T>
{
public IEnumerable<T> Items { get; set; }
public int TotalCount { get; set; }
public int PageNumber { get; set; }
public int PageSize { get; set; }
public int TotalPages => (int)Math.Ceiling(TotalCount / (double)PageSize);
public bool HasPrevious => PageNumber > 1;
public bool HasNext => PageNumber < TotalPages;
public PaginatedResult(IEnumerable<T> items, int totalCount, int pageNumber, int pageSize)
{
Items = items;
TotalCount = totalCount;
PageNumber = pageNumber;
PageSize = pageSize;
}
}
}
Advanced Query Building
Services/QueryBuilderService.cs
using Dapper;
using DapperDeepDive.Models;
using System.Data;
using System.Text;
namespace DapperDeepDive.Services
{
public interface IQueryBuilderService
{
Task<IEnumerable<Product>> SearchProductsAsync(ProductSearchCriteria criteria);
Task<IEnumerable<User>> GetUsersByCriteriaAsync(UserSearchCriteria criteria);
string BuildProductSearchQuery(ProductSearchCriteria criteria, out DynamicParameters parameters);
string BuildUserSearchQuery(UserSearchCriteria criteria, out DynamicParameters parameters);
}
public class QueryBuilderService : IQueryBuilderService
{
private readonly IDbConnection _connection;
public QueryBuilderService(IDbConnection connection)
{
_connection = connection;
}
public async Task<IEnumerable<Product>> SearchProductsAsync(ProductSearchCriteria criteria)
{
var query = BuildProductSearchQuery(criteria, out var parameters);
return await _connection.QueryAsync<Product>(query, parameters);
}
public async Task<IEnumerable<User>> GetUsersByCriteriaAsync(UserSearchCriteria criteria)
{
var query = BuildUserSearchQuery(criteria, out var parameters);
return await _connection.QueryAsync<User>(query, parameters);
}
public string BuildProductSearchQuery(ProductSearchCriteria criteria, out DynamicParameters parameters)
{
parameters = new DynamicParameters();
var sqlBuilder = new StringBuilder("SELECT * FROM Products WHERE 1=1");
if (!string.IsNullOrEmpty(criteria.Name))
{
sqlBuilder.Append(" AND Name LIKE '%' + @Name + '%'");
parameters.Add("Name", criteria.Name);
}
if (!string.IsNullOrEmpty(criteria.Category))
{
sqlBuilder.Append(" AND Category = @Category");
parameters.Add("Category", criteria.Category);
}
if (criteria.MinPrice.HasValue)
{
sqlBuilder.Append(" AND Price >= @MinPrice");
parameters.Add("MinPrice", criteria.MinPrice.Value);
}
if (criteria.MaxPrice.HasValue)
{
sqlBuilder.Append(" AND Price <= @MaxPrice");
parameters.Add("MaxPrice", criteria.MaxPrice.Value);
}
if (criteria.InStockOnly)
{
sqlBuilder.Append(" AND StockQuantity > 0");
}
if (criteria.IsActive.HasValue)
{
sqlBuilder.Append(" AND IsActive = @IsActive");
parameters.Add("IsActive", criteria.IsActive.Value);
}
// Order by
sqlBuilder.Append(" ORDER BY ");
sqlBuilder.Append(criteria.SortBy?.ToLower() switch
{
"name" => "Name",
"price" => "Price",
"created" => "CreatedAt",
_ => "CreatedAt"
});
sqlBuilder.Append(criteria.SortDescending ? " DESC" : " ASC");
// Pagination
if (criteria.PageSize > 0 && criteria.PageNumber > 0)
{
var offset = (criteria.PageNumber - 1) * criteria.PageSize;
sqlBuilder.Append(" OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY");
parameters.Add("Offset", offset);
parameters.Add("PageSize", criteria.PageSize);
}
return sqlBuilder.ToString();
}
public string BuildUserSearchQuery(UserSearchCriteria criteria, out DynamicParameters parameters)
{
parameters = new DynamicParameters();
var sqlBuilder = new StringBuilder("SELECT * FROM Users WHERE 1=1");
if (!string.IsNullOrEmpty(criteria.Username))
{
sqlBuilder.Append(" AND Username LIKE '%' + @Username + '%'");
parameters.Add("Username", criteria.Username);
}
if (!string.IsNullOrEmpty(criteria.Email))
{
sqlBuilder.Append(" AND Email LIKE '%' + @Email + '%'");
parameters.Add("Email", criteria.Email);
}
if (!string.IsNullOrEmpty(criteria.FirstName))
{
sqlBuilder.Append(" AND FirstName LIKE '%' + @FirstName + '%'");
parameters.Add("FirstName", criteria.FirstName);
}
if (!string.IsNullOrEmpty(criteria.LastName))
{
sqlBuilder.Append(" AND LastName LIKE '%' + @LastName + '%'");
parameters.Add("LastName", criteria.LastName);
}
if (criteria.CreatedAfter.HasValue)
{
sqlBuilder.Append(" AND CreatedAt >= @CreatedAfter");
parameters.Add("CreatedAfter", criteria.CreatedAfter.Value);
}
if (criteria.CreatedBefore.HasValue)
{
sqlBuilder.Append(" AND CreatedAt <= @CreatedBefore");
parameters.Add("CreatedBefore", criteria.CreatedBefore.Value);
}
if (criteria.IsActive.HasValue)
{
sqlBuilder.Append(" AND IsActive = @IsActive");
parameters.Add("IsActive", criteria.IsActive.Value);
}
// Order by
sqlBuilder.Append(" ORDER BY ");
sqlBuilder.Append(criteria.SortBy?.ToLower() switch
{
"username" => "Username",
"email" => "Email",
"created" => "CreatedAt",
_ => "CreatedAt"
});
sqlBuilder.Append(criteria.SortDescending ? " DESC" : " ASC");
// Pagination
if (criteria.PageSize > 0 && criteria.PageNumber > 0)
{
var offset = (criteria.PageNumber - 1) * criteria.PageSize;
sqlBuilder.Append(" OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY");
parameters.Add("Offset", offset);
parameters.Add("PageSize", criteria.PageSize);
}
return sqlBuilder.ToString();
}
}
public class ProductSearchCriteria
{
public string? Name { get; set; }
public string? Category { get; set; }
public decimal? MinPrice { get; set; }
public decimal? MaxPrice { get; set; }
public bool InStockOnly { get; set; } = false;
public bool? IsActive { get; set; } = true;
public string? SortBy { get; set; } = "created";
public bool SortDescending { get; set; } = true;
public int PageNumber { get; set; } = 1;
public int PageSize { get; set; } = 20;
}
public class UserSearchCriteria
{
public string? Username { get; set; }
public string? Email { get; set; }
public string? FirstName { get; set; }
public string? LastName { get; set; }
public DateTime? CreatedAfter { get; set; }
public DateTime? CreatedBefore { get; set; }
public bool? IsActive { get; set; } = true;
public string? SortBy { get; set; } = "created";
public bool SortDescending { get; set; } = true;
public int PageNumber { get; set; } = 1;
public int PageSize { get; set; } = 20;
}
}
4. Performance Optimization Strategies
Connection Management and Pooling
Services/ConnectionFactory.cs
using Microsoft.Data.SqlClient;
using System.Data;
namespace DapperDeepDive.Services
{
public interface IConnectionFactory
{
IDbConnection CreateConnection();
Task<IDbConnection> CreateOpenConnectionAsync();
}
public class ConnectionFactory : IConnectionFactory
{
private readonly string _connectionString;
public ConnectionFactory(string connectionString)
{
_connectionString = connectionString;
}
public IDbConnection CreateConnection()
{
var connection = new SqlConnection(_connectionString);
// Configure connection for optimal performance
var builder = new SqlConnectionStringBuilder(_connectionString)
{
MaxPoolSize = 100,
MinPoolSize = 0,
Pooling = true,
ConnectionTimeout = 30,
CommandTimeout = 30
};
connection.ConnectionString = builder.ConnectionString;
return connection;
}
public async Task<IDbConnection> CreateOpenConnectionAsync()
{
var connection = CreateConnection();
await connection.OpenAsync();
return connection;
}
}
}
Performance Monitoring
Services/PerformanceMonitor.cs
using Dapper;
using System.Data;
using System.Diagnostics;
namespace DapperDeepDive.Services
{
public interface IPerformanceMonitor
{
Task<T> MonitorQueryAsync<T>(Func<Task<T>> query, string operationName);
Task MonitorActionAsync(Func<Task> action, string operationName);
void LogPerformanceMetrics(string operation, TimeSpan duration, int? recordsAffected = null);
}
public class PerformanceMonitor : IPerformanceMonitor
{
private readonly ILogger<PerformanceMonitor> _logger;
public PerformanceMonitor(ILogger<PerformanceMonitor> logger)
{
_logger = logger;
}
public async Task<T> MonitorQueryAsync<T>(Func<Task<T>> query, string operationName)
{
var stopwatch = Stopwatch.StartNew();
try
{
var result = await query();
stopwatch.Stop();
LogPerformanceMetrics(operationName, stopwatch.Elapsed);
return result;
}
catch (Exception ex)
{
stopwatch.Stop();
_logger.LogError(ex, "Error executing {OperationName} after {ElapsedMs}ms",
operationName, stopwatch.ElapsedMilliseconds);
throw;
}
}
public async Task MonitorActionAsync(Func<Task> action, string operationName)
{
var stopwatch = Stopwatch.StartNew();
try
{
await action();
stopwatch.Stop();
LogPerformanceMetrics(operationName, stopwatch.Elapsed);
}
catch (Exception ex)
{
stopwatch.Stop();
_logger.LogError(ex, "Error executing {OperationName} after {ElapsedMs}ms",
operationName, stopwatch.ElapsedMilliseconds);
throw;
}
}
public void LogPerformanceMetrics(string operation, TimeSpan duration, int? recordsAffected = null)
{
var logMessage = $"Operation '{operation}' completed in {duration.TotalMilliseconds}ms";
if (recordsAffected.HasValue)
{
logMessage += $", Records affected: {recordsAffected.Value}";
}
if (duration.TotalMilliseconds > 1000)
{
_logger.LogWarning(logMessage);
}
else if (duration.TotalMilliseconds > 100)
{
_logger.LogInformation(logMessage);
}
else
{
_logger.LogDebug(logMessage);
}
}
}
// Performance-optimized repository
public class OptimizedProductRepository
{
private readonly IDbConnection _connection;
private readonly IPerformanceMonitor _performanceMonitor;
public OptimizedProductRepository(IDbConnection connection, IPerformanceMonitor performanceMonitor)
{
_connection = connection;
_performanceMonitor = performanceMonitor;
}
public async Task<IEnumerable<Product>> GetProductsOptimizedAsync()
{
return await _performanceMonitor.MonitorQueryAsync(
async () =>
{
const string sql = @"
SELECT
Id, Name, Price, StockQuantity, Category
FROM Products
WHERE IsActive = 1
ORDER BY CreatedAt DESC";
return await _connection.QueryAsync<Product>(sql);
},
"GetProductsOptimized");
}
public async Task<Product?> GetProductByIdOptimizedAsync(int id)
{
return await _performanceMonitor.MonitorQueryAsync(
async () =>
{
const string sql = @"
SELECT
Id, Name, Price, StockQuantity, Category
FROM Products
WHERE Id = @Id";
return await _connection.QueryFirstOrDefaultAsync<Product>(sql, new { Id = id });
},
"GetProductByIdOptimized");
}
public async Task<bool> BulkUpdateProductsAsync(IEnumerable<Product> products)
{
return await _performanceMonitor.MonitorQueryAsync(
async () =>
{
const string sql = @"
UPDATE Products
SET Price = @Price, StockQuantity = @StockQuantity
WHERE Id = @Id";
var affectedRows = await _connection.ExecuteAsync(sql, products);
return affectedRows > 0;
},
"BulkUpdateProducts");
}
}
}
Caching Strategies
Services/CacheService.cs
using Microsoft.Extensions.Caching.Memory;
namespace DapperDeepDive.Services
{
public interface ICacheService
{
Task<T> GetOrCreateAsync<T>(string key, Func<Task<T>> factory, TimeSpan? expiration = null);
void Remove(string key);
void Clear();
}
public class CacheService : ICacheService
{
private readonly IMemoryCache _memoryCache;
private readonly ILogger<CacheService> _logger;
public CacheService(IMemoryCache memoryCache, ILogger<CacheService> logger)
{
_memoryCache = memoryCache;
_logger = logger;
}
public async Task<T> GetOrCreateAsync<T>(string key, Func<Task<T>> factory, TimeSpan? expiration = null)
{
if (_memoryCache.TryGetValue(key, out T cachedValue))
{
_logger.LogDebug("Cache hit for key: {Key}", key);
return cachedValue;
}
_logger.LogDebug("Cache miss for key: {Key}", key);
var value = await factory();
var cacheOptions = new MemoryCacheEntryOptions
{
AbsoluteExpirationRelativeToNow = expiration ?? TimeSpan.FromMinutes(5)
};
_memoryCache.Set(key, value, cacheOptions);
return value;
}
public void Remove(string key)
{
_memoryCache.Remove(key);
_logger.LogDebug("Cache removed for key: {Key}", key);
}
public void Clear()
{
// Note: IMemoryCache doesn't have a Clear method by default
// This would need to be implemented with a custom cache implementation
// For now, we'll log a warning
_logger.LogWarning("Clear operation not supported by default IMemoryCache");
}
}
// Cached repository example
public class CachedProductRepository
{
private readonly IProductRepository _productRepository;
private readonly ICacheService _cacheService;
public CachedProductRepository(IProductRepository productRepository, ICacheService cacheService)
{
_productRepository = productRepository;
_cacheService = cacheService;
}
public async Task<Product?> GetByIdAsync(int id)
{
var cacheKey = $"product_{id}";
return await _cacheService.GetOrCreateAsync(cacheKey,
() => _productRepository.GetByIdAsync(id),
TimeSpan.FromMinutes(10));
}
public async Task<IEnumerable<Product>> GetByCategoryAsync(string category)
{
var cacheKey = $"products_category_{category}";
return await _cacheService.GetOrCreateAsync(cacheKey,
() => _productRepository.GetByCategoryAsync(category),
TimeSpan.FromMinutes(5));
}
public async Task<bool> UpdateAsync(Product product)
{
var result = await _productRepository.UpdateAsync(product);
if (result)
{
// Invalidate cache for this product
_cacheService.Remove($"product_{product.Id}");
// Invalidate category cache as well
_cacheService.Remove($"products_category_{product.Category}");
}
return result;
}
}
}
5. Multi-Mapping and Complex Relationships
One-to-Many Relationships
Repositories/OrderRepository.cs
using Dapper;
using DapperDeepDive.Models;
using System.Data;
namespace DapperDeepDive.Repositories
{
public interface IOrderRepository
{
Task<Order?> GetOrderWithDetailsAsync(int orderId);
Task<IEnumerable<Order>> GetUserOrdersWithDetailsAsync(int userId);
Task<OrderSummary> GetOrderSummaryAsync(int orderId);
Task<int> CreateOrderWithItemsAsync(Order order, List<OrderItem> items);
Task<bool> UpdateOrderStatusAsync(int orderId, string status);
}
public class OrderRepository : IOrderRepository
{
private readonly IDbConnection _connection;
public OrderRepository(IDbConnection connection)
{
_connection = connection;
}
public async Task<Order?> GetOrderWithDetailsAsync(int orderId)
{
const string sql = @"
SELECT
o.*,
u.*,
oi.*,
p.*
FROM Orders o
INNER JOIN Users u ON o.UserId = u.Id
LEFT JOIN OrderItems oi ON o.Id = oi.OrderId
LEFT JOIN Products p ON oi.ProductId = p.Id
WHERE o.Id = @OrderId
ORDER BY oi.Id";
var orderDict = new Dictionary<int, Order>();
var order = await _connection.QueryAsync<Order, User, OrderItem, Product, Order>(
sql,
(order, user, orderItem, product) =>
{
if (!orderDict.TryGetValue(order.Id, out var orderEntry))
{
orderEntry = order;
orderEntry.User = user;
orderEntry.OrderItems = new List<OrderItem>();
orderDict.Add(orderEntry.Id, orderEntry);
}
if (orderItem != null)
{
orderItem.Product = product;
orderEntry.OrderItems.Add(orderItem);
}
return orderEntry;
},
new { OrderId = orderId },
splitOn: "Id,Id,Id");
return orderDict.Values.FirstOrDefault();
}
public async Task<IEnumerable<Order>> GetUserOrdersWithDetailsAsync(int userId)
{
const string sql = @"
SELECT
o.*,
oi.*,
p.*
FROM Orders o
LEFT JOIN OrderItems oi ON o.Id = oi.OrderId
LEFT JOIN Products p ON oi.ProductId = p.Id
WHERE o.UserId = @UserId
ORDER BY o.OrderDate DESC, oi.Id";
var orderDict = new Dictionary<int, Order>();
var orders = await _connection.QueryAsync<Order, OrderItem, Product, Order>(
sql,
(order, orderItem, product) =>
{
if (!orderDict.TryGetValue(order.Id, out var orderEntry))
{
orderEntry = order;
orderEntry.OrderItems = new List<OrderItem>();
orderDict.Add(orderEntry.Id, orderEntry);
}
if (orderItem != null)
{
orderItem.Product = product;
orderEntry.OrderItems.Add(orderItem);
}
return orderEntry;
},
new { UserId = userId },
splitOn: "Id,Id");
return orderDict.Values;
}
public async Task<OrderSummary> GetOrderSummaryAsync(int orderId)
{
const string sql = @"
SELECT
o.Id,
o.OrderDate,
o.TotalAmount,
o.Status,
u.Username,
u.Email,
COUNT(oi.Id) as ItemCount,
SUM(oi.Quantity) as TotalQuantity
FROM Orders o
INNER JOIN Users u ON o.UserId = u.Id
LEFT JOIN OrderItems oi ON o.Id = oi.OrderId
WHERE o.Id = @OrderId
GROUP BY o.Id, o.OrderDate, o.TotalAmount, o.Status, u.Username, u.Email";
return await _connection.QueryFirstOrDefaultAsync<OrderSummary>(sql, new { OrderId = orderId });
}
public async Task<int> CreateOrderWithItemsAsync(Order order, List<OrderItem> items)
{
using var transaction = _connection.BeginTransaction();
try
{
// Insert order
const string orderSql = @"
INSERT INTO Orders (UserId, OrderDate, TotalAmount, Status, ShippingAddress)
OUTPUT INSERTED.Id
VALUES (@UserId, @OrderDate, @TotalAmount, @Status, @ShippingAddress)";
var orderId = await _connection.ExecuteScalarAsync<int>(orderSql, order, transaction);
// Insert order items
const string itemsSql = @"
INSERT INTO OrderItems (OrderId, ProductId, Quantity, UnitPrice, TotalPrice)
VALUES (@OrderId, @ProductId, @Quantity, @UnitPrice, @TotalPrice)";
foreach (var item in items)
{
item.OrderId = orderId;
await _connection.ExecuteAsync(itemsSql, item, transaction);
}
transaction.Commit();
return orderId;
}
catch
{
transaction.Rollback();
throw;
}
}
public async Task<bool> UpdateOrderStatusAsync(int orderId, string status)
{
const string sql = "UPDATE Orders SET Status = @Status WHERE Id = @OrderId";
var affectedRows = await _connection.ExecuteAsync(sql,
new { OrderId = orderId, Status = status });
return affectedRows > 0;
}
}
public class OrderSummary
{
public int Id { get; set; }
public DateTime OrderDate { get; set; }
public decimal TotalAmount { get; set; }
public string Status { get; set; } = string.Empty;
public string Username { get; set; } = string.Empty;
public string Email { get; set; } = string.Empty;
public int ItemCount { get; set; }
public int TotalQuantity { get; set; }
}
}
Many-to-Many Relationships
Repositories/TagRepository.cs
using Dapper;
using DapperDeepDive.Models;
using System.Data;
namespace DapperDeepDive.Repositories
{
public class Tag
{
public int Id { get; set; }
public string Name { get; set; } = string.Empty;
public string Description { get; set; } = string.Empty;
public DateTime CreatedAt { get; set; }
}
public class ProductTag
{
public int ProductId { get; set; }
public int TagId { get; set; }
public DateTime AssociatedAt { get; set; }
}
public interface ITagRepository
{
Task<IEnumerable<Tag>> GetProductTagsAsync(int productId);
Task<IEnumerable<Product>> GetProductsByTagAsync(int tagId);
Task<bool> AddTagToProductAsync(int productId, int tagId);
Task<bool> RemoveTagFromProductAsync(int productId, int tagId);
Task<IEnumerable<Product>> GetProductsByMultipleTagsAsync(IEnumerable<int> tagIds);
}
public class TagRepository : ITagRepository
{
private readonly IDbConnection _connection;
public TagRepository(IDbConnection connection)
{
_connection = connection;
}
public async Task<IEnumerable<Tag>> GetProductTagsAsync(int productId)
{
const string sql = @"
SELECT t.*
FROM Tags t
INNER JOIN ProductTags pt ON t.Id = pt.TagId
WHERE pt.ProductId = @ProductId
ORDER BY t.Name";
return await _connection.QueryAsync<Tag>(sql, new { ProductId = productId });
}
public async Task<IEnumerable<Product>> GetProductsByTagAsync(int tagId)
{
const string sql = @"
SELECT p.*
FROM Products p
INNER JOIN ProductTags pt ON p.Id = pt.ProductId
WHERE pt.TagId = @TagId AND p.IsActive = 1
ORDER BY p.Name";
return await _connection.QueryAsync<Product>(sql, new { TagId = tagId });
}
public async Task<bool> AddTagToProductAsync(int productId, int tagId)
{
const string sql = @"
INSERT INTO ProductTags (ProductId, TagId, AssociatedAt)
VALUES (@ProductId, @TagId, GETUTCDATE())";
try
{
var affectedRows = await _connection.ExecuteAsync(sql,
new { ProductId = productId, TagId = tagId });
return affectedRows > 0;
}
catch (SqlException ex) when (ex.Number == 2627) // Primary key violation
{
// Tag already associated with product
return false;
}
}
public async Task<bool> RemoveTagFromProductAsync(int productId, int tagId)
{
const string sql = @"
DELETE FROM ProductTags
WHERE ProductId = @ProductId AND TagId = @TagId";
var affectedRows = await _connection.ExecuteAsync(sql,
new { ProductId = productId, TagId = tagId });
return affectedRows > 0;
}
public async Task<IEnumerable<Product>> GetProductsByMultipleTagsAsync(IEnumerable<int> tagIds)
{
const string sql = @"
SELECT p.*
FROM Products p
WHERE p.Id IN (
SELECT pt.ProductId
FROM ProductTags pt
WHERE pt.TagId IN @TagIds
GROUP BY pt.ProductId
HAVING COUNT(DISTINCT pt.TagId) = @TagCount
) AND p.IsActive = 1
ORDER BY p.Name";
var tagIdList = tagIds.ToList();
return await _connection.QueryAsync<Product>(sql,
new { TagIds = tagIdList, TagCount = tagIdList.Count });
}
}
}
6. Stored Procedures and Dynamic Parameters
Stored Procedure Integration
Repositories/StoredProcedureRepository.cs
using Dapper;
using DapperDeepDive.Models;
using System.Data;
namespace DapperDeepDive.Repositories
{
public interface IStoredProcedureRepository
{
Task<IEnumerable<Product>> GetTopSellingProductsAsync(int count);
Task<decimal> GetUserLifetimeValueAsync(int userId);
Task<IEnumerable<User>> SearchUsersAdvancedAsync(UserSearchParameters parameters);
Task<SalesReport> GenerateSalesReportAsync(DateTime startDate, DateTime endDate);
Task<bool> ArchiveOldOrdersAsync(DateTime cutoffDate);
}
public class StoredProcedureRepository : IStoredProcedureRepository
{
private readonly IDbConnection _connection;
public StoredProcedureRepository(IDbConnection connection)
{
_connection = connection;
}
public async Task<IEnumerable<Product>> GetTopSellingProductsAsync(int count)
{
const string sql = "EXEC GetTopSellingProducts @Count";
return await _connection.QueryAsync<Product>(sql, new { Count = count });
}
public async Task<decimal> GetUserLifetimeValueAsync(int userId)
{
const string sql = "EXEC GetUserLifetimeValue @UserId";
return await _connection.ExecuteScalarAsync<decimal>(sql, new { UserId = userId });
}
public async Task<IEnumerable<User>> SearchUsersAdvancedAsync(UserSearchParameters parameters)
{
var dynamicParams = new DynamicParameters();
dynamicParams.Add("@SearchTerm", parameters.SearchTerm);
dynamicParams.Add("@MinOrderCount", parameters.MinOrderCount);
dynamicParams.Add("@MinTotalSpent", parameters.MinTotalSpent);
dynamicParams.Add("@StartDate", parameters.StartDate);
dynamicParams.Add("@EndDate", parameters.EndDate);
dynamicParams.Add("@PageNumber", parameters.PageNumber);
dynamicParams.Add("@PageSize", parameters.PageSize);
const string sql = "EXEC SearchUsersAdvanced @SearchTerm, @MinOrderCount, @MinTotalSpent, @StartDate, @EndDate, @PageNumber, @PageSize";
return await _connection.QueryAsync<User>(sql, dynamicParams);
}
public async Task<SalesReport> GenerateSalesReportAsync(DateTime startDate, DateTime endDate)
{
const string sql = "EXEC GenerateSalesReport @StartDate, @EndDate";
using var multi = await _connection.QueryMultipleAsync(sql,
new { StartDate = startDate, EndDate = endDate });
var report = await multi.ReadFirstAsync<SalesReport>();
report.DailySales = await multi.ReadAsync<DailySales>();
report.TopProducts = await multi.ReadAsync<TopProduct>();
return report;
}
public async Task<bool> ArchiveOldOrdersAsync(DateTime cutoffDate)
{
const string sql = "EXEC ArchiveOldOrders @CutoffDate";
var affectedRows = await _connection.ExecuteAsync(sql, new { CutoffDate = cutoffDate });
return affectedRows > 0;
}
}
public class UserSearchParameters
{
public string? SearchTerm { get; set; }
public int? MinOrderCount { get; set; }
public decimal? MinTotalSpent { get; set; }
public DateTime? StartDate { get; set; }
public DateTime? EndDate { get; set; }
public int PageNumber { get; set; } = 1;
public int PageSize { get; set; } = 20;
}
public class SalesReport
{
public DateTime StartDate { get; set; }
public DateTime EndDate { get; set; }
public int TotalOrders { get; set; }
public decimal TotalRevenue { get; set; }
public decimal AverageOrderValue { get; set; }
public int UniqueCustomers { get; set; }
public IEnumerable<DailySales> DailySales { get; set; } = new List<DailySales>();
public IEnumerable<TopProduct> TopProducts { get; set; } = new List<TopProduct>();
}
public class DailySales
{
public DateTime Date { get; set; }
public int OrderCount { get; set; }
public decimal TotalRevenue { get; set; }
}
public class TopProduct
{
public int ProductId { get; set; }
public string ProductName { get; set; } = string.Empty;
public int QuantitySold { get; set; }
public decimal TotalRevenue { get; set; }
}
}
Dynamic Parameters and Output Parameters
Services/DynamicParameterService.cs
using Dapper;
using System.Data;
namespace DapperDeepDive.Services
{
public interface IDynamicParameterService
{
Task<(int TotalRecords, IEnumerable<User> Users)> GetUsersWithOutputParameterAsync(
int pageNumber, int pageSize);
Task<decimal> CalculateOrderTotalWithOutputAsync(int orderId);
Task<bool> RegisterUserWithValidationAsync(UserRegistration registration);
}
public class DynamicParameterService : IDynamicParameterService
{
private readonly IDbConnection _connection;
public DynamicParameterService(IDbConnection connection)
{
_connection = connection;
}
public async Task<(int TotalRecords, IEnumerable<User> Users)> GetUsersWithOutputParameterAsync(
int pageNumber, int pageSize)
{
var parameters = new DynamicParameters();
parameters.Add("@PageNumber", pageNumber);
parameters.Add("@PageSize", pageSize);
parameters.Add("@TotalRecords", dbType: DbType.Int32, direction: ParameterDirection.Output);
const string sql = @"
SELECT
Id, Username, Email, FirstName, LastName
FROM Users
WHERE IsActive = 1
ORDER BY CreatedAt DESC
OFFSET (@PageNumber - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;
SELECT @TotalRecords = COUNT(*) FROM Users WHERE IsActive = 1;";
using var multi = await _connection.QueryMultipleAsync(sql, parameters);
var users = await multi.ReadAsync<User>();
var totalRecords = parameters.Get<int>("@TotalRecords");
return (totalRecords, users);
}
public async Task<decimal> CalculateOrderTotalWithOutputAsync(int orderId)
{
var parameters = new DynamicParameters();
parameters.Add("@OrderId", orderId);
parameters.Add("@TotalAmount", dbType: DbType.Decimal,
direction: ParameterDirection.Output, size: 18);
await _connection.ExecuteAsync(
"CalculateOrderTotal",
parameters,
commandType: CommandType.StoredProcedure);
return parameters.Get<decimal>("@TotalAmount");
}
public async Task<bool> RegisterUserWithValidationAsync(UserRegistration registration)
{
var parameters = new DynamicParameters();
parameters.Add("@Username", registration.Username);
parameters.Add("@Email", registration.Email);
parameters.Add("@FirstName", registration.FirstName);
parameters.Add("@LastName", registration.LastName);
parameters.Add("@PasswordHash", registration.PasswordHash);
parameters.Add("@IsSuccess", dbType: DbType.Boolean, direction: ParameterDirection.Output);
parameters.Add("@ErrorMessage", dbType: DbType.String, direction: ParameterDirection.Output, size: 500);
await _connection.ExecuteAsync(
"RegisterUser",
parameters,
commandType: CommandType.StoredProcedure);
var isSuccess = parameters.Get<bool>("@IsSuccess");
var errorMessage = parameters.Get<string>("@ErrorMessage");
if (!isSuccess && !string.IsNullOrEmpty(errorMessage))
{
throw new InvalidOperationException(errorMessage);
}
return isSuccess;
}
}
public class UserRegistration
{
public string Username { get; set; } = string.Empty;
public string Email { get; set; } = string.Empty;
public string FirstName { get; set; } = string.Empty;
public string LastName { get; set; } = string.Empty;
public string PasswordHash { get; set; } = string.Empty;
}
}
7. Bulk Operations and Performance
Bulk Insert and Update Operations
Services/BulkOperationService.cs
using Dapper;
using DapperDeepDive.Models;
using System.Data;
using Microsoft.Data.SqlClient;
namespace DapperDeepDive.Services
{
public interface IBulkOperationService
{
Task<int> BulkInsertProductsAsync(IEnumerable<Product> products);
Task<int> BulkUpdateProductPricesAsync(IEnumerable<ProductPriceUpdate> updates);
Task<int> BulkMergeProductsAsync(IEnumerable<Product> products);
Task<int> BulkInsertUsersAsync(IEnumerable<User> users);
Task<BulkOperationResult> ExecuteBulkOperationsAsync(BulkOperationRequest request);
}
public class BulkOperationService : IBulkOperationService
{
private readonly IDbConnection _connection;
private readonly ILogger<BulkOperationService> _logger;
public BulkOperationService(IDbConnection connection, ILogger<BulkOperationService> logger)
{
_connection = connection;
_logger = logger;
}
public async Task<int> BulkInsertProductsAsync(IEnumerable<Product> products)
{
const string sql = @"
INSERT INTO Products (Name, Description, Price, StockQuantity, Category)
VALUES (@Name, @Description, @Price, @StockQuantity, @Category)";
var productList = products.ToList();
var affectedRows = await _connection.ExecuteAsync(sql, productList);
_logger.LogInformation("Bulk inserted {Count} products", affectedRows);
return affectedRows;
}
public async Task<int> BulkUpdateProductPricesAsync(IEnumerable<ProductPriceUpdate> updates)
{
const string sql = @"
UPDATE Products
SET Price = @NewPrice,
Description = CASE
WHEN @UpdateDescription = 1 THEN
Description + ' (Price updated: ' + CAST(Price AS NVARCHAR(20)) + ' -> ' + CAST(@NewPrice AS NVARCHAR(20)) + ')'
ELSE Description
END
WHERE Id = @ProductId";
var affectedRows = await _connection.ExecuteAsync(sql, updates);
_logger.LogInformation("Bulk updated prices for {Count} products", affectedRows);
return affectedRows;
}
public async Task<int> BulkMergeProductsAsync(IEnumerable<Product> products)
{
// Using MERGE statement for upsert operations
const string sql = @"
MERGE Products AS target
USING (VALUES (@Id, @Name, @Description, @Price, @StockQuantity, @Category))
AS source (Id, Name, Description, Price, StockQuantity, Category)
ON target.Id = source.Id
WHEN MATCHED THEN
UPDATE SET
Name = source.Name,
Description = source.Description,
Price = source.Price,
StockQuantity = source.StockQuantity,
Category = source.Category
WHEN NOT MATCHED THEN
INSERT (Name, Description, Price, StockQuantity, Category)
VALUES (source.Name, source.Description, source.Price, source.StockQuantity, source.Category);";
var affectedRows = await _connection.ExecuteAsync(sql, products);
_logger.LogInformation("Bulk merged {Count} products", affectedRows);
return affectedRows;
}
public async Task<int> BulkInsertUsersAsync(IEnumerable<User> users)
{
const string sql = @"
INSERT INTO Users (Username, Email, FirstName, LastName, DateOfBirth)
VALUES (@Username, @Email, @FirstName, @LastName, @DateOfBirth)";
var userList = users.ToList();
var affectedRows = await _connection.ExecuteAsync(sql, userList);
_logger.LogInformation("Bulk inserted {Count} users", affectedRows);
return affectedRows;
}
public async Task<BulkOperationResult> ExecuteBulkOperationsAsync(BulkOperationRequest request)
{
var result = new BulkOperationResult();
var stopwatch = System.Diagnostics.Stopwatch.StartNew();
using var transaction = _connection.BeginTransaction();
try
{
if (request.ProductsToInsert?.Any() == true)
{
result.ProductsInserted = await BulkInsertProductsAsync(request.ProductsToInsert);
}
if (request.ProductsToUpdate?.Any() == true)
{
result.ProductsUpdated = await BulkUpdateProductPricesAsync(request.ProductsToUpdate);
}
if (request.UsersToInsert?.Any() == true)
{
result.UsersInserted = await BulkInsertUsersAsync(request.UsersToInsert);
}
transaction.Commit();
result.Success = true;
}
catch (Exception ex)
{
transaction.Rollback();
result.Success = false;
result.ErrorMessage = ex.Message;
_logger.LogError(ex, "Bulk operations failed");
}
finally
{
stopwatch.Stop();
result.Duration = stopwatch.Elapsed;
}
return result;
}
}
public class ProductPriceUpdate
{
public int ProductId { get; set; }
public decimal NewPrice { get; set; }
public bool UpdateDescription { get; set; } = false;
}
public class BulkOperationRequest
{
public IEnumerable<Product>? ProductsToInsert { get; set; }
public IEnumerable<ProductPriceUpdate>? ProductsToUpdate { get; set; }
public IEnumerable<User>? UsersToInsert { get; set; }
}
public class BulkOperationResult
{
public bool Success { get; set; }
public string? ErrorMessage { get; set; }
public TimeSpan Duration { get; set; }
public int ProductsInserted { get; set; }
public int ProductsUpdated { get; set; }
public int UsersInserted { get; set; }
public int TotalOperations => ProductsInserted + ProductsUpdated + UsersInserted;
}
}
Performance Comparison Utilities
Services/PerformanceComparisonService.cs
using Dapper;
using DapperDeepDive.Models;
using System.Data;
using System.Diagnostics;
namespace DapperDeepDive.Services
{
public interface IPerformanceComparisonService
{
Task<PerformanceComparisonResult> CompareQueryMethodsAsync();
Task<PerformanceComparisonResult> CompareBulkOperationsAsync(int recordCount);
Task<PerformanceComparisonResult> CompareMappingStrategiesAsync();
}
public class PerformanceComparisonService : IPerformanceComparisonService
{
private readonly IDbConnection _connection;
private readonly ILogger<PerformanceComparisonService> _logger;
public PerformanceComparisonService(IDbConnection connection, ILogger<PerformanceComparisonService> logger)
{
_connection = connection;
_logger = logger;
}
public async Task<PerformanceComparisonResult> CompareQueryMethodsAsync()
{
var result = new PerformanceComparisonResult("Query Methods Comparison");
var stopwatch = new Stopwatch();
// Test 1: Dapper QueryAsync
stopwatch.Start();
var dapperResult = await _connection.QueryAsync<Product>(
"SELECT * FROM Products WHERE IsActive = 1");
stopwatch.Stop();
result.AddResult("Dapper QueryAsync", stopwatch.Elapsed, dapperResult.Count());
// Test 2: Dapper QueryFirstOrDefaultAsync
stopwatch.Restart();
var singleResult = await _connection.QueryFirstOrDefaultAsync<Product>(
"SELECT * FROM Products WHERE Id = 1");
stopwatch.Stop();
result.AddResult("Dapper QueryFirstOrDefault", stopwatch.Elapsed, 1);
// Test 3: Multiple queries with QueryMultiple
stopwatch.Restart();
using var multi = await _connection.QueryMultipleAsync(@"
SELECT * FROM Products WHERE IsActive = 1;
SELECT COUNT(*) FROM Products;");
var products = await multi.ReadAsync<Product>();
var count = await multi.ReadSingleAsync<int>();
stopwatch.Stop();
result.AddResult("Dapper QueryMultiple", stopwatch.Elapsed, products.Count());
return result;
}
public async Task<PerformanceComparisonResult> CompareBulkOperationsAsync(int recordCount)
{
var result = new PerformanceComparisonResult($"Bulk Operations Comparison ({recordCount} records)");
var testProducts = GenerateTestProducts(recordCount);
// Test 1: Individual inserts
var stopwatch = Stopwatch.StartNew();
foreach (var product in testProducts)
{
await _connection.ExecuteAsync(
"INSERT INTO Products (Name, Price, StockQuantity) VALUES (@Name, @Price, @StockQuantity)",
product);
}
stopwatch.Stop();
result.AddResult("Individual Inserts", stopwatch.Elapsed, recordCount);
// Clean up
await _connection.ExecuteAsync("DELETE FROM Products WHERE Name LIKE 'TestProduct%'");
// Test 2: Bulk insert with Dapper
stopwatch.Restart();
await _connection.ExecuteAsync(
"INSERT INTO Products (Name, Price, StockQuantity) VALUES (@Name, @Price, @StockQuantity)",
testProducts);
stopwatch.Stop();
result.AddResult("Dapper Bulk Insert", stopwatch.Elapsed, recordCount);
// Clean up
await _connection.ExecuteAsync("DELETE FROM Products WHERE Name LIKE 'TestProduct%'");
return result;
}
public async Task<PerformanceComparisonResult> CompareMappingStrategiesAsync()
{
var result = new PerformanceComparisonResult("Mapping Strategies Comparison");
// Test 1: Simple mapping
var stopwatch = Stopwatch.StartNew();
var simpleResults = await _connection.QueryAsync<Product>(
"SELECT * FROM Products WHERE IsActive = 1");
stopwatch.Stop();
result.AddResult("Simple Mapping", stopwatch.Elapsed, simpleResults.Count());
// Test 2: Complex mapping with joins
stopwatch.Restart();
const string complexSql = @"
SELECT
o.*,
u.*,
oi.*,
p.*
FROM Orders o
INNER JOIN Users u ON o.UserId = u.Id
LEFT JOIN OrderItems oi ON o.Id = oi.OrderId
LEFT JOIN Products p ON oi.ProductId = p.Id
WHERE o.OrderDate >= DATEADD(day, -30, GETUTCDATE())";
var orderDict = new Dictionary<int, Order>();
var complexResults = await _connection.QueryAsync<Order, User, OrderItem, Product, Order>(
complexSql,
(order, user, orderItem, product) =>
{
if (!orderDict.TryGetValue(order.Id, out var orderEntry))
{
orderEntry = order;
orderEntry.User = user;
orderEntry.OrderItems = new List<OrderItem>();
orderDict.Add(orderEntry.Id, orderEntry);
}
if (orderItem != null)
{
orderItem.Product = product;
orderEntry.OrderItems.Add(orderItem);
}
return orderEntry;
},
splitOn: "Id,Id,Id");
stopwatch.Stop();
result.AddResult("Complex Multi-Mapping", stopwatch.Elapsed, orderDict.Count);
return result;
}
private IEnumerable<Product> GenerateTestProducts(int count)
{
for (int i = 0; i < count; i++)
{
yield return new Product
{
Name = $"TestProduct_{i}",
Price = i * 10.0m,
StockQuantity = i * 5
};
}
}
}
public class PerformanceComparisonResult
{
public string TestName { get; set; }
public List<MethodResult> Results { get; set; } = new List<MethodResult>();
public PerformanceComparisonResult(string testName)
{
TestName = testName;
}
public void AddResult(string methodName, TimeSpan duration, int recordsProcessed)
{
Results.Add(new MethodResult
{
MethodName = methodName,
Duration = duration,
RecordsProcessed = recordsProcessed,
RecordsPerSecond = duration.TotalSeconds > 0 ? recordsProcessed / duration.TotalSeconds : 0
});
}
public MethodResult? GetFastestMethod()
{
return Results.OrderBy(r => r.Duration).FirstOrDefault();
}
public MethodResult? GetSlowestMethod()
{
return Results.OrderByDescending(r => r.Duration).FirstOrDefault();
}
}
public class MethodResult
{
public string MethodName { get; set; } = string.Empty;
public TimeSpan Duration { get; set; }
public int RecordsProcessed { get; set; }
public double RecordsPerSecond { get; set; }
}
}
8. Real-World Enterprise Scenarios
E-Commerce Application Services
Services/ECommerceService.cs
using Dapper;
using DapperDeepDive.Models;
using System.Data;
namespace DapperDeepDive.Services
{
public interface IECommerceService
{
Task<OrderResult> PlaceOrderAsync(OrderRequest request);
Task<InventoryStatus> CheckInventoryAsync(int productId, int quantity);
Task<decimal> CalculateOrderTotalAsync(OrderCalculationRequest request);
Task<IEnumerable<Product>> GetRecommendedProductsAsync(int userId);
Task<CustomerDashboard> GetCustomerDashboardAsync(int userId);
}
public class ECommerceService : IECommerceService
{
private readonly IDbConnection _connection;
private readonly ILogger<ECommerceService> _logger;
public ECommerceService(IDbConnection connection, ILogger<ECommerceService> logger)
{
_connection = connection;
_logger = logger;
}
public async Task<OrderResult> PlaceOrderAsync(OrderRequest request)
{
using var transaction = _connection.BeginTransaction();
try
{
// 1. Validate inventory
var inventoryStatus = await CheckInventoryAsync(request.ProductId, request.Quantity);
if (!inventoryStatus.IsAvailable)
{
return new OrderResult
{
Success = false,
Message = $"Insufficient inventory. Available: {inventoryStatus.AvailableQuantity}"
};
}
// 2. Calculate order total
var calculationRequest = new OrderCalculationRequest
{
ProductId = request.ProductId,
Quantity = request.Quantity,
UserId = request.UserId
};
var totalAmount = await CalculateOrderTotalAsync(calculationRequest);
// 3. Create order
var order = new Order
{
UserId = request.UserId,
TotalAmount = totalAmount,
Status = "Pending",
ShippingAddress = request.ShippingAddress
};
const string orderSql = @"
INSERT INTO Orders (UserId, TotalAmount, Status, ShippingAddress)
OUTPUT INSERTED.Id
VALUES (@UserId, @TotalAmount, @Status, @ShippingAddress)";
var orderId = await _connection.ExecuteScalarAsync<int>(orderSql, order, transaction);
// 4. Create order item
var orderItem = new OrderItem
{
OrderId = orderId,
ProductId = request.ProductId,
Quantity = request.Quantity,
UnitPrice = inventoryStatus.UnitPrice,
TotalPrice = totalAmount
};
const string itemSql = @"
INSERT INTO OrderItems (OrderId, ProductId, Quantity, UnitPrice, TotalPrice)
VALUES (@OrderId, @ProductId, @Quantity, @UnitPrice, @TotalPrice)";
await _connection.ExecuteAsync(itemSql, orderItem, transaction);
// 5. Update inventory
const string updateInventorySql = @"
UPDATE Products
SET StockQuantity = StockQuantity - @Quantity
WHERE Id = @ProductId";
await _connection.ExecuteAsync(updateInventorySql,
new { ProductId = request.ProductId, Quantity = request.Quantity },
transaction);
transaction.Commit();
_logger.LogInformation("Order {OrderId} placed successfully for user {UserId}",
orderId, request.UserId);
return new OrderResult
{
Success = true,
OrderId = orderId,
TotalAmount = totalAmount,
Message = "Order placed successfully"
};
}
catch (Exception ex)
{
transaction.Rollback();
_logger.LogError(ex, "Failed to place order for user {UserId}", request.UserId);
return new OrderResult
{
Success = false,
Message = "Failed to place order: " + ex.Message
};
}
}
public async Task<InventoryStatus> CheckInventoryAsync(int productId, int quantity)
{
const string sql = @"
SELECT
StockQuantity as AvailableQuantity,
Price as UnitPrice,
CASE WHEN StockQuantity >= @Quantity THEN 1 ELSE 0 END as IsAvailable
FROM Products
WHERE Id = @ProductId AND IsActive = 1";
var status = await _connection.QueryFirstOrDefaultAsync<InventoryStatus>(sql,
new { ProductId = productId, Quantity = quantity });
return status ?? new InventoryStatus { IsAvailable = false };
}
public async Task<decimal> CalculateOrderTotalAsync(OrderCalculationRequest request)
{
const string sql = @"
DECLARE @BasePrice DECIMAL(18,2);
DECLARE @Discount DECIMAL(18,2) = 0;
-- Get base price
SELECT @BasePrice = Price
FROM Products
WHERE Id = @ProductId;
-- Check for user discounts
SELECT @Discount = DiscountPercent
FROM UserDiscounts
WHERE UserId = @UserId AND IsActive = 1;
-- Calculate total
SELECT (@BasePrice * @Quantity) * (1 - ISNULL(@Discount, 0) / 100) as TotalAmount;";
return await _connection.ExecuteScalarAsync<decimal>(sql, request);
}
public async Task<IEnumerable<Product>> GetRecommendedProductsAsync(int userId)
{
const string sql = @"
-- Based on user's order history
SELECT DISTINCT p.*
FROM Products p
INNER JOIN OrderItems oi ON p.Id = oi.ProductId
INNER JOIN Orders o ON oi.OrderId = o.Id
WHERE o.UserId = @UserId
AND p.IsActive = 1
AND p.Id NOT IN (
SELECT ProductId
FROM OrderItems oi2
INNER JOIN Orders o2 ON oi2.OrderId = o2.Id
WHERE o2.UserId = @UserId
AND o2.OrderDate >= DATEADD(day, -30, GETUTCDATE())
)
ORDER BY p.CreatedAt DESC";
return await _connection.QueryAsync<Product>(sql, new { UserId = userId });
}
public async Task<CustomerDashboard> GetCustomerDashboardAsync(int userId)
{
const string sql = @"
-- Customer basic info
SELECT
u.Id, u.Username, u.Email, u.FirstName, u.LastName,
COUNT(o.Id) as TotalOrders,
SUM(o.TotalAmount) as TotalSpent,
MAX(o.OrderDate) as LastOrderDate
FROM Users u
LEFT JOIN Orders o ON u.Id = o.UserId
WHERE u.Id = @UserId
GROUP BY u.Id, u.Username, u.Email, u.FirstName, u.LastName;
-- Recent orders
SELECT
o.Id, o.OrderDate, o.TotalAmount, o.Status
FROM Orders o
WHERE o.UserId = @UserId
ORDER BY o.OrderDate DESC
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;
-- Favorite categories
SELECT
p.Category,
COUNT(oi.Id) as OrderCount,
SUM(oi.Quantity) as TotalQuantity
FROM OrderItems oi
INNER JOIN Products p ON oi.ProductId = p.Id
INNER JOIN Orders o ON oi.OrderId = o.Id
WHERE o.UserId = @UserId
GROUP BY p.Category
ORDER BY TotalQuantity DESC;";
using var multi = await _connection.QueryMultipleAsync(sql, new { UserId = userId });
var customerInfo = await multi.ReadFirstOrDefaultAsync<CustomerDashboard>();
var recentOrders = await multi.ReadAsync<OrderSummary>();
var favoriteCategories = await multi.ReadAsync<FavoriteCategory>();
if (customerInfo != null)
{
customerInfo.RecentOrders = recentOrders.ToList();
customerInfo.FavoriteCategories = favoriteCategories.ToList();
}
return customerInfo ?? new CustomerDashboard();
}
}
public class OrderRequest
{
public int UserId { get; set; }
public int ProductId { get; set; }
public int Quantity { get; set; }
public string ShippingAddress { get; set; } = string.Empty;
}
public class OrderResult
{
public bool Success { get; set; }
public int OrderId { get; set; }
public decimal TotalAmount { get; set; }
public string Message { get; set; } = string.Empty;
}
public class InventoryStatus
{
public int AvailableQuantity { get; set; }
public decimal UnitPrice { get; set; }
public bool IsAvailable { get; set; }
}
public class OrderCalculationRequest
{
public int UserId { get; set; }
public int ProductId { get; set; }
public int Quantity { get; set; }
}
public class CustomerDashboard
{
public int Id { get; set; }
public string Username { get; set; } = string.Empty;
public string Email { get; set; } = string.Empty;
public string FirstName { get; set; } = string.Empty;
public string LastName { get; set; } = string.Empty;
public int TotalOrders { get; set; }
public decimal TotalSpent { get; set; }
public DateTime? LastOrderDate { get; set; }
public List<OrderSummary> RecentOrders { get; set; } = new List<OrderSummary>();
public List<FavoriteCategory> FavoriteCategories { get; set; } = new List<FavoriteCategory>();
}
public class FavoriteCategory
{
public string Category { get; set; } = string.Empty;
public int OrderCount { get; set; }
public int TotalQuantity { get; set; }
}
}
Reporting and Analytics
Services/ReportingService.cs
using Dapper;
using DapperDeepDive.Models;
using System.Data;
namespace DapperDeepDive.Services
{
public interface IReportingService
{
Task<SalesReport> GetSalesReportAsync(SalesReportRequest request);
Task<IEnumerable<MonthlySales>> GetMonthlySalesTrendAsync(int year);
Task<ProductPerformanceReport> GetProductPerformanceReportAsync(ProductReportRequest request);
Task<CustomerAnalytics> GetCustomerAnalyticsAsync();
Task<IEnumerable<GeographicSales>> GetGeographicSalesAsync();
}
public class ReportingService : IReportingService
{
private readonly IDbConnection _connection;
public ReportingService(IDbConnection connection)
{
_connection = connection;
}
public async Task<SalesReport> GetSalesReportAsync(SalesReportRequest request)
{
const string sql = @"
-- Total sales and orders
SELECT
COUNT(o.Id) as TotalOrders,
SUM(o.TotalAmount) as TotalRevenue,
AVG(o.TotalAmount) as AverageOrderValue,
MIN(o.OrderDate) as FirstOrderDate,
MAX(o.OrderDate) as LastOrderDate
FROM Orders o
WHERE o.OrderDate BETWEEN @StartDate AND @EndDate;
-- Daily breakdown
SELECT
CAST(o.OrderDate as DATE) as Date,
COUNT(o.Id) as OrderCount,
SUM(o.TotalAmount) as DailyRevenue,
AVG(o.TotalAmount) as AverageOrderValue
FROM Orders o
WHERE o.OrderDate BETWEEN @StartDate AND @EndDate
GROUP BY CAST(o.OrderDate as DATE)
ORDER BY Date;
-- Top products
SELECT
p.Id,
p.Name,
p.Category,
SUM(oi.Quantity) as TotalQuantity,
SUM(oi.TotalPrice) as TotalRevenue
FROM OrderItems oi
INNER JOIN Products p ON oi.ProductId = p.Id
INNER JOIN Orders o ON oi.OrderId = o.Id
WHERE o.OrderDate BETWEEN @StartDate AND @EndDate
GROUP BY p.Id, p.Name, p.Category
ORDER BY TotalRevenue DESC
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;
-- Customer segments
SELECT
CASE
WHEN COUNT(o.Id) = 1 THEN 'One-Time'
WHEN COUNT(o.Id) BETWEEN 2 AND 5 THEN 'Regular'
ELSE 'VIP'
END as Segment,
COUNT(DISTINCT u.Id) as CustomerCount,
SUM(o.TotalAmount) as SegmentRevenue
FROM Users u
INNER JOIN Orders o ON u.Id = o.UserId
WHERE o.OrderDate BETWEEN @StartDate AND @EndDate
GROUP BY
CASE
WHEN COUNT(o.Id) = 1 THEN 'One-Time'
WHEN COUNT(o.Id) BETWEEN 2 AND 5 THEN 'Regular'
ELSE 'VIP'
END;";
using var multi = await _connection.QueryMultipleAsync(sql, request);
var report = await multi.ReadFirstAsync<SalesReport>();
report.DailySales = await multi.ReadAsync<DailySales>();
report.TopProducts = await multi.ReadAsync<TopProduct>();
report.CustomerSegments = await multi.ReadAsync<CustomerSegment>();
report.StartDate = request.StartDate;
report.EndDate = request.EndDate;
return report;
}
public async Task<IEnumerable<MonthlySales>> GetMonthlySalesTrendAsync(int year)
{
const string sql = @"
SELECT
YEAR(OrderDate) as Year,
MONTH(OrderDate) as Month,
COUNT(Id) as OrderCount,
SUM(TotalAmount) as TotalRevenue,
AVG(TotalAmount) as AverageOrderValue
FROM Orders
WHERE YEAR(OrderDate) = @Year
GROUP BY YEAR(OrderDate), MONTH(OrderDate)
ORDER BY Year, Month";
return await _connection.QueryAsync<MonthlySales>(sql, new { Year = year });
}
public async Task<ProductPerformanceReport> GetProductPerformanceReportAsync(ProductReportRequest request)
{
const string sql = @"
-- Product performance summary
SELECT
p.Id,
p.Name,
p.Category,
p.Price,
p.StockQuantity,
COUNT(oi.Id) as TimesOrdered,
SUM(oi.Quantity) as TotalSold,
SUM(oi.TotalPrice) as TotalRevenue,
AVG(oi.Quantity) as AverageOrderQuantity
FROM Products p
LEFT JOIN OrderItems oi ON p.Id = oi.ProductId
LEFT JOIN Orders o ON oi.OrderId = o.Id
WHERE (@Category IS NULL OR p.Category = @Category)
AND (@StartDate IS NULL OR o.OrderDate >= @StartDate)
AND (@EndDate IS NULL OR o.OrderDate <= @EndDate)
GROUP BY p.Id, p.Name, p.Category, p.Price, p.StockQuantity
ORDER BY TotalRevenue DESC NULLS LAST;
-- Monthly trend for top products
WITH TopProducts AS (
SELECT TOP 5 p.Id
FROM Products p
LEFT JOIN OrderItems oi ON p.Id = oi.ProductId
LEFT JOIN Orders o ON oi.OrderId = o.Id
WHERE (@Category IS NULL OR p.Category = @Category)
AND (@StartDate IS NULL OR o.OrderDate >= @StartDate)
AND (@EndDate IS NULL OR o.OrderDate <= @EndDate)
GROUP BY p.Id
ORDER BY SUM(oi.TotalPrice) DESC NULLS LAST
)
SELECT
p.Id as ProductId,
p.Name as ProductName,
YEAR(o.OrderDate) as Year,
MONTH(o.OrderDate) as Month,
SUM(oi.Quantity) as MonthlyQuantity,
SUM(oi.TotalPrice) as MonthlyRevenue
FROM OrderItems oi
INNER JOIN Products p ON oi.ProductId = p.Id
INNER JOIN Orders o ON oi.OrderId = o.Id
WHERE p.Id IN (SELECT Id FROM TopProducts)
AND (@StartDate IS NULL OR o.OrderDate >= @StartDate)
AND (@EndDate IS NULL OR o.OrderDate <= @EndDate)
GROUP BY p.Id, p.Name, YEAR(o.OrderDate), MONTH(o.OrderDate)
ORDER BY p.Name, Year, Month;";
using var multi = await _connection.QueryMultipleAsync(sql, request);
var products = await multi.ReadAsync<ProductPerformance>();
var monthlyTrends = await multi.ReadAsync<ProductMonthlyTrend>();
return new ProductPerformanceReport
{
Products = products.ToList(),
MonthlyTrends = monthlyTrends.ToList()
};
}
public async Task<CustomerAnalytics> GetCustomerAnalyticsAsync()
{
const string sql = @"
-- Customer acquisition trends
SELECT
YEAR(CreatedAt) as Year,
MONTH(CreatedAt) as Month,
COUNT(Id) as NewCustomers
FROM Users
WHERE CreatedAt >= DATEADD(year, -1, GETUTCDATE())
GROUP BY YEAR(CreatedAt), MONTH(CreatedAt)
ORDER BY Year, Month;
-- Customer lifetime value
SELECT
u.Id,
u.Username,
u.Email,
COUNT(o.Id) as TotalOrders,
SUM(o.TotalAmount) as LifetimeValue,
MIN(o.OrderDate) as FirstOrderDate,
MAX(o.OrderDate) as LastOrderDate,
DATEDIFF(day, MIN(o.OrderDate), MAX(o.OrderDate)) as CustomerLifetimeDays
FROM Users u
INNER JOIN Orders o ON u.Id = o.UserId
GROUP BY u.Id, u.Username, u.Email
HAVING COUNT(o.Id) >= 1
ORDER BY LifetimeValue DESC;
-- Repeat customer rate
SELECT
COUNT(*) as TotalCustomers,
SUM(CASE WHEN OrderCount > 1 THEN 1 ELSE 0 END) as RepeatCustomers,
CAST(SUM(CASE WHEN OrderCount > 1 THEN 1 ELSE 0 END) AS FLOAT) / COUNT(*) as RepeatRate
FROM (
SELECT u.Id, COUNT(o.Id) as OrderCount
FROM Users u
LEFT JOIN Orders o ON u.Id = o.UserId
GROUP BY u.Id
) CustomerOrders;";
using var multi = await _connection.QueryMultipleAsync(sql);
var acquisitionTrends = await multi.ReadAsync<CustomerAcquisition>();
var customerLifetimeValues = await multi.ReadAsync<CustomerLifetimeValue>();
var repeatCustomerStats = await multi.ReadFirstAsync<RepeatCustomerStats>();
return new CustomerAnalytics
{
AcquisitionTrends = acquisitionTrends.ToList(),
CustomerLifetimeValues = customerLifetimeValues.ToList(),
RepeatCustomerStats = repeatCustomerStats
};
}
public async Task<IEnumerable<GeographicSales>> GetGeographicSalesAsync()
{
const string sql = @"
-- Extract state from shipping address (simplified)
SELECT
CASE
WHEN CHARINDEX(',', ShippingAddress) > 0 THEN
LTRIM(RTRIM(SUBSTRING(ShippingAddress,
CHARINDEX(',', ShippingAddress) + 1,
LEN(ShippingAddress))))
ELSE 'Unknown'
END as State,
COUNT(Id) as OrderCount,
SUM(TotalAmount) as TotalRevenue,
AVG(TotalAmount) as AverageOrderValue
FROM Orders
WHERE ShippingAddress IS NOT NULL
AND ShippingAddress != ''
GROUP BY
CASE
WHEN CHARINDEX(',', ShippingAddress) > 0 THEN
LTRIM(RTRIM(SUBSTRING(ShippingAddress,
CHARINDEX(',', ShippingAddress) + 1,
LEN(ShippingAddress))))
ELSE 'Unknown'
END
ORDER BY TotalRevenue DESC";
return await _connection.QueryAsync<GeographicSales>(sql);
}
}
// ... (DTO classes for reporting)
public class SalesReportRequest
{
public DateTime StartDate { get; set; }
public DateTime EndDate { get; set; }
}
public class ProductReportRequest
{
public string? Category { get; set; }
public DateTime? StartDate { get; set; }
public DateTime? EndDate { get; set; }
}
public class ProductPerformance
{
public int Id { get; set; }
public string Name { get; set; } = string.Empty;
public string Category { get; set; } = string.Empty;
public decimal Price { get; set; }
public int StockQuantity { get; set; }
public int TimesOrdered { get; set; }
public int TotalSold { get; set; }
public decimal TotalRevenue { get; set; }
public decimal AverageOrderQuantity { get; set; }
}
// ... (Additional DTO classes for various reports)
}
9. Integration with ASP.NET Core
Controllers
Controllers/UsersController.cs
csharp
using DapperDeepDive.Models;
using DapperDeepDive.Repositories;
using DapperDeepDive.Services;
using Microsoft.AspNetCore.Mvc;
namespace DapperDeepDive.Controllers
{
[ApiController]
[Route("api/[controller]")]
public class UsersController : ControllerBase
{
private readonly IUserRepository _userRepository;
private readonly IAdvancedUserRepository _advancedUserRepository;
private readonly IPerformanceMonitor _performanceMonitor;
private readonly ILogger<UsersController> _logger;
public UsersController(
IUserRepository userRepository,
IAdvancedUserRepository advancedUserRepository,
IPerformanceMonitor performanceMonitor,
ILogger<UsersController> logger)
{
_userRepository = userRepository;
_advancedUserRepository = advancedUserRepository;
_performanceMonitor = performanceMonitor;
_logger = logger;
}
[HttpGet]
public async Task<ActionResult<IEnumerable<User>>> GetUsers()
{
try
{
var users = await _performanceMonitor.MonitorQueryAsync(
() => _userRepository.GetAllAsync(),
"GetAllUsers");
return Ok(users);
}
catch (Exception ex)
{
_logger.LogError(ex, "Error retrieving users");
return StatusCode(500, "An error occurred while retrieving users");
}
}
[HttpGet("{id}")]
public async Task<ActionResult<User>> GetUser(int id)
{
try
{
var user = await _userRepository.GetByIdAsync(id);
if (user == null)
{
return NotFound();
}
return Ok(user);
}
catch (Exception ex)
{
_logger.LogError(ex, "Error retrieving user with ID {UserId}", id);
return StatusCode(500, "An error occurred while retrieving the user");
}
}
[HttpPost]
public async Task<ActionResult<User>> CreateUser(User user)
{
try
{
if (!ModelState.IsValid)
{
return BadRequest(ModelState);
}
var userId = await _userRepository.CreateAsync(user);
user.Id = userId;
return CreatedAtAction(nameof(GetUser), new { id = userId }, user);
}
catch (Exception ex)
{
_logger.LogError(ex, "Error creating user");
return StatusCode(500, "An error occurred while creating the user");
}
}
[HttpPut("{id}")]
public async Task<IActionResult> UpdateUser(int id, User user)
{
try
{
if (id != user.Id)
{
return BadRequest("User ID mismatch");
}
if (!ModelState.IsValid)
{
return BadRequest(ModelState);
}
var existingUser = await _userRepository.GetByIdAsync(id);
if (existingUser == null)
{
return NotFound();
}
var success = await _userRepository.UpdateAsync(user);
if (!success)
{
return StatusCode(500, "Failed to update user");
}
return NoContent();
}
catch (Exception ex)
{
_logger.LogError(ex, "Error updating user with ID {UserId}", id);
return StatusCode(500, "An error occurred while updating the user");
}
}
[HttpDelete("{id}")]
public async Task<IActionResult> DeleteUser(int id)
{
try
{
var existingUser = await _userRepository.GetByIdAsync(id);
if (existingUser == null)
{
return NotFound();
}
var success = await _userRepository.DeleteAsync(id);
if (!success)
{
return StatusCode(500, "Failed to delete user");
}
return NoContent();
}
catch (Exception ex)
{
_logger.LogError(ex, "Error deleting user with ID {UserId}", id);
return StatusCode(500, "An error occurred while deleting the user");
}
}
[HttpGet("{id}/orders")]
public async Task<ActionResult<UserWithOrders>> GetUserWithOrders(int id)
{
try
{
var (user, orders) = await _advancedUserRepository.GetUserWithOrdersAsync(id);
if (user == null)
{
return NotFound();
}
var result = new UserWithOrders
{
User = user,
Orders = orders.ToList()
};
return Ok(result);
}
catch (Exception ex)
{
_logger.LogError(ex, "Error retrieving user orders for user ID {UserId}", id);
return StatusCode(500, "An error occurred while retrieving user orders");
}
}
[HttpGet("search")]
public async Task<ActionResult<PaginatedResult<User>>> SearchUsers(
[FromQuery] UserSearchCriteria criteria)
{
try
{
var result = await _advancedUserRepository.GetUsersPaginatedAsync(
criteria.PageNumber, criteria.PageSize);
return Ok(result);
}
catch (Exception ex)
{
_logger.LogError(ex, "Error searching users");
return StatusCode(500, "An error occurred while searching users");
}
}
}
public class UserWithOrders
{
public User User { get; set; } = new User();
public List<Order> Orders { get; set; } = new List<Order>();
}
}
Controllers/ProductsController.cs
using DapperDeepDive.Models;
using DapperDeepDive.Repositories;
using DapperDeepDive.Services;
using Microsoft.AspNetCore.Mvc;
namespace DapperDeepDive.Controllers
{
[ApiController]
[Route("api/[controller]")]
public class ProductsController : ControllerBase
{
private readonly IProductRepository _productRepository;
private readonly ICacheService _cacheService;
private readonly IQueryBuilderService _queryBuilderService;
private readonly ILogger<ProductsController> _logger;
public ProductsController(
IProductRepository productRepository,
ICacheService cacheService,
IQueryBuilderService queryBuilderService,
ILogger<ProductsController> logger)
{
_productRepository = productRepository;
_cacheService = cacheService;
_queryBuilderService = queryBuilderService;
_logger = logger;
}
[HttpGet]
public async Task<ActionResult<IEnumerable<Product>>> GetProducts(
[FromQuery] ProductSearchCriteria criteria)
{
try
{
var cacheKey = $"products_search_{criteria.GetHashCode()}";
var products = await _cacheService.GetOrCreateAsync(cacheKey,
async () =>
{
if (HasSearchCriteria(criteria))
{
return await _queryBuilderService.SearchProductsAsync(criteria);
}
else
{
return await _productRepository.GetActiveProductsAsync();
}
},
TimeSpan.FromMinutes(5));
return Ok(products);
}
catch (Exception ex)
{
_logger.LogError(ex, "Error retrieving products");
return StatusCode(500, "An error occurred while retrieving products");
}
}
[HttpGet("{id}")]
public async Task<ActionResult<Product>> GetProduct(int id)
{
try
{
var product = await _productRepository.GetByIdAsync(id);
if (product == null)
{
return NotFound();
}
return Ok(product);
}
catch (Exception ex)
{
_logger.LogError(ex, "Error retrieving product with ID {ProductId}", id);
return StatusCode(500, "An error occurred while retrieving the product");
}
}
[HttpGet("category/{category}")]
public async Task<ActionResult<IEnumerable<Product>>> GetProductsByCategory(string category)
{
try
{
var products = await _productRepository.GetByCategoryAsync(category);
return Ok(products);
}
catch (Exception ex)
{
_logger.LogError(ex, "Error retrieving products for category {Category}", category);
return StatusCode(500, "An error occurred while retrieving products");
}
}
[HttpGet("search/{searchTerm}")]
public async Task<ActionResult<IEnumerable<Product>>> SearchProducts(string searchTerm)
{
try
{
var products = await _productRepository.SearchProductsAsync(searchTerm);
return Ok(products);
}
catch (Exception ex)
{
_logger.LogError(ex, "Error searching products with term {SearchTerm}", searchTerm);
return StatusCode(500, "An error occurred while searching products");
}
}
[HttpPost]
public async Task<ActionResult<Product>> CreateProduct(Product product)
{
try
{
if (!ModelState.IsValid)
{
return BadRequest(ModelState);
}
var productId = await _productRepository.CreateAsync(product);
product.Id = productId;
// Invalidate relevant caches
_cacheService.Remove($"products_category_{product.Category}");
_cacheService.Remove("products_search_*"); // Would need custom implementation for pattern removal
return CreatedAtAction(nameof(GetProduct), new { id = productId }, product);
}
catch (Exception ex)
{
_logger.LogError(ex, "Error creating product");
return StatusCode(500, "An error occurred while creating the product");
}
}
[HttpPut("{id}")]
public async Task<IActionResult> UpdateProduct(int id, Product product)
{
try
{
if (id != product.Id)
{
return BadRequest("Product ID mismatch");
}
if (!ModelState.IsValid)
{
return BadRequest(ModelState);
}
var existingProduct = await _productRepository.GetByIdAsync(id);
if (existingProduct == null)
{
return NotFound();
}
var success = await _productRepository.UpdateAsync(product);
if (!success)
{
return StatusCode(500, "Failed to update product");
}
// Invalidate caches
_cacheService.Remove($"product_{id}");
_cacheService.Remove($"products_category_{existingProduct.Category}");
if (existingProduct.Category != product.Category)
{
_cacheService.Remove($"products_category_{product.Category}");
}
return NoContent();
}
catch (Exception ex)
{
_logger.LogError(ex, "Error updating product with ID {ProductId}", id);
return StatusCode(500, "An error occurred while updating the product");
}
}
[HttpDelete("{id}")]
public async Task<IActionResult> DeleteProduct(int id)
{
try
{
var existingProduct = await _productRepository.GetByIdAsync(id);
if (existingProduct == null)
{
return NotFound();
}
var success = await _productRepository.DeleteAsync(id);
if (!success)
{
return StatusCode(500, "Failed to delete product");
}
// Invalidate caches
_cacheService.Remove($"product_{id}");
_cacheService.Remove($"products_category_{existingProduct.Category}");
return NoContent();
}
catch (Exception ex)
{
_logger.LogError(ex, "Error deleting product with ID {ProductId}", id);
return StatusCode(500, "An error occurred while deleting the product");
}
}
[HttpGet("inventory/value")]
public async Task<ActionResult<decimal>> GetTotalInventoryValue()
{
try
{
var value = await _productRepository.GetTotalInventoryValueAsync();
return Ok(value);
}
catch (Exception ex)
{
_logger.LogError(ex, "Error calculating inventory value");
return StatusCode(500, "An error occurred while calculating inventory value");
}
}
[HttpGet("categories/stats")]
public async Task<ActionResult<Dictionary<string, int>>> GetProductCountByCategory()
{
try
{
var stats = await _productRepository.GetProductCountByCategoryAsync();
return Ok(stats);
}
catch (Exception ex)
{
_logger.LogError(ex, "Error retrieving category statistics");
return StatusCode(500, "An error occurred while retrieving category statistics");
}
}
private bool HasSearchCriteria(ProductSearchCriteria criteria)
{
return !string.IsNullOrEmpty(criteria.Name) ||
!string.IsNullOrEmpty(criteria.Category) ||
criteria.MinPrice.HasValue ||
criteria.MaxPrice.HasValue ||
criteria.InStockOnly;
}
}
}
Dependency Injection Configuration
Program.cs (Extended)
using DapperDeepDive.Data;
using DapperDeepDive.Repositories;
using DapperDeepDive.Services;
using System.Data;
var builder = WebApplication.CreateBuilder(args);
// Add services to container
builder.Services.AddControllers();
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();
// Database connection
builder.Services.AddScoped<IDbConnection>(provider =>
{
var connectionString = builder.Configuration.GetConnectionString("DefaultConnection");
return new Microsoft.Data.SqlClient.SqlConnection(connectionString);
});
// Repositories
builder.Services.AddScoped<IUserRepository, UserRepository>();
builder.Services.AddScoped<IProductRepository, ProductRepository>();
builder.Services.AddScoped<IOrderRepository, OrderRepository>();
builder.Services.AddScoped<IAdvancedUserRepository, AdvancedUserRepository>();
builder.Services.AddScoped<IStoredProcedureRepository, StoredProcedureRepository>();
builder.Services.AddScoped<ITagRepository, TagRepository>();
// Services
builder.Services.AddScoped<IConnectionFactory, ConnectionFactory>();
builder.Services.AddScoped<IPerformanceMonitor, PerformanceMonitor>();
builder.Services.AddScoped<ICacheService, CacheService>();
builder.Services.AddScoped<IQueryBuilderService, QueryBuilderService>();
builder.Services.AddScoped<IBulkOperationService, BulkOperationService>();
builder.Services.AddScoped<IPerformanceComparisonService, PerformanceComparisonService>();
builder.Services.AddScoped<IECommerceService, ECommerceService>();
builder.Services.AddScoped<IReportingService, ReportingService>();
builder.Services.AddScoped<IDynamicParameterService, DynamicParameterService>();
// Caching
builder.Services.AddMemoryCache();
// Logging
builder.Services.AddLogging();
var app = builder.Build();
// Initialize database
using (var scope = app.Services.CreateScope())
{
var initializer = new DatabaseInitializer(
builder.Configuration.GetConnectionString("DefaultConnection"));
await initializer.InitializeDatabaseAsync();
}
// Configure pipeline
if (app.Environment.IsDevelopment())
{
app.UseDeveloperExceptionPage();
app.UseSwagger();
app.UseSwaggerUI();
}
app.UseRouting();
app.MapControllers();
app.Run();
10. Best Practices and Advanced Patterns
Best Practices Guide
BestPractices/DapperBestPractices.cs
using Dapper;
using System.Data;
namespace DapperDeepDive.BestPractices
{
public static class DapperBestPractices
{
public static class ConnectionManagement
{
public static IDbConnection CreateConnection(string connectionString)
{
return new Microsoft.Data.SqlClient.SqlConnection(connectionString);
}
public static async Task<IDbConnection> CreateOpenConnectionAsync(string connectionString)
{
var connection = CreateConnection(connectionString);
await connection.OpenAsync();
return connection;
}
public static void UseConnectionPooling(string connectionString)
{
// Connection pooling is handled by the connection string
// Ensure proper configuration:
// - Max Pool Size
// - Min Pool Size
// - Connection Timeout
var builder = new Microsoft.Data.SqlClient.SqlConnectionStringBuilder(connectionString)
{
MaxPoolSize = 100,
MinPoolSize = 0,
Pooling = true,
ConnectionTimeout = 30
};
}
}
public static class QueryExecution
{
public static void UseParameterizedQueries(IDbConnection connection, string sql, object parameters)
{
// GOOD: Parameterized query (prevents SQL injection)
var result = connection.Query<User>("SELECT * FROM Users WHERE Id = @Id", new { Id = userId });
// BAD: String concatenation (vulnerable to SQL injection)
// var result = connection.Query<User>($"SELECT * FROM Users WHERE Id = {userId}");
}
public static void SetAppropriateCommandTimeout(IDbCommand command, int timeoutSeconds = 30)
{
command.CommandTimeout = timeoutSeconds;
}
public static async Task<T> QueryWithTimeoutAsync<T>(
IDbConnection connection,
string sql,
object parameters,
int timeoutSeconds = 30)
{
using var command = new Microsoft.Data.SqlClient.SqlCommand(sql,
(Microsoft.Data.SqlClient.SqlConnection)connection);
command.Parameters.AddRange(GetParameters(parameters));
command.CommandTimeout = timeoutSeconds;
// Execute query...
// This is a simplified example - in practice, use Dapper's methods
return await connection.QueryFirstOrDefaultAsync<T>(sql, parameters);
}
}
public static class Performance
{
public static void UseAppropriateFetchSize(int fetchSize = 5000)
{
// For large datasets, consider using buffered: false
var results = connection.Query<User>("SELECT * FROM Users", buffered: false);
}
public static void OptimizeLargeResultSets()
{
// Use streaming for large result sets
var results = connection.Query<User>("SELECT * FROM Users", buffered: false);
// Process results incrementally
foreach (var user in results)
{
ProcessUser(user);
}
}
public static void UseStoredProceduresForComplexOperations()
{
// Complex business logic is better handled in stored procedures
var result = connection.Query<User>("GetUserWithOrders",
new { UserId = userId },
commandType: CommandType.StoredProcedure);
}
}
public static class Mapping
{
public static void UseExplicitColumnMapping()
{
// Use custom mapping for complex scenarios
SqlMapper.SetTypeMap(typeof(User), new CustomPropertyTypeMap(typeof(User),
(type, columnName) =>
{
if (columnName == "user_id") return type.GetProperty("Id");
if (columnName == "user_name") return type.GetProperty("Username");
// ... other mappings
return null;
}));
}
public static void HandleNullValues()
{
// Use nullable types and proper null checking
var user = connection.QuerySingleOrDefault<User>(
"SELECT * FROM Users WHERE Id = @Id", new { Id = userId });
if (user != null)
{
// Process user
}
}
}
public static class ErrorHandling
{
public static async Task<T> ExecuteWithRetryAsync<T>(
Func<Task<T>> operation,
int maxRetries = 3)
{
var retries = 0;
while (true)
{
try
{
return await operation();
}
catch (SqlException ex) when (IsTransientError(ex) && retries < maxRetries)
{
retries++;
await Task.Delay(TimeSpan.FromSeconds(Math.Pow(2, retries))); // Exponential backoff
}
}
}