Testing Data Access: Unit and Integration Tests – Data Access | Lesson 63

Published / updated7 Oct 2026

Introduction

So far, every lesson checked its code by running Program.cs and reading the output. That works once. It does not tell you, six months later, that a small change broke the fee rules.

An automated test is code that runs your code and checks the result, every time you ask, in seconds. This lesson adds a test project to SchoolApp with two kinds of tests: unit tests that need no database, and integration tests that run against the real SchoolDb.

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

Explain unit tests and integration tests, and what each one is for.
Write xUnit tests with [Fact], [Theory], InlineData, and MemberData.
Test a service with a fake repository, following Arrange, Act, Assert.
Run one set of integration tests against ADO.NET, Dapper, and EF Core.
Keep the database unchanged by rolling back every test.

Why this matters

SchoolApp now has rules in Core and three data-access tools in Data. Every change risks breaking something far away from the line you edited, and the next module adds a whole Web API on top.

Tests are what let a team change code without fear. In interviews, "how do you test this?" follows almost every design question, and the answer separates people who have maintained real systems from people who have only written them.

The concept

Example: unit and integration tests

	Unit test	Integration test
Tests	One class's logic, alone	Several parts working together, such as a repository and SQL Server
Database	None: a fake replaces it	The real one
Speed	Milliseconds	Tens or hundreds of milliseconds
Finds	Wrong rules and calculations	Wrong SQL, wrong mapping, missing constraints
SchoolApp examples	`StudentService`, `FeeInvoice` rules	All three repositories against SchoolDb

The usual advice is a test pyramid: many unit tests, fewer integration tests, and a handful of end-to-end tests through the whole application. Each level catches mistakes the others cannot.

Example: xUnit in five words

xUnit is the most common test framework for .NET. A test is a public method with an attribute:

Piece	Meaning
`[Fact]`	A test with no inputs: always true
`[Theory]`	A test that runs once per set of inputs
`[InlineData(...)]`	One set of inputs written in the attribute
`[MemberData(nameof(...))]`	Inputs from a property, for values attributes cannot hold
`Assert`	Checks: `Equal`, `Null`, `True`, `Throws`, `ThrowsAsync`, `Single`, and more

A failing Assert stops the test and reports what it expected and what it got. A test with no failed check passes.

Example: Arrange, Act, Assert

Good tests have three parts, in order: Arrange sets up the objects, Act calls the one thing being tested, and Assert checks the result.

Think of checking a new fee counter clerk. Arrange: give them an invoice for 45,500. Act: ask them to take 20,000.

Assert: the receipt says 20,000 and the balance is 25,500. One clear task, one clear check.

Example: fakes

A fake is a simple, working stand-in for a dependency, such as a repository that keeps students in a list. Because StudentService depends on IStudentRepository (Lesson 62), a test can hand it a fake, and the service cannot tell the difference.

Fakes can also record what happened. FakeStudentRepository counts its AddAsync calls, so a test can prove that a rejected admission saved nothing.

Apply it to the SMS

Step 1: the test project

Terminal
dotnet new xunit -n SchoolApp.Tests -o tests/SchoolApp.Tests -f net10.0
dotnet add tests/SchoolApp.Tests reference src/SchoolApp.Core src/SchoolApp.Data
dotnet sln SchoolApp.slnx add tests/SchoolApp.Tests/SchoolApp.Tests.csproj --solution-folder tests

Delete the template's UnitTest1.cs. The project file references the code under test:

tests/SchoolApp.Tests/SchoolApp.Tests.csproj
<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <TargetFramework>net10.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
    <IsPackable>false</IsPackable>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="coverlet.collector" Version="6.0.4" />
    <PackageReference Include="Microsoft.NET.Test.Sdk" Version="17.14.1" />
    <PackageReference Include="xunit" Version="2.9.3" />
    <PackageReference Include="xunit.runner.visualstudio" Version="3.1.4" />
  </ItemGroup>

  <ItemGroup>
    <Using Include="Xunit" />
  </ItemGroup>

  <!-- Lesson 63: the code under test. -->
  <ItemGroup>
    <ProjectReference Include="..\..\src\SchoolApp.Core\SchoolApp.Core.csproj" />
    <ProjectReference Include="..\..\src\SchoolApp.Data\SchoolApp.Data.csproj" />
  </ItemGroup>

</Project>

Step 2: a fake repository

tests/SchoolApp.Tests/Fakes/FakeStudentRepository.cs
using SchoolApp.Core.Entities;
using SchoolApp.Core.Interfaces;

namespace SchoolApp.Tests.Fakes;

// Lesson 63: a fake repository for unit tests. It keeps students in a list and counts the calls,
// so a test can check not only the answer but also what the service asked the repository to do.
public class FakeStudentRepository : IStudentRepository
{
    public List<Student> Students { get; } = new List<Student>();
    public int AddCalls { get; private set; }

    public Task<Student?> GetByAdmissionNumberAsync(string admissionNumber, CancellationToken cancellationToken = default)
    {
        return Task.FromResult(Students.FirstOrDefault(s => s.AdmissionNumber == admissionNumber));
    }

    public Task AddAsync(Student student, CancellationToken cancellationToken = default)
    {
        AddCalls++;
        student.Id = 10000 + Students.Count + 1;
        Students.Add(student);
        return Task.CompletedTask;
    }
}

Step 3: unit tests for the service

tests/SchoolApp.Tests/Unit/StudentServiceTests.cs
using SchoolApp.Core.Dtos;
using SchoolApp.Core.Entities;
using SchoolApp.Core.Enums;
using SchoolApp.Core.Services;
using SchoolApp.Tests.Fakes;

namespace SchoolApp.Tests.Unit;

// Lesson 63: unit tests for StudentService. No database: the fake repository answers in memory,
// so all of these run in milliseconds. Each test follows Arrange, Act, Assert.
public class StudentServiceTests
{
    private static CreateStudentRequest NewRequest(string admissionNumber = "ADM-2026-10001", string firstName = "Ishaan")
    {
        return new CreateStudentRequest(admissionNumber, firstName, "Kumar", new DateOnly(2016, 4, 12),
                                        Gender.Male, new DateOnly(2026, 10, 7), null);
    }

    [Fact]
    public async Task AdmitAsync_NewStudent_SavesAndReturnsDto()
    {
        // Arrange
        FakeStudentRepository repository = new FakeStudentRepository();
        StudentService service = new StudentService(repository);

        // Act
        StudentDto admitted = await service.AdmitAsync(NewRequest());

        // Assert
        Assert.Equal("Ishaan Kumar", admitted.FullName);
        Assert.Equal("Male", admitted.Gender);
        Assert.Equal("Active", admitted.Status);
        Assert.True(admitted.Id > 0);
        Assert.Equal(1, repository.AddCalls);
    }

    [Fact]
    public async Task AdmitAsync_DuplicateAdmissionNumber_ThrowsAndSavesNothing()
    {
        FakeStudentRepository repository = new FakeStudentRepository();
        repository.Students.Add(new Student("ADM-2017-0003", "Kabir", "Singh", new DateOnly(2011, 1, 30), Gender.Male));
        StudentService service = new StudentService(repository);

        InvalidOperationException error = await Assert.ThrowsAsync<InvalidOperationException>(
            () => service.AdmitAsync(NewRequest(admissionNumber: "ADM-2017-0003")));

        Assert.Contains("ADM-2017-0003", error.Message);
        Assert.Equal(0, repository.AddCalls);
    }

    [Theory]
    [InlineData("")]
    [InlineData("   ")]
    public async Task AdmitAsync_BlankFirstName_ThrowsArgumentException(string firstName)
    {
        StudentService service = new StudentService(new FakeStudentRepository());

        await Assert.ThrowsAsync<ArgumentException>(() => service.AdmitAsync(NewRequest(firstName: firstName)));
    }

    [Fact]
    public async Task FindAsync_UnknownAdmissionNumber_ReturnsNull()
    {
        StudentService service = new StudentService(new FakeStudentRepository());

        StudentDto? found = await service.FindAsync("ADM-1999-0001");

        Assert.Null(found);
    }
}

Test names follow Method_Situation_Expected, so a failure reads like a sentence: "AdmitAsync, duplicate admission number, throws and saves nothing". The blank-name theory runs twice, once for each InlineData row.

Step 4: unit tests for the fee rules

Show tests/SchoolApp.Tests/Unit/FeeInvoiceTests.cs

tests/SchoolApp.Tests/Unit/FeeInvoiceTests.cs
using SchoolApp.Core.Entities;
using SchoolApp.Core.Enums;
using SchoolApp.Core.Exceptions;

namespace SchoolApp.Tests.Unit;

// Lesson 63: the fee rules from Lessons 38, 51, and 60, tested without any database.
// The error numbers are the same as usp_RecordFeePayment's (Lesson 22).
public class FeeInvoiceTests
{
    private static FeeInvoice NewInvoice() => new FeeInvoice("INV-TEST-000001", totalAmount: 45500m);

    [Fact]
    public void RecordPayment_PartOfBalance_MarksPartiallyPaid()
    {
        FeeInvoice invoice = NewInvoice();

        invoice.RecordPayment(20000m, PaymentMode.Cash, "RCP-TEST-1");

        Assert.Equal(InvoiceStatus.PartiallyPaid, invoice.Status);
        Assert.Equal(25500m, invoice.Balance);
        Assert.Single(invoice.Payments);
    }

    [Fact]
    public void RecordPayment_WholeBalanceInTwoParts_MarksPaid()
    {
        FeeInvoice invoice = NewInvoice();

        invoice.RecordPayment(20000m, PaymentMode.Cash, "RCP-TEST-1");
        invoice.RecordPayment(25500m, PaymentMode.Online, "RCP-TEST-2");

        Assert.Equal(InvoiceStatus.Paid, invoice.Status);
        Assert.Equal(0m, invoice.Balance);
    }

    // One test method, three cases: each InlineData row runs as its own test.
    [Theory]
    [InlineData(0, 50012)]
    [InlineData(-100, 50012)]
    [InlineData(45500.01, 50013)]
    public void RecordPayment_InvalidAmount_ThrowsWithErrorNumber(decimal amount, int expectedErrorNumber)
    {
        FeeInvoice invoice = NewInvoice();

        SchoolRuleException error = Assert.Throws<SchoolRuleException>(
            () => invoice.RecordPayment(amount, PaymentMode.Cash, "RCP-TEST-1"));

        Assert.Equal(expectedErrorNumber, error.ErrorNumber);
        Assert.Empty(invoice.Payments);
    }

    [Fact]
    public void RecordPayment_CancelledInvoice_Throws50011()
    {
        FeeInvoice invoice = NewInvoice();
        invoice.Cancel();

        SchoolRuleException error = Assert.Throws<SchoolRuleException>(
            () => invoice.RecordPayment(100m, PaymentMode.Cash, "RCP-TEST-1"));

        Assert.Equal(50011, error.ErrorNumber);
    }

    [Fact]
    public void Cancel_InvoiceWithPayments_Throws()
    {
        FeeInvoice invoice = NewInvoice();
        invoice.RecordPayment(100m, PaymentMode.Cash, "RCP-TEST-1");

        Assert.Throws<InvalidOperationException>(() => invoice.Cancel());
        Assert.Equal(InvoiceStatus.PartiallyPaid, invoice.Status);
    }

    [Fact]
    public void RecordPayment_RaisesPaymentRecordedOnce()
    {
        FeeInvoice invoice = NewInvoice();
        List<PaymentRecordedEventArgs> raised = new List<PaymentRecordedEventArgs>();
        invoice.PaymentRecorded += (sender, e) => raised.Add(e);

        invoice.RecordPayment(5000m, PaymentMode.Card, "RCP-TEST-1");

        PaymentRecordedEventArgs e = Assert.Single(raised);
        Assert.Equal(5000m, e.Amount);
        Assert.Equal(40500m, e.Balance);
    }
}

These test the rules from Lessons 38, 51, and 60, with the stored procedure's error numbers. 50011 means a cancelled invoice, 50012 a zero or negative amount, and 50013 more than the balance.

Example: a test that catches a bug

To see the tests work, change one character in FeeInvoice.RecordPayment: if (amount <= 0) becomes if (amount < 0). Now a payment of zero is accepted. Run only the unit tests:

Terminal
dotnet test SchoolApp.slnx --filter "Category!=Integration"

Output
  Failed SchoolApp.Tests.Unit.FeeInvoiceTests.RecordPayment_InvalidAmount_ThrowsWithErrorNumber(amount: 0, expectedErrorNumber: 50012) [5 ms]
   Assert.Throws() Failure: No exception was thrown
Expected: typeof(SchoolApp.Core.Exceptions.SchoolRuleException)
Failed!  - Failed:     1, Passed:    16, Skipped:     0, Total:    17, Duration: 89 ms - SchoolApp.Tests.dll (net10.0)

One line names the rule, the input, and what went wrong, in 89 milliseconds. Put the = back, and all 17 pass again.

Step 5: integration tests for all three tools

Show tests/SchoolApp.Tests/Integration/StudentRepositoryTests.cs

tests/SchoolApp.Tests/Integration/StudentRepositoryTests.cs
using System.Transactions;
using Microsoft.Data.SqlClient;
using SchoolApp.Core.Entities;
using SchoolApp.Core.Enums;
using SchoolApp.Core.Interfaces;
using SchoolApp.Data;
using SchoolApp.Data.AdoNet;
using SchoolApp.Data.Dapper;
using SchoolApp.Data.EfCore;

namespace SchoolApp.Tests.Integration;

// Lesson 63: integration tests against the real SchoolDb (SCHOOLDB_CONNECTION, or localhost).
// Every test runs three times, once per data-access tool, and every change is rolled back,
// so the database is the same after the tests as before.
// Run only the unit tests with:  dotnet test --filter "Category!=Integration"
[Trait("Category", "Integration")]
public class StudentRepositoryTests
{
    private static readonly SqlConnectionFactory Connections = SqlConnectionFactory.FromEnvironment();

    public static TheoryData<string> Tools => new TheoryData<string> { "AdoNet", "Dapper", "EfCore" };

    private static IStudentRepository CreateRepository(string tool, SchoolDbContext db) => tool switch
    {
        "AdoNet" => new AdoNetStudentRepository(Connections),
        "Dapper" => new DapperStudentRepository(Connections),
        "EfCore" => new EfStudentRepository(db),
        _ => throw new ArgumentOutOfRangeException(nameof(tool))
    };

    // A scope that is never completed: everything inside it is rolled back when it is disposed.
    private static TransactionScope RollbackScope() => new TransactionScope(TransactionScopeAsyncFlowOption.Enabled);

    [Theory]
    [MemberData(nameof(Tools))]
    public async Task GetByAdmissionNumberAsync_Kabir_ReturnsEveryField(string tool)
    {
        await using SchoolDbContext db = SchoolDbContextFactory.Create(Connections);
        IStudentRepository repository = CreateRepository(tool, db);

        Student? kabir = await repository.GetByAdmissionNumberAsync("ADM-2017-0003");

        Assert.NotNull(kabir);
        Assert.Equal(3, kabir.Id);
        Assert.Equal("Kabir Singh", kabir.FullName);
        Assert.Equal(new DateOnly(2011, 1, 30), kabir.DateOfBirth);
        Assert.Equal(Gender.Male, kabir.Gender);
        Assert.Equal(StudentStatus.Active, kabir.Status);
    }

    [Theory]
    [MemberData(nameof(Tools))]
    public async Task GetByAdmissionNumberAsync_Unknown_ReturnsNull(string tool)
    {
        await using SchoolDbContext db = SchoolDbContextFactory.Create(Connections);
        IStudentRepository repository = CreateRepository(tool, db);

        Assert.Null(await repository.GetByAdmissionNumberAsync("ADM-1999-0001"));
    }

    [Theory]
    [MemberData(nameof(Tools))]
    public async Task AddAsync_NewStudent_SetsIdAndCanBeReadBack(string tool)
    {
        using TransactionScope scope = RollbackScope();
        await using SchoolDbContext db = SchoolDbContextFactory.Create(Connections);
        IStudentRepository repository = CreateRepository(tool, db);
        Student anvi = new Student("ADM-2026-10002", "Anvi", "Rao", new DateOnly(2016, 8, 3), Gender.Female)
        {
            AdmissionDate = new DateOnly(2026, 10, 7)
        };

        await repository.AddAsync(anvi);
        Student? readBack = await repository.GetByAdmissionNumberAsync("ADM-2026-10002");

        Assert.True(anvi.Id > 10012);
        Assert.NotNull(readBack);
        Assert.Equal(anvi.Id, readBack.Id);
        Assert.Equal(Gender.Female, readBack.Gender);   // 'F' went in and came back as Gender.Female
    }

    // The unique constraint is the last line of defence when two clerks admit the same student at once.
    // Each tool reports it differently, so the test looks for SQL Server error 2627 anywhere inside.
    [Theory]
    [MemberData(nameof(Tools))]
    public async Task AddAsync_DuplicateAdmissionNumber_FailsWithUniqueKeyError(string tool)
    {
        using TransactionScope scope = RollbackScope();
        await using SchoolDbContext db = SchoolDbContextFactory.Create(Connections);
        IStudentRepository repository = CreateRepository(tool, db);
        Student duplicate = new Student("ADM-2017-0003", "Kabir", "Singh", new DateOnly(2011, 1, 30), Gender.Male)
        {
            AdmissionDate = new DateOnly(2017, 6, 1)
        };

        Exception error = await Assert.ThrowsAnyAsync<Exception>(() => repository.AddAsync(duplicate));

        SqlException? sqlError = FindSqlException(error);
        Assert.NotNull(sqlError);
        Assert.Equal(2627, sqlError.Number);  // violation of a UNIQUE constraint
    }

    private static SqlException? FindSqlException(Exception? error)
    {
        while (error is not null && error is not SqlException)
        {
            error = error.InnerException;
        }
        return error as SqlException;
    }
}

Tools is a list of three names, and [MemberData(nameof(Tools))] runs every test once per name. Four test methods become twelve tests, so ADO.NET, Dapper, and EF Core must all pass the same checks.

Example: rolling back every test

Integration tests must not leave data behind; otherwise the second run finds the student from the first run and fails. Each test that writes opens a TransactionScope and never calls Complete(), so everything inside is rolled back when the test ends.

Every connection opened inside the scope joins its transaction automatically, whichever tool opens it. TransactionScopeAsyncFlowOption.Enabled makes the scope follow the code across await.

Without that option, a quick experiment lost the transaction after the first await. An UPDATE was saved for real, and disposing the scope threw "A TransactionScope must be disposed on the same thread that it was created".

Example: what the duplicate test revealed

The duplicate-admission test expects SQL Server error 2627, a broken UNIQUE constraint. It passes for all three tools, but the exception arrives differently: ADO.NET and Dapper throw SqlException, while EF Core wraps it in a DbUpdateException.

So the test searches the inner exceptions for the SqlException. A real application should translate such errors into one exception of its own in the data layer, so callers never depend on the tool.

Example: the Lesson 60 bug, as a test

tests/SchoolApp.Tests/Integration/FeeInvoiceLoadingTests.cs
using Microsoft.EntityFrameworkCore;
using SchoolApp.Core.Entities;
using SchoolApp.Core.Enums;
using SchoolApp.Data;
using SchoolApp.Data.EfCore;

namespace SchoolApp.Tests.Integration;

// Lesson 63: the Lesson 60 bug as a test. A partly paid invoice must show its real balance;
// if someone removes the Include, this test fails instead of the fee reminders.
[Trait("Category", "Integration")]
public class FeeInvoiceLoadingTests
{
    [Fact]
    public async Task PartlyPaidInvoice_LoadedWithPayments_ShowsRealBalance()
    {
        await using SchoolDbContext db = SchoolDbContextFactory.Create(SqlConnectionFactory.FromEnvironment());

        FeeInvoice diya = await db.FeeInvoices
            .Include(i => i.Payments)
            .SingleAsync(i => i.InvoiceNumber == "INV-2026-000002");

        Assert.Equal(InvoiceStatus.PartiallyPaid, diya.Status);
        Assert.Equal(20000m, diya.AmountPaid);
        Assert.Equal(25500m, diya.Balance);
    }
}

This is a regression test: a test for a bug that happened once, so it can never come back unnoticed. If someone removes the Include, the balance is 45,500 and the test fails.

Step 6: running the tests

Terminal
dotnet test SchoolApp.slnx
dotnet test SchoolApp.slnx --filter "Category!=Integration"

Output
Passed!  - Failed:     0, Passed:    32, Skipped:     0, Total:    32, Duration: 1 s - SchoolApp.Tests.dll (net10.0)
Passed!  - Failed:     0, Passed:    17, Skipped:     0, Total:    17, Duration: 82 ms - SchoolApp.Tests.dll (net10.0)

The full run includes the 6 tests from this lesson's homework. The [Trait("Category", "Integration")] attribute marks the database tests, so the filter runs only the 17 tests that need no database, in 82 milliseconds. After the full run, SchoolDb still had exactly 10,012 students.

Common mistakes

Tests that depend on each other's data. They pass alone and fail together. Fix: each test arranges its own data and rolls back what it writes.
Testing several things in one test. A failure does not say which. Fix: one behaviour per test, named Method_Situation_Expected.
Only unit tests for data access. Fakes cannot find wrong SQL or mapping. Fix: integration tests against a real database for every repository.
Forgetting TransactionScopeAsyncFlowOption.Enabled. The transaction is lost after await, and data is saved for real. Fix: always pass it in async code.
Asserting nothing. A test with no Assert passes even when the code is wrong. Fix: every test checks the result it exists for.

In real projects

From 16 years of .NET projects

On a school fees system, a developer "simplified" the balance calculation and deleted a check for refunded payments. It looked harmless, and it passed review.

Nothing failed for three weeks, until parents who had received refunds were told they owed nothing. The fix took ten minutes; the apology emails took a week.

The project had no tests for the fee rules. Today, the first thing I add to any finance code is a theory test with the awkward cases: zero, negative, more than due, and refunded.

Best practices

Write unit tests for rules and calculations; integration tests for SQL and mapping.
Follow Arrange, Act, Assert, with one behaviour per test.
Use theories for many inputs to the same rule.
Roll back every integration test with a scope that is never completed.
Mark slow tests with a trait, so the fast ones can run on every save.

Recap

Unit tests check logic alone, with fakes; integration tests check the real database.
xUnit uses [Fact] and [Theory], with InlineData or MemberData for inputs.
A fake repository let the service be tested in milliseconds.
One integration test class checked ADO.NET, Dapper, and EF Core with the same tests.
TransactionScope without Complete() rolled back every change.
A changed character in a fee rule was caught by one failing test.

Homework

Library module task: In the test project, add a theory for the library fine rule (14-day loan, 2 per late day) with four return dates. Then add two integration tests: every copy marked Issued has exactly one open issue, and returning The Jungle Book makes its copy Available (rolled back).

A correct result looks like:

Terminal
dotnet test SchoolApp.slnx --filter "FullyQualifiedName~Library"

Output
Passed!  - Failed:     0, Passed:     6, Skipped:     0, Total:     6, Duration: 424 ms - SchoolApp.Tests.dll (net10.0)

Show the solution

tests/SchoolApp.Tests/Library/LibraryTests.cs
using System.Transactions;
using Dapper;
using Microsoft.Data.SqlClient;
using SchoolApp.Data;

namespace SchoolApp.Tests.Library;

// Library homework, Lesson 63: a unit test for the fine rule, and integration tests for the library
// tables in SchoolDb. Run with:  dotnet test --filter "FullyQualifiedName~Library"

// The rule under test. In your own Library project, this class would live in its Core project.
public static class LibraryFines
{
    public const int LoanDays = 14;
    public const decimal FinePerDay = 2m;

    public static decimal FineFor(DateOnly dueOn, DateOnly returnedOn)
    {
        int daysLate = returnedOn.DayNumber - dueOn.DayNumber;
        return daysLate > 0 ? daysLate * FinePerDay : 0m;
    }
}

public class LibraryFineTests
{
    [Theory]
    [InlineData("2026-07-20", "2026-07-14", 0)]   // early
    [InlineData("2026-07-20", "2026-07-20", 0)]   // on the due date
    [InlineData("2026-07-20", "2026-07-21", 2)]   // one day late
    [InlineData("2026-07-20", "2026-08-01", 24)]  // The Jungle Book, 12 days late
    public void FineFor_ReturnDate_ChargesTwoPerLateDay(string dueOn, string returnedOn, decimal expected)
    {
        decimal fine = LibraryFines.FineFor(DateOnly.Parse(dueOn), DateOnly.Parse(returnedOn));

        Assert.Equal(expected, fine);
    }
}

[Trait("Category", "Integration")]
public class LibraryDatabaseTests
{
    private static readonly SqlConnectionFactory Connections = SqlConnectionFactory.FromEnvironment();

    // A data-consistency check: a copy marked Issued must have exactly one open issue, and the
    // other way round. It would catch a broken trigger or a manual UPDATE that forgot one side.
    [Fact]
    public async Task IssuedCopies_MatchOpenIssues()
    {
        await using SqlConnection connection = Connections.Create();

        // For each copy: 1 if marked Issued, else 0, compared with its number of open issues.
        int mismatches = await connection.ExecuteScalarAsync<int>("""
            SELECT COUNT(*)
            FROM dbo.BookCopies AS c
            WHERE CASE WHEN c.Status = 'Issued' THEN 1 ELSE 0 END
                  <> (SELECT COUNT(*) FROM dbo.BookIssues AS i WHERE i.BookCopyId = c.Id AND i.ReturnedOn IS NULL);
            """);

        Assert.Equal(0, mismatches);
    }

    [Fact]
    public async Task ReturningABook_TriggerMarksCopyAvailable()
    {
        using TransactionScope scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled);
        await using SqlConnection connection = Connections.Create();

        await connection.ExecuteAsync(
            "UPDATE dbo.BookIssues SET ReturnedOn = '2026-08-01', FineAmount = 24 WHERE BookCopyId = 3 AND ReturnedOn IS NULL;");
        string status = await connection.ExecuteScalarAsync<string>("SELECT Status FROM dbo.BookCopies WHERE Id = 3;") ?? "";

        Assert.Equal("Available", status);
    }
}

The first integration test is a data-consistency test: it checks the data itself, not the code. It would catch a disabled trigger or a manual UPDATE that changed one table and forgot the other.

Quiz

What does a unit test of StudentService use instead of SchoolDb?
1. A copy of SchoolDb
2. A fake repository
3. LocalDB
4. Nothing: it skips the repository
Show answer
Answer: B. A fake repository FakeStudentRepository keeps students in a list.
What is the difference between [Fact] and [Theory]?
1. Fact is async
2. A theory runs once per set of inputs
3. A theory skips the Assert
4. There is none
Show answer
Answer: B. A theory runs once per set of inputs Each InlineData or MemberData row becomes its own test.
How do the integration tests leave SchoolDb unchanged?
1. They delete their rows at the end
2. A TransactionScope that is never completed rolls everything back
3. They use a copy of the database
4. They only read
Show answer
Answer: B. A TransactionScope that is never completed rolls everything back SchoolDb still had 10,012 students after the full run.
Why can't fakes replace integration tests?
1. Fakes are slow
2. Fakes cannot find wrong SQL, mapping, or constraints
3. Fakes need a database
4. xUnit does not allow fakes
Show answer
Answer: B. Fakes cannot find wrong SQL, mapping, or constraints Only the real database runs the real SQL.
What does the filter Category!=Integration do?
1. Runs only integration tests
2. Runs every test without the Integration trait
3. Skips failing tests
4. Deletes integration tests
Show answer
Answer: B. Runs every test without the Integration trait 17 tests ran in 82 ms with no database.

Interview questions

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

Fresher (0-2 yrs)

F What is the difference between a unit test and an integration test? concept

A unit test checks one class's logic alone, with fakes for its dependencies, and runs in milliseconds. An integration test checks parts working together, such as a repository with the real SQL Server, so it finds wrong SQL, mapping, and constraints that fakes cannot.

See the example · Permalink

F What is the difference between [Fact] and [Theory] in xUnit? coding

A [Fact] is a test with no inputs. A [Theory] runs once for each set of inputs supplied by [InlineData] rows or a [MemberData] property; each set is reported as its own test. The fee tests use a theory for zero, negative, and too-large amounts.

See the example · Permalink

F What does Arrange, Act, Assert mean? concept

A structure for each test: Arrange sets up the objects and data, Act calls the one behaviour being tested, and Assert checks the result. It keeps tests short, readable, and focused on one behaviour.

See the example · Permalink

Mid (3-5 yrs)

M What is a fake, and how is it different from a mock? concept

A fake is a simple working implementation, such as a repository that stores students in a list. A mock is usually generated by a library and set up with expected calls and answers. Fakes are easy to read and reuse; mocks are handy for checking interactions. Both depend on the code using interfaces.

See the example · Permalink

M How do you keep integration tests from leaving data in the database? coding

Run each test inside a transaction that is rolled back. With TransactionScope created with TransactionScopeAsyncFlowOption.Enabled and never completed, every connection opened inside joins the transaction, whatever the tool, and disposal rolls it back. Alternatives are a fresh database per run or deleting test data, both slower or more fragile.

See the example · Permalink

M What is a regression test? Give an example. concept

A test written for a bug that already happened, so it cannot come back unnoticed. In SchoolApp, an invoice loaded without its payments showed the full balance; a test now loads Diya's partly paid invoice and checks that the balance is 25,500.

See the example · Permalink

Senior (6-9 yrs)

S You have three repository implementations. How do you make sure they behave the same? design

Write one integration test class against the interface, and run every test once per implementation with a theory and MemberData. Four tests became twelve for ADO.NET, Dapper, and EF Core. Where behaviour legitimately differs, such as exception types, assert on what matters to callers or translate the differences in the data layer.

How to answer: Describe the shared contract tests, how inputs select the implementation, and what to do about differences the tests uncover.

See the example · Permalink

S Your integration tests pass alone but fail when run together. What do you check? scenario

Shared state: tests that rely on data left by others, missing rollbacks, identity or sequence values assumed to be fixed, or a transaction lost after await because the scope was not created with async flow. Make each test arrange its own data, roll back everything it writes, and never assume run order.

How to answer: List the likely causes, explain how to confirm each (run in a different order, inspect the data), and give the structural fix rather than ordering the tests.

See the example · Permalink

Architect / Lead (10+ yrs)

A How would you design a test strategy for a system like the SMS, from unit tests to production? design

Many fast unit tests for rules and services with fakes; integration tests for every repository and query against a database built from the real scripts; a few end-to-end API tests for key flows such as admission and fee payment; and data-consistency checks. Run unit tests on every change, integration tests in CI, and keep realistic data volumes for performance tests.

How to answer: Use the test pyramid, say what each level catches and when it runs, and mention test data, database setup in CI, and how failures block a release.

See the example · Permalink

Resources

Code for this lesson: git tag lesson-63-testing-data-access (code repository coming soon)
Files: tests/SchoolApp.Tests/Unit/StudentServiceTests.cs, tests/SchoolApp.Tests/Unit/FeeInvoiceTests.cs, tests/SchoolApp.Tests/Integration/StudentRepositoryTests.cs, tests/SchoolApp.Tests/Library/LibraryTests.cs
Video: coming soon

What's next

The tests showed what happens when one operation fails. Next: several operations that must succeed or fail together, and two clerks paying the same invoice at the same moment. Lesson 64: Transactions and concurrency

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

Introduction​

Why this matters​

The concept​

Example: unit and integration tests​

Example: xUnit in five words​

Example: Arrange, Act, Assert​

Example: fakes​

Apply it to the SMS​

Step 1: the test project​

Step 2: a fake repository​

Step 3: unit tests for the service​

Step 4: unit tests for the fee rules​

Example: a test that catches a bug​

Step 5: integration tests for all three tools​

Example: rolling back every test​

Example: what the duplicate test revealed​

Example: the Lesson 60 bug, as a test​

Step 6: running the tests​

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: unit and integration tests

Example: xUnit in five words

Example: Arrange, Act, Assert

Example: fakes

Apply it to the SMS

Step 1: the test project

Step 2: a fake repository

Step 3: unit tests for the service

Step 4: unit tests for the fee rules

Example: a test that catches a bug

Step 5: integration tests for all three tools

Example: rolling back every test

Example: what the duplicate test revealed

Example: the Lesson 60 bug, as a test

Step 6: running the tests

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