Repository Pattern
A repository hides SQL behind a simple interface. Controllers ask for products; they never see a SqlCommand.
On this page · 5 min · Advanced
On this page
- In simple words
- Learning objectives
- Explanation
- Code example
- Step by step
- Real-world use
- Common mistakes
- Best practices
- Remember this
- Practice exercise
- Mini quiz
- Related topics
- My notes
- Reading time
- 5 min
- Difficulty
- Advanced
- Updated
- Oct 29, 2026
Related tutorials
In simple words#
The repository is the office clerk: controllers ask “give me product 5”; the clerk knows which register, which query and how to fill the form.
- Repository
- Class that hides data access.
- Interface
- IProductRepository — the contract.
- Benefit
- Controllers never see SQL.
Learning objectives#
- Define a repository interface
- Implement async CRUD with ADO.NET
- Use it from API endpoints
- Make code testable
Explanation#
The interface describes what the app needs (GetAll, GetById, Create, Update, Delete) in terms of domain types.
The implementation contains all SQL and ADO.NET code, using the connection factory from DI.
Callers depend on the interface, so you can swap ADO.NET for Dapper or EF Core, or use a fake in unit tests.
Code example: ProductRepository#
public record Product(int Id, string Name, decimal Price, int Stock);
public interface IProductRepository
{
Task<List<Product>> GetAllAsync(CancellationToken ct = default);
Task<Product?> GetByIdAsync(int id, CancellationToken ct = default);
Task<int> CreateAsync(Product p, CancellationToken ct = default);
Task<bool> UpdateAsync(Product p, CancellationToken ct = default);
Task<bool> DeleteAsync(int id, CancellationToken ct = default);
}
public sealed class ProductRepository(IDbConnectionFactory db) : IProductRepository
{
private const string Columns = "Id, Name, Price, Stock";
private static Product Map(SqlDataReader r) => new(r.GetInt32(0), r.GetString(1), r.GetDecimal(2), r.GetInt32(3));
public async Task<List<Product>> GetAllAsync(CancellationToken ct = default)
{
await using var conn = db.Create();
await conn.OpenAsync(ct);
await using var cmd = new SqlCommand(quot;SELECT {Columns} FROM Products ORDER BY Name", conn);
await using var r = await cmd.ExecuteReaderAsync(ct);
var list = new List<Product>();
while (await r.ReadAsync(ct)) list.Add(Map(r));
return list;
}
public async Task<Product?> GetByIdAsync(int id, CancellationToken ct = default)
{
await using var conn = db.Create();
await conn.OpenAsync(ct);
await using var cmd = new SqlCommand(quot;SELECT {Columns} FROM Products WHERE Id = @id", conn);
cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
await using var r = await cmd.ExecuteReaderAsync(ct);
return await r.ReadAsync(ct) ? Map(r) : null;
}
public async Task<int> CreateAsync(Product p, CancellationToken ct = default)
{
await using var conn = db.Create();
await conn.OpenAsync(ct);
await using var cmd = new SqlCommand(
"INSERT INTO Products (Name, Price, Stock) OUTPUT INSERTED.Id VALUES (@name, @price, @stock)", conn);
AddValues(cmd, p);
return (int)(await cmd.ExecuteScalarAsync(ct))!;
}
public async Task<bool> UpdateAsync(Product p, CancellationToken ct = default)
{
await using var conn = db.Create();
await conn.OpenAsync(ct);
await using var cmd = new SqlCommand(
"UPDATE Products SET Name = @name, Price = @price, Stock = @stock WHERE Id = @id", conn);
AddValues(cmd, p);
cmd.Parameters.Add("@id", SqlDbType.Int).Value = p.Id;
return await cmd.ExecuteNonQueryAsync(ct) == 1;
}
public async Task<bool> DeleteAsync(int id, CancellationToken ct = default)
{
await using var conn = db.Create();
await conn.OpenAsync(ct);
await using var cmd = new SqlCommand("DELETE FROM Products WHERE Id = @id", conn);
cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
return await cmd.ExecuteNonQueryAsync(ct) == 1;
}
private static void AddValues(SqlCommand cmd, Product p)
{
cmd.Parameters.Add("@name", SqlDbType.NVarChar, 100).Value = p.Name;
cmd.Parameters.Add(new SqlParameter("@price", SqlDbType.Decimal) { Precision = 10, Scale = 2, Value = p.Price });
cmd.Parameters.Add("@stock", SqlDbType.Int).Value = p.Stock;
}
}
Endpoints call repo.GetByIdAsync(5) and return 404 when it is null — no SQL anywhere outside the repository.
This example is illustrative. Copy or download it and run it in your own environment; the expected output is shown above.
Step-by-step explanation#
- The interface uses domain records and async methods with cancellation.
- A single Map method and AddValues helper avoid repetition.
- Every method opens its own pooled connection.
- Update and Delete return false when no row matched, so the API can return 404.
Real-world use case#
The Web API tracks on this site use exactly this shape; switching one method to a stored procedure changes only the repository.
Common mistakes#
Repositories load and save. Keep pricing rules and validation in a service layer.
A one-size-fits-all IRepository<T> often hides useful, specific queries. Prefer focused interfaces.
Best practices#
- One repository per aggregate
- Async methods with CancellationToken
- Return domain types or DTOs
- Unit test services with fake repositories; integration test repositories against a real database
Remember this#
Interface = what. Implementation = SQL.
Controllers never see SqlCommand.
false/null → 404.
Practice exercise#
Your task: Add a SearchAsync(string term) method to the interface and implementation.
Show solution
public async Task<List<Product>> SearchAsync(string term, CancellationToken ct = default)
{
await using var conn = db.Create();
await conn.OpenAsync(ct);
await using var cmd = new SqlCommand(quot;SELECT {Columns} FROM Products WHERE Name LIKE @q ORDER BY Name", conn);
cmd.Parameters.Add("@q", SqlDbType.NVarChar, 102).Value = quot;%{term}%";
await using var r = await cmd.ExecuteReaderAsync(ct);
var list = new List<Product>();
while (await r.ReadAsync(ct)) list.Add(Map(r));
return list;
}
Mini quiz#
What is the main benefit of depending on IProductRepository instead of ProductRepository?
Want more? Practise all ADO.NET MCQs · ADO.NET interview questions
My notes#
Saved in this browser.