Dapper Deep Dive Part 8: Ultra-Fast Querying & Mapping Secrets for ASP.NET Core Pros - FreeLearning365.com

 

Dapper Deep Dive: Speed up your apps with Dapper's ultra-fast querying and mapping in ASP.NET Core

Module Sequence: Part 8 - Mastering Dapper for High-Performance Data Access

Table of Contents

1. Dapper Fundamentals and Setup

2. Basic Query Operations and Mapping

3. Advanced Query Techniques

4. Performance Optimization Strategies

5. Multi-Mapping and Complex Relationships

6. Stored Procedures and Dynamic Parameters

7. Bulk Operations and Performance

8. Real-World Enterprise Scenarios

9. Integration with ASP.NET Core

10. 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

csharp

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

json

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=.;Database=DapperDemo;Trusted_Connection=true;TrustServerCertificate=true;"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  }
}

Database Setup and Initialization

DatabaseInitializer.cs

csharp

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', 'john@example.com', 'John', 'Doe', '1985-05-15'),
                ('jane_smith', 'jane@example.com', 'Jane', 'Smith', '1990-08-22'),
                ('bob_wilson', 'bob@example.com', 'Bob', 'Wilson', '1988-12-10'),
                ('alice_brown', 'alice@example.com', '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

csharp

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

csharp

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

csharp

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

csharp

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

csharp

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

csharp

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

csharp

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

csharp

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

csharp

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

csharp

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

csharp

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

csharp

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

csharp

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

csharp

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

csharp

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

csharp

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

csharp

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

csharp

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)

csharp

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

csharp

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
                    }
                }
            }

📘ASP.NET Core Mastery with Latest Features : 40-Part Series 🎯 Visit Free Learning Zone