Fees API: Bill a Whole Class at Once and Report Outstanding Fees – Web API | Lesson 83

Published / updated8 Oct 2026

Introduction

Lesson 74 let the office look at one invoice and take a payment. A school does not create invoices one by one, though. At the start of a term, the office bills a whole class.

This lesson builds that, with three more endpoints: a class's fees, a student's invoice history, and the outstanding fees report the principal reads every week.

By the end of this lesson, you will be able to:

Bill a whole class with set-based SQL: three statements instead of thousands of round trips.
Make a batch request safe to repeat, and safe when two people run it at once.
Number documents with a SQL Server sequence, and explain why gaps are normal.
Build a report with totals and paging in one round trip.
Decide which rules belong in the service and which belong in the database.

Why this matters

Billing is where schools lose money and trust. A class billed twice means angry parents and refunds. A student missed means fees never collected.

Billing also runs at the busiest moment of the term, often by two clerks at once. The design must stay correct when someone clicks twice or the network retries.

The concept

Think of posting 1,000 letters. You could walk to the post office once per letter, or hand over the whole stack in one visit. Set-based SQL is the second way: one statement handles every row.

Example: the fee data

Table	One row is	Class 10 example
`FeeStructures`	one fee line for a class in a year	Tuition 42,000 · Exam 2,000 · Library 1,500
`FeeInvoices`	one student's bill for the year	INV-2026-000003, Kabir, 45,500, due 2026-06-30
`FeeInvoiceItems`	one fee line on an invoice	Tuition 42,000 on INV-2026-000003
`FeePayments`	money received (Lesson 64)	RCP-2026-000002, 20,000 from Diya

Example: row by row or set-based

	Row by row from C#	Set-based in SQL
Statements for 1,000 students	about 4,000 (an invoice and three items each)	2 inserts and 1 summary
Network round trips	about 4,000	1
Partly done on failure	possible, unless every call shares one transaction	never: one transaction

Rules that need C# (dates, messages) run before the SQL. The SQL then does the heavy, repetitive work.

Example: safe to repeat, safe at the same time

Three guards stop a second invoice for the same student:

One run per class at a time. An application lock (sp_getapplock) is a lock on a name you choose, such as FeeBilling:10:2. A second clerk billing the same class waits for it, up to 30 seconds.
The query skips students who already have one. The NOT EXISTS check finds them, so the second clerk, once the lock is free, creates nothing.
The database refuses a duplicate. A filtered unique index allows one non-cancelled invoice per enrollment. If anything ever slips past the first two guards, SQL Server says no.

Example: invoice numbers from a sequence

A sequence is a SQL Server object that hands out the next number, like the ticket machine at a bank. The expression NEXT VALUE FOR dbo.InvoiceNumbers OVER (ORDER BY sec.Name, e.RollNumber) numbers the new invoices in section and roll number order.

A sequence never takes a number back, even when a transaction rolls back. Gaps in invoice numbers are normal and expected; an auditor should check for duplicates, not for gaps.

Apply it to the SMS

Step 1: the sequence and the guard index

database/083-invoice-numbers.sql
-- Lesson 83: invoices for a whole class at once.
-- 1. Invoice numbers come from a sequence, like receipt numbers (Lesson 22). It starts after the
--    highest invoice number already used: INV-2026-000012 means it starts at 13.
-- 2. A student has at most one invoice per enrollment (one enrollment = one academic year) that is
--    not cancelled. A filtered unique index makes the database refuse a second one.
-- Safe to run more than once.
USE SchoolDb;
GO

IF OBJECT_ID(N'dbo.InvoiceNumbers', N'SO') IS NULL
BEGIN
    -- The last 6 characters of an invoice number are its running number.
    DECLARE @StartWith BIGINT = (SELECT ISNULL(MAX(CAST(RIGHT(InvoiceNumber, 6) AS BIGINT)), 0) + 1 FROM dbo.FeeInvoices);
    DECLARE @Sql NVARCHAR(200) = CONCAT(
        N'CREATE SEQUENCE dbo.InvoiceNumbers AS BIGINT START WITH ', @StartWith, N' INCREMENT BY 1;');
    EXEC sys.sp_executesql @Sql;
END;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'UQ_FeeInvoices_EnrollmentId_Active')
    CREATE UNIQUE INDEX UQ_FeeInvoices_EnrollmentId_Active
        ON dbo.FeeInvoices (EnrollmentId)
        WHERE Status <> 'Cancelled';
GO

-- Check: the sequence's next number, and that no enrollment has two active invoices.
-- A sequence that was never used has no last_used_value yet: its next number is its start value.
SELECT CASE WHEN last_used_value IS NULL THEN CAST(start_value AS BIGINT)
            ELSE CAST(last_used_value AS BIGINT) + 1 END AS NextInvoiceNumber
FROM sys.sequences
WHERE name = N'InvoiceNumbers';

SELECT COUNT(*) AS EnrollmentsWithTwoActiveInvoices
FROM (
    SELECT EnrollmentId
    FROM dbo.FeeInvoices
    WHERE Status <> 'Cancelled'
    GROUP BY EnrollmentId
    HAVING COUNT(*) > 1
) AS d;
GO

The sequence starts after the highest number already used, INV-2026-000012, so the first new number is 13. The check at the end prints 13 and 0. Our first version printed 14: an unused sequence has no last_used_value yet, and its next number is its start value.

Step 2: the requests and results

src/SchoolApp.Core/Dtos/FeeDtos.cs
using System.ComponentModel.DataAnnotations;
using SchoolApp.Core.Common;

namespace SchoolApp.Core.Dtos;

// Lesson 83: fee structures, invoices for a whole class, and the outstanding fees report.

// One line of a class's fees for the year, such as Tuition 42,000.
public record FeeStructureLineDto(string FeeType, decimal Amount);

public record FeeStructureDto(int ClassId, string AcademicYear, IReadOnlyList<FeeStructureLineDto> Lines, decimal Total);

// What the office sends to bill a whole class for the current academic year.
public record GenerateInvoicesRequest([Range(1, 10)] int ClassId, DateOnly DueDate);

public record GenerateInvoicesResult(int ClassId, string AcademicYear, DateOnly InvoiceDate, DateOnly DueDate,
                                     decimal AmountEach, int Created, int AlreadyInvoiced,
                                     string? FirstInvoiceNumber, string? LastInvoiceNumber);

// What the repository reports after one run of invoice generation.
public record InvoiceRun(int Created, int AlreadyInvoiced, string? FirstInvoiceNumber, string? LastInvoiceNumber);

// One invoice as a parent sees it in the student's fee history.
public record StudentInvoiceDto(string InvoiceNumber, DateOnly InvoiceDate, DateOnly DueDate, decimal Total,
                                decimal Paid, decimal Balance, string Status, bool IsOverdue);

// One line of the outstanding fees report.
public record OutstandingInvoiceDto(string InvoiceNumber, string AdmissionNumber, string StudentName, string ClassName,
                                    string SectionName, DateOnly DueDate, decimal Balance, int DaysOverdue);

// The report: totals for everything outstanding, and one page of the invoices, largest balance first.
public record OutstandingReportDto(int Invoices, int Overdue, decimal TotalOutstanding, PagedResult<OutstandingInvoiceDto> Page);

Step 3: the rules

src/SchoolApp.Core/Services/FeeBillingService.cs
using SchoolApp.Core.Dtos;
using SchoolApp.Core.Exceptions;
using SchoolApp.Core.Interfaces;

namespace SchoolApp.Core.Services;

// Lesson 83: billing a whole class for the current academic year.
// Rules: a current year exists (50035), the class has fees set up for it (50015), and the due date is
// between today and the end of the year (50016). The invoice date is today.
public class FeeBillingService
{
    private readonly IMasterDataRepository _masterData;
    private readonly IFeeBillingRepository _billing;
    private readonly TimeProvider _time;

    public FeeBillingService(IMasterDataRepository masterData, IFeeBillingRepository billing, TimeProvider time)
    {
        _masterData = masterData;
        _billing = billing;
        _time = time;
    }

    public DateOnly Today => DateOnly.FromDateTime(_time.GetLocalNow().DateTime);

    // null when the class has no fees set up this year.
    public async Task<FeeStructureDto?> GetStructureAsync(int classId, CancellationToken cancellationToken = default)
    {
        AcademicYearDto year = await CurrentYearAsync(cancellationToken);
        List<FeeStructureLineDto> lines = await _billing.GetStructureAsync(classId, year.Id, cancellationToken);
        return lines.Count == 0 ? null : new FeeStructureDto(classId, year.Name, lines, lines.Sum(l => l.Amount));
    }

    public async Task<GenerateInvoicesResult> GenerateAsync(GenerateInvoicesRequest request, CancellationToken cancellationToken = default)
    {
        AcademicYearDto year = await CurrentYearAsync(cancellationToken);
        List<FeeStructureLineDto> lines = await _billing.GetStructureAsync(request.ClassId, year.Id, cancellationToken);
        if (lines.Count == 0)
        {
            throw new SchoolRuleException(50015, $"Class {request.ClassId} has no fees set up for {year.Name}.");
        }

        DateOnly today = Today;
        if (request.DueDate < today || request.DueDate > year.EndDate)
        {
            throw new SchoolRuleException(50016,
                $"The due date must be between {today:yyyy-MM-dd} and the end of {year.Name} ({year.EndDate:yyyy-MM-dd}).");
        }

        InvoiceRun run = await _billing.GenerateInvoicesAsync(request.ClassId, year.Id, today, request.DueDate, cancellationToken);
        return new GenerateInvoicesResult(request.ClassId, year.Name, today, request.DueDate, lines.Sum(l => l.Amount),
                                          run.Created, run.AlreadyInvoiced, run.FirstInvoiceNumber, run.LastInvoiceNumber);
    }

    private async Task<AcademicYearDto> CurrentYearAsync(CancellationToken cancellationToken) =>
        await _masterData.GetCurrentAcademicYearAsync(cancellationToken)
        ?? throw new SchoolRuleException(50035, "No academic year is marked as current.");
}

The service checks what needs a clear message: the year, the class's fees, and the due date. "Today" comes from TimeProvider (Lesson 79), so tests can fix it.

Step 4: billing in three statements

Show src/SchoolApp.Data/Dapper/DapperFeeBillingRepository.cs

src/SchoolApp.Data/Dapper/DapperFeeBillingRepository.cs
using System.Text;
using Dapper;
using Microsoft.Data.SqlClient;
using SchoolApp.Core.Common;
using SchoolApp.Core.Dtos;
using SchoolApp.Core.Exceptions;
using SchoolApp.Core.Interfaces;

namespace SchoolApp.Data.Dapper;

// Lesson 83: billing a whole class, and the outstanding fees report, with Dapper.
public class DapperFeeBillingRepository : IFeeBillingRepository
{
    private readonly SqlConnectionFactory _connections;

    public DapperFeeBillingRepository(SqlConnectionFactory connections)
    {
        _connections = connections;
    }

    public async Task<List<FeeStructureLineDto>> GetStructureAsync(int classId, int academicYearId,
                                                                   CancellationToken cancellationToken = default)
    {
        await using SqlConnection connection = _connections.Create();
        IEnumerable<FeeStructureLineDto> lines = await connection.QueryAsync<FeeStructureLineDto>(new CommandDefinition("""
            SELECT FeeType, Amount
            FROM dbo.FeeStructures
            WHERE ClassId = @ClassId AND AcademicYearId = @AcademicYearId
            ORDER BY Amount DESC, FeeType;
            """, new { ClassId = classId, AcademicYearId = academicYearId }, cancellationToken: cancellationToken));
        return lines.ToList();
    }

    // Set-based: one INSERT ... SELECT creates every invoice of the class, and one more creates every item.
    // A class of 1,000 students is three statements, not 4,000 round trips.
    public async Task<InvoiceRun> GenerateInvoicesAsync(int classId, int academicYearId, DateOnly invoiceDate, DateOnly dueDate,
                                                        CancellationToken cancellationToken = default)
    {
        const string sql = """
            -- Only one billing run per class and year at a time. A second clerk waits here (up to 30 seconds)
            -- until the first run commits, then finds every student already billed. Lesson 83's first version
            -- used UPDLOCK, HOLDLOCK instead, and two runs at once could deadlock (error 1205).
            DECLARE @LockName NVARCHAR(255) = CONCAT(N'FeeBilling:', @ClassId, N':', @AcademicYearId);
            DECLARE @LockResult INT;
            EXEC @LockResult = sys.sp_getapplock @Resource = @LockName, @LockMode = 'Exclusive',
                                                 @LockOwner = 'Transaction', @LockTimeout = 30000;
            IF @LockResult < 0
                THROW 50017, 'Invoices for this class are being created by someone else. Try again in a minute.', 1;

            DECLARE @New TABLE (Id INT NOT NULL, InvoiceNumber VARCHAR(20) NOT NULL);
            DECLARE @Total DECIMAL(10, 2) =
                (SELECT SUM(Amount) FROM dbo.FeeStructures WHERE ClassId = @ClassId AND AcademicYearId = @AcademicYearId);

            -- Every active student of the class this year without an active invoice. Numbers are given in
            -- section and roll number order.
            INSERT INTO dbo.FeeInvoices (InvoiceNumber, EnrollmentId, InvoiceDate, DueDate, TotalAmount)
            OUTPUT INSERTED.Id, INSERTED.InvoiceNumber INTO @New (Id, InvoiceNumber)
            SELECT CONCAT('INV-', YEAR(@InvoiceDate), '-',
                          RIGHT(CONCAT('000000', NEXT VALUE FOR dbo.InvoiceNumbers OVER (ORDER BY sec.Name, e.RollNumber)), 6)),
                   e.Id, @InvoiceDate, @DueDate, @Total
            FROM dbo.Enrollments AS e
            JOIN dbo.Students AS s ON s.Id = e.StudentId AND s.IsDeleted = 0 AND s.Status = 'Active'
            JOIN dbo.Sections AS sec ON sec.Id = e.SectionId
            WHERE sec.ClassId = @ClassId AND e.AcademicYearId = @AcademicYearId
              AND NOT EXISTS (SELECT 1 FROM dbo.FeeInvoices AS i
                              WHERE i.EnrollmentId = e.Id AND i.Status <> 'Cancelled');

            -- One item per fee line for each new invoice.
            INSERT INTO dbo.FeeInvoiceItems (FeeInvoiceId, FeeStructureId, Amount)
            SELECT n.Id, fs.Id, fs.Amount
            FROM @New AS n
            CROSS JOIN dbo.FeeStructures AS fs
            WHERE fs.ClassId = @ClassId AND fs.AcademicYearId = @AcademicYearId;

            SELECT (SELECT COUNT(*) FROM @New) AS Created,
                   (SELECT COUNT(*)
                    FROM dbo.Enrollments AS e
                    JOIN dbo.Students AS s ON s.Id = e.StudentId AND s.IsDeleted = 0 AND s.Status = 'Active'
                    JOIN dbo.Sections AS sec ON sec.Id = e.SectionId
                    WHERE sec.ClassId = @ClassId AND e.AcademicYearId = @AcademicYearId)
                   - (SELECT COUNT(*) FROM @New) AS AlreadyInvoiced,
                   (SELECT MIN(InvoiceNumber) FROM @New) AS FirstInvoiceNumber,
                   (SELECT MAX(InvoiceNumber) FROM @New) AS LastInvoiceNumber;
            """;

        await using SqlConnection connection = _connections.Create();
        await connection.OpenAsync(cancellationToken);
        await using SqlTransaction transaction = connection.BeginTransaction();
        try
        {
            InvoiceRun run = await connection.QuerySingleAsync<InvoiceRun>(new CommandDefinition(sql, new
            {
                ClassId = classId,
                AcademicYearId = academicYearId,
                InvoiceDate = invoiceDate,
                DueDate = dueDate
            }, transaction, commandTimeout: 120, cancellationToken: cancellationToken));
            await transaction.CommitAsync(cancellationToken);
            return run;
        }
        catch (SqlException ex) when (ex.Number == 50017)
        {
            // The lock above waited 30 seconds and another run still held it.
            throw new SchoolRuleException(50017, ex.Message, ex);
        }
        catch (SqlException ex) when (SqlErrors.IsUniqueKeyViolation(ex, "UQ_FeeInvoices_EnrollmentId_Active"))
        {
            // The final guard from 083-invoice-numbers.sql: an enrollment never gets two active invoices.
            throw new SchoolRuleException(50017, "Some of these students were invoiced at the same moment. Run it again.", ex);
        }
    }

    public async Task<List<StudentInvoiceDto>?> GetStudentInvoicesAsync(string admissionNumber, DateOnly today,
                                                                        CancellationToken cancellationToken = default)
    {
        const string sql = """
            SELECT COUNT(*) FROM dbo.Students WHERE AdmissionNumber = @AdmissionNumber AND IsDeleted = 0;

            SELECT i.InvoiceNumber, i.InvoiceDate, i.DueDate,
                   i.TotalAmount - i.DiscountAmount AS Total,
                   ISNULL(p.Paid, 0) AS Paid,
                   i.TotalAmount - i.DiscountAmount - ISNULL(p.Paid, 0) AS Balance,
                   i.Status
            FROM dbo.FeeInvoices AS i
            JOIN dbo.Enrollments AS e ON e.Id = i.EnrollmentId
            JOIN dbo.Students AS s ON s.Id = e.StudentId AND s.IsDeleted = 0
            OUTER APPLY (SELECT SUM(fp.Amount) AS Paid
                         FROM dbo.FeePayments AS fp
                         WHERE fp.FeeInvoiceId = i.Id AND fp.IsDeleted = 0) AS p
            WHERE s.AdmissionNumber = @AdmissionNumber
            ORDER BY i.InvoiceDate DESC, i.InvoiceNumber DESC;
            """;

        await using SqlConnection connection = _connections.Create();
        await using SqlMapper.GridReader results = await connection.QueryMultipleAsync(new CommandDefinition(sql,
            new { AdmissionNumber = new DbString { Value = admissionNumber, IsAnsi = true, Length = 15 } },
            cancellationToken: cancellationToken));
        if (await results.ReadSingleAsync<int>() == 0)
        {
            return null;
        }

        // Overdue: still owed, and the due date has passed.
        return (await results.ReadAsync<InvoiceRow>())
            .Select(r => new StudentInvoiceDto(r.InvoiceNumber, r.InvoiceDate, r.DueDate, r.Total, r.Paid, r.Balance, r.Status,
                                               IsOverdue: r.Balance > 0 && r.Status != "Cancelled" && r.DueDate < today))
            .ToList();
    }

    public async Task<OutstandingReportDto> GetOutstandingAsync(int? classId, DateOnly today, int page, int pageSize,
                                                                CancellationToken cancellationToken = default)
    {
        if (page < 1)
        {
            throw new ArgumentOutOfRangeException(nameof(page), "Page numbers start at 1.");
        }
        if (pageSize < 1 || pageSize > Paging.MaxPageSize)
        {
            throw new ArgumentOutOfRangeException(nameof(pageSize), $"The page size must be between 1 and {Paging.MaxPageSize}.");
        }

        // The same FROM and WHERE for the totals and for the page; the class filter only when it is given.
        StringBuilder from = new StringBuilder("""
            FROM dbo.FeeInvoices AS i
            JOIN dbo.Enrollments AS e ON e.Id = i.EnrollmentId
            JOIN dbo.Students AS s ON s.Id = e.StudentId AND s.IsDeleted = 0
            JOIN dbo.Sections AS sec ON sec.Id = e.SectionId
            JOIN dbo.Classes AS c ON c.Id = sec.ClassId
            OUTER APPLY (SELECT SUM(fp.Amount) AS Paid
                         FROM dbo.FeePayments AS fp
                         WHERE fp.FeeInvoiceId = i.Id AND fp.IsDeleted = 0) AS p
            WHERE i.Status IN ('Unpaid', 'PartiallyPaid')
            """);
        if (classId is not null)
        {
            from.Append(" AND sec.ClassId = @ClassId");
        }
        const string balance = "(i.TotalAmount - i.DiscountAmount - ISNULL(p.Paid, 0))";

        string sql = $"""
            SELECT COUNT(*) AS Invoices,
                   SUM(CASE WHEN i.DueDate < @Today THEN 1 ELSE 0 END) AS Overdue,
                   ISNULL(SUM({balance}), 0) AS TotalOutstanding
            {from};

            SELECT i.InvoiceNumber, s.AdmissionNumber, s.FirstName + ' ' + s.LastName AS StudentName,
                   c.Name AS ClassName, sec.Name AS SectionName, i.DueDate,
                   {balance} AS Balance,
                   CASE WHEN i.DueDate < @Today THEN DATEDIFF(DAY, i.DueDate, @Today) ELSE 0 END AS DaysOverdue
            {from}
            ORDER BY Balance DESC, i.InvoiceNumber
            OFFSET @Skip ROWS FETCH NEXT @Take ROWS ONLY;
            """;

        await using SqlConnection connection = _connections.Create();
        await using SqlMapper.GridReader results = await connection.QueryMultipleAsync(new CommandDefinition(sql, new
        {
            ClassId = classId,
            Today = today,
            Skip = (page - 1) * pageSize,
            Take = pageSize
        }, cancellationToken: cancellationToken));

        Totals totals = await results.ReadSingleAsync<Totals>();
        List<OutstandingInvoiceDto> items = (await results.ReadAsync<OutstandingRow>())
            .Select(r => new OutstandingInvoiceDto(r.InvoiceNumber, r.AdmissionNumber, r.StudentName, r.ClassName,
                                                   r.SectionName, r.DueDate, r.Balance, r.DaysOverdue))
            .ToList();
        return new OutstandingReportDto(totals.Invoices, totals.Overdue ?? 0, totals.TotalOutstanding,
                                        new PagedResult<OutstandingInvoiceDto>(items, page, pageSize, totals.Invoices));
    }

    // Rows with DATE columns land in classes first (Lessons 58 and 81).
    private sealed class InvoiceRow
    {
        public string InvoiceNumber { get; set; } = "";
        public DateOnly InvoiceDate { get; set; }
        public DateOnly DueDate { get; set; }
        public decimal Total { get; set; }
        public decimal Paid { get; set; }
        public decimal Balance { get; set; }
        public string Status { get; set; } = "";
    }

    private sealed class OutstandingRow
    {
        public string InvoiceNumber { get; set; } = "";
        public string AdmissionNumber { get; set; } = "";
        public string StudentName { get; set; } = "";
        public string ClassName { get; set; } = "";
        public string SectionName { get; set; } = "";
        public DateOnly DueDate { get; set; }
        public decimal Balance { get; set; }
        public int DaysOverdue { get; set; }
    }

    // SUM over no rows is NULL, so Overdue is nullable; COUNT is never NULL.
    private sealed class Totals
    {
        public int Invoices { get; set; }
        public int? Overdue { get; set; }
        public decimal TotalOutstanding { get; set; }
    }
}

GenerateInvoicesAsync sends one batch in one transaction. It starts by taking the class's application lock, then:

An INSERT ... SELECT creates an invoice for every active student without one. Its OUTPUT ... INTO @New remembers each new Id and number. The table has no trigger, so OUTPUT is allowed here (Lesson 23).
INSERT ... SELECT with CROSS JOIN adds one item per fee line for each new invoice.
One SELECT reports how many were created, how many students already had one, and the number range.

The outstanding report sends two statements with the same FROM and WHERE: one for the totals, one for the page. Dapper's QueryMultiple reads both from one round trip.

Step 5: the controller

Show src/SchoolApp.Api/Controllers/FeeBillingController.cs

src/SchoolApp.Api/Controllers/FeeBillingController.cs
using System.ComponentModel.DataAnnotations;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Options;
using SchoolApp.Api.Models;
using SchoolApp.Api.Options;
using SchoolApp.Core.Common;
using SchoolApp.Core.Dtos;
using SchoolApp.Core.Interfaces;
using SchoolApp.Core.Services;

namespace SchoolApp.Api.Controllers;

// Lesson 83: fee structures, billing a whole class, a student's invoices, and the outstanding report.
//   GET  /api/fee-structures?classId=10
//   POST /api/fee-invoices/generate                  { "classId": 10, "dueDate": "2026-10-31" }
//   GET  /api/fee-invoices/outstanding?classId=10&page=1&pageSize=20
//   GET  /api/students/{admissionNumber}/fee-invoices
// (Single invoices and payments stay in FeeInvoicesController, Lesson 74.)
[ApiController]
[Produces("application/json")]
[ProducesErrorResponseType(typeof(ApiResponse))]
public class FeeBillingController : ControllerBase
{
    private readonly FeeBillingService _service;
    private readonly IFeeBillingRepository _billing;
    private readonly SchoolOptions _school;

    public FeeBillingController(FeeBillingService service, IFeeBillingRepository billing, IOptions<SchoolOptions> school)
    {
        _service = service;
        _billing = billing;
        _school = school.Value;
    }

    /// <summary>Gets a class's fees for the current academic year.</summary>
    /// <response code="200">Each fee line and the total.</response>
    /// <response code="404">The class has no fees set up this year.</response>
    [HttpGet("/api/fee-structures")]
    [ProducesResponseType(StatusCodes.Status200OK)]
    [ProducesResponseType(StatusCodes.Status404NotFound)]
    public async Task<ActionResult<ApiResponse<FeeStructureDto>>> GetStructure([FromQuery, Required, Range(1, 10)] int classId,
                                                                               CancellationToken cancellationToken)
    {
        FeeStructureDto? structure = await _service.GetStructureAsync(classId, cancellationToken);
        if (structure is null)
        {
            return NotFound(ApiResponse.Fail($"Class {classId} has no fees set up this year.", 50015));
        }
        return ApiResponse.Ok(structure, $"Class {classId} fees for {structure.AcademicYear}.");
    }

    /// <summary>Creates this year's invoice for every active student of a class who does not have one yet.</summary>
    /// <remarks>Safe to run again: students who already have an active invoice are skipped.</remarks>
    /// <response code="200">How many invoices were created, and their number range.</response>
    /// <response code="400">The request is invalid.</response>
    /// <response code="409">Some students were invoiced at the same moment (50017).</response>
    /// <response code="422">No fees for the class (50015), or the due date is out of range (50016).</response>
    [HttpPost("/api/fee-invoices/generate")]
    [ProducesResponseType(StatusCodes.Status200OK)]
    [ProducesResponseType(StatusCodes.Status400BadRequest)]
    [ProducesResponseType(StatusCodes.Status409Conflict)]
    [ProducesResponseType(StatusCodes.Status422UnprocessableEntity)]
    public async Task<ApiResponse<GenerateInvoicesResult>> Generate(GenerateInvoicesRequest request, CancellationToken cancellationToken)
    {
        GenerateInvoicesResult result = await _service.GenerateAsync(request, cancellationToken);
        return ApiResponse.Ok(result, $"{result.Created} invoices created; {result.AlreadyInvoiced} students already had one.");
    }

    /// <summary>Every invoice still owed, largest balance first, with totals.</summary>
    [HttpGet("/api/fee-invoices/outstanding")]
    [ProducesResponseType(StatusCodes.Status200OK)]
    [ProducesResponseType(StatusCodes.Status400BadRequest)]
    public async Task<ApiResponse<OutstandingReportDto>> GetOutstanding(
        [FromQuery, Range(1, 10)] int? classId = null,
        [FromQuery, Range(1, int.MaxValue)] int page = 1,
        [FromQuery, Range(1, Paging.MaxPageSize)] int? pageSize = null,
        CancellationToken cancellationToken = default)
    {
        OutstandingReportDto report = await _billing.GetOutstandingAsync(classId, _service.Today, page,
                                                                         pageSize ?? _school.DefaultPageSize, cancellationToken);
        return ApiResponse.Ok(report, $"{report.Invoices} invoices outstanding, {report.Overdue} overdue.");
    }

    /// <summary>A student's invoices, newest first, with what is paid and what is still owed.</summary>
    /// <response code="200">The invoices; an empty list when the student has none.</response>
    /// <response code="404">No student has this admission number.</response>
    [HttpGet("/api/students/{admissionNumber}/fee-invoices")]
    [ProducesResponseType(StatusCodes.Status200OK)]
    [ProducesResponseType(StatusCodes.Status404NotFound)]
    public async Task<ActionResult<ApiResponse<List<StudentInvoiceDto>>>> GetStudentInvoices(string admissionNumber,
                                                                                             CancellationToken cancellationToken)
    {
        List<StudentInvoiceDto>? invoices = await _billing.GetStudentInvoicesAsync(admissionNumber, _service.Today, cancellationToken);
        if (invoices is null)
        {
            return NotFound(ApiResponse.Fail($"No student has admission number {admissionNumber}.", 50030));
        }
        return ApiResponse.Ok(invoices, $"Invoices found: {invoices.Count}.");
    }
}

GET /api/fee-invoices/outstanding and GET /api/fee-invoices/{invoiceNumber} from Lesson 74 share the prefix. Routing prefers the fixed word "outstanding" over a parameter, so both work.

Step 6: bill Class 10, twice

Class 10 has 1,006 students. Six of them (10-A and 10-B) were billed in June, in the sample data:

Output
GET /api/fee-structures?classId=10
"lines":[{"feeType":"Tuition","amount":42000.00},{"feeType":"Exam","amount":2000.00},{"feeType":"Library","amount":1500.00}],"total":45500.00

POST /api/fee-invoices/generate   {"classId":10,"dueDate":"2026-10-31"}                           [200, 0.30 s]
{"status":"success","message":"1000 invoices created; 6 students already had one.","data":{"classId":10,"academicYear":"2026-27","invoiceDate":"2026-10-08","dueDate":"2026-10-31","amountEach":45500.00,"created":1000,"alreadyInvoiced":6,"firstInvoiceNumber":"INV-2026-002013","lastInvoiceNumber":"INV-2026-003012"}}

POST (the same request again)                                                                  [200, 0.09 s]
{"status":"success","message":"0 invoices created; 1006 students already had one.", ... "created":0,"alreadyInvoiced":1006,"firstInvoiceNumber":null,"lastInvoiceNumber":null}}

A thousand invoices and 3,000 items took 0.30 seconds. The second click created nothing.

Notice the first number: 2013, not 13. The integration tests had already billed Classes 3 and 4, using 2,000 numbers, and then deleted those invoices. The sequence never gives numbers back, which is exactly the gap the concept section described.

Step 7: a student's invoices, and the report

Output
GET /api/students/ADM-2017-0002/fee-invoices   (Diya)
[{"invoiceNumber":"INV-2026-000002","invoiceDate":"2026-06-01","dueDate":"2026-06-30","total":45500.00,"paid":20000.00,"balance":25500.00,"status":"PartiallyPaid","isOverdue":true}]

GET /api/fee-invoices/outstanding?classId=10&pageSize=3   (before billing)
"invoices":5,"overdue":5,"totalOutstanding":207500.00   first: INV-2026-000003, Kabir Singh, 45500.00, 100 days overdue

GET /api/fee-invoices/outstanding?classId=10&pageSize=3   (after billing)
"invoices":1005,"overdue":5,"totalOutstanding":45707500.00, "totalPages":335

Before billing, Class 10 owed 207,500 on five June invoices, all 100 days overdue. After billing, it owed 45,707,500, but still only five were overdue, because the new invoices are due on 2026-10-31.

Step 8: every rule

Request	Status	Message
Due date 2026-09-30	422, 50016	The due date must be between 2026-10-08 and the end of 2026-27 (2027-03-31).
Due date 2027-04-15	422, 50016	(the same message)
`classId` 11	400	The field ClassId must be between 1 and 10.
An unknown student's invoices	404, 50030	No student has admission number ADM-1999-0001.

Every class has fees in the sample data, so 50015 is shown by a unit test instead. All demo invoices were removed from SchoolDb afterwards.

Step 9: tests

FeeBillingServiceTests checks a valid run and the 50015 and 50016 rules, with "today" fixed at 2026-10-08.

The integration tests in FeeBillingTests bill real classes in SchoolDb. Class 3 billed twice gives 1,000 invoices, then 0, with 3,000 items.

Class 4 billed by two clerks at the same moment gives 1,000 invoices in total, not 2,000. The suite has 82 tests, all passing.

Example: the deadlock the tests found later

The first version of this lesson guarded the NOT EXISTS check with the hints UPDLOCK, HOLDLOCK instead of an application lock. Alone, the two-clerk test always passed. Two lessons later, with more tests running at the same time, it sometimes failed:

Output
Microsoft.Data.SqlClient.SqlException : Transaction (Process ID 71) was deadlocked on lock | generic waitable
object resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Both runs had locked ranges of FeeInvoices, and each then needed to insert into a range the other held. SQL Server cancelled one of them. The fix is the application lock: the second run now waits at the very start, before it locks anything else.

After the fix, the whole suite passed ten times in a row, and two billing requests sent at once over HTTP answered "1000 invoices created" and "0 invoices created". The fixed code is the tag lesson-83-fees-v2; warm runs bill a class of 1,000 in about 0.22 seconds.

Common mistakes

A loop of single inserts. Thousands of round trips, and half-billed classes on failure. Fix: INSERT ... SELECT in one transaction.
Checking "already billed" without a lock. Two clerks both see "not billed" and both insert. Fix: one run per class with sp_getapplock, plus a unique index as the final guard.
MAX(number) + 1 for document numbers. Two requests get the same number. Fix: a sequence.
Treating gaps as errors. Rolled-back work always leaves gaps. Fix: check for duplicates, not gaps.
Totals computed from one page. The report's total changes with the page size. Fix: a separate totals statement over the whole filter.

In real projects

From 16 years of .NET projects

A school's billing screen created invoices in a C# loop, one request per student. One afternoon the server restarted halfway through Class 8.

Half the class had invoices; the other half did not. The clerk clicked "Generate" again, and the first half got a second invoice each.

We moved billing into one set-based transaction that skips students already billed, and added the unique index. A restart now leaves either the whole class billed or none of it, and clicking again is harmless.

Best practices

Use set-based SQL for work on many rows; keep per-row loops for small, rare jobs.
Make batch endpoints safe to repeat, and report what was skipped.
Number documents with a sequence, never with MAX + 1.
Back every "only one" rule with a unique index.
Compute report totals over the whole filter, not over the current page.

Recap

POST /api/fee-invoices/generate billed 1,000 students in 0.30 seconds.
A second run created nothing; two clerks at once still made one invoice per student.
The InvoiceNumbers sequence numbers invoices; its gaps are normal.
The outstanding report gives totals and a page in one round trip.
Rules 50015 to 50017 cover missing fees, bad due dates, and a rare clash.

Homework

Library module task: Add GET /api/library/fines?asOf=&page=&pageSize= to a single-file library API. It returns the number of overdue loans and the total fines as on asOf, plus one page of overdue loans, largest fine first. Use one round trip with QueryMultiple, and a fine of 2 per day late.

A correct result looks like:

Output
GET /api/library/fines?asOf=2026-08-01
{"status":"success","message":"Overdue loans: 1. Fines due: 24.","data":{"asOf":"2026-08-01","overdueLoans":1,"totalFines":24,"page":1,"pageSize":20,"items":[{"title":"The Jungle Book","admissionNumber":"ADM-2021-0001","studentName":"Advika Sharma","dueOn":"2026-07-20","daysLate":12,"fine":24}]}}
GET /api/library/fines?asOf=2026-10-08&page=2&pageSize=1
... "overdueLoans":1,"totalFines":160,"page":2,"pageSize":1,"items":[]

Show the solution

library-assignment/083-library-fines-report.cs
// Library homework, Lesson 83: the library's "outstanding" report, built like the fees report. Totals for
// everything still owed, plus one page of the loans, largest fine first, in one round trip (QueryMultiple).
// Run with:  dotnet run library-assignment/083-library-fines-report.cs
#:project ../src/SchoolApp.Data/SchoolApp.Data.csproj
#:sdk Microsoft.NET.Sdk.Web
#:property PublishAot=false
using System.ComponentModel.DataAnnotations;
using System.Text.Json.Serialization;
using Dapper;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Data.SqlClient;
using SchoolApp.Data;

WebApplicationBuilder builder = WebApplication.CreateBuilder(args);
builder.Services.AddControllers();
builder.Services.AddSingleton(SqlConnectionFactory.FromEnvironment());
WebApplication app = builder.Build();
app.MapControllers();
app.Run("http://localhost:5083");

public class ApiResponse<T>
{
    public string Status { get; init; } = "success";
    public string Message { get; init; } = "";
    [JsonPropertyOrder(1)]
    public T? Data { get; init; }
}

// One overdue loan. A class: DATE columns into DateOnly (Lesson 58).
public class OverdueLoan
{
    public string Title { get; set; } = "";
    public string AdmissionNumber { get; set; } = "";
    public string StudentName { get; set; } = "";
    public DateOnly DueOn { get; set; }
    public int DaysLate { get; set; }
    public decimal Fine { get; set; }
}

public class FinesTotals
{
    public int OverdueLoans { get; set; }
    public decimal TotalFines { get; set; }
}

public record FinesReport(DateOnly AsOf, int OverdueLoans, decimal TotalFines, int Page, int PageSize, IReadOnlyList<OverdueLoan> Items);

[ApiController]
[Route("api/library/fines")]
public class FinesController : ControllerBase
{
    private readonly SqlConnectionFactory _connections;

    public FinesController(SqlConnectionFactory connections)
    {
        _connections = connections;
    }

    // GET /api/library/fines?asOf=2026-08-01&page=1&pageSize=20
    [HttpGet]
    public async Task<ApiResponse<FinesReport>> Get([FromQuery, Required] DateOnly asOf,
                                                    [FromQuery, Range(1, int.MaxValue)] int page = 1,
                                                    [FromQuery, Range(1, 100)] int pageSize = 20)
    {
        // The same FROM and WHERE for both statements: books not returned and past their due date.
        const string from = """
            FROM dbo.BookIssues AS i
            JOIN dbo.BookCopies AS c ON c.Id = i.BookCopyId
            JOIN dbo.Books AS b ON b.Id = c.BookId
            JOIN dbo.Students AS s ON s.Id = i.StudentId AND s.IsDeleted = 0
            WHERE i.ReturnedOn IS NULL AND i.DueOn < @AsOf
            """;
        string sql = $"""
            SELECT COUNT(*) AS OverdueLoans, ISNULL(SUM(DATEDIFF(DAY, i.DueOn, @AsOf) * 2), 0) AS TotalFines
            {from};

            SELECT b.Title, s.AdmissionNumber, s.FirstName + ' ' + s.LastName AS StudentName, i.DueOn,
                   DATEDIFF(DAY, i.DueOn, @AsOf) AS DaysLate,
                   DATEDIFF(DAY, i.DueOn, @AsOf) * 2 AS Fine
            {from}
            ORDER BY Fine DESC, b.Title
            OFFSET @Skip ROWS FETCH NEXT @Take ROWS ONLY;
            """;

        await using SqlConnection connection = _connections.Create();
        await using SqlMapper.GridReader results = await connection.QueryMultipleAsync(sql,
            new { AsOf = asOf, Skip = (page - 1) * pageSize, Take = pageSize });
        FinesTotals totals = await results.ReadSingleAsync<FinesTotals>();
        List<OverdueLoan> items = (await results.ReadAsync<OverdueLoan>()).ToList();

        return new ApiResponse<FinesReport>
        {
            Message = $"Overdue loans: {totals.OverdueLoans}. Fines due: {totals.TotalFines}.",
            Data = new FinesReport(asOf, totals.OverdueLoans, totals.TotalFines, page, pageSize, items)
        };
    }
}

Page 2 is empty, but the totals still count everything: they come from their own statement. Without asOf, the request gets a 400. In this small file it uses the default error shape; Lesson 80's homework shows how to wrap it too.

Quiz

Why bill a class with INSERT ... SELECT instead of a loop in C#?
1. C# cannot insert rows
2. One statement handles every row in one round trip and one transaction
3. Loops are not allowed in APIs
4. It avoids the need for a transaction
Show answer
Answer: B. One statement handles every row in one round trip and one transaction 1,000 invoices and 3,000 items took 0.30 seconds.
What happened when the same billing request was sent twice?
1. 2,000 invoices
2. The second run created 0 and reported 1,006 already billed
3. An error 409
4. The first invoices were replaced
Show answer
Answer: B. The second run created 0 and reported 1,006 already billed NOT EXISTS skips students who already have an active invoice.
Why did the first new invoice number start at 2013?
1. A bug
2. Earlier test runs used 2,000 sequence numbers, and a sequence never gives numbers back
3. The seed data
4. Class 10 starts at 2000
Show answer
Answer: B. Earlier test runs used 2,000 sequence numbers, and a sequence never gives numbers back Gaps are normal; duplicates are what to check for.
What stops two clerks from billing the same students at the same moment?
1. Nothing
2. An application lock per class, and a filtered unique index as the final guard
3. The API runs one request at a time
4. The sequence
Show answer
Answer: B. An application lock per class, and a filtered unique index as the final guard The test with two clerks made 1,000 invoices, not 2,000.
Why does the outstanding report have a separate totals statement?
1. For speed only
2. Totals must cover the whole filter, not just the current page
3. QueryMultiple needs two statements
4. To sort the page
Show answer
Answer: B. Totals must cover the whole filter, not just the current page Page 2 of the library report was empty, yet the total still counted every loan.

Interview questions

Questions are grouped by experience level. Click a question to see a model answer. All questions: Web API interview questions.

Fresher (0-2 yrs)

F What is set-based SQL, and why use it to create invoices for a whole class? concept

Set-based SQL handles many rows in one statement, such as INSERT ... SELECT, instead of one statement per row in a loop. The SMS bills a class of 1,000 students with two inserts and a summary in one transaction and one round trip, in about 0.3 seconds, instead of about 4,000 separate statements.

See the example · Permalink

F What is a SQL Server sequence, and why use one for invoice numbers? concept

A sequence is a database object that hands out the next number each time NEXT VALUE FOR is called, safely across many users at once. MAX(number) + 1 can give two requests the same number; a sequence never does. The SMS formats the value as INV-2026-NNNNNN.

See the example · Permalink

F Why are there gaps in invoice numbers, and is that a problem? concept

A sequence never takes a number back, even when the transaction that used it rolls back or the row is later deleted. In the SMS, test runs used 2,000 numbers, so the next real invoice was INV-2026-002013. Gaps are normal; what must never happen is a duplicate, which the unique constraint on InvoiceNumber prevents.

See the example · Permalink

Mid (3-5 yrs)

M How do you make a batch endpoint, such as 'bill this class', safe to run twice? design

Let the batch skip what is already done and report it. The SMS insert uses NOT EXISTS to skip students who already have an active invoice; the second run reported 0 created and 1,006 already invoiced. A unique index (one active invoice per enrollment) is the final guard.

See the example · Permalink

M How do you add an item row for each new invoice created by an INSERT ... SELECT? coding

Capture the new rows with OUTPUT INSERTED.Id INTO a table variable, then insert the items with INSERT ... SELECT from that table variable joined to the fee lines (a CROSS JOIN, since every invoice gets every line). This works because FeeInvoices has no trigger; on a table with triggers, OUTPUT must use INTO, as here.

See the example · Permalink

M How do you build a paged report that also shows totals for everything, in one round trip? coding

Send two statements with the same FROM and WHERE: one returns COUNT and SUM over the whole filter, the other returns one page with ORDER BY and OFFSET/FETCH. Read both with Dapper's QueryMultiple. Totals computed from the page alone would change with the page size.

See the example · Permalink

Senior (6-9 yrs)

S A server restart halfway through billing left half a class with invoices; a retry then billed the first half twice. How do you fix it? scenario

Repair first: find enrollments with two active invoices and cancel the duplicates, with an audit note. Then fix the design: bill the class in one set-based transaction, so a failure leaves nothing half-done; skip students already billed, so a retry is harmless; and add a filtered unique index so the database refuses a second active invoice.

How to answer: Separate the data repair from the design fix, and explain how atomicity, idempotency, and a database constraint each prevent a part of the problem.

See the example · Permalink

S Two clerks click 'Generate invoices' for the same class at the same moment. What happens in your design? scenario

Each run first takes an application lock for the class (sp_getapplock), so the second clerk waits until the first commits, then its NOT EXISTS check finds every student already billed and creates nothing. If anything slipped past, the filtered unique index would refuse the duplicate and the API would answer 409 (50017). The first SMS version used UPDLOCK, HOLDLOCK on the check instead, and under load two runs could deadlock (error 1205); the application lock removed that.

How to answer: Walk through both transactions step by step, name the lock and the constraint, and say what the second clerk sees.

See the example · Permalink

Architect / Lead (10+ yrs)

A Billing must run for 1,000 schools at the start of each term. How would you design it? design

Run billing as background jobs per school and class, not inside web requests. Each job is set-based, one transaction, and idempotent, so failed jobs can simply be retried. Record each run (who, when, counts) for audit, spread the load over time, give each school its own number series, and notify the office when its run is done. Monitor failures and totals per school.

How to answer: Cover moving work off the request path, idempotent retries, data partitioning per school, numbering, auditing, and monitoring.

See the example · Permalink

Resources

Code for this lesson: git tag lesson-83-fees-v2 (code repository coming soon)
Files: database/083-invoice-numbers.sql, src/SchoolApp.Core/Services/FeeBillingService.cs, src/SchoolApp.Data/Dapper/DapperFeeBillingRepository.cs, src/SchoolApp.Api/Controllers/FeeBillingController.cs, tests/SchoolApp.Tests/Integration/FeeBillingTests.cs, library-assignment/083-library-fines-report.cs
Video: coming soon

What's next

Students, attendance, marks, and fees all have endpoints. Next: the office dashboard, which brings their numbers together in one fast request. Lesson 84: The dashboard API

Want live sessions, code reviews, and interview preparation? Join the next batch at Sahasra Technologies.

Introduction​

Why this matters​

The concept​

Example: the fee data​

Example: row by row or set-based​

Example: safe to repeat, safe at the same time​

Example: invoice numbers from a sequence​

Apply it to the SMS​

Step 1: the sequence and the guard index​

Step 2: the requests and results​

Step 3: the rules​

Step 4: billing in three statements​

Step 5: the controller​

Step 6: bill Class 10, twice​

Step 7: a student's invoices, and the report​

Step 8: every rule​

Step 9: tests​

Example: the deadlock the tests found later​

Common mistakes​

In real projects​

Best practices​

Recap​

Homework​

Quiz​

Interview questions​

Fresher (0-2 yrs)​

Mid (3-5 yrs)​

Senior (6-9 yrs)​

Architect / Lead (10+ yrs)​

Resources​

What's next​

Introduction

Why this matters

The concept

Example: the fee data

Example: row by row or set-based

Example: safe to repeat, safe at the same time

Example: invoice numbers from a sequence

Apply it to the SMS

Step 1: the sequence and the guard index

Step 2: the requests and results

Step 3: the rules

Step 4: billing in three statements

Step 5: the controller

Step 6: bill Class 10, twice

Step 7: a student's invoices, and the report

Step 8: every rule

Step 9: tests

Example: the deadlock the tests found later

Common mistakes

In real projects

Best practices

Recap

Homework

Quiz

Interview questions

Fresher (0-2 yrs)

Mid (3-5 yrs)

Senior (6-9 yrs)

Architect / Lead (10+ yrs)

Resources

What's next