Enterprise Transaction Procedures — Complete Guide

Introduction

Enterprise Transaction Procedures — 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 enterprise transaction procedures 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 Notifications.

After this article you will

• Explain Enterprise Transaction Procedures in plain English and in SQL Server and high-performance data access terms
• Implement enterprise transaction procedures in ShopNest.DataAccess — Enterprise High-Performance Data Platform (Notifications)
• 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 31 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 29 — Reporting Procedures — Complete Guide
• Time: 24 min reading + 30–45 min hands-on

Concept deep-dive

Level 1 — Analogy

Actions are specific services the reception desk offers — "show catalog", "process checkout", "print receipt".

Level 2 — Technical

Enterprise Transaction Procedures 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 Notifications 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 (Notifications)

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 — Enterprise Transaction Procedures on ShopNest (Notifications)
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 Enterprise Transaction Procedures

// Enterprise Transaction Procedures — ShopNest.DataAccess (Notifications)
builder.Services.AddScoped<IEnterpriseTransactionProceduresService, EnterpriseTransactionProceduresService>();

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

SQL performance and connection management — Enterprise Transaction Procedures

• 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

Transaction handling in production

Use explicit SqlTransaction for multi-statement business operations. Banking and payment modules in ShopNest.DataAccess require ReadCommitted minimum; ledger updates may need Serializable on hot accounts.

await using var tx = await conn.BeginTransactionAsync(IsolationLevel.ReadCommitted, ct);
try { /* commands */ await tx.CommitAsync(ct); }
catch { await tx.RollbackAsync(ct); throw; }

Real-World Example 1 — ICICI-Style NEFT/IMPS Fund Transfer

MANDATORY enterprise scenario (Indian Banking): Enterprise Transaction Procedures in ShopNest.DataAccess Notifications.

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.

Real-World Example 2 — Razorpay-Style Payment Reconciliation

MANDATORY enterprise scenario (Payment Gateway): Enterprise Transaction Procedures in ShopNest.DataAccess Notifications.

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 — Enterprise Transaction Procedures

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

Q2: How would you implement Enterprise Transaction Procedures 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 Enterprise Transaction Procedures for ShopNest Notifications: show interface, concrete class, DI registration, and xUnit test with mock.

public class EnterpriseTransactionProceduresPatternTests
{
    [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 30: Enterprise Transaction Procedures — Complete Guide
• Module: Module 3: Stored Procedures · Level: INTERMEDIATE
• Applied to ShopNest.DataAccess — Notifications

Previous: Reporting Procedures — Complete Guide
Next: SQL Transactions — Complete Guide

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

FAQ