Enterprise Legacy Modernization — Capstone

Introduction

Enterprise Legacy Modernization — Capstone is essential for .NET developers building ShopNest.DataAccess — Enterprise High-Performance Data Platform — Toolliyo's 100-article ADO.NET Core master path covering SqlConnection, stored procedures, transactions, connection pooling, ASP.NET Core integration, Azure SQL, and ten enterprise portfolio projects. Every article includes minimum two enterprise real-world examples (ICICI banking, TCS ERP reporting, insurance batch, legacy modernization).

In Indian delivery projects (TCS, Infosys, Wipro), interviewers expect enterprise legacy modernization with real banking transfers, ERP GL reports, or legacy stored procedure modernization — not toy animal demos. This article delivers production depth on Analytics.

After this article you will

• Explain Enterprise Legacy Modernization in plain English and in SQL Server / ADO.NET terms
• Implement enterprise legacy modernization in ShopNest.DataAccess — Enterprise High-Performance Data Platform (Analytics)
• Compare SQL-concat / sync anti-patterns vs production-ready parameterized async ADO.NET
• Answer fresher, mid-level, and senior ADO.NET and SQL Server interview questions confidently
• Connect this lesson to Article 100 and the 100-article ADO.NET Core roadmap

Prerequisites

• Software: .NET 8 SDK, SQL Server Express / LocalDB or Docker, SSMS or Azure Data Studio
• Knowledge: C# basics and SQL Server
• Previous: Article 99 — High-Performance Order System — Complete Guide
• Time: 28 min reading + 30–45 min hands-on in SSMS

Concept deep-dive

Level 1 — Analogy

Legacy modernization keeps proven stored procedures — ADO.NET wraps old SQL with new ASP.NET Core APIs via strangler fig.

Level 2 — Technical

Enterprise Legacy Modernization scales ShopNest enterprise data — read replicas, warehouse ETL, background workers, and strangler-fig legacy modernization on Analytics.

Level 3 — Data platform view

[ASP.NET Core API / MVC Controller]
       ▼
[Application Service — IOrderRepository interface]
       ▼
[ADO.NET Repository — SqlConnection + SqlCommand + SqlParameter]
       ▼
[SQL Server — Tables · Indexes · Stored Procedures · Transactions]
       ▼
[Connection Pool · Read Replica · Azure SQL · Serilog + SQL Profiler]

Project structure

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

Hands-on implementation — Analytics

Write Enterprise Legacy Modernization in ShopNest.DataAccess for Analytics: SqlConnection/SqlCommand with parameters, async calls, and verify in SSMS with execution plan.

1. Open ShopNest.DataAccess repository for this lesson module.
2. Use SqlConnection with await using and connection string from IConfiguration.
3. Add SqlParameter for every user input — never string concatenation.
4. Use ExecuteReaderAsync for reads; transactions for multi-statement writes.
5. Verify in SSMS — check execution plan, row counts, and connection pool metrics.

Anti-pattern (SQL concat, sync calls, DataSet for huge reports)

// ❌ BAD — SQL concat, sync call, no disposal
public List<Order> GetOrders(string status) {
    var conn = new SqlConnection(_connStr);
    conn.Open();
    var cmd = new SqlCommand("SELECT * FROM Orders WHERE Status = '" + status + "'", conn);
    var reader = cmd.ExecuteReader(); // sync, blocks thread pool
    // connection never disposed — pool exhaustion under load
    return Parse(reader);
}

Production-style ADO.NET data access

// ✅ CORRECT — Enterprise Legacy Modernization on ShopNest (Analytics)
public async Task<IReadOnlyList<OrderDto>> GetByStatusAsync(string status, CancellationToken ct) {
    await using var conn = new SqlConnection(_config.GetConnectionString("ShopNestDb"));
    await conn.OpenAsync(ct);
    await using var cmd = new SqlCommand("usp_Orders_GetByStatus", conn) {
        CommandType = CommandType.StoredProcedure
    };
    cmd.Parameters.Add("@Status", SqlDbType.NVarChar, 20).Value = status;
    var list = new List<OrderDto>();
    await using var reader = await cmd.ExecuteReaderAsync(ct);
    while (await reader.ReadAsync(ct))
        list.Add(new OrderDto(reader.GetInt32(0), reader.GetDecimal(1), reader.GetString(2)));
    return list;
}

Complete example

// Capstone: ShopNest.DataAccess — Banking + ERP + Legacy SPs + Azure SQL deploy

SQL performance and connection management — Enterprise Legacy Modernization

• 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): Enterprise Legacy Modernization in ShopNest.DataAccess Analytics.

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 — ICICI-Style NEFT/IMPS Fund Transfer

MANDATORY enterprise scenario (Indian Banking): Enterprise Legacy Modernization in ShopNest.DataAccess Analytics.

Business problem

Core banking must process 50,000+ transfers per hour during salary day. EF Core change tracking adds overhead; ADO.NET with stored procedures and explicit SqlTransaction gives predictable latency and full SQL control for auditors.

Architecture

[Mobile Banking API] → [TransferRepository (ADO.NET)]
  → SqlConnection (pooled) → usp_TransferFunds @FromAcct, @ToAcct, @Amount
  → SqlTransaction (Serializable for ledger rows)
  → AuditLog INSERT via usp_WriteAuditTrail
Connection string: ShopNestPaymentsDb; CommandTimeout: 30s; Retry on deadlock 1205.

Production ADO.NET code

// ShopNest.DataAccess/Payments/TransferRepository.cs
public async Task<TransferResult> TransferAsync(TransferRequest req, CancellationToken ct)
{
    await using var conn = new SqlConnection(_connectionString);
    await conn.OpenAsync(ct);
    await using var tx = (SqlTransaction)await conn.BeginTransactionAsync(IsolationLevel.ReadCommitted, ct);

    try
    {
        await using var cmd = new SqlCommand("usp_TransferFunds", conn, tx)
        {
            CommandType = CommandType.StoredProcedure,
            CommandTimeout = 30
        };
        cmd.Parameters.Add("@FromAccount", SqlDbType.VarChar, 20).Value = req.FromAccount;
        cmd.Parameters.Add("@ToAccount", SqlDbType.VarChar, 20).Value = req.ToAccount;
        cmd.Parameters.Add("@Amount", SqlDbType.Decimal).Value = req.Amount;
        cmd.Parameters.Add("@IdempotencyKey", SqlDbType.UniqueIdentifier).Value = req.IdempotencyKey;

        var rows = await cmd.ExecuteNonQueryAsync(ct);
        await tx.CommitAsync(ct);
        return TransferResult.Success(req.IdempotencyKey);
    }
    catch (SqlException ex) when (ex.Number == 1205) // deadlock
    {
        await tx.RollbackAsync(ct);
        throw new TransientDatabaseException("Deadlock — retry with Polly", ex);
    }
}

Outcome

P99 transfer latency 45ms vs 180ms with EF Core tracked entities; RBI audit passed with stored procedure versioning.

ADO.NET with ASP.NET Core — Enterprise Legacy Modernization

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

builder.Services.AddScoped<IOrderRepository, OrderRepository>();
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 and review execution plans in SSMS.

Project checklist

• Parameterized stored procedures (usp_ prefix) for all Analytics writes
• SqlTransaction for multi-statement financial operations
• Async ADO.NET repositories registered as Scoped in DI
• Integration tests with Testcontainers SQL Server
• Azure SQL connection + Key Vault secrets + slow-query Serilog alerts

Common errors & fixes

• SQL built with string concatenation from user input — Use SqlParameter with typed values for every dynamic value.
• Not disposing SqlConnection / SqlDataReader — Use await using for connection, command, and reader — return connections to pool.
• Loading million-row reports into DataTable — Stream with SqlDataReader and yield batches; avoid DataSet for large data.
• Hard-coding connection strings in repository classes — IConfiguration + User Secrets locally; Azure Key Vault in production.

Best practices

• 🟢 SqlParameter for every dynamic value — zero string concatenation
• 🟢 await using for SqlConnection, SqlCommand, SqlDataReader — return to pool
• 🟡 Async ADO.NET end-to-end on ASP.NET Core request paths
• 🟡 Stream large reports with SqlDataReader; avoid DataSet for millions of rows
• 🔴 SqlTransaction for multi-statement financial writes with explicit rollback
• 🔴 Connection strings in Key Vault — never committed to Git

Interview questions

Fresher level

Q1: What is Enterprise Legacy Modernization in ADO.NET Core?
A: Enterprise Legacy Modernization on ShopNest Analytics: SqlConnection lifecycle, SqlCommand with parameters, async execution, and disposal for connection pool health.

Q2: ADO.NET vs EF Core — when to use which?
A: ADO.NET for stored procedures, bulk load, streaming reports, and legacy SQL; EF Core for rapid CRUD and migrations. ShopNest uses both.

Q3: How do you prevent SQL injection in ADO.NET?
A: Always SqlParameter with typed SqlDbType — never string concatenation, even for "trusted" internal tools.

Mid / senior level

Q4: Explain connection pooling and why disposal matters.
A: SqlConnection.Close/Dispose returns the physical connection to the pool. Leaked connections exhaust Max Pool Size and cause timeouts.

Q5: How do you handle transactions in ADO.NET?
A: SqlTransaction with try/commit/catch/rollback; choose isolation level (ReadCommitted default); retry deadlocks with Polly.

Q6: How would you optimize a slow stored procedure report?
A: Check execution plan in SSMS, add covering indexes, avoid SELECT *, stream with SqlDataReader, consider read replica for analytics.

Coding round

Implement a parameterized ADO.NET repository method for ShopNest Analytics — show SqlConnection, SqlCommand, SqlParameter, async disposal, and one xUnit integration test.

Summary & next steps

• Article 100: Enterprise Legacy Modernization — Capstone
• Module: Module 10: Real-World Enterprise Projects · Level: ADVANCED
• Applied to ShopNest.DataAccess — Analytics

Previous: High-Performance Order System — Complete Guide
Next: Take an ADO.NET quiz

Practice: Run today's SQL in SSMS with execution plan — commit with feat(adonet): article-100.

FAQ