Connection Pooling — Complete Guide

Introduction

Connection Pooling — 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 connection pooling 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 Connection Pooling in plain English and in SQL Server and high-performance data access terms
• Implement connection pooling 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 42 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 40 — ACID Principles — Complete Guide
• Time: 28 min reading + 30–45 min hands-on

Concept deep-dive

Level 1 — Analogy

Connection pooling is a taxi stand — idle connections wait for the next request instead of hiring a new driver every trip.

Level 2 — Technical

Connection Pooling 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 — Connection Pooling 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 Connection Pooling

builder.Services.AddDbContext<ShopNestDbContext>(o =>
    o.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

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

SQL performance and connection management — Connection Pooling

• 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 — TCS ERP Monthly GL Reporting

MANDATORY enterprise scenario (Enterprise ERP): Connection Pooling 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.

Real-World Example 2 — Razorpay-Style Payment Reconciliation

MANDATORY enterprise scenario (Payment Gateway): Connection Pooling in ShopNest.DataAccess Orders.

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 — Connection Pooling

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 Connection Pooling in ASP.NET Core MVC?
A: Connection Pooling 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 Connection Pooling 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 Connection Pooling for ShopNest Orders: show interface, concrete class, DI registration, and xUnit test with mock.

public class ConnectionPoolingPatternTests
{
    [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 41: Connection Pooling — Complete Guide
• Module: Module 5: Performance Optimization · Level: ADVANCED
• Applied to ShopNest.DataAccess — Orders

Previous: ACID Principles — Complete Guide
Next: Async Database Operations — Complete Guide

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

FAQ