ADO.NET with ASP.NET Core MVC — Complete Guide

Introduction

ADO.NET with ASP.NET Core MVC — 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 ado.net with asp.net core mvc 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 Reporting.

After this article you will

• Explain ADO.NET with ASP.NET Core MVC in plain English and in SQL Server and high-performance data access terms
• Implement ado.net with asp.net core mvc in ShopNest.DataAccess — Enterprise High-Performance Data Platform (Reporting)
• 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 52 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 50 — High-Performance APIs — Complete Guide
• Time: 28 min reading + 30–45 min hands-on

Concept deep-dive

Level 1 — Analogy

ADO.NET with ASP.NET Core MVC on ShopNest.DataAccess adds high-performance SQL Server data access for enterprise modules.

Level 2 — Technical

ADO.NET with ASP.NET Core MVC 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 Reporting 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 (Reporting)

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 — ADO.NET with ASP.NET Core MVC on ShopNest (Reporting)
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 ADO.NET with ASP.NET Core MVC

builder.Services.AddIdentity<ApplicationUser, IdentityRole>()
    .AddEntityFrameworkStores<ShopNestDbContext>()
    .AddDefaultTokenProviders();

dotnet run --project ShopNest.DataAccess.Api
# Verify ADO.NET with ASP.NET Core MVC — check SQL Server Management Studio and connection pool metrics and integration tests pass

SQL performance and connection management — ADO.NET with ASP.NET Core MVC

• 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 — Government Pension Legacy Modernization

MANDATORY enterprise scenario (Government / PSU): ADO.NET with ASP.NET Core MVC in ShopNest.DataAccess Reporting.

Business problem

20-year-old VB6 + inline SQL pension system migrated to ASP.NET Core. ADO.NET wraps existing stored procedures — no ORM rewrite — minimizing regression risk while adding connection pooling and async I/O.

Architecture

Legacy SPs (unchanged) ← AdoNetLegacyBridge → ASP.NET Core Web API
  → Repository per bounded context (Pension, Disbursement, Audit)
  → Strangler Fig: new modules EF Core, legacy modules ADO.NET

Production ADO.NET code

// Bridge pattern — call legacy SP by name
public async Task<PensionDetail?> GetPensionAsync(string pensionId, CancellationToken ct)
{
    const string sql = "EXEC dbo.usp_GetPensionDetail @PensionId";
    await using var conn = new SqlConnection(_legacyConnStr);
    await using var cmd = new SqlCommand(sql, conn);
    cmd.Parameters.Add("@PensionId", SqlDbType.VarChar, 15).Value = pensionId;
    await conn.OpenAsync(ct);
    await using var r = await cmd.ExecuteReaderAsync(ct);
    return r.Read() ? MapPension(r) : null;
}

Outcome

Migration delivered in 8 months vs 24-month full rewrite estimate; 99.2% SP compatibility on day one.

Real-World Example 2 — Razorpay-Style Payment Reconciliation

MANDATORY enterprise scenario (Payment Gateway): ADO.NET with ASP.NET Core MVC in ShopNest.DataAccess Reporting.

Business problem

End-of-day reconciliation matches 1M gateway transactions against internal ledger. ADO.NET table-valued parameters feed set-based MERGE in SQL Server — impossible to express efficiently in LINQ.

Architecture

Gateway CSV → TVP dbo.TransactionBatch → usp_ReconcilePayments
  → MERGE Payments.Ledger → Output mismatches to ReconciliationExceptions

Production ADO.NET code

var tvp = new SqlParameter("@Batch", SqlDbType.Structured)
{
    TypeName = "dbo.TransactionBatchType",
    Value = BuildDataTable(transactions)
};
cmd.Parameters.Add(tvp);
await cmd.ExecuteNonQueryAsync(ct);

Outcome

Reconciliation completes in 12 minutes; EF Core prototype timed out at 45 minutes on same hardware.

ADO.NET with ASP.NET Core — ADO.NET with ASP.NET Core MVC

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 ADO.NET with ASP.NET Core MVC in ASP.NET Core MVC?
A: ADO.NET with ASP.NET Core MVC is a core MVC capability used in ShopNest.DataAccess for Reporting. Explain in one sentence, then describe controller/view/service placement.

Q2: How would you implement ADO.NET with ASP.NET Core MVC 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 ADO.NET with ASP.NET Core MVC for ShopNest Reporting: show interface, concrete class, DI registration, and xUnit test with mock.

public class ADO.NETwithASP.NETCoreMVCPatternTests
{
    [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 51: ADO.NET with ASP.NET Core MVC — Complete Guide
• Module: Module 6: ASP.NET Core Integration · Level: ADVANCED
• Applied to ShopNest.DataAccess — Reporting

Previous: High-Performance APIs — Complete Guide
Next: ADO.NET with ASP.NET Core Web API — Complete Guide

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

FAQ