ADO.NET with ASP.NET Core Web API — Complete Guide

Introduction

ADO.NET with ASP.NET Core Web API — 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 web api 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 Analytics.

After this article you will

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

Concept deep-dive

Level 1 — Analogy

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

Level 2 — Technical

ADO.NET with ASP.NET Core Web API 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 Analytics 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 (Analytics)

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 Web API on ShopNest (Analytics)
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 Web API

// ADO.NET with ASP.NET Core Web API — ShopNest.DataAccess (Analytics)
builder.Services.AddScoped<IADONETwithASPNETCoreWebAPIService, ADONETwithASPNETCoreWebAPIService>();

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

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

• 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)): ADO.NET with ASP.NET Core Web API in ShopNest.DataAccess Analytics.

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 — Hospital Patient Records (Apollo-Style)

MANDATORY enterprise scenario (Healthcare): ADO.NET with ASP.NET Core Web API in ShopNest.DataAccess Analytics.

Business problem

Patient vitals, lab results, and billing hit different schemas. ADO.NET multi-result sets from usp_GetPatientDashboard return 3 grids in one round trip — faster than 3 EF Core queries with Include chains.

Architecture

usp_GetPatientDashboard @PatientId → Result set 1: Demographics
  → Result set 2: Latest vitals → Result set 3: Pending bills

Production ADO.NET code

await using var reader = await cmd.ExecuteReaderAsync(ct);
var demo = ReadDemographics(reader);
await reader.NextResultAsync(ct);
var vitals = ReadVitals(reader);
await reader.NextResultAsync(ct);
var bills = ReadBills(reader);

Outcome

Dashboard load 320ms vs 890ms EF Core; HIPAA audit trail via ADO.NET audit interceptor.

ADO.NET with ASP.NET Core — ADO.NET with ASP.NET Core Web API

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

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

public class ADO.NETwithASP.NETCoreWebAPIPatternTests
{
    [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 52: ADO.NET with ASP.NET Core Web API — Complete Guide
• Module: Module 6: ASP.NET Core Integration · Level: ADVANCED
• Applied to ShopNest.DataAccess — Analytics

Previous: ADO.NET with ASP.NET Core MVC — Complete Guide
Next: Repository Pattern — Complete Guide

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

FAQ