Audit Trails — Complete Guide

Introduction

Audit Trails — 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 audit trails 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 Audit Logs.

After this article you will

• Explain Audit Trails in plain English and in SQL Server and high-performance data access terms
• Implement audit trails in ShopNest.DataAccess — Enterprise High-Performance Data Platform (Audit Logs)
• 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 40 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 38 — Logging — Complete Guide
• Time: 24 min reading + 30–45 min hands-on

Concept deep-dive

Level 1 — Analogy

Audit Trails on ShopNest.DataAccess adds high-performance SQL Server data access for enterprise modules.

Level 2 — Technical

Audit Trails 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 Audit Logs 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 (Audit Logs)

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 — Audit Trails on ShopNest (Audit Logs)
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 Audit Trails

[HttpPost]
public async Task<IActionResult> Upload(IFormFile file)
{
    var path = Path.Combine(_env.WebRootPath, "uploads", file.FileName);
    using var stream = new FileStream(path, FileMode.Create);
    await file.CopyToAsync(stream);
    return RedirectToAction("Index");
}

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

SQL performance and connection management — Audit Trails

• 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): Audit Trails in ShopNest.DataAccess Audit Logs.

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): Audit Trails in ShopNest.DataAccess Audit Logs.

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 — Audit Trails

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

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

public class AuditTrailsPatternTests
{
    [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 39: Audit Trails — Complete Guide
• Module: Module 4: Transactions and Error Handling · Level: INTERMEDIATE
• Applied to ShopNest.DataAccess — Audit Logs

Previous: Logging — Complete Guide
Next: ACID Principles — Complete Guide

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

FAQ