Mocking Database Calls — Complete Guide

Introduction

Mocking Database Calls — Complete Guide is essential for .NET architects building ShopNest.DataAccess — Enterprise High-Performance Data Platform — Toolliyo's 100-article ADO.NET Core master path covering SqlConnection, stored procedures, transactions, connection pooling, performance tuning, ASP.NET Core integration, and senior interview preparation. Every article includes minimum 2 detailed enterprise real-world examples (banking transfers, ERP reporting, insurance batch, legacy modernization) in different business domains.

In Indian delivery projects (TCS, Infosys, Wipro), interviewers expect mocking database calls with real ICICI-style banking, TCS ERP reporting, insurance batch processing, or government legacy modernization examples — not toy animal demos. This article delivers two mandatory enterprise examples on Orders.

After this article you will

• Explain Mocking Database Calls in plain English and in SQL Server and high-performance data access terms
• Implement mocking database calls in ShopNest.DataAccess — Enterprise High-Performance Data Platform (Orders)
• Compare the wrong approach vs the production-ready enterprise approach
• Answer fresher, mid-level, and senior ADO.NET and SQL Server interview questions confidently
• Connect this lesson to Article 74 and the 100-article ADO.NET Core roadmap

Prerequisites

• Software: .NET 8 SDK, VS 2022 or VS Code, SQL Server Express / LocalDB
• Knowledge: C# basics
• Previous: Article 72 — Integration Testing — Complete Guide
• Time: 28 min reading + 30–45 min hands-on

Concept deep-dive

Level 1 — Analogy

Mocking Database Calls on ShopNest.DataAccess adds high-performance SQL Server data access for enterprise modules.

Level 2 — Technical

Mocking Database Calls integrates with the LINQ query layer: write queries against IEnumerable or IQueryable, understand deferred execution, project to DTOs for ShopNest.DataAccess reports. On ShopNest.DataAccess this powers Orders without coupling UI to database internals.

Level 3 — Architecture

[Browser] → [HTTPS/Kestrel] → [Middleware Pipeline]
  → [Routing] → [Controller Action] → [Service Layer]
  → [EF Core / Identity] → [Razor View Engine] → [HTML Response]

Project structure

ShopNest.DataAccess/
├── ShopNest.DataAccess/
├── src/
│   ├── ShopNest.DataAccess.Api/       ← ASP.NET Core Web API
│   ├── ShopNest.DataAccess.Core/      ← Repository interfaces
│   ├── ShopNest.DataAccess.AdoNet/    ← SqlConnection, SPs, transactions
│   ├── ShopNest.DataAccess.Reports/   ← Streaming readers, GL reports
│   └── ShopNest.DataAccess.Tests/     ← Integration tests (Testcontainers SQL)
├── sql/
│   ├── migrations/
│   └── stored-procedures/
└── docker-compose.yml                 ← SQL Server 2022 + Redis

Step-by-Step Implementation — ShopNest (Orders)

Follow the prompt template: create project → core classes → interfaces → pattern implementation → client code → run → enterprise refactor.

Step 1 — The wrong way

// ❌ BAD — fat controller, no ViewModel, sync DB call
public IActionResult Index()
{
    return _context.Products.Find(id); // sync, exposes entity, no auth
}

Step 2 — The right way

// ✅ CORRECT — Mocking Database Calls on ShopNest (Orders)
var results = await _context.Products
    .Where(p => p.IsPublished && p.CategoryId == categoryId)
    .OrderBy(p => p.Name)
    .Select(p => new ProductReportDto { Id = p.Id, Name = p.Name, Revenue = p.Orders.Sum(o => o.Total) })
    .ToListAsync(ct);

Step 3 — Apply Mocking Database Calls

public record CreateOrderCommand(int CustomerId, List<int> ProductIds) : IRequest<int>;
public class CreateOrderHandler : IRequestHandler<CreateOrderCommand, int> { }

dotnet run --project ShopNest.DataAccess.Api
# Verify Mocking Database Calls — check SQL Server Management Studio and connection pool metrics and integration tests pass

SQL performance and connection management — Mocking Database Calls

• Connection pooling — default enabled; never disable without load testing; watch pool exhaustion (error 10053/10054)
• Parameterized queries — always use SqlParameter; prevents SQL injection and enables plan cache reuse
• Async — ExecuteReaderAsync/ExecuteNonQueryAsync free thread pool under load
• CommandBehavior.SequentialAccess — stream large BLOB/text columns without loading full row into memory
• Indexes — align with WHERE/JOIN columns; use SQL Server DMVs to find missing indexes

Real-World Example 1 — Insurance Claims Batch Processing

MANDATORY enterprise scenario (Insurance (LIC-style)): Mocking Database Calls in ShopNest.DataAccess Orders.

Business problem

Nightly batch validates 500K claims against policy rules. SqlBulkCopy inserts staging rows; stored procedure usp_ValidateClaims runs set-based SQL; invalid claims roll back in one transaction per batch of 5,000.

Architecture

CSV ingest → SqlBulkCopy → Staging.Claims
  → BEGIN TRANSACTION per batch
  → EXEC usp_ValidateClaims @BatchId
  → COMMIT or ROLLBACK + ErrorLog

Production ADO.NET code

using var bulk = new SqlBulkCopy(conn, SqlBulkCopyOptions.TableLock, tx)
{
    DestinationTableName = "Staging.Claims",
    BatchSize = 5000,
    BulkCopyTimeout = 600
};
await bulk.WriteToServerAsync(dataTable, ct);
await using var validate = new SqlCommand("usp_ValidateClaims", conn, tx) { CommandType = CommandType.StoredProcedure };
validate.Parameters.Add("@BatchId", SqlDbType.Int).Value = batchId;
await validate.ExecuteNonQueryAsync(ct);

Outcome

Batch window reduced from 6 hours to 90 minutes; zero partial batches after explicit transaction boundaries.

Real-World Example 2 — TCS ERP Monthly GL Reporting

MANDATORY enterprise scenario (Enterprise ERP): Mocking Database Calls in ShopNest.DataAccess Orders.

Business problem

Finance teams run month-end General Ledger reports across 200+ cost centers. Report queries join 12 tables and return 2M rows — EF Core materializes entire graphs into memory. ADO.NET SqlDataReader streams rows to CSV/PDF generators with constant memory.

Architecture

[Report Scheduler] → [GlReportRepository]
  → EXEC usp_GenerateMonthlyGL @Year, @Month, @CostCenterId
  → SqlDataReader forward-only stream → IAsyncEnumerable
  → Bulk copy to staging → SSRS / Excel export
Read uncommitted avoided; NOLOCK only on read replica for analytics.

Production ADO.NET code

public async IAsyncEnumerable<GlLineDto> StreamGlReportAsync(int year, int month, [EnumeratorCancellation] CancellationToken ct)
{
    await using var conn = new SqlConnection(_readReplicaConnectionString);
    await conn.OpenAsync(ct);
    await using var cmd = new SqlCommand("usp_GenerateMonthlyGL", conn)
    {
        CommandType = CommandType.StoredProcedure
    };
    cmd.Parameters.Add("@Year", SqlDbType.Int).Value = year;
    cmd.Parameters.Add("@Month", SqlDbType.Int).Value = month;

    await using var reader = await cmd.ExecuteReaderAsync(CommandBehavior.SequentialAccess, ct);
    while (await reader.ReadAsync(ct))
    {
        yield return new GlLineDto(
            reader.GetString(0),
            reader.GetDecimal(1),
            reader.GetDateTime(2));
    }
}

Outcome

Memory flat at 80MB for 2M-row report vs 1.2GB EF Core ToList(); report runtime cut from 14 min to 3 min.

ADO.NET with ASP.NET Core — Mocking Database Calls

Register IOrderRepository implementations in DI as Scoped. Never hold SqlConnection across requests. Use IConfiguration for connection strings; User Secrets locally, Azure Key Vault in production.

builder.Services.AddScoped();
builder.Services.AddHealthChecks().AddSqlServer(connectionString);

Stored procedures and SQL safety

Enterprise ShopNest modules use versioned stored procedures (usp_ prefix). Never concatenate user input — always SqlParameter. Log slow queries (>500ms) with Serilog.

Common errors & fixes

🔴 Mistake 1: Fat controllers with EF Core queries inline
✅ Fix: Move data access to services/repositories; keep controllers thin.

🔴 Mistake 2: Calling .ToList() too early materializing millions of rows into memory
✅ Fix: Defer execution — build IQueryable pipeline, then ToListAsync() once at the end.

🔴 Mistake 3: Filtering in memory after .ToList() instead of in the database query
✅ Fix: Keep filters in IQueryable, use Select projection, paginate with Skip/Take before materialization.

🔴 Mistake 4: Hard-coding connection strings in controllers
✅ Fix: Use appsettings.json + User Secrets locally; Azure Key Vault in production.

Best practices

• 🟢 Use async/await end-to-end for database and I/O calls
• 🟢 Register DbContext as Scoped; avoid capturing it in singletons
• 🟡 Use IQueryable until the last moment; avoid multiple enumeration; project with Select before ToList
• 🟡 Prefer method syntax for complex chains; use query syntax for joins when readability wins
• 🔴 Log structured data with Serilog — include OrderId, UserId, not passwords
• 🔴 Use HTTPS, secure cookies, and authorization policies in production

Interview questions

Fresher level

Q1: What is Mocking Database Calls in ASP.NET Core MVC?
A: Mocking Database Calls is a core MVC capability used in ShopNest.DataAccess for Orders. Explain in one sentence, then describe controller/view/service placement.

Q2: How would you implement Mocking Database Calls on a TCS-style delivery project?
A: Deferred execution, IQueryable pipelines, Select projection, Skip/Take pagination, and SQL logging in development.

Q3: IEnumerable vs IQueryable — when to use which?
A: IEnumerable for in-memory collections; IQueryable for EF Core database queries that translate to SQL.

Mid / senior level

Q4: Explain LINQ deferred execution and query translation briefly.
A: LINQ → Expression Tree → IQueryProvider → SQL (EF) or Iterator (in-memory) → Results.

Q5: Common production mistake with this topic?
A: Skipping validation, exposing secrets in Git, or untested edge cases (null model, unauthorized user).

Q6: .NET LINQ vs SQL — when to push logic to database?
A: Core is cross-platform, faster, cloud-ready; Framework is maintenance mode on Windows/IIS.

Coding round

Implement Mocking Database Calls for ShopNest Orders: show interface, concrete class, DI registration, and xUnit test with mock.

public class MockingDatabaseCallsPatternTests
{
    [Fact]
    public async Task ExecuteAsync_ReturnsSuccess()
    {
        var mock = new Mock();
        mock.Setup(s => s.ExecuteAsync(It.IsAny(), default))
            .ReturnsAsync(Result.Success("test-id"));
        var result = await mock.Object.ExecuteAsync(new Request("test-id"));
        Assert.True(result.IsSuccess);
    }
}

Summary & next steps

• Article 73: Mocking Database Calls — Complete Guide
• Module: Module 8: Testing and Debugging · Level: ADVANCED
• Applied to ShopNest.DataAccess — Orders

Previous: Integration Testing — Complete Guide
Next: SQL Profiling — Complete Guide

Practice: Add one small feature using today's pattern — commit with feat(adonet): article-73.

FAQ