Published / updated29 Aug 2026

Repository Project

Before you start

You need: all of Articles 01–09, and the schema from Track 06.

In Visual Studio: a Class Library for the repositories plus a Console App or test project to exercise them.

Time: 6–10 hours.

Goal

Demonstrate that you can organise data access behind a clear boundary, keep transactions and business rules in the right layer, and document the complete flow from a method call to a row.

Assignment

Build the data-access layer for the NexCoding Academy school system. Deliver a class library plus a console harness, and one document.

Deliverable	Contents
Domain/	Entities and enums, no dependencies
Data/Abstractions/	Repository interfaces
Data/Dapper/	Dapper implementations
Data/Ado/	One repository written in raw ADO.NET, for comparison
Services/	Operations that span repositories, owning transactions
Tests/	Unit tests for mapping and integration tests for CRUD
Harness/	Console app exercising every method
DATAFLOW.md	The documented flow and decisions

Required repositories

public interface IStudentRepository
{
    Task<PagedResult<Student>> SearchAsync(
        int schoolId, string? term, string? className, int page, int pageSize, CancellationToken ct);

    Task<Student?> GetByPublicIdAsync(int schoolId, Guid publicId, CancellationToken ct);

    Task<bool> RollNumberExistsAsync(
        int schoolId, string rollNumber, Guid? excludePublicId, CancellationToken ct);

    Task<Guid> CreateAsync(Student student, CancellationToken ct);
    Task UpdateAsync(Student student, CancellationToken ct);
    Task DeactivateAsync(int schoolId, Guid publicId, CancellationToken ct);
}

Plus IFeeRepository (accounts, payments, outstanding summary) and IExamRepository (exams, results, bulk save).

Required service operation

EnrolmentService.EnrolStudentAsync must, atomically: create the student, create their fee account for the current academic year, and write an AuditLog row. A failure at any point must leave none of them.

Required reports

1. Paged student search with an optional term and class filter.
2. Students with fees outstanding, excluding cancelled payments.
3. A student dashboard — details, recent results and fee account — in one round trip.
4. Exam results for a class with student and subject, in one query.
5. Monthly fee collection totals.

Worked example: the boundary

A repository takes and returns domain objects, and exposes nothing about how they are stored.

// Wrong — leaks ADO.NET to the caller
Task<SqlDataReader> GetStudentsAsync(int schoolId);
Task<DataTable> GetStudentsAsync(int schoolId);

// Right
Task<PagedResult<Student>> SearchAsync(int schoolId, ..., CancellationToken ct);

The test: could you rewrite the implementation in raw ADO.NET without changing one line of calling code? If not, the abstraction leaks. That is exactly why the assignment asks for one repository in both styles.

Where things belong

Concern	Layer
SQL text	Repository
Parameter binding and mapping	Repository
Tenant filter in the WHERE clause	Repository
Translating SqlException to a domain exception	Repository
Transaction boundary	Service
Business rules spanning entities	Service
Authorisation	Above the service

The tenant filter is in the repository deliberately. Putting it in the service means every future service method must remember it; putting it in the SQL means no caller can omit it.

Worked example: implementation

public sealed class StudentRepository : IStudentRepository
{
    private readonly ISqlConnectionFactory _connectionFactory;

    public StudentRepository(ISqlConnectionFactory connectionFactory)
    {
        _connectionFactory = connectionFactory;
    }

    public async Task<Student?> GetByPublicIdAsync(
        int schoolId, Guid publicId, CancellationToken ct)
    {
        const string sql = @"
            SELECT s.Id, s.PublicId, s.SchoolId, s.Name, s.RollNumber, s.ClassName,
                   s.Section, s.DateOfBirth, s.ParentName, s.ParentPhone, s.Address, s.Status
            FROM   dbo.Student AS s
            WHERE  s.SchoolId = @SchoolId AND s.PublicId = @PublicId;";

        using (SqlConnection connection = _connectionFactory.Create())
        {
            CommandDefinition command = new CommandDefinition(
                sql, new { SchoolId = schoolId, PublicId = publicId }, cancellationToken: ct);

            return await connection.QuerySingleOrDefaultAsync<Student>(command);
        }
    }

    public async Task<Guid> CreateAsync(Student student, CancellationToken ct)
    {
        const string sql = @"
            INSERT INTO dbo.Student (PublicId, SchoolId, Name, RollNumber, ClassName, Section,
                                     DateOfBirth, ParentName, ParentPhone, Address, Status)
            VALUES (@PublicId, @SchoolId, @Name, @RollNumber, @ClassName, @Section,
                    @DateOfBirth, @ParentName, @ParentPhone, @Address, @Status);";

        student.PublicId = Guid.NewGuid();

        using (SqlConnection connection = _connectionFactory.Create())
        {
            try
            {
                await connection.ExecuteAsync(new CommandDefinition(sql, student, cancellationToken: ct));
            }
            catch (SqlException ex) when (ex.Number == 2627 || ex.Number == 2601)
            {
                throw new DuplicateRollNumberException(student.RollNumber, ex);
            }
        }

        return student.PublicId;
    }
}

A connection factory rather than a raw connection string keeps the repository testable and puts the string in one place:

public interface ISqlConnectionFactory
{
    SqlConnection Create();
}

public sealed class SqlConnectionFactory : ISqlConnectionFactory
{
    private readonly string _connectionString;

    public SqlConnectionFactory(IConfiguration configuration)
    {
        _connectionString = configuration.GetConnectionString("SchoolDb")
            ?? throw new InvalidOperationException("Connection string 'SchoolDb' is not configured.");
    }

    public SqlConnection Create()
    {
        return new SqlConnection(_connectionString);
    }
}

Worked example: the transaction boundary

Repository methods must be composable, so they cannot own transactions. Add overloads that accept an existing connection and transaction:

public interface IStudentRepository
{
    Task<Guid> CreateAsync(Student student, CancellationToken ct);

    Task<Guid> CreateAsync(
        SqlConnection connection, SqlTransaction transaction, Student student, CancellationToken ct);
}

public sealed class EnrolmentService
{
    private readonly ISqlConnectionFactory _connectionFactory;
    private readonly IStudentRepository _studentRepository;
    private readonly IFeeRepository _feeRepository;
    private readonly IAuditRepository _auditRepository;

    public async Task<Guid> EnrolStudentAsync(
        Student student, decimal totalFees, string academicYear,
        string performedBy, CancellationToken ct)
    {
        bool exists = await _studentRepository.RollNumberExistsAsync(
            student.SchoolId, student.RollNumber, null, ct);

        if (exists)
        {
            throw new DuplicateRollNumberException(student.RollNumber);
        }

        using (SqlConnection connection = _connectionFactory.Create())
        {
            await connection.OpenAsync(ct);

            using (SqlTransaction transaction =
                   (SqlTransaction)await connection.BeginTransactionAsync(ct))
            {
                try
                {
                    Guid publicId = await _studentRepository.CreateAsync(
                        connection, transaction, student, ct);

                    FeeAccount account = new FeeAccount
                    {
                        SchoolId = student.SchoolId,
                        StudentId = student.Id,
                        AcademicYear = academicYear,
                        TotalFees = totalFees,
                        DueDate = DateTime.UtcNow.AddMonths(1)
                    };

                    await _feeRepository.CreateAccountAsync(connection, transaction, account, ct);

                    await _auditRepository.WriteAsync(
                        connection, transaction,
                        new AuditLog
                        {
                            SchoolId = student.SchoolId,
                            Action = "EnrolStudent",
                            EntityName = nameof(Student),
                            EntityId = student.Id,
                            ChangedBy = performedBy,
                            ChangedAt = DateTime.UtcNow
                        },
                        ct);

                    await transaction.CommitAsync(ct);

                    return publicId;
                }
                catch
                {
                    await transaction.RollbackAsync(ct);
                    throw;
                }
            }
        }
    }
}

Three points a reviewer will look for:

• The duplicate check runs before BeginTransaction. A rejected enrolment takes no locks. The unique constraint still wins any race.
• Every repository call receives the same connection and transaction. Omitting it on one means that write is not rolled back.
• The service owns the boundary. The repositories know nothing about atomicity.

Worked example: testing

Unit-test the mapping without a database, using an in-memory provider or a fake:

[Fact]
public void MapStudent_AbsentResult_LeavesMarksNull()
{
    // A NULL MarksObtained must map to null, never to zero
}

Integration-test the SQL against a real database. Mapping bugs, splitOn mistakes and tenant-filter omissions only appear against SQL Server:

[Fact]
public async Task GetByPublicIdAsync_OtherSchoolsStudent_ReturnsNull()
{
    Guid publicId = await SeedStudentAsync(schoolId: 2);

    Student? student = await _repository.GetByPublicIdAsync(
        schoolId: 1, publicId, CancellationToken.None);

    Assert.Null(student);
}

That test is the most valuable one in the suite. It fails the moment someone writes a query without the tenant filter.

Each integration test should run in a transaction that is rolled back afterwards, so tests do not depend on each other's data.

Submission template

DATAFLOW.md

Layering:
  Project list and reference direction:
  What each layer may and may not know:

The complete flow (one operation, end to end):
  Caller → service → repository → SQL → SQL Server → rows → objects → caller:
  Where the tenant filter is applied:
  Where mapping happens:
  Where the transaction begins and ends:

Repository inventory:
  Interface → methods → what each returns and when it returns null:

ADO.NET versus Dapper:
  The repository written both ways:
  Line count for each:
  What Dapper removed:
  What you still had to decide yourself:

Parameter decisions:
  Where DbType.AnsiString was needed and why:
  Where precision and scale were set:

Transactions:
  Which operations are multi-statement:
  Why the boundary is where it is:

Error translation:
  SQL error number → domain exception → what the caller does:

Query round trips:
  Any N+1 found and how it was removed:
  Where QueryMultiple was used and what it saved:

Testing:
  Unit tests and what they cover:
  Integration tests, especially tenant isolation:

Deliberately not done, and why:

Verification

It runs from nothing. Create the database from the SQL Server track's scripts, run the harness, and every method works with no manual setup.

Tenant isolation holds. Seed two schools. For every repository method, confirm that passing school 1 never returns or modifies a row belonging to school 2. Then remove SchoolId from one query's WHERE clause and confirm the leak appears — that is the test proving the filter was doing work.

Transactions are atomic. Force a failure after the student insert but before the fee account insert. Confirm neither row exists, and no audit row either.

A dropped transaction argument is caught. Remove transaction from the fee-account call only, force the same failure, and confirm the fee account survives the rollback. Then restore it. This is the bug that looks correct in review.

No N+1. Run each report with SQL Server Profiler or Extended Events and count the round trips. A report over 400 students that issues 400 queries fails this check.

Nulls are preserved. An absent exam result must map to null marks, not 0. Verify by reading it back and asserting.

Errors are translated. Insert a duplicate roll number through the service and confirm the caller receives DuplicateRollNumberException, not SqlException.

Cancellation works. Cancel a token mid-query and confirm the operation stops.

AI practice

Three AI exercises from this track's syllabus. Do each after the repository works, and apply Track 18's discipline — every answer is a hypothesis until you have run it.

1. Ask AI to explain a Dapper mapping. Paste a QuerySingleOrDefaultAsync<FeeReceipt> call and ask how each column reaches each property, and what happens when a column name does not match. Then rename one column and confirm the property arrives as null with no error.
2. Review generated SQL for parameterisation. Ask for a student search with optional filters — the shape most likely to produce concatenation. Check every user value is a parameter, then search for O'Brien to prove it.
3. Validate that an async change remains correct. Ask for a synchronous repository method converted to async. Check three things: is every call awaited, is .Result gone, and does every command inside the transaction still receive the transaction object? A dropped transaction argument leaves half the operation committed after a rollback, silently.

Exercise 3 is the one worth doing twice. Transaction propagation is the failure that produces no exception and no log line.

Track 18 — Reviewing AI-generated code — has the full checklist.

Self-assessment

Your submission is complete when someone can read DATAFLOW.md, trace one operation from the caller to the database and back, run your tests, and see which decisions were deliberate.

Four specific tests of quality:

• Could the Dapper repository be swapped for the ADO.NET one with no change to the service? If not, the interface leaks its implementation.
• Does one integration test fail when the tenant filter is removed? If nothing fails, the isolation is untested and therefore unproven.
• Does the service own every transaction, and does the repository own none? Repositories that open their own transactions cannot compose.
• Does DATAFLOW.md state what Dapper did not do for you? Naming the parts you still had to decide — the SQL, the split points, the null handling, the round-trip count — is the difference between using a tool and understanding it.

Track completion criteria

You understand the C# to SQL Server data flow, can use ADO.NET safely, can build practical Dapper CRUD and procedure calls, and can debug mapping and transaction problems.

Specifically, you can:

• Explain every step from a C# call to a returned row
• Configure connection strings safely and diagnose a failure from its error number
• Explain what connection pooling does and why a leak looks like a slow site
• Parameterise every query, and explain how a parameter's type can defeat an index
• Map results by hand, and say exactly what Dapper is doing on your behalf
• Choose Query, QueryFirst, or QuerySingle to assert your expectation
• Use splitOn, QueryMultiple and DTOs to return the right shape in minimal round trips
• Place a transaction boundary in the service layer and pass it to every call
• Recognise and remove an N+1
• Translate SQL errors into domain exceptions at the repository boundary

The syllabus recommends Track 10 — ASP.NET Core Development next — it puts these repositories behind a REST API. Track 08 — Entity Framework Core is an optional ORM alternative to the Dapper approach you have just learned.