Forem: FreeLearning365.com

ASP.NET Core Mastery with Latest Features : 40-Part Series from Zero to Cloud Expert | FreeLearning365

FreeLearning365.com — Wed, 22 Oct 2025 11:22:58 +0000

ASP.NET Core 2025 Mastery Series

Your complete journey from beginner to cloud-native expert with 40 hands-on tutorials, real-world projects, and professional training

40 Detailed Posts Beginner to Expert Cloud & AI Integration

Beginner

Intermediate

Advanced

Expert

Beginner Level - Foundation Building

Start your ASP.NET Core journey with these fundamental concepts and hands-on tutorials.

Post 1

ASP.NET Core 2025 Unleashed: Ultimate Beginner's Guide

Dive into ASP.NET Core 2025 from zero! This ultimate guide covers setup, tools, and your first app to spark your dev passion.

Post 2

Setting Up ASP.NET Core Like a Pro

Unlock insider secrets for ASP.NET Core setup with Visual Studio, CLI, and workflow boosts. Elevate your coding game today!

Post 3

Build Your First ASP.NET Core Web App

Transform into a web dev hero with this thrilling tutorial on creating your debut ASP.NET Core app using MVC and Razor.

Post 4

C# 12 Mastery: Essential Features

Master C# 12's game-changing features like records and pattern matching tailored for ASP.NET Core. Level up your code!

Post 5

Deep Dive into ASP.NET Core MVC

Conquer MVC in ASP.NET Core with in-depth coverage of controllers, views, routing, and DI for scalable apps.

Post 6

Entity Framework Core 9 Fundamentals

Unleash EF Core 9 for seamless database integration—models, migrations, and relationships made easy.

Post 7

SqlClient Power Play

Go raw with SqlClient for high-performance data access in ASP.NET Core—beyond ORMs for speed demons.

Post 8

Dapper Deep Dive

Speed up your apps with Dapper's ultra-fast querying and mapping in ASP.NET Core. The secret weapon for pros!

Intermediate Level - Core Development

Deepen your skills with API development, authentication, and essential patterns.

Post 9

Minimal APIs Revolution

Revolutionize API development with Minimal APIs in ASP.NET Core for rapid, efficient builds. Game-changer alert!

Post 10

RESTful API Mastery

Dominate REST APIs with best practices, versioning, and serialization in ASP.NET Core. Build irresistible services!

Post 11

GraphQL Explosion

Supercharge your APIs with GraphQL using HotChocolate—schemas, resolvers, and more for client-driven power.

Post 12

JWT Authentication Ninja

Lock down your apps with JWT auth, claims, and refresh strategies in ASP.NET Core. Cybersecurity mastery!

Post 13

ASP.NET Core Identity Unleashed

Build secure identity systems with registration, roles, and 2FA in ASP.NET Core Identity. Magic for pros!

Post 14

Advanced Authorization

Master policy-based auth and resource protection in ASP.NET Core for ultimate security. Defend like a pro!

Post 15

Swagger Mastery

Wow your team with interactive API docs using Swagger in ASP.NET Core. Effortless and viral-ready!

Post 16

Middleware Domination

Take full control of ASP.NET Core pipelines with custom middleware for logging and auth. Ultimate power!

Post 17

Configuration and Secrets Mastery

Safeguard configs with appsettings, secrets, and cloud vaults in ASP.NET Core. Flexible and secure!

Post 18

Design Patterns Revolution

Apply essential patterns like Repository and Singleton in ASP.NET Core for clean code. Elegance unlocked!

Advanced Level - Architecture & Performance

Master advanced patterns, performance optimization, and real-world application architecture.

Post 19

Architectural Patterns Epic

Architect resilient systems with CQRS, DDD, and patterns in ASP.NET Core. Enterprise-ready!

Post 20

Error Handling Triumph

Turn bugs into wins with middleware and logging in ASP.NET Core error handling. Stay robust!

Post 21

Razor Pages Breakthrough

Simplify UIs with Razor Pages—syntax, models, and helpers for engaging interfaces. Game-changer!

Post 22

Blazor Superpowers

Create SPA-like experiences with Blazor in ASP.NET Core—components and real-time features. Ignite your apps!

Post 23

Frontend Fusion

Blend JS frameworks with ASP.NET Core for powerful hybrids. Merge worlds seamlessly!

Post 26

Performance Hacks Unleashed

Turbocharge with async, profiling, and tweaks in ASP.NET Core. Speed demons rejoice!

Post 27

Caching Strategies Epic

Slash load times with caching in ASP.NET Core—Redis and more for massive traffic. Accelerate now!

Post 28

Security Fortification

Armor against threats with OWASP best practices in ASP.NET Core. Bulletproof your code!

Post 29

Testing Mastery

Catch bugs early with comprehensive testing in ASP.NET Core. Triumph in quality!

Post 30

Background Jobs Revolution

Automate with background jobs in ASP.NET Core—emails, reports, and more. Grind no more!

Post 31

Real-Time Magic: SignalR and gRPC

Bring apps alive with SignalR chats and gRPC efficiency in ASP.NET Core. Interactive wonders!

Expert Level - Cloud, AI & Production

Become a cloud-native expert with AI integration, microservices, and production deployment.

Post 24

AI Integration Boom

Infuse AI with ML.NET and OpenAI in ASP.NET Core for predictive smarts. Future-proof your apps!

Post 25

Cloud Domination

Scale globally with Azure and AWS in ASP.NET Core—storage, hosting, and more. Sky-high success!

Post 32

Deployment Domination

Launch seamlessly with Docker and K8s in ASP.NET Core. From local to live!

Post 33

CI/CD Pipelines Epic

Streamline workflows with CI/CD in ASP.NET Core. Efficiency unlocked!

Post 34

Monitoring and Logging Mastery

Stay proactive with monitoring and logs in ASP.NET Core. Keep apps thriving!

Post 35

Future Trends in ASP.NET Core 2025

Stay ahead with MAUI, AI, and trends shaping ASP.NET Core. Future-proof your skills!

Post 36

CQRS and MediatR Masterclass

Solve business complexity with CQRS and MediatR in ASP.NET Core. Scalable elegance!

Post 37

Repository and Unit of Work Patterns

Build testable data access with Repository patterns in ASP.NET Core. Pro-level abstraction!

Post 38

FluentValidation and AutoMapper

Ensure clean data with FluentValidation and AutoMapper in ASP.NET Core. Robust and efficient!

Post 39

Microservices Foundations

Architect microservices with gRPC and brokers in ASP.NET Core. Foundational mastery!

Post 40

Full-Stack ASP.NET Core Project

Build a complete full-stack project in ASP.NET Core—AI, cloud, and more. Real-world hero!

FreeLearning365

Master ASP.NET Core with our comprehensive 40-part series designed to take you from beginner to cloud-native expert. Join thousands of developers who have transformed their careers with our real-world projects and professional training.

Subscribe All Posts

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

FreeLearning365.com — Thu, 16 Oct 2025 10:33:13 +0000

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
Dapper Fundamentals and Setup

Basic Query Operations and Mapping

Advanced Query Techniques

Performance Optimization Strategies

Multi-Mapping and Complex Relationships

Stored Procedures and Dynamic Parameters

Bulk Operations and Performance

Real-World Enterprise Scenarios

Integration with ASP.NET Core

Best Practices and Advanced Patterns

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(provider =>
{
var connectionString = builder.Configuration.GetConnectionString("DefaultConnection");
return new SqlConnection(connectionString);
});

builder.Services.AddScoped();
builder.Services.AddScoped();
builder.Services.AddScoped();

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

}

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 GetByIdAsync(int id);
Task> GetAllAsync();
Task GetByUsernameAsync(string username);
Task GetByEmailAsync(string email);
Task CreateAsync(User user);
Task UpdateAsync(User user);
Task DeleteAsync(int id);
Task ExistsAsync(int id);
Task 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 GetByIdAsync(int id);
Task> GetAllAsync();
Task> GetByCategoryAsync(string category);
Task> GetActiveProductsAsync();
Task> SearchProductsAsync(string searchTerm);
Task GetTotalInventoryValueAsync();
Task> GetProductCountByCategoryAsync();
Task CreateAsync(Product product);
Task UpdateAsync(Product product);
Task UpdateStockAsync(int productId, int newStockQuantity);
Task 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;
    }
}

}

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 Orders)> GetUserWithOrdersAsync(int userId);
Task GetUserStatisticsAsync(int userId);
Task> GetUsersPaginatedAsync(int pageNumber, int pageSize);
Task> GetUsersWithOrdersAsync();
Task 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> SearchProductsAsync(ProductSearchCriteria criteria);
Task> 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;
}

}

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 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 MonitorQueryAsync(Func> query, string operationName);
Task MonitorActionAsync(Func 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 GetOrCreateAsync(string key, Func> 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;
    }
}

}

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 GetOrderWithDetailsAsync(int orderId);
Task> GetUserOrdersWithDetailsAsync(int userId);
Task GetOrderSummaryAsync(int orderId);
Task CreateOrderWithItemsAsync(Order order, List items);
Task 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 });
    }
}

}

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> GetTopSellingProductsAsync(int count);
Task GetUserLifetimeValueAsync(int userId);
Task> SearchUsersAdvancedAsync(UserSearchParameters parameters);
Task GenerateSalesReportAsync(DateTime startDate, DateTime endDate);
Task 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 Users)> GetUsersWithOutputParameterAsync(
int pageNumber, int pageSize);
Task CalculateOrderTotalWithOutputAsync(int orderId);
Task 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;
}

}

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 BulkInsertProductsAsync(IEnumerable products);
Task BulkUpdateProductPricesAsync(IEnumerable updates);
Task BulkMergeProductsAsync(IEnumerable products);
Task BulkInsertUsersAsync(IEnumerable users);
Task 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 CompareQueryMethodsAsync();
Task CompareBulkOperationsAsync(int recordCount);
Task 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; }
}

}

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 PlaceOrderAsync(OrderRequest request);
Task CheckInventoryAsync(int productId, int quantity);
Task CalculateOrderTotalAsync(OrderCalculationRequest request);
Task> GetRecommendedProductsAsync(int userId);
Task 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 GetSalesReportAsync(SalesReportRequest request);
Task> GetMonthlySalesTrendAsync(int year);
Task GetProductPerformanceReportAsync(ProductReportRequest request);
Task GetCustomerAnalyticsAsync();
Task> 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)

}

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 _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 _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(provider =>
{
var connectionString = builder.Configuration.GetConnectionString("DefaultConnection");
return new Microsoft.Data.SqlClient.SqlConnection(connectionString);
});

// Repositories
builder.Services.AddScoped();
builder.Services.AddScoped();
builder.Services.AddScoped();
builder.Services.AddScoped();
builder.Services.AddScoped();
builder.Services.AddScoped();

// Services
builder.Services.AddScoped();
builder.Services.AddScoped();
builder.Services.AddScoped();
builder.Services.AddScoped();
builder.Services.AddScoped();
builder.Services.AddScoped();
builder.Services.AddScoped();
builder.Services.AddScoped();
builder.Services.AddScoped();

// 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();

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

ERP Success in Bangladesh: Step-by-Step Realistic Roadmap to Avoid Project Failure - FreeLearning365.com

FreeLearning365.com — Tue, 14 Oct 2025 04:54:28 +0000

My Main Article: https://www.freelearning365.com/2025/10/erp-success-in-bangladesh-step-by-step.html

🧭 How to Make ERP Projects Successful in Bangladesh — The Complete Realistic Roadmap

💡 From Failure to Future: Practical, Proven, and Bangladeshi-Context ERP Implementation Strategy

**
🌍 1️⃣ Define Clear Business Goals Before Coding**
🧩 Realistic Resolution:

Conduct Business Process Study Workshops (BPSW) for each department.
Document existing manual workflows → identify inefficiencies → map “To-Be” processes.
Assign process owners (not just IT) to approve each step.
Use Lucidchart / Visio / Draw.io to create visual workflows before any development.
🧠 Why it works: A visual process eliminates ambiguity. When everyone understands the flow, developers can build logic accurately.

📋 2️⃣ Conduct a Feasibility & ROI Study
🧩 Realistic Resolution:

Calculate measurable KPIs: time saved, error reduction, faster reporting.
Prepare a 3-year ROI Projection Sheet (excel-based).
Share with management to set realistic expectations.
Revisit ROI quarterly to show progress.
🧠 Why it works: Realistic expectations = long-term confidence = reduced pressure on project team.

**
🧍‍♀️ 3️⃣ Drive Employee Adoption from Day One**
🧩 Realistic Resolution:

Involve employees in early testing and feedback loops.
Conduct short “Show & Tell” demo sessions 🧑‍💻 every Friday.
Launch ERP Awareness Campaigns inside the company (email posters, short videos).
Reward top ERP users (“ERP Champion of the Month” 🏅).
🧠 Why it works: ERP success is 70% psychology and 30% technology.

⚒️ 4️⃣ Balance Customization with Standardization
🧩 Realistic Resolution:

Keep 80% standard and only 20% customized.
Maintain a “Customization Register” to track changes.
Avoid unnecessary stored procedure overrides.
Prefer configuration over hard-coded customization.
🧠 Why it works: When your ERP upgrades later, minimal breakage and faster support.

**
🧹 5️⃣ Data Cleaning & Governance Policy**
🧩 Realistic Resolution:

Assign a “Data Steward” per department.
Run Excel-based data validation before importing.
Use scripts to detect duplicates, missing codes, invalid IDs.
Define data entry SOPs: Naming conventions, date formats (YYYY-MM-DD).
🧠 Why it works: Clean data ensures dashboard accuracy and user trust.

💰 6️⃣ Smart Vendor Selection Framework
🧩 Realistic Resolution:

Prepare an ERP Vendor Scorecard based on:
Technical depth (30%)
Post-Go-Live Support (25%)
Domain Experience (25%)
Pricing (20%)
Check existing clients and support feedback.
Prefer vendors offering SLA (Service Level Agreement) with defined response time.
🧠 Why it works: You’re selecting a partner, not just a software vendor.

🧑‍🏫 7️⃣ Continuous Training Culture
🧩 Realistic Resolution:

Conduct training in phases: Pre-Go-Live, Go-Live Week, Post-Go-Live.
Use video-based micro learning (2–3 min clips).
Create an internal “ERP Knowledge Base” using Google Sites / Notion.
Conduct quarterly refresh sessions.
🧠 Why it works: Sustained training ensures ERP remains alive and relevant.

⚡ 8️⃣ Strengthen Infrastructure & Backup
🧩 Realistic Resolution:

Use Cloud hosting or hybrid (local+cloud) with daily automated backups.
Set up power redundancy (UPS, inverter, generator).
Monitor system health via Power BI dashboards (uptime %, latency, user sessions).
Use SSL & firewall for all API endpoints.
🧠 Why it works: Stable system → user confidence → adoption.

🤝 9️⃣ Foster Inter-Departmental Collaboration
🧩 Realistic Resolution:

Form an ERP Steering Committee (Finance, HR, Production, IT).
Monthly cross-department meetings to discuss issues.
Build dashboards that show how one department’s data impacts another.
🧠 Why it works: Collaboration = Shared Ownership = Long-term success.

🕵️ 🔟 Capture Hidden Requirements Early
🧩 Realistic Resolution:

Conduct UAT simulation workshops before final build.
Use mock data and screen prototypes for review.
Apply “Requirement Traceability Matrix (RTM)” — maps each requirement to its test case.
🧠 Why it works: You catch surprises before they become disasters.

🏦 11️⃣ Budget for Quality & Scalability
🧩 Realistic Resolution:

Keep a 10–15% contingency budget for change requests.
Invest in cloud scalability and security audits.
Pay for SLA-based annual support — it’s cheaper than downtime.
🧠 Why it works: Predictable cost → zero panic during crisis.

**
🕰️ 12️⃣ Plan Practical Timelines**
🧩 Realistic Resolution:

Use a Gantt Chart (Excel or MS Project).
Include time for testing, training, and data migration.
Define clear milestones with acceptance criteria.
Avoid festival seasons for go-live (production load & staff leaves).
🧠 Why it works: A well-paced project = fewer mistakes, better adoption.

**
🧩 13️⃣ Strong Project Management Framework**
🧩 Realistic Resolution:

Assign a dedicated Project Manager (PM).
Weekly progress reports with traffic-light status (Green/Amber/Red).
Use collaboration tools like Trello, Asana, or Jira.
Keep management engaged with short, visual summaries.
🧠 Why it works: Visibility ensures accountability.

**
👓 14️⃣ Empower Skilled Business Analysts**
🧩 Realistic Resolution:

Hire BAs with domain + technical blend (Finance, Supply Chain, HR).
BA should write clear BRD (Business Requirement Document) and FRD (Functional Requirement Document).
Conduct requirement review sessions before sign-off.
🧠 Why it works: A skilled BA translates chaos into clarity.

🔄 15️⃣ Master Data Migration Process
🧩 Realistic Resolution:

Run 3 mock migrations: dry run, pilot, final.
Validate totals with finance team after each.
Keep Excel-based reconciliation sheets.
Backup all previous data securely before switching systems.
🧠 Why it works: Reliable migration = confidence on Day 1 of Go-Live.

🎯 Final Words: From ERP Chaos to ERP Confidence
ERP success doesn’t depend on big budgets —
it depends on discipline, ownership, and continuous improvement.

Bangladesh has the talent — what’s needed is structured execution.
Once companies treat ERP as a strategic growth partner (not an IT tool),
the success rate will skyrocket 🚀.

ERPSuccess #ERPImplementation #BangladeshBusiness #DigitalBangladesh #TechTransformation #ERPStrategy #ProcessAutomation #ERPTraining #DataMigration #BusinessProcessReengineering #EnterpriseSoftware #BangladeshIT #FreeLearning365 #BusinessAutomation #TechLeadership #SAP #Oracle #MicrosoftDynamics #SAP #OracleERP #OracleFusion #MicrosoftDynamics365 #DynamicsERP #InforERP #WorkdayERP #EpicorERP #NetSuite #Odoo #Tally #ZohoBooks #SAPBusinessOne #SAPERPHANA #S4HANA #BangladeshERP #LocalERP #ERPforSME #ERPforBangladesh #PridesysERP #SouthtechERP #SmartERP #BEPZASolutions #TechnoVistaERP #DataSoftERP #BanglaSoft #DigitalERP #BangladeshIT #ERPConsultingBD

Repository Pattern in .NET and Java: Clean Data Access

FreeLearning365.com — Sun, 14 Sep 2025 04:44:34 +0000

Introduction
In the world of software development, managing data access effectively is crucial for building scalable, maintainable applications. The Repository Pattern emerges as a powerful architectural solution to separate database logic from business logic, promoting clean, testable, and flexible code. This pattern acts as an abstraction layer between your application's domain model and the underlying data storage, whether it's a relational database like SQL Server or PostgreSQL, a NoSQL store, or even external APIs.

At its core, the Repository Pattern encapsulates all data access operations—such as creating, reading, updating, and deleting (CRUD)—into dedicated classes or interfaces. This decoupling ensures that changes in the data layer (e.g., switching from Entity Framework in .NET to Dapper, or from JPA in Java to MyBatis) don't ripple through the entire application. It's particularly valuable in enterprise environments where business requirements evolve rapidly, and data sources may vary across microservices or hybrid systems.

This blog post dives deep into the Repository Pattern, exploring its implementation in both .NET and Java ecosystems. We'll walk through step-by-step guides with real-life examples centered around an e-commerce inventory management system—a common business scenario where products are stored, retrieved, and updated frequently. We'll cover pros and cons, real-life usage, and business implications, drawing from practical experiences in building robust applications.

Whether you're a .NET developer using ASP.NET Core or a Java enthusiast working with Spring Boot, this guide will equip you with actionable insights to implement clean data access in your projects.

What is the Repository Pattern?
The Repository Pattern, popularized by Domain-Driven Design (DDD) principles from Eric Evans' book Domain-Driven Design, treats data access as a collection-like interface. Instead of scattering SQL queries or ORM calls throughout your business logic, you centralize them in repositories. These repositories provide methods that mimic in-memory collection operations (e.g., GetAll(), FindById(), Add(), Update()), hiding the complexities of persistence.

Key components include:

Interface: Defines the contract for data operations, ensuring abstraction.
Implementation: Handles the actual data access, often using ORMs like Entity Framework (EF) in .NET or Hibernate/JPA in Java.
Dependency Injection (DI): Wires the interface to its implementation, allowing easy swapping for testing or migration.
In real-life terms, imagine an online retail business where inventory levels must be checked in real-time. Without the pattern, your order processing service might directly query the database, mixing concerns. With repositories, the service calls IProductRepository.GetById(id), keeping logic clean and focused on business rules like stock validation.

This pattern aligns with Clean Architecture by keeping the domain layer "persistence-agnostic," meaning your core business entities remain unaware of how data is stored.

Why Use the Repository Pattern? Benefits and Real-Life Scenarios
The Repository Pattern shines in scenarios demanding maintainability and scalability. Here's why it's a go-to choice:

Separation of Concerns
By isolating data access, business logic stays pure. In a real-life e-commerce app, your OrderService can focus on calculating discounts and taxes without worrying about SQL joins or connection strings.

Improved Testability
Repositories make unit testing straightforward. You can mock the interface for in-memory fakes, testing business rules without a database. For instance, in a financial services firm, testers can simulate high-volume transactions without hitting production databases.

Flexibility and Decoupling
Switch data sources effortlessly. A healthcare business migrating from on-premises SQL Server to cloud-based Azure Cosmos DB in .NET, or from MySQL to MongoDB in Java, only updates the repository implementation.

Reusability
Centralized logic reduces duplication. In a multi-tenant SaaS platform, multiple modules (e.g., reporting and analytics) can reuse the same UserRepository.

Real-Life Usage
In business contexts, this pattern is ubiquitous. Consider a logistics company like UPS: Their route optimization service uses repositories to fetch shipment data from various sources (databases, APIs), ensuring seamless integration. In banking apps, repositories handle secure data access for transaction histories, complying with regulations like GDPR by abstracting sensitive queries.

During the COVID-19 pandemic, many e-commerce platforms scaled rapidly using this pattern to handle surging inventory queries, allowing quick pivots to new data providers without downtime.

Implementing the Repository Pattern in .NET
.NET, with its robust ecosystem including ASP.NET Core and Entity Framework Core (EF Core), makes Repository implementation seamless. We'll use a generic repository for reusability, focusing on an e-commerce example with products.

Step 1: Define the Domain Model
Start with entities in your Domain layer. For our inventory system:

csharp
// Domain/Entities/Product.cs
public class Product
{
public int Id { get; set; }
public string Name { get; set; }
public decimal Price { get; set; }
public int StockQuantity { get; set; }
public DateTime LastUpdated { get; set; }
}
This represents a simple product entity, central to business operations like stock checks.

Step 2: Create the Repository Interface
In the Application or Domain layer, define a generic interface for CRUD operations:

csharp
// Application/Interfaces/IRepository.cs
using System.Linq.Expressions;

public interface IRepository where T : class
{
Task> GetAllAsync();
Task GetByIdAsync(int id);
Task> FindAsync(Expression> predicate);
Task AddAsync(T entity);
Task UpdateAsync(T entity);
Task DeleteAsync(int id);
void Attach(T entity); // For EF tracking
}
For specificity, create IProductRepository : IRepository with custom methods like GetLowStockProductsAsync(int threshold).

Step 3: Implement the Repository Using EF Core
In the Infrastructure layer, implement using EF Core. Assume a DbContext for the database.

csharp
// Infrastructure/Repositories/Repository.cs
using Microsoft.EntityFrameworkCore;
using Application.Interfaces;

public class Repository : IRepository where T : class
{
protected readonly DbContext _context;
private readonly DbSet _dbSet;

public Repository(DbContext context)
{
    _context = context;
    _dbSet = context.Set<T>();
}

public async Task<IEnumerable<T>> GetAllAsync()
{
    return await _dbSet.ToListAsync();
}

public async Task<T> GetByIdAsync(int id)
{
    return await _dbSet.FindAsync(id);
}

public async Task<IEnumerable<T>> FindAsync(Expression<Func<T, bool>> predicate)
{
    return await _dbSet.Where(predicate).ToListAsync();
}

public async Task AddAsync(T entity)
{
    await _dbSet.AddAsync(entity);
    await _context.SaveChangesAsync(); // Or handle in Unit of Work
}

public async Task UpdateAsync(T entity)
{
    _dbSet.Attach(entity);
    _context.Entry(entity).State = EntityState.Modified;
    await _context.SaveChangesAsync();
}

public async Task DeleteAsync(int id)
{
    var entity = await GetByIdAsync(id);
    if (entity != null)
    {
        _dbSet.Remove(entity);
        await _context.SaveChangesAsync();
    }
}

public void Attach(T entity)
{
    _dbSet.Attach(entity);
}

}
For ProductRepository:

csharp
// Infrastructure/Repositories/ProductRepository.cs
public class ProductRepository : Repository, IProductRepository
{
public ProductRepository(DbContext context) : base(context) { }

public async Task<IEnumerable<Product>> GetLowStockProductsAsync(int threshold)
{
    return await _dbSet.Where(p => p.StockQuantity < threshold).ToListAsync();
}

}
Step 4: Configure Dependency Injection
In Program.cs (ASP.NET Core):

csharp
builder.Services.AddDbContext(options =>
options.UseSqlServer(connectionString));

builder.Services.AddScoped(typeof(IRepository<>), typeof(Repository<>));
builder.Services.AddScoped();
Step 5: Use in Business Logic
In a service class:

csharp
// Application/Services/ProductService.cs
public class ProductService
{
private readonly IProductRepository _productRepository;

public ProductService(IProductRepository productRepository)
{
    _productRepository = productRepository;
}

public async Task<Product> GetProductAsync(int id)
{
    return await _productRepository.GetByIdAsync(id);
}

public async Task UpdateStockAsync(int productId, int newQuantity)
{
    var product = await _productRepository.GetByIdAsync(productId);
    if (product != null)
    {
        product.StockQuantity = newQuantity;
        product.LastUpdated = DateTime.UtcNow;
        await _productRepository.UpdateAsync(product);
    }
}

// Real-life: Alert for low stock
public async Task CheckLowStockAsync(int threshold)
{
    var lowStock = await _productRepository.GetLowStockProductsAsync(threshold);
    // Business logic: Send email or notify warehouse
    foreach (var p in lowStock)
    {
        // Simulate notification
        Console.WriteLine($"Low stock for {p.Name}");
    }
}

}
In a controller:

csharp
[ApiController]
[Route("api/[controller]")]
public class ProductsController : ControllerBase
{
private readonly ProductService _service;

public ProductsController(ProductService service)
{
    _service = service;
}

[HttpGet("{id}")]
public async Task<IActionResult> Get(int id)
{
    var product = await _service.GetProductAsync(id);
    return Ok(product);
}

}
This setup allows injecting mocks in tests, e.g., using Moq for unit tests simulating inventory depletion during peak sales.

Implementing the Repository Pattern in Java
Java's Spring framework pairs excellently with the Repository Pattern via Spring Data JPA. We'll mirror the .NET example for consistency.

Step 1: Define the Domain Model
Use JPA annotations for entities:

java
// domain/entities/Product.java
import jakarta.persistence.*;

@Entity
@Table(name = "Products")
public class Product {
@id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;

private String name;
private BigDecimal price;
private Integer stockQuantity;
private LocalDateTime lastUpdated = LocalDateTime.now();

// Constructors, getters, setters
public Product() {}

public Product(String name, BigDecimal price, Integer stockQuantity) {
    this.name = name;
    this.price = price;
    this.stockQuantity = stockQuantity;
}

// Getters and setters...
public Integer getId() { return id; }
public void setId(Integer id) { this.id = id; }
public String getName() { return name; }
public void setName(String name) { this.name = name; }
public BigDecimal getPrice() { return price; }
public void setPrice(BigDecimal price) { this.price = price; }
public Integer getStockQuantity() { return stockQuantity; }
public void setStockQuantity(Integer stockQuantity) { this.stockQuantity = stockQuantity; }
public LocalDateTime getLastUpdated() { return lastUpdated; }
public void setLastUpdated(LocalDateTime lastUpdated) { this.lastUpdated = lastUpdated; }

}
Step 2: Create the Repository Interface
Leverage Spring Data's JpaRepository for built-in CRUD:

java
// application/interfaces/ProductRepository.java
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import java.util.List;

@Repository
public interface ProductRepository extends JpaRepository {
List findByStockQuantityLessThan(Integer threshold);

@Query("SELECT p FROM Product p WHERE p.name LIKE %?1%")
List<Product> findByNameContaining(String name);

}
For a generic base, extend PagingAndSortingRepository if needed.

Step 3: Implement Custom Logic (If Beyond Spring Data)
Spring Data handles most CRUD, but for custom queries:

java
// infrastructure/repositories/CustomProductRepositoryImpl.java
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import jakarta.persistence.EntityManager;
import jakarta.persistence.PersistenceContext;
import java.util.List;

@Repository
public class CustomProductRepositoryImpl {
@PersistenceContext
private EntityManager entityManager;

public List<Product> getLowStockProducts(int threshold) {
    // Custom JPQL or native query
    return entityManager.createQuery(
        "SELECT p FROM Product p WHERE p.stockQuantity < :threshold", Product.class)
        .setParameter("threshold", threshold)
        .getResultList();
}

}
Extend the interface to use this.

Step 4: Configure Dependency Injection
In Spring Boot's application.properties:

text
spring.datasource.url=jdbc:postgresql://localhost:5432/ecommerce
spring.jpa.hibernate.ddl-auto=update
In a configuration class or auto-wired via @EnableJpaRepositories.

Step 5: Use in Business Logic
In a service:

java
// application/services/ProductService.java
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.time.LocalDateTime;
import java.util.List;

@Service
@Transactional
public class ProductService {
@Autowired
private ProductRepository productRepository;

public Product getProductById(Integer id) {
    return productRepository.findById(id).orElse(null);
}

public void updateStock(Integer productId, Integer newQuantity) {
    Product product = productRepository.findById(productId).orElse(null);
    if (product != null) {
        product.setStockQuantity(newQuantity);
        product.setLastUpdated(LocalDateTime.now());
        productRepository.save(product);
    }
}

// Real-life: Check low stock
public List<Product> checkLowStock(Integer threshold) {
    List<Product> lowStock = productRepository.findByStockQuantityLessThan(threshold);
    // Business logic: Integrate with email service or inventory alert
    lowStock.forEach(p -> System.out.println("Low stock for " + p.getName()));
    return lowStock;
}

}
In a REST controller:

java
// web/controllers/ProductController.java
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;

@RestController
@RequestMapping("/api/products")
public class ProductController {
@Autowired
private ProductService productService;

@GetMapping("/{id}")
public ResponseEntity<Product> get(@PathVariable Integer id) {
    Product product = productService.getProductById(id);
    return product != null ? ResponseEntity.ok(product) : ResponseEntity.notFound().build();
}

}
This enables easy testing with @MockBean in Spring Boot tests, simulating scenarios like supply chain disruptions.

Pros and Cons of the Repository Pattern
Pros
Maintainability: Centralized data logic simplifies debugging and refactoring. In a growing business like an online marketplace, updating query optimizations affects one place.
Testability: Mocking interfaces speeds up CI/CD pipelines, crucial for agile teams in finance where compliance testing is rigorous.
Scalability: Supports microservices; each service can have tailored repositories. Real-life: Netflix uses similar abstractions for personalized recommendations.
Consistency: Enforces uniform data access, reducing errors in distributed teams.
Integration with ORMs: Enhances EF Core or JPA, adding caching or logging via decorators.
Cons
Overhead in Small Apps: Adds boilerplate for simple CRUD apps, like a personal blog, where direct ORM use suffices.
Leaky Abstractions: If not careful, domain-specific queries can expose data details, as noted in some critiques.
Learning Curve: Teams new to DDD may struggle initially, delaying projects.
Performance Tuning: Custom implementations might require manual optimization, unlike raw SQL.
Generic vs. Specific: Overly generic repositories can lead to bloated methods; balance with specific ones for complex queries.
In business, pros outweigh cons for mid-to-large projects, but evaluate based on scale—start simple and refactor.

Usage in Real Life and Business Applications
In real life, the Repository Pattern is a staple in enterprise software. For businesses, it drives efficiency:

E-Commerce (e.g., Amazon-like Systems): Repositories manage product catalogs, enabling quick switches between databases during Black Friday traffic spikes. Business value: Reduced downtime, faster feature rollouts.
Financial Services (e.g., Banking Apps): Secure transaction repositories abstract sensitive data access, aiding audits and compliance. A bank might use it to integrate legacy mainframes with modern cloud services, minimizing risk.
Healthcare (e.g., Patient Management): Repositories handle HIPAA-compliant data retrieval, allowing seamless migration to telehealth platforms during pandemics.
Logistics and Supply Chain: In companies like FedEx, repositories aggregate data from IoT sensors and ERPs, optimizing routes. Business impact: Cost savings through reusable logic across global operations.
In a real project I consulted on, a retail chain used .NET repositories to unify data from multiple vendors, cutting maintenance costs by 30% and enabling real-time inventory syncing.

For Java, Spring-based enterprise apps in telecom use it for billing systems, where data volume is massive, ensuring high availability.

Pair it with Unit of Work for transaction management in complex ops, like order processing involving multiple repositories.

Conclusion

The Repository Pattern is indispensable for clean data access in .NET and Java, fostering maintainable architectures that adapt to business needs. By following the step-by-step implementations above, you can apply it to real-life scenarios like inventory management, reaping benefits in testability and flexibility while mitigating cons through thoughtful design.