Customer.API: a layered .NET API, from stored procedures to a typed client
Customer.API is a REST API for managing customer records: names, dates of birth, addresses and contact details. The domain is deliberately simple, and that’s the point. With the business problem out of the way, the project is really about structure: how to split an API into layers that each have one job, and how those layers talk to each other.
It’s built on .NET 5 and ASP.NET Core, with SQL Server for storage and Dapper for data access. The whole thing went from an empty repo to a working API, a typed client and a test suite over a few days in September 2021, with features merged through pull requests.
The solution at a glance
The solution is split into eight projects. Each layer only knows about the one directly below it, and they all share a common set of models.
The database: schema and stored procedures
The schema lives in a SQL Server Database Project (.sqlproj), so tables and stored procedures are versioned alongside the C# code and deployed together. A customer is split across three tables:
CustomerId, but without a foreign key constraint.All data access goes through stored procedures: 13 in total, covering get, get-all, insert, update, delete and search for each table. That keeps the SQL in one place, reviewable and tunable by anyone who knows SQL Server, and it means the application only needs permission to execute procedures rather than read or write tables directly.
Search is the most interesting procedure. It accepts a forename, surname, postcode or email, and collects matching customer IDs from each table into a table variable before returning them:
DECLARE @results TABLE (Id uniqueidentifier not null)
IF (@Forename is not null or @Surename is not null)
INSERT INTO @results
SELECT TOP 100 CU.Id FROM dbo.Customer CU
WHERE (@Forename = CU.Forename) or (@Surename = CU.Surename)
IF (@PostCode is not null)
INSERT INTO @results
SELECT TOP 100 AD.CustomerId FROM dbo.[Address] AD
WHERE (@PostCode = AD.Postcode)
-- …email search against ContactInformation…
SELECT * FROM @results
The repository layer: Dapper and a unit of work
Each table gets a repository built on a generic base class. The base holds the connection string and hands out a fresh SqlConnection on every call, which suits ADO.NET’s connection pooling:
public abstract class Repository<T> : IRepository<T> where T : class
{
private readonly string _connection;
public Repository(string connection) => _connection = connection;
public IDbConnection Connection => new SqlConnection(_connection);
public abstract Task<IEnumerable<T>> GetAllAsync();
public abstract Task<T> GetAsync(Guid id);
public abstract Task<Guid?> InsertAsync(Guid? id, T entity);
public abstract Task<bool> UpdateAsync(T entity);
}
The concrete repositories use Dapper to call the stored procedures and map results straight onto the shared models. Writes run inside a transaction and only commit when exactly one row was affected:
using (IDbConnection connection = Connection)
{
connection.Open();
using (var tx = connection.BeginTransaction())
{
var result = await tx.Connection.ExecuteAsync("[dbo].[Customer_Insert]",
parameters, commandType: CommandType.StoredProcedure, transaction: tx);
if (result == 1) { tx.Commit(); return parameters.Get<Guid>("@CustomerId"); }
tx.Rollback();
return null;
}
}
A UnitOfWork class groups the three repositories behind one interface, so the business layer takes a single dependency instead of three:
public interface IUnitOfWork
{
IAddressRepository AddressRepo { get; }
IContactInformationRepository ContactInformationRepo { get; }
ICustomerRepository CustomerRepo { get; }
}
The business layer: assembling a customer
The API deals in whole customers, but the database stores them in three pieces. The business layer bridges that gap. Reading a customer means fetching the customer row, its address and its contact details, then stitching them into one object. Creating one runs the same steps in reverse:
Updates are smarter than a blind overwrite. The business layer loads the existing customer, compares it with the incoming one, and skips the database entirely if nothing has changed. When something has, it updates the customer and address rows and replaces the contact details, running the independent steps in parallel with Task.WhenAll:
var oldCustomer = await this.GetAsync(model.Id.Value);
if (model.CompareTo(oldCustomer) <= 0)
return false; // nothing to update
var updateResults = TaskSummary.TaskBoolSummary(await Task.WhenAll(new List<Task<bool>>
{
_CustomerRepository.UpdateAsync(model), // customer + address
_ContactInformationRepository.DeleteAsync(model.Id.Value)
}));
foreach (var contactInfo in model.ContactInformation)
if (await _ContactInformationRepository.InsertAsync(model.Id, contactInfo) == null)
updateResults = false;
Validation with FluentValidation
Validation rules live in their own classes rather than as attributes on the models, which keeps the models clean and makes the rules testable on their own. The customer validator checks the GUID, the surname, a UK date format and a full UK postcode pattern, then validates each contact detail with a child validator:
public CustomerValidator()
{
RuleFor(x => x.Id).NotNull();
RuleFor(x => x.Surename).MinimumLength(3).Matches("[a-zA-Z]");
RuleFor(x => x.DateOfBirth).Must(BeAValidDate)
.WithMessage("Date of birth is not in the correct format");
RuleFor(x => x.Address.Postcode).NotNull().Matches(UkPostcodePattern);
RuleForEach(x => x.ContactInformation).SetValidator(new ContactInformationValidator());
}
The contact validator checks the type is a known ContactInfomationType, then validates the value against that type: email addresses through System.Net.Mail.MailAddress, phone numbers against a pattern. The controller runs validation before touching the business layer and returns 400 with the full list of errors if anything fails.
A typed HTTP client
Rather than leave consumers to build URLs and parse JSON themselves, the solution ships its own client library. ICustomerService exposes methods like GetAllCustomers(), GetCustomers(guid) and InsertCustomer(customer), and returns the same shared models the API uses. A small console app exercises it end to end: list customers, insert one, read it back, update it, then delete it.
Tests
The test project has 37 MSTest tests across three areas:
- Validators (19 tests) use FluentValidation’s
TestHelperto check each rule against valid and invalid customers: missing contact details, bad postcodes, malformed dates and invalid emails. - Business layer (8 tests) and repositories (10 tests) use Moq to set up the expected inputs and outputs for each operation.
Looking back: what I’d change
I built this in 2021. Reading it again now, as a team lead who reviews this kind of code every week, a few things stand out. I’ve left the code as it was, because the gap between then and now is part of the story.
- Make the unit of work a real unit of work. Here it’s a container for three repositories, each opening its own connection. Creating a customer runs three separate operations, so if the contact insert fails, a half-created customer is left behind. A proper unit of work would share one connection and one transaction across all three repositories, and commit once at the end.
- Commit the delete.
DeleteAsyncexecutes inside a transaction but never callsCommit(), so disposing the transaction rolls the delete back. - Fix two search bugs. The email clause compares the parameter with itself (
@EmailAddress = @EmailAddress), so it matches every customer who has an email. And in the business layer,customerIds.Distinct()discards its result, so duplicates aren’t actually removed. - Give the models value equality.
CompareTocomparesAddressand the contact list by reference, so it always reports a change and the “skip unchanged updates” optimisation never triggers. Making the models C#recordtypes would fix this in one line each. - Test the real classes. The business and repository tests mock the very interfaces they’re testing, so they check Moq’s setup rather than my code. The validator tests are the genuinely useful ones. Today I’d mock the layer below the class under test, and add integration tests against a real SQL Server in a container using Testcontainers.
- Avoid N+1 queries. Getting all customers runs one query for the list, then two more per customer. A single procedure returning multiple result sets, read with Dapper’s
QueryMultiple, would do it in one round trip. - Modernise the plumbing. Use
IHttpClientFactoryinstead of creating anHttpClientper call and blocking on.Result; usethrow;instead ofthrow ex;so stack traces survive; store dates of birth asdate, not text; and actually wire up the Serilog packages the API already references.
None of these change the shape of the solution, which I’d still largely keep. Clear layers, schema in source control, validation separate from models, and a client library for consumers have all held up. The fixes are in the details, which is where most real-world bugs live.
The code is on GitHub.