Loan Management System with Dapper

Build a WinForms loan app with DI, repository pattern, and LINQ filtering.

Introduction

30 Second Summary

Every bank, credit union, and fintech app tracks the same core data: who borrowed money, how much they owe, and when they pay it back. Behind every loan approval screen is a system that manages borrowers, calculates repayments, and filters through active accounts.

In this project, you will build a desktop loan management application using C# and WinForms. You will wire up a professional layered architecture with dependency injection, a repository pattern backed by Dapper, and LINQ-powered search and filtering.

What You'll Build

You will have a running desktop app where you can add borrowers, create loans with interest calculations, and instantly filter through loan records by status or borrower name.

By the end of this project, you'll have:

  • A multi-form WinForms application where you can add a borrower and immediately see them appear in a searchable grid.
  • A service layer with business logic that validates loan inputs and calculates total repayment before saving to SQL Server.
  • A LINQ-powered filtering panel where you can search loans by borrower name and filter by status, with live summary statistics updating as you type.
  • Secret Mission: Add a loan amortization schedule that calculates and displays monthly payment breakdowns showing principal versus interest.

Are there any prerequisites?

You should have Visual Studio installed, SQL Server Express running locally, and familiarity with C#, WinForms basics, and SQL queries. This project assumes you can create a WinForms project and write basic SQL statements.

Before We Start

Before we dive in, let's lock in what you're about to build and why it matters.

You're going to build a desktop loan management application with a properly layered architecture. That means dependency injection wiring your forms to services, a repository pattern managing data access with Dapper, and LINQ filtering to search through loan records in real time.

Set Up the Project and Wire Dependency Injection

WinForms applications have no built-in dependency injection support. Without explicit wiring, every form would need to manually create its own dependencies. This leads to tightly coupled code that is impossible to unit test.

In this step, you will create the WinForms project, install the packages needed for DI and database access, and wire up the Generic Host pattern. By the end, your app will resolve its main form from the dependency injection container instead of creating it directly.

In this step, get ready to:
  • Create a .NET 9 WinForms project and install NuGet packages.
  • Wire Program.cs with the Generic Host pattern for dependency injection.
  • Verify the app launches with MainForm resolved from the container.
Create the WinForms project and install packages
  • Open a terminal in Visual Studio by clicking View in the top menu, then Terminal.
  • Navigate to your Desktop by running this command:
cd ~/Desktop
  • Create a new .NET 9 WinForms project by running this command:
dotnet new winforms -n LoanManager --framework net9.0
  • Move into the project folder by running:
cd LoanManager

What did that command do?

The dotnet new winforms template creates a WinForms project pre-configured with a Form1.cs file and a Program.cs entry point.

The --framework net9.0 flag pins the project to .NET 9 specifically.

  • Install the three NuGet packages your project needs by running these commands one at a time:
dotnet add package Microsoft.Extensions.Hosting --version 9.0.13
dotnet add package Dapper --version 2.1.79
dotnet add package Microsoft.Data.SqlClient --version 7.0.2

What are these packages for?

  • Microsoft.Extensions.Hosting gives you the Generic Host pattern with built-in DI, configuration, and logging.
  • Dapper is a lightweight ORM that maps SQL query results to C# objects.
  • Microsoft.Data.SqlClient is the actively maintained SQL Server driver that replaces the older System.Data.SqlClient.
  • Confirm the packages installed successfully by running:
dotnet restore

You should see a message confirming all packages were restored with no errors.

Seeing a package version error?

Make sure you typed the version numbers exactly as shown. If you see a "version not found" error, check your NuGet source is configured correctly by running dotnet nuget list source.

Still stuck? Help me troubleshoot my NuGet package installation error.

Set up MainForm and wire the Generic Host

The project template created a default Form1.cs file, but your architecture calls for forms to live in a Forms folder with a proper namespace.

  • Open the LoanManager project in Visual Studio by double-clicking LoanManager.csproj in the project folder, or by clicking File then Open then Project/Solution and navigating to the file.
  • In Solution Explorer, right-click the LoanManager project node and select Add then New Folder.
  • Name the folder Forms.
  • Right-click the Forms folder and select Add then Form (Windows Forms).
  • Name it MainForm.cs and click Add.
  • In the MainForm designer, click the form's title bar area.
  • In the Properties panel (press F4 if hidden), find the Text property and set it to Loan Management System.
  • Delete the original Form1.cs file by right-clicking it in Solution Explorer and selecting Delete.

You should now see only the Forms folder with MainForm.cs inside it in Solution Explorer.

Why a Forms folder?

Visual Studio automatically sets the namespace of files based on their folder path. Placing MainForm.cs inside Forms gives it the namespace LoanManager.Forms, which keeps UI code separated from data and service layers you will add later.

Now replace the default Program.cs with the Generic Host pattern that wires up dependency injection.

  • Open Program.cs from Solution Explorer.
  • Select all the existing code and replace it with the following:
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using LoanManager.Forms;

namespace LoanManager;

static class Program
{
    [STAThread]
    static void Main()
    {
        ApplicationConfiguration.Initialize();

        var builder = Host.CreateApplicationBuilder();

        // Forms
        builder.Services.AddTransient<MainForm>();

        var host = builder.Build();
        host.Start();

        var mainForm = host.Services.GetRequiredService<MainForm>();
        Application.Run(mainForm);

        host.StopAsync().GetAwaiter().GetResult();
        host.Dispose();
    }
}

What does this code do?

  • ApplicationConfiguration.Initialize() configures WinForms defaults like visual styles and high DPI settings.
  • Host.CreateApplicationBuilder() creates a builder that gives you DI, configuration (appsettings.json), and logging out of the box.
  • AddTransient<MainForm>() registers MainForm so the container knows how to create it and inject its dependencies.
  • GetRequiredService<MainForm>() resolves MainForm from the container, automatically injecting any constructor parameters it needs.
  • The host.StopAsync() and host.Dispose() calls clean up background services when the app closes.
  • Add the configuration file by right-clicking the LoanManager project in Solution Explorer and selecting Add then New Item.
  • Search for JSON in the template search box, select JSON File, name it appsettings.json, and click Add.
  • Replace any default content with the following:
{
  "ConnectionStrings": {
    "Default": "Server=.\\SQLEXPRESS;Database=LoanManagement;Trusted_Connection=True;TrustServerCertificate=True;"
  }
}

What is this connection string?

This tells your app how to reach SQL Server Express running on your machine. Server=.\\SQLEXPRESS points to the local Express instance. TrustServerCertificate=True is required for local development to avoid SSL certificate errors.

  • Make sure appsettings.json gets copied to the output folder by right-clicking it in Solution Explorer, selecting Properties, and setting Copy to Output Directory to Copy if newer.
  • Save both files by pressing Ctrl+Shift+S.
Run the app and verify DI works

Before you run this, what do you expect to see? The DI container should resolve MainForm and display the window with your custom title.

  • Press F5 or click the green Start button to run the application.

A window appears with "Loan Management System" in the title bar. This confirms the Generic Host built successfully and resolved MainForm from the DI container.

Form not appearing or seeing an error?

  • If you see a build error mentioning LoanManager.Forms.MainForm, confirm that MainForm.cs is inside the Forms folder and its namespace is LoanManager.Forms.
  • If you see a runtime error about missing configuration, check that appsettings.json has Copy to Output Directory set to Copy if newer.
  • If the title bar still says "Form1", make sure you deleted the original Form1.cs and set the Text property on MainForm to Loan Management System.

Help me debug why my WinForms app with Generic Host isn't launching.

✔️ Awesome, I've got everything!

Great. Make sure you have saved all files before moving on.

ⓧ I'd like to double check the full code

Here is what each file should look like at this point.

using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using LoanManager.Forms;

namespace LoanManager;

static class Program
{
    [STAThread]
    static void Main()
    {
        ApplicationConfiguration.Initialize();

        var builder = Host.CreateApplicationBuilder();

        // Forms
        builder.Services.AddTransient<MainForm>();

        var host = builder.Build();
        host.Start();

        var mainForm = host.Services.GetRequiredService<MainForm>();
        Application.Run(mainForm);

        host.StopAsync().GetAwaiter().GetResult();
        host.Dispose();
    }
}
{
  "ConnectionStrings": {
    "Default": "Server=.\\SQLEXPRESS;Database=LoanManagement;Trusted_Connection=True;TrustServerCertificate=True;"
  }
}

Your project skeleton is wired up with dependency injection. Next, you will create the database and get real data flowing through Dapper into the grid.

Create the Database and Display Borrowers

Your app has a DI container and a blank main form, but no data to display. The fastest way to prove the full pipeline works is to get real rows on screen: from SQL Server through Dapper to a WinForms grid.

In this step you will create the database, build a data access layer using the repository pattern, and wire it into the form so seeded borrowers appear the moment the app launches.

In this step, get ready to:
  • Create the LoanManagement database with Borrowers and Loans tables.
  • Build a SqlConnectionFactory, Borrower model, and BorrowerRepository using Dapper.
  • Display seeded borrowers in a DataGridView on MainForm.
Set up the database and seed data

Before the C# app can read anything, the database needs to exist with tables and some test rows. You will run a SQL script in SQL Server Management Studio (SSMS) to create everything in one go.

  • Open SSMS and connect to your local SQL Express instance.
  • Right-click your server in Object Explorer and select New Query.
  • Create the database and tables by pasting the following SQL and pressing F5 to execute:
CREATE DATABASE LoanManagement;
GO

USE LoanManagement;
GO

CREATE TABLE Borrowers (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    FirstName NVARCHAR(100) NOT NULL,
    LastName NVARCHAR(100) NOT NULL,
    Email NVARCHAR(200) NOT NULL,
    Phone NVARCHAR(20) NOT NULL,
    CreatedAt DATETIME NOT NULL DEFAULT GETDATE()
);

CREATE TABLE Loans (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    BorrowerId INT NOT NULL FOREIGN KEY REFERENCES Borrowers(Id),
    Amount DECIMAL(18,2) NOT NULL,
    InterestRate DECIMAL(5,2) NOT NULL,
    TermMonths INT NOT NULL,
    StartDate DATE NOT NULL,
    Status NVARCHAR(20) NOT NULL DEFAULT 'Active',
    TotalRepayment DECIMAL(18,2) NOT NULL,
    CreatedAt DATETIME NOT NULL DEFAULT GETDATE()
);

What does this script do?

It creates a fresh LoanManagement database, then two tables inside it.

  • Borrowers stores people who take out loans (name, email, phone).
  • Loans stores each loan linked to a borrower via a foreign key.
  • IDENTITY(1,1) auto-generates IDs starting at 1.
  • Insert three seed borrowers by pasting this into the same query window and pressing F5:
-- Seed data
INSERT INTO Borrowers (FirstName, LastName, Email, Phone)
VALUES 
    ('John', 'Smith', 'john.smith@email.com', '555-0101'),
    ('Sarah', 'Johnson', 'sarah.j@email.com', '555-0102'),
    ('Michael', 'Williams', 'mwilliams@email.com', '555-0103');
GO
  • Verify the seed data by running this query:
SELECT * FROM Borrowers;

You should see three rows: John Smith, Sarah Johnson, and Michael Williams.

Query returns an error?

  • Make sure the database dropdown at the top of the query window shows LoanManagement. If it shows master, switch it.
  • If you see a "database already exists" error, the script ran partially before. Drop it first with DROP DATABASE LoanManagement; then re-run.

Help me troubleshoot my SQL Server database creation

Build the data access layer

Now you need C# classes that can reach into SQL Server and pull those rows back. You will create a model, a connection factory, and a repository.

  • In Solution Explorer, right-click the LoanManager project and select Add then New Folder. Name it Models.
  • Right-click the Models folder, select Add then Class, and name it Borrower.cs. Replace its contents with:
namespace LoanManager.Models;

public class Borrower
{
    public int Id { get; set; }
    public string FirstName { get; set; } = string.Empty;
    public string LastName { get; set; } = string.Empty;
    public string Email { get; set; } = string.Empty;
    public string Phone { get; set; } = string.Empty;
    public DateTime CreatedAt { get; set; } = DateTime.Now;
}

What does this class do?

This is a plain C# class whose properties match the columns in the Borrowers table. Dapper uses property names to map SQL result columns to object fields automatically.

  • Create another folder at the project root called Data.
  • Inside Data, add a new class file named SqlConnectionFactory.cs with this content:
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using System.Data;

namespace LoanManager.Data;

public class SqlConnectionFactory
{
    private readonly string _connectionString;

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

    public IDbConnection CreateConnection()
    {
        return new SqlConnection(_connectionString);
    }
}

What does this class do?

SqlConnectionFactory reads the connection string from appsettings.json (via IConfiguration provided by the Generic Host) and hands out a fresh SqlConnection each time a repository needs one.

Centralising connection creation means you change the connection string in one place and every repository picks it up.

  • Build the solution by pressing Ctrl+Shift+B to confirm everything compiles.

You should see "Build succeeded" in the Output window with zero errors.

  • In the Data folder, add an interface file called IBorrowerRepository.cs:
using LoanManager.Models;

namespace LoanManager.Data;

public interface IBorrowerRepository
{
    Task<IEnumerable<Borrower>> GetAllAsync();
    Task<Borrower?> GetByIdAsync(int id);
    Task<int> InsertAsync(Borrower borrower);
    Task UpdateAsync(Borrower borrower);
    Task DeleteAsync(int id);
}

Why an interface?

Coding to an interface (IBorrowerRepository) lets the DI container swap implementations without touching the forms. It also makes the code testable because you can mock the interface in unit tests.

  • Add the implementation file BorrowerRepository.cs in the Data folder. Start with the class structure and read methods:
using Dapper;
using LoanManager.Models;

namespace LoanManager.Data;

public class BorrowerRepository : IBorrowerRepository
{
    private readonly SqlConnectionFactory _factory;

    public BorrowerRepository(SqlConnectionFactory factory)
    {
        _factory = factory;
    }

    public async Task<IEnumerable<Borrower>> GetAllAsync()
    {
        using var connection = _factory.CreateConnection();
        return await connection.QueryAsync<Borrower>("SELECT * FROM Borrowers ORDER BY LastName, FirstName");
    }

    public async Task<Borrower?> GetByIdAsync(int id)
    {
        using var connection = _factory.CreateConnection();
        return await connection.QueryFirstOrDefaultAsync<Borrower>(
            "SELECT * FROM Borrowers WHERE Id = @Id", new { Id = id });
    }
}

What does this code do?

  • GetAllAsync() opens a connection, runs a SELECT, and Dapper maps each row to a Borrower object automatically.
  • GetByIdAsync() uses a parameterised query (@Id) to fetch a single borrower safely.
  • The using keyword ensures the connection is disposed after each call.
  • Now add the write methods below GetByIdAsync, inside the same class (before the closing }):
    public async Task<int> InsertAsync(Borrower borrower)
    {
        using var connection = _factory.CreateConnection();
        var sql = """
            INSERT INTO Borrowers (FirstName, LastName, Email, Phone, CreatedAt)
            VALUES (@FirstName, @LastName, @Email, @Phone, @CreatedAt);
            SELECT CAST(SCOPE_IDENTITY() AS INT);
            """;
        return await connection.ExecuteScalarAsync<int>(sql, borrower);
    }

    public async Task UpdateAsync(Borrower borrower)
    {
        using var connection = _factory.CreateConnection();
        var sql = """
            UPDATE Borrowers 
            SET FirstName = @FirstName, LastName = @LastName, Email = @Email, Phone = @Phone
            WHERE Id = @Id
            """;
        await connection.ExecuteAsync(sql, borrower);
    }

    public async Task DeleteAsync(int id)
    {
        using var connection = _factory.CreateConnection();
        await connection.ExecuteAsync("DELETE FROM Borrowers WHERE Id = @Id", new { Id = id });
    }

What do the write methods do?

  • InsertAsync inserts a row and returns the new Id via SCOPE_IDENTITY().
  • UpdateAsync and DeleteAsync modify or remove an existing row by its Id.
  • Dapper maps the Borrower object properties directly to the SQL parameters (@FirstName, etc.).
  • Build again with Ctrl+Shift+B.

You should see "Build succeeded" with zero errors. The data layer is complete.

Build errors in BorrowerRepository?

  • If you see CS0246: The type or namespace 'Dapper' could not be found, confirm you installed the Dapper NuGet package in Step 1.
  • If SqlConnectionFactory cannot be found, check that the class and the repository are both in the LoanManager.Data namespace.

Help me fix build errors in my Dapper repository

Wire the repository into MainForm

The classes exist but the DI container does not know about them yet. You need to register the factory and repository, then inject the repository into MainForm so it can load borrowers on startup.

  • Open Program.cs and replace its contents with:
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using LoanManager.Data;
using LoanManager.Forms;

namespace LoanManager;

static class Program
{
    [STAThread]
    static void Main()
    {
        ApplicationConfiguration.Initialize();

        var builder = Host.CreateApplicationBuilder();

        // Data access
        builder.Services.AddSingleton<SqlConnectionFactory>();
        builder.Services.AddScoped<IBorrowerRepository, BorrowerRepository>();

        // Forms
        builder.Services.AddTransient<MainForm>();

        var host = builder.Build();
        host.Start();

        var mainForm = host.Services.GetRequiredService<MainForm>();
        Application.Run(mainForm);

        host.StopAsync().GetAwaiter().GetResult();
        host.Dispose();
    }
}

What changed here?

  • Added using LoanManager.Data; at the top.
  • Registered SqlConnectionFactory as a singleton (one instance for the app's lifetime).
  • Registered IBorrowerRepository mapped to BorrowerRepository as scoped (one instance per scope).
  • Open Forms/MainForm.cs and replace its contents with the following. This adds a DataGridView and loads borrowers on startup:
using LoanManager.Data;

namespace LoanManager.Forms;

public partial class MainForm : Form
{
    private readonly IBorrowerRepository _borrowerRepository;
    private DataGridView _borrowersGrid;

    public MainForm(IBorrowerRepository borrowerRepository)
    {
        InitializeComponent();
        _borrowerRepository = borrowerRepository;
        Text = "Loan Management System";
        SetupBorrowersGrid();
    }

    private void SetupBorrowersGrid()
    {
        // Create a grid that fills the form
        _borrowersGrid = new DataGridView
        {
            Dock = DockStyle.Fill,
            AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill,
            ReadOnly = true
        };
        Controls.Add(_borrowersGrid);
    }
}

What does this code do?

  • The constructor now accepts IBorrowerRepository from the DI container.
  • SetupBorrowersGrid() creates a DataGridView that fills the entire form.
  • ReadOnly = true prevents accidental inline edits.
  • Add the data-loading logic below SetupBorrowersGrid(), before the closing } of the class:
    protected override async void OnLoad(EventArgs e)
    {
        base.OnLoad(e);
        await LoadBorrowersAsync();
    }

    private async Task LoadBorrowersAsync()
    {
        // Fetch all borrowers and display in the grid
        var borrowers = await _borrowerRepository.GetAllAsync();
        _borrowersGrid.DataSource = borrowers.ToList();
    }

How does the data reach the grid?

OnLoad fires when the form first displays. It calls GetAllAsync() which executes the Dapper query against SQL Server. The returned IEnumerable<Borrower> is converted to a List and bound to the DataGridView. The grid auto-generates columns from the Borrower class public properties.

  • Save all files with Ctrl+Shift+S.

Before you run the app, what do you expect to see in the grid? Think about how many rows the seed script inserted.

  • Press F5 to run the application.

The form appears with "Loan Management System" in the title bar and a DataGridView showing three rows: John Smith, Sarah Johnson, and Michael Williams.

Grid is empty or the app crashes?

  • If you see an InvalidOperationException about a missing connection string, confirm appsettings.json has Copy to Output Directory set to Copy if newer in its Properties.
  • If you see a login or connection failure, verify SQL Server Express is running. Open Services (Windows search) and check that SQL Server (SQLEXPRESS) shows as Running.
  • If columns appear but rows are empty, the seed INSERT may not have succeeded. Go back to SSMS and re-run the seed query.

Help me debug why my DataGridView is not showing borrowers

✔️ Awesome, I've got everything!

Great. Double check that all files are saved before moving on.

ⓧ I'd like to double check the full code

Here are the key files you should have at this point. Scroll through and confirm each matches yours.

Program.cs:

using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using LoanManager.Data;
using LoanManager.Forms;

namespace LoanManager;

static class Program
{
    [STAThread]
    static void Main()
    {
        ApplicationConfiguration.Initialize();

        var builder = Host.CreateApplicationBuilder();

        // Data access
        builder.Services.AddSingleton<SqlConnectionFactory>();
        builder.Services.AddScoped<IBorrowerRepository, BorrowerRepository>();

        // Forms
        builder.Services.AddTransient<MainForm>();

        var host = builder.Build();
        host.Start();

        var mainForm = host.Services.GetRequiredService<MainForm>();
        Application.Run(mainForm);

        host.StopAsync().GetAwaiter().GetResult();
        host.Dispose();
    }
}

Models/Borrower.cs:

namespace LoanManager.Models;

public class Borrower
{
    public int Id { get; set; }
    public string FirstName { get; set; } = string.Empty;
    public string LastName { get; set; } = string.Empty;
    public string Email { get; set; } = string.Empty;
    public string Phone { get; set; } = string.Empty;
    public DateTime CreatedAt { get; set; } = DateTime.Now;
}

Data/SqlConnectionFactory.cs:

using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using System.Data;

namespace LoanManager.Data;

public class SqlConnectionFactory
{
    private readonly string _connectionString;

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

    public IDbConnection CreateConnection()
    {
        return new SqlConnection(_connectionString);
    }
}

Data/IBorrowerRepository.cs:

using LoanManager.Models;

namespace LoanManager.Data;

public interface IBorrowerRepository
{
    Task<IEnumerable<Borrower>> GetAllAsync();
    Task<Borrower?> GetByIdAsync(int id);
    Task<int> InsertAsync(Borrower borrower);
    Task UpdateAsync(Borrower borrower);
    Task DeleteAsync(int id);
}

Scroll up to confirm your BorrowerRepository.cs has both the read methods (GetAllAsync, GetByIdAsync) and the write methods (InsertAsync, UpdateAsync, DeleteAsync) inside a single class.

Your data pipeline is proven end-to-end. Next up, you will add the ability to create new borrowers through a child form that gets its own dependencies resolved by the DI container.

Add Borrower CRUD with DI-Resolved Child Forms

Your MainForm displays borrowers because the DI container resolved it at startup. But what happens when you need to open a second form, like an "Add Borrower" dialog?

That child form also needs IBorrowerRepository injected through its constructor. You cannot just write new AddBorrowerForm() because there is no parameterless constructor. In this step, you will create the form, hit that exact problem, and solve it by resolving child forms from the DI container using IServiceProvider.

In this step, get ready to:
  • Create an AddBorrowerForm that accepts repository dependencies through its constructor.
  • Resolve child forms from the DI container using IServiceProvider.
  • Wire Edit and Delete functionality to complete borrower CRUD.
Create the AddBorrowerForm

The Add Borrower form needs four text fields and a Save button. Its constructor will accept IBorrowerRepository so it can persist new borrowers directly.

  • In Solution Explorer, right-click the Forms folder (or create one if it does not exist).
  • Select Add then Form (Windows Forms).
  • Name the file AddBorrowerForm.cs and click Add.
  • In the form designer, add four TextBox controls and name them txtFirstName, txtLastName, txtEmail, and txtPhone.
  • Add a Label above each TextBox with descriptive text (First Name, Last Name, Email, Phone).
  • Add a Button named btnSave with its Text property set to Save.
  • Set the form's Text property to Add Borrower.
  • Open the code-behind for AddBorrowerForm.cs and replace the constructor and class body with this:
using LoanManager.Data;
using LoanManager.Models;

namespace LoanManager.Forms;

public partial class AddBorrowerForm : Form
{
    private readonly IBorrowerRepository _borrowerRepository;

    public AddBorrowerForm(IBorrowerRepository borrowerRepository)
    {
        InitializeComponent();
        _borrowerRepository = borrowerRepository;
    }
}

What does this code do?

The constructor takes IBorrowerRepository as a parameter and stores it in a private field.

Because you defined a constructor with a parameter, C# no longer provides a default parameterless constructor. This is exactly what forces the DI container to inject the dependency.

  • Double-click btnSave in the designer to generate a click event handler, then add the save logic:
private async void btnSave_Click(object sender, EventArgs e)
{
    // Create a new borrower from the form fields
    var borrower = new Borrower
    {
        FirstName = txtFirstName.Text.Trim(),
        LastName = txtLastName.Text.Trim(),
        Email = txtEmail.Text.Trim(),
        Phone = txtPhone.Text.Trim(),
        CreatedAt = DateTime.Now
    };

    await _borrowerRepository.InsertAsync(borrower);

    // Signal success back to the calling form
    DialogResult = DialogResult.OK;
    Close();
}

What does this code do?

The handler builds a Borrower object from the TextBox values and calls InsertAsync on the repository. Dapper's ExecuteScalarAsync runs the INSERT and returns the new ID.

Setting DialogResult = DialogResult.OK tells the parent form that the operation succeeded. The parent can check this value after the dialog closes to decide whether to refresh its grid.

  • Save AddBorrowerForm.cs.
Hit the child form problem and fix it with IServiceProvider

With the form created, you need a way to open it from MainForm. The instinct is to write new AddBorrowerForm(), but that will not work here.

  • Switch back to MainForm.cs in the designer.
  • Add a Button named btnAddBorrower with its Text set to Add Borrower.
  • Double-click the button and try writing this naive approach in the click handler:
private void btnAddBorrower_Click(object sender, EventArgs e)
{
    // Attempt to open the form directly
    var form = new AddBorrowerForm();
    form.ShowDialog();
}

Before you build the project, what do you think the compiler will say about new AddBorrowerForm()?

  • Press Ctrl+Shift+B to build the solution.

You will see a compile error: there is no argument given that corresponds to the required parameter borrowerRepository. This is the designed obstacle. Because AddBorrowerForm's only constructor requires IBorrowerRepository, you cannot instantiate it manually.

Why does this happen?

In ASP.NET, the framework resolves forms (controllers/pages) from DI automatically on each request. WinForms has no such mechanism for child forms. You need to ask the DI container directly by injecting IServiceProvider into your parent form.

  • Fix the MainForm constructor to accept IServiceProvider alongside the existing IBorrowerRepository:
private readonly IBorrowerRepository _borrowerRepository;
private readonly IServiceProvider _serviceProvider;

public MainForm(IBorrowerRepository borrowerRepository, IServiceProvider serviceProvider)
{
    InitializeComponent();
    _borrowerRepository = borrowerRepository;
    _serviceProvider = serviceProvider;
}

What does this code do?

The DI container injects IServiceProvider automatically because the Generic Host always registers it. This gives MainForm a reference to the container itself, so it can resolve any registered form on demand.

  • Now replace the broken btnAddBorrower_Click handler with the DI-resolved version:
private async void btnAddBorrower_Click(object sender, EventArgs e)
{
    // Resolve the form from DI so its dependencies get injected
    using var form = _serviceProvider.GetRequiredService<AddBorrowerForm>();

    if (form.ShowDialog() == DialogResult.OK)
    {
        // Refresh the grid after a successful save
        var borrowers = await _borrowerRepository.GetAllAsync();
        dgvBorrowers.DataSource = borrowers.ToList();
    }
}

What does this code do?

GetRequiredService<AddBorrowerForm>() asks the DI container to build the form, injecting IBorrowerRepository into its constructor automatically.

When the dialog closes, you check DialogResult.OK to know a borrower was saved. Then you re-query all borrowers and rebind the grid so the new entry appears immediately.

  • You also need to register AddBorrowerForm in the DI container. Open Program.cs and find the Forms registration section:
// Forms
builder.Services.AddTransient<MainForm>();
  • Add the AddBorrowerForm registration directly below that line:
// Forms
builder.Services.AddTransient<MainForm>();
builder.Services.AddTransient<AddBorrowerForm>();
  • Add the missing using directive at the top of MainForm.cs if it is not already present:
using Microsoft.Extensions.DependencyInjection;

Why AddTransient for forms?

Transient lifetime means each request for AddBorrowerForm creates a fresh instance. This is correct for WinForms dialogs because each time the user clicks "Add Borrower", they get a clean form with empty fields.

  • Save all files and press Ctrl+Shift+B to build.

The build should succeed now with no errors.

Still seeing a compile error?

  • Make sure using Microsoft.Extensions.DependencyInjection; is at the top of MainForm.cs. The GetRequiredService extension method lives in that namespace.
  • Confirm that AddBorrowerForm is in the LoanManager.Forms namespace. If Visual Studio placed it in a different namespace, update the class file.
  • Check that you removed the old new AddBorrowerForm() line entirely.

Help me debug my DI setup for child forms.

Wire Edit, Delete, and test the full CRUD flow

With the Add flow working through DI, Edit and Delete follow the same pattern. These call the repository methods you already have.

  • In the MainForm designer, add two more buttons: btnEdit (Text: Edit) and btnDelete (Text: Delete).
  • Double-click btnEdit and add the edit handler that reads the selected row and updates it:
private async void btnEdit_Click(object sender, EventArgs e)
{
    if (dgvBorrowers.CurrentRow == null) return;

    // Get the selected borrower's data from the grid
    var id = (int)dgvBorrowers.CurrentRow.Cells["Id"].Value;
    var borrower = await _borrowerRepository.GetByIdAsync(id);
    if (borrower == null) return;

    // Prompt user for updated values
    borrower.FirstName = ShowInputBox("First Name:", borrower.FirstName);
    borrower.LastName = ShowInputBox("Last Name:", borrower.LastName);
    borrower.Email = ShowInputBox("Email:", borrower.Email);
    borrower.Phone = ShowInputBox("Phone:", borrower.Phone);

    await _borrowerRepository.UpdateAsync(borrower);

    // Refresh the grid
    var borrowers = await _borrowerRepository.GetAllAsync();
    dgvBorrowers.DataSource = borrowers.ToList();
}

What does this code do?

The handler reads the Id from the currently selected DataGridView row, fetches the full borrower, prompts for updated values, and calls UpdateAsync on the repository. The grid rebinds afterwards to show the changes.

  • Add a helper method to MainForm for the simple input prompt:
private string ShowInputBox(string prompt, string defaultValue)
{
    // Simple input dialog using built-in Interaction
    return Microsoft.VisualBasic.Interaction.InputBox(prompt, "Edit Borrower", defaultValue);
}

Alternative to InputBox

The Microsoft.VisualBasic.Interaction.InputBox method provides a quick dialog without building a custom form. For a production app, you would create a dedicated Edit form resolved from DI, following the same GetRequiredService pattern.

  • Double-click btnDelete and add the delete handler:
private async void btnDelete_Click(object sender, EventArgs e)
{
    if (dgvBorrowers.CurrentRow == null) return;

    var id = (int)dgvBorrowers.CurrentRow.Cells["Id"].Value;
    var name = dgvBorrowers.CurrentRow.Cells["FirstName"].Value?.ToString();

    // Confirm before deleting
    var result = MessageBox.Show(
        $"Delete borrower '{name}'?",
        "Confirm Delete",
        MessageBoxButtons.YesNo,
        MessageBoxIcon.Warning);

    if (result == DialogResult.Yes)
    {
        await _borrowerRepository.DeleteAsync(id);
        var borrowers = await _borrowerRepository.GetAllAsync();
        dgvBorrowers.DataSource = borrowers.ToList();
    }
}

What does this code do?

The delete handler shows a confirmation dialog before calling DeleteAsync on the repository. After deletion, the grid refreshes to remove the deleted row.

  • Save all files.
  • Press F5 to run the application.

What do you expect to see when you click Add Borrower, fill in the fields, and click Save?

  • Click Add Borrower.
  • Enter a first name, last name, email, and phone number in the fields.
  • Click Save.

The dialog closes and the new borrower appears in the main grid immediately, without restarting the app. This confirms the full DI pipeline works: MainForm resolved AddBorrowerForm from the container, the form received its repository, and the grid refreshed on success.

  • Select a borrower row and click Edit to test the update flow.
  • Select a borrower row and click Delete to test removal.

New borrower not appearing in the grid?

  • Confirm you are setting DialogResult = DialogResult.OK in AddBorrowerForm before calling Close(). Without this, the parent's if (form.ShowDialog() == DialogResult.OK) check skips the refresh.
  • Check your connection string in appsettings.json points to the correct LoanManagement database. If the INSERT succeeds but the grid shows stale data, the SELECT might be hitting a different database.
  • Make sure dgvBorrowers.DataSource = borrowers.ToList() is inside the if (form.ShowDialog() == DialogResult.OK) block, not outside it.

Help me debug my AddBorrowerForm refresh logic.

✔️ Awesome, I've got everything!

Great. Double check you have saved all your files, including Program.cs, MainForm.cs, and AddBorrowerForm.cs.

ⓧ I'd like to double check the full code

Here is the updated Program.cs with the AddBorrowerForm registration. Confirm yours matches:

using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using LoanManager.Data;
using LoanManager.Forms;

namespace LoanManager;

static class Program
{
    [STAThread]
    static void Main()
    {
        ApplicationConfiguration.Initialize();

        var builder = Host.CreateApplicationBuilder();

        // Data access
        builder.Services.AddSingleton<SqlConnectionFactory>();
        builder.Services.AddScoped<IBorrowerRepository, BorrowerRepository>();

        // Forms
        builder.Services.AddTransient<MainForm>();
        builder.Services.AddTransient<AddBorrowerForm>();

        var host = builder.Build();
        host.Start();

        var mainForm = host.Services.GetRequiredService<MainForm>();
        Application.Run(mainForm);

        host.StopAsync().GetAwaiter().GetResult();
        host.Dispose();
    }
}

The form code files (AddBorrowerForm.cs and MainForm.cs) depend on your designer layout, but confirm these key elements are present:

  • AddBorrowerForm constructor accepts IBorrowerRepository and stores it in a private field.
  • Save button handler calls _borrowerRepository.InsertAsync(borrower) then sets DialogResult = DialogResult.OK.
  • MainForm constructor accepts both IBorrowerRepository and IServiceProvider.
  • Add Borrower button uses _serviceProvider.GetRequiredService<AddBorrowerForm>() to open the form.
  • After DialogResult.OK, the grid rebinds with await _borrowerRepository.GetAllAsync().

You now have full borrower CRUD powered by dependency injection. Next up, you will build a service layer that adds business rules to loan creation.

Create Loans with a Service Layer

Your borrower CRUD is working. You can add, edit, and delete borrowers from a form that the DI container resolves automatically.

Now you need to create loans against those borrowers. Creating a loan is not a simple INSERT. It involves business rules: the borrower must exist, the amount must be positive, the term must be valid, and the total repayment needs to be calculated. If you put all that logic directly in the form, it becomes untestable spaghetti that cannot be reused anywhere else. A service layer encapsulates these rules in a dedicated class.

In this step, get ready to:
  • Create a loan repository with Dapper queries including a JOIN for borrower names.
  • Build a service layer that validates loan inputs and calculates total repayment.
  • Wire a CreateLoanForm through DI and confirm the calculated repayment appears on save.
Create the loan models and repository

Before writing any business logic, you need the data models that represent a loan and the repository that talks to SQL Server.

  • In the Models folder, create a new file called Loan.cs with the following code:
namespace LoanManager.Models;

public class Loan
{
    public int Id { get; set; }
    public int BorrowerId { get; set; }
    public decimal Amount { get; set; }
    public decimal InterestRate { get; set; }
    public int TermMonths { get; set; }
    public DateTime StartDate { get; set; }
    public string Status { get; set; } = "Active";
    public decimal TotalRepayment { get; set; }
    public DateTime CreatedAt { get; set; } = DateTime.Now;
}

What does this model represent?

Each property maps directly to a column in the Loans table you created earlier. The Status defaults to "Active" because every new loan starts as active. TotalRepayment is calculated by the service layer before the loan is saved.

  • In the same Models folder, create LoanViewModel.cs with this code:
namespace LoanManager.Models;

public class LoanViewModel
{
    public int Id { get; set; }
    public decimal Amount { get; set; }
    public decimal InterestRate { get; set; }
    public int TermMonths { get; set; }
    public DateTime StartDate { get; set; }
    public string Status { get; set; } = string.Empty;
    public decimal TotalRepayment { get; set; }
    public string BorrowerName { get; set; } = string.Empty;
}

Why a separate ViewModel?

The Loan model maps to the database row, which stores a BorrowerId integer. But when you display loans in a grid, you want to show the borrower's full name. LoanViewModel adds a BorrowerName property that gets populated by a JOIN query.

  • Save both files.
  • Build the solution by pressing Ctrl+Shift+B.

You should see "Build succeeded" in the Output window. The two model classes compile cleanly with no dependencies.

Build failed with errors?

Check that both files are in the Models folder and use the namespace LoanManager.Models. A mismatched namespace causes "type not found" errors later.

Help me fix my model build errors

  • In the Data folder, create ILoanRepository.cs with the interface definition:
using LoanManager.Models;

namespace LoanManager.Data;

public interface ILoanRepository
{
    Task<IEnumerable<LoanViewModel>> GetAllWithBorrowerAsync();
    Task<IEnumerable<Loan>> GetByBorrowerIdAsync(int borrowerId);
    Task<int> InsertAsync(Loan loan);
}

What does this interface define?

  • GetAllWithBorrowerAsync returns LoanViewModel objects (with the borrower name already joined in).
  • GetByBorrowerIdAsync fetches raw Loan records for a specific borrower.
  • InsertAsync persists a new loan and returns the generated ID.
  • In the Data folder, create LoanRepository.cs and add the class shell with the first query method:
using Dapper;
using LoanManager.Models;

namespace LoanManager.Data;

public class LoanRepository : ILoanRepository
{
    private readonly SqlConnectionFactory _factory;

    public LoanRepository(SqlConnectionFactory factory)
    {
        _factory = factory;
    }

    public async Task<IEnumerable<LoanViewModel>> GetAllWithBorrowerAsync()
    {
        using var connection = _factory.CreateConnection();
        var sql = """
            SELECT l.Id, l.Amount, l.InterestRate, l.TermMonths, l.StartDate, l.Status,
                   l.TotalRepayment, b.FirstName + ' ' + b.LastName AS BorrowerName
            FROM Loans l
            INNER JOIN Borrowers b ON l.BorrowerId = b.Id
            ORDER BY l.StartDate DESC
            """;
        return await connection.QueryAsync<LoanViewModel>(sql);
    }

What does the JOIN query do?

The SQL joins the Loans table with Borrowers on the foreign key. It concatenates FirstName and LastName into a single BorrowerName column. Dapper maps this directly to the LoanViewModel.BorrowerName property.

  • Below the GetAllWithBorrowerAsync method (still inside the class), add the remaining two methods:
    public async Task<IEnumerable<Loan>> GetByBorrowerIdAsync(int borrowerId)
    {
        using var connection = _factory.CreateConnection();
        return await connection.QueryAsync<Loan>(
            "SELECT * FROM Loans WHERE BorrowerId = @BorrowerId", new { BorrowerId = borrowerId });
    }

    public async Task<int> InsertAsync(Loan loan)
    {
        using var connection = _factory.CreateConnection();
        var sql = """
            INSERT INTO Loans (BorrowerId, Amount, InterestRate, TermMonths, StartDate, Status, TotalRepayment, CreatedAt)
            VALUES (@BorrowerId, @Amount, @InterestRate, @TermMonths, @StartDate, @Status, @TotalRepayment, @CreatedAt);
            SELECT CAST(SCOPE_IDENTITY() AS INT);
            """;
        return await connection.ExecuteScalarAsync<int>(sql, loan);
    }
}

What do these methods do?

  • GetByBorrowerIdAsync fetches all loans belonging to one borrower using a parameterized query.
  • InsertAsync inserts all loan fields and returns the auto-generated ID using SCOPE_IDENTITY().
  • Save ILoanRepository.cs and LoanRepository.cs.
  • Build the solution with Ctrl+Shift+B.

You should see "Build succeeded" again. The repository compiles because it depends only on the models and SqlConnectionFactory which already exist.

Seeing CS0535 interface not implemented?

This means LoanRepository is missing one of the three methods from ILoanRepository. Check that you have GetAllWithBorrowerAsync, GetByBorrowerIdAsync, and InsertAsync all present with matching signatures.

Help me fix my interface implementation errors

Build the service layer

The service layer sits between your forms and the repository. It enforces business rules before any data reaches the database.

  • Create a new folder called Services in the project root. Inside it, create ILoanService.cs:
using LoanManager.Models;

namespace LoanManager.Services;

public interface ILoanService
{
    Task<int> CreateLoanAsync(int borrowerId, decimal amount, decimal interestRate, int termMonths);
}

Why does CreateLoanAsync take primitives instead of a Loan object?

The service owns the creation logic. It decides the StartDate, Status, and TotalRepayment values. Accepting only the raw inputs forces the caller (your form) to pass data without making decisions that belong to the service.

  • In the Services folder, create LoanService.cs with the class structure and validation logic:
using LoanManager.Data;
using LoanManager.Models;

namespace LoanManager.Services;

public class LoanService : ILoanService
{
    private readonly ILoanRepository _loanRepository;
    private readonly IBorrowerRepository _borrowerRepository;

    public LoanService(ILoanRepository loanRepository, IBorrowerRepository borrowerRepository)
    {
        _loanRepository = loanRepository;
        _borrowerRepository = borrowerRepository;
    }

    public async Task<int> CreateLoanAsync(int borrowerId, decimal amount, decimal interestRate, int termMonths)
    {
        // Verify the borrower exists in the database
        var borrower = await _borrowerRepository.GetByIdAsync(borrowerId);
        if (borrower is null)
            throw new ArgumentException("Borrower not found.");

        // Validate loan parameters
        if (amount <= 0)
            throw new ArgumentException("Loan amount must be greater than zero.");

        if (termMonths < 1 || termMonths > 360)
            throw new ArgumentException("Term must be between 1 and 360 months.");

        if (interestRate < 0)
            throw new ArgumentException("Interest rate cannot be negative.");

What do these validations enforce?

  • The borrower check prevents orphaned loans pointing to non-existent borrowers.
  • Amount must be positive. A zero or negative loan makes no sense.
  • Term is capped at 360 months (30 years). This prevents absurd values from entering the database.
  • Interest rate can be zero (interest-free loan) but not negative.
  • Below the validation checks (still inside the CreateLoanAsync method), add the calculation and loan creation logic:
        // Calculate total repayment using simple interest
        var totalRepayment = amount + (amount * interestRate / 100m * termMonths / 12m);

        var loan = new Loan
        {
            BorrowerId = borrowerId,
            Amount = amount,
            InterestRate = interestRate,
            TermMonths = termMonths,
            StartDate = DateTime.Today,
            Status = "Active",
            TotalRepayment = totalRepayment,
            CreatedAt = DateTime.Now
        };

        return await _loanRepository.InsertAsync(loan);
    }
}

How is TotalRepayment calculated?

The formula is: Amount + (Amount * InterestRate/100 * TermMonths/12). For a $10,000 loan at 5% over 24 months, that gives $10,000 + ($10,000 * 0.05 * 2) = $11,000. This is simple interest. The Secret Mission later introduces compound amortization for more accurate monthly breakdowns.

  • Save ILoanService.cs and LoanService.cs.
  • Build the solution with Ctrl+Shift+B.

You should see "Build succeeded". The service compiles because it depends on the repository interfaces you created moments ago.

Cannot resolve symbol IBorrowerRepository?

Make sure LoanService.cs has using LoanManager.Data; at the top. The borrower repository interface lives in the Data namespace.

Help me fix my LoanService using statements

Create the form and wire dependency injection

You now have the full backend pipeline: models, repository, and service. The last piece is a form that lets users input loan details and a DI registration that ties everything together.

  • Right-click the Forms folder in Solution Explorer and select Add then Form (Windows Forms).
  • Name it CreateLoanForm.cs and click Add.
  • In the Designer, add a ComboBox named cmbBorrower for selecting the borrower.
  • Add TextBox controls named txtAmount, txtInterestRate, and txtTermMonths.
  • Add a Button named btnSave and add labels for each field.
  • Set the form's Text property to Create Loan in the Properties panel.
  • Open the CreateLoanForm.cs code-behind and replace its contents with the following constructor and load logic:
using LoanManager.Data;
using LoanManager.Models;
using LoanManager.Services;

namespace LoanManager.Forms;

public partial class CreateLoanForm : Form
{
    private readonly ILoanService _loanService;
    private readonly IBorrowerRepository _borrowerRepository;

    public CreateLoanForm(ILoanService loanService, IBorrowerRepository borrowerRepository)
    {
        InitializeComponent();
        _loanService = loanService;
        _borrowerRepository = borrowerRepository;
    }

    private async void CreateLoanForm_Load(object sender, EventArgs e)
    {
        // Populate the borrower dropdown
        var borrowers = await _borrowerRepository.GetAllAsync();
        cmbBorrower.DataSource = borrowers.ToList();
        cmbBorrower.DisplayMember = "LastName";
        cmbBorrower.ValueMember = "Id";
    }

What does this code do?

  • The constructor receives ILoanService and IBorrowerRepository via DI. The form never creates these itself.
  • On form load, it queries all borrowers and binds them to the ComboBox. DisplayMember shows the last name while ValueMember stores the ID for lookup.
  • Below the load method, add the Save button click handler:
    private async void btnSave_Click(object sender, EventArgs e)
    {
        try
        {
            var borrowerId = (int)cmbBorrower.SelectedValue!;
            var amount = decimal.Parse(txtAmount.Text);
            var interestRate = decimal.Parse(txtInterestRate.Text);
            var termMonths = int.Parse(txtTermMonths.Text);

            await _loanService.CreateLoanAsync(borrowerId, amount, interestRate, termMonths);

            // Show the calculated total repayment
            var totalRepayment = amount + (amount * interestRate / 100m * termMonths / 12m);
            MessageBox.Show($"Loan created! Total repayment: {totalRepayment:C}",
                "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);

            DialogResult = DialogResult.OK;
            Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
    }
}

What happens when the user clicks Save?

The handler reads all field values, calls CreateLoanAsync on the service, and displays the total repayment in a MessageBox. If the service throws an ArgumentException (invalid inputs), the catch block shows the validation message to the user.

  • Wire the Load event to CreateLoanForm_Load in the Designer by double-clicking the form background.
  • Wire btnSave.Click to btnSave_Click by double-clicking the button in the Designer.
  • Save CreateLoanForm.cs.

Now register everything in the DI container so the app knows how to resolve these new types.

  • Open Program.cs and find the data access registration section. Add the loan repository and service registrations so the file looks like this:
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using LoanManager.Data;
using LoanManager.Services;
using LoanManager.Forms;

namespace LoanManager;

static class Program
{
    [STAThread]
    static void Main()
    {
        ApplicationConfiguration.Initialize();

        var builder = Host.CreateApplicationBuilder();

        // Data access
        builder.Services.AddSingleton<SqlConnectionFactory>();
        builder.Services.AddScoped<IBorrowerRepository, BorrowerRepository>();
        builder.Services.AddScoped<ILoanRepository, LoanRepository>();

        // Services
        builder.Services.AddScoped<ILoanService, LoanService>();
  • In the Forms registration section below, add CreateLoanForm as transient:
        // Forms
        builder.Services.AddTransient<MainForm>();
        builder.Services.AddTransient<AddBorrowerForm>();
        builder.Services.AddTransient<CreateLoanForm>();

What changed in Program.cs?

  • Added using LoanManager.Services; at the top for the service namespace.
  • Registered ILoanRepository and ILoanService as scoped services so they share a connection scope.
  • Registered CreateLoanForm as transient so a fresh instance is created each time the dialog opens.
  • In MainForm, add a Button named btnCreateLoan with the text "Create Loan". Wire its click handler to resolve CreateLoanForm from the service provider:
private void btnCreateLoan_Click(object sender, EventArgs e)
{
    // Resolve the form with all its dependencies from DI
    var form = _serviceProvider.GetRequiredService<CreateLoanForm>();
    form.ShowDialog();
}
  • Save all files.

Before you run the app, what do you think will happen when you fill in a loan amount of 10000, interest rate of 5, and term of 24 months?

  • Press F5 to run the application.
  • Click the Create Loan button on the main form.
  • Select a borrower from the dropdown (one of your seeded borrowers should appear).
  • Enter 10000 for Amount, 5 for Interest Rate, and 24 for Term Months.
  • Click Save.

You should see a MessageBox displaying "Loan created! Total repayment: $11,000.00". That confirms the service calculated 10000 + (10000 * 5/100 * 24/12) = $11,000.

ComboBox shows empty or throws a NullReferenceException?

Check that CreateLoanForm_Load is wired to the form's Load event. In the Designer, click the form background, open the Events panel (lightning bolt icon), and confirm Load points to CreateLoanForm_Load.

Also confirm your SQL Server Express instance is running and the LoanManagement database has the seeded borrowers from Step 2.

Help me debug my ComboBox loading issue

✔️ Awesome, I've got everything!

Great. Double check you have saved all your files across the Models, Data, Services, and Forms folders.

ⓧ I'd like to double check the full code

Scroll up and confirm each of these files is present in your project, in order:

  • Models/Loan.cs with 9 properties including TotalRepayment.
  • Models/LoanViewModel.cs with 8 properties including BorrowerName.
  • Data/ILoanRepository.cs with 3 method signatures.
  • Data/LoanRepository.cs with the JOIN query in GetAllWithBorrowerAsync, plus GetByBorrowerIdAsync and InsertAsync.
  • Services/ILoanService.cs with CreateLoanAsync.
  • Services/LoanService.cs with validation checks and the total repayment formula.
  • Program.cs with ILoanRepository, ILoanService, and CreateLoanForm registered.

Your loan creation pipeline is live. You have a clean separation: the form handles UI, the service handles rules, and the repository handles data. Next up, you will add LINQ-powered search and filtering so you can find loans without scrolling through a flat grid.

Add LINQ-Powered Search and Filtering

Your loan data is growing, but the only way to find a specific loan is to scroll through a flat grid. With real data, that becomes impractical fast.

LINQ solves this by letting you filter and sort an in-memory collection without writing additional SQL queries for every filter combination. In this step, you will add a Loans tab with real-time search and status filtering powered entirely by LINQ.

In this step, get ready to:
  • Add a TabControl with Borrowers and Loans tabs to MainForm.
  • Wire LINQ filtering on the Loans grid by borrower name and status.
  • Add a summary panel with LINQ aggregation showing loan statistics.
Add a TabControl with Borrowers and Loans tabs

Right now MainForm shows borrowers in a single DataGridView. You need a second view for loans with search controls. A TabControl lets you organize both views in one form without cluttering the layout.

  • Open MainForm.cs in the Visual Studio code editor.
  • Add the following fields at the top of the class, below your existing fields, for the new UI controls:
// Loans tab UI controls
private TabControl tabControl;
private TabPage borrowersTab;
private TabPage loansTab;
private DataGridView loansGrid;
private TextBox searchBox;
private ComboBox statusFilter;

What does this code do?

These fields declare the controls you will build in code. The TabControl holds two pages: one for your existing borrower grid and one for the new loans view with search and filter controls.

  • Add a new private method called InitializeLoansTab to build the Loans tab UI:
private void InitializeLoansTab()
{
    // Create the TabControl and tabs
    tabControl = new TabControl { Dock = DockStyle.Fill };
    borrowersTab = new TabPage("Borrowers");
    loansTab = new TabPage("Loans");

    // Move existing borrower grid into the Borrowers tab
    Controls.Remove(borrowersGrid);
    borrowersTab.Controls.Add(borrowersGrid);
    borrowersGrid.Dock = DockStyle.Fill;

    // Search box for borrower name filtering
    searchBox = new TextBox
    {
        PlaceholderText = "Search by borrower name...",
        Dock = DockStyle.Top,
        Height = 30
    };
}

What does this code do?

This method creates the TabControl, moves your existing borrower DataGridView into a dedicated tab, and starts building the Loans tab with a search TextBox at the top.

  • Continue building InitializeLoansTab by adding the status ComboBox and loans DataGridView. Add this code directly below the closing brace of the searchBox initializer, still inside the method:
    // Status filter dropdown
    statusFilter = new ComboBox
    {
        Dock = DockStyle.Top,
        DropDownStyle = ComboBoxStyle.DropDownList
    };
    statusFilter.Items.AddRange(new[] { "All", "Active", "Paid", "Overdue" });
    statusFilter.SelectedIndex = 0;

    // Loans DataGridView
    loansGrid = new DataGridView
    {
        Dock = DockStyle.Fill,
        ReadOnly = true,
        AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill,
        SelectionMode = DataGridViewSelectionMode.FullRowSelect
    };

What does this code do?

The ComboBox offers four filter options: All, Active, Paid, and Overdue. Setting DropDownStyle to DropDownList prevents the user from typing arbitrary values. The loans grid is read-only and auto-sizes columns to fill the tab.

  • Finish the method by assembling the controls into the Loans tab and adding both tabs to the TabControl. Add this code at the end of InitializeLoansTab, before the closing brace:
    // Assemble Loans tab (add in reverse dock order)
    loansTab.Controls.Add(loansGrid);
    loansTab.Controls.Add(searchBox);
    loansTab.Controls.Add(statusFilter);

    // Add tabs to the TabControl
    tabControl.TabPages.Add(borrowersTab);
    tabControl.TabPages.Add(loansTab);
    Controls.Add(tabControl);
}

Why reverse dock order?

WinForms docks controls from last-added to first-added. Adding the grid first means it fills the remaining space after the top-docked search box and filter take their share.

  • Call InitializeLoansTab() from your MainForm constructor, after your existing UI setup code.
  • Save MainForm.cs and run the app with F5.

You should see two tabs at the top of the form: Borrowers (with your existing grid) and Loans (with an empty grid, a search box, and a status dropdown).

Don't see the tabs?

Make sure you called InitializeLoansTab() in the constructor after your existing setup. If the borrower grid disappeared, confirm you are using the same field name (borrowersGrid) that you declared earlier.

help me debug my TabControl not showing in MainForm

Load loans and wire LINQ filtering

The Loans tab has controls but no data. You need to load all loans into memory once, then use LINQ to filter that collection every time the user types or changes the status dropdown.

  • Add ILoanRepository as a constructor parameter on MainForm (alongside the existing IServiceProvider and IBorrowerRepository). Store it in a private field.
  • Add a field to hold the full loan list and the data-loading method:
// In-memory loan collection for LINQ filtering
private List<LoanViewModel> _allLoans = new();

private async Task LoadLoansAsync()
{
    var loans = await _loanRepository.GetAllWithBorrowerAsync();
    _allLoans = loans.ToList();
    ApplyLoanFilters();
}

What does this code do?

The _allLoans field stores every loan in memory. GetAllWithBorrowerAsync() runs the JOIN query you built in Step 4 to fetch loans with their borrower names. Once loaded, ApplyLoanFilters() handles the LINQ filtering.

  • Add the ApplyLoanFilters method that performs the LINQ queries:
private void ApplyLoanFilters()
{
    var searchText = searchBox.Text;
    var selectedStatus = statusFilter.SelectedItem?.ToString();

    // Start with all loans
    var filtered = _allLoans.AsEnumerable();

    // Filter by borrower name (case-insensitive)
    if (!string.IsNullOrWhiteSpace(searchText))
    {
        filtered = filtered.Where(l =>
            l.BorrowerName.Contains(searchText, StringComparison.OrdinalIgnoreCase));
    }

    // Filter by loan status
    if (selectedStatus != "All" && !string.IsNullOrEmpty(selectedStatus))
    {
        filtered = filtered.Where(l => l.Status == selectedStatus);
    }

    loansGrid.DataSource = filtered.ToList();
}

What does this code do?

This is where LINQ does the heavy lifting. The method chains two .Where() calls on the in-memory list. The first filters by borrower name using StringComparison.OrdinalIgnoreCase so searches are case-insensitive. The second filters by status unless "All" is selected.

No new SQL queries run here. You loaded the data once, and LINQ filters the in-memory collection instantly.

Why filter in memory instead of SQL?

For an MVP with hundreds or thousands of records, LINQ filtering on a pre-loaded list is fast and avoids a database round-trip on every keystroke. For millions of records you would push filtering to SQL, but for this scale LINQ is the right tool.

  • Wire the search and filter events by adding these two lines at the end of your InitializeLoansTab method, just before the closing brace:
// Wire filter events to trigger LINQ re-filtering
searchBox.TextChanged += (s, e) => ApplyLoanFilters();
statusFilter.SelectedIndexChanged += (s, e) => ApplyLoanFilters();
  • Call await LoadLoansAsync() in your form's Load event handler (alongside where you load borrowers).
  • Save MainForm.cs and run the app with F5.

Switch to the Loans tab. You should see your loans listed in the grid with borrower names. Type part of a borrower's name in the search box and watch the grid filter in real-time.

Loans grid is empty?

Make sure you created at least one loan in Step 4. If you did, confirm that LoadLoansAsync() is being called in the form's Load event. Also check that _loanRepository is assigned from the constructor parameter.

help me debug why my loans grid shows no data

Add a summary panel with LINQ aggregation

Filtering shows individual loans, but a summary panel gives you instant insight into the portfolio. LINQ aggregation methods let you compute totals and averages across the filtered collection in a single pass.

  • Add three Label fields to MainForm for the summary statistics:
// Summary panel labels
private Label lblActiveCount;
private Label lblTotalOutstanding;
private Label lblAverageRate;
private Panel summaryPanel;
  • Add a method to build the summary panel. Place it below InitializeLoansTab:
private void InitializeSummaryPanel()
{
    summaryPanel = new Panel
    {
        Dock = DockStyle.Bottom,
        Height = 60,
        Padding = new Padding(10)
    };

    lblActiveCount = new Label { AutoSize = true, Location = new Point(10, 10) };
    lblTotalOutstanding = new Label { AutoSize = true, Location = new Point(10, 35) };
    lblAverageRate = new Label { AutoSize = true, Location = new Point(300, 10) };

    summaryPanel.Controls.Add(lblActiveCount);
    summaryPanel.Controls.Add(lblTotalOutstanding);
    summaryPanel.Controls.Add(lblAverageRate);

    // Add panel to Loans tab before the grid
    loansTab.Controls.Add(summaryPanel);
}

What does this code do?

The summary panel docks at the bottom of the Loans tab. Three labels display the aggregate statistics that you will calculate with LINQ in the next chunk.

  • Add a method that calculates the aggregates using LINQ:
private void UpdateSummary()
{
    // Count active loans
    var activeCount = _allLoans
        .Where(l => l.Status == "Active")
        .Count();

    // Sum total outstanding amount for active loans
    var totalOutstanding = _allLoans
        .Where(l => l.Status == "Active")
        .Sum(l => l.Amount);

    // Average interest rate across all loans
    var averageRate = _allLoans.Any()
        ? _allLoans.Average(l => l.InterestRate)
        : 0m;

    lblActiveCount.Text = $"Active Loans: {activeCount}";
    lblTotalOutstanding.Text = $"Outstanding: {totalOutstanding:C}";
    lblAverageRate.Text = $"Avg Rate: {averageRate:F2}%";
}

What does this code do?

Three LINQ aggregation methods compute the statistics. .Count() tallies active loans. .Sum() totals the outstanding amount. .Average() computes the mean interest rate.

The .Any() guard prevents a divide-by-zero exception when the list is empty.

  • Call InitializeSummaryPanel() from your constructor, right after InitializeLoansTab().
  • Call UpdateSummary() at the end of LoadLoansAsync(), after the line that calls ApplyLoanFilters().
  • Save MainForm.cs.

Before you run the app, predict: what values do you expect to see in the summary panel based on the loans you created in Step 4?

  • Run the app with F5 and switch to the Loans tab.

You should see the summary panel at the bottom showing your active loan count, total outstanding amount, and average interest rate. Type a borrower name in the search box and the grid filters in real-time. Select Overdue from the status dropdown and only overdue loans appear (or an empty grid if you have none yet).

Summary shows all zeros?

Confirm that UpdateSummary() is called after _allLoans is populated. If you placed it before the ToList() assignment, the list is still empty when the labels are set.

help me fix my LINQ summary panel showing zeros

Your loan management app now has a professional search and filter experience. Users can find any loan instantly by name or status, and the summary panel gives a portfolio overview at a glance. Next up, you will extend this with an amortization schedule in the Secret Mission.

Secret mission

Add a Loan Amortization Schedule Calculator

Your app shows a total repayment number, but borrowers want to know what they pay each month. Build an amortization calculator that breaks any loan into a month-by-month schedule showing exactly how much goes to principal vs. interest.

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project runs entirely locally using SQL Server Express, so there are no ongoing costs.

Resources you used:

  • The LoanManagement database in SQL Server Express.
  • The LoanManager project folder on your Desktop.

Keep everything running

No action needed. Choose this if you're still actively building or plan to continue with future projects in this series.

  • Your LoanManagement database stays in SQL Server Express and only uses disk space.
  • Your LoanManager project folder stays on your Desktop ready to open in Visual Studio.
  • SQL Server Express does not consume resources when idle. It only activates when a connection is made.

Pause - I'll come back to this later

No action needed. SQL Server Express only runs when accessed, so your database sits idle with no resource consumption until you reconnect.

  • Your database and project files remain intact on disk.
  • When you return, open the LoanManager solution in Visual Studio and run it. Everything will pick up where you left off.

Delete - I don't want to use this again

Remove all project resources and start fresh. This deletes your database and project files permanently.

  • Drop the database by opening SQL Server Management Studio (SSMS), connecting to your SQL Express instance, and running this query in a new query window:
DROP DATABASE LoanManagement;
  • Delete the project folder. On macOS/Linux, run:
rm -rf ~/Desktop/LoanManager

On Windows, run:

rmdir /s /q %USERPROFILE%\Desktop\LoanManager

Nice Work!

Nice Work!

Nice work! You just built a full loan management desktop application with a professional layered architecture, from SQL Server all the way up to WinForms.

You've learned how to:

  • Built a multi-form WinForms application with dependency injection using Microsoft.Extensions.Hosting, resolving child forms from IServiceProvider on demand.
  • Created a repository layer with Dapper for high-performance SQL Server data access, writing raw SQL queries behind clean interfaces.
  • Implemented a service layer with business validation and used LINQ for in-memory filtering, searching, and aggregation on loan data.
  • Secret Mission: Built a loan amortization calculator that breaks loans into month-by-month payment schedules showing principal vs interest.

Ready to quiz yourself?