Introduction
Modern enterprise applications require more than just simple CRUD operations. As applications grow larger, developers must focus on writing clean, maintainable, scalable, and organised code. This is where backend architecture patterns such as Repository Pattern and Unit of Work become extremely important.
Additionally, most real-world applications need stored procedures, server-side pagination, sorting, and searching, especially when handling large sets of data. When combined, these concepts help you build a stable, high-performance, production-quality application.
In this guide, you will learn how to create a complete backend architecture using:
Repository Pattern
Unit of Work Pattern
Stored Procedures in SQL Server
EF Core as the ORM
ASP.NET Core Web API as the backend
Pagination, Sorting, and Searching
Angular frontend (for consuming the API)
This is a complete and professional architecture used in real enterprise-level projects.
Let us begin.
Understanding Repository Pattern
What is the Repository Pattern?
Repository Pattern ensures that all database operations pass through a dedicated class called a repository.
Instead of writing database code inside controllers, you keep all data access logic in repositories.
This helps in:
A simple example of repository responsibilities:
Get all products
Add a product
Update a product
Delete a product
Get product by ID
Call stored procedures
The controller should only call repository methods and should not know how the data is fetched.
Understanding Unit of Work (UoW)
What is the Unit of Work Pattern?
Unit of Work Pattern ensures that when multiple repositories perform operations, the database save process is coordinated so that all changes either commit or fail together.
In short:
UoW manages multiple repositories
Ensures a single save point
Helps avoid inconsistent data
Makes transactions easier
In large applications, Unit of Work becomes extremely important.
Creating the Database and Stored Procedures
Let us create a Products table.
CREATE TABLE Products
(
Id INT PRIMARY KEY IDENTITY(1,1),
Name NVARCHAR(100),
Price DECIMAL(18,2),
Quantity INT
);
Stored Procedure for Pagination, Sorting, Searching
CREATE PROCEDURE GetProductsPaged
@PageNumber INT,
@PageSize INT,
@SearchTerm NVARCHAR(100) = NULL,
@SortColumn NVARCHAR(50) = 'Name',
@SortDirection NVARCHAR(4) = 'ASC'
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Offset INT = (@PageNumber - 1) * @PageSize;
SELECT *
FROM Products
WHERE (@SearchTerm IS NULL
OR Name LIKE '%' + @SearchTerm + '%')
ORDER BY
CASE WHEN @SortColumn = 'Name' AND @SortDirection = 'ASC' THEN Name END ASC,
CASE WHEN @SortColumn = 'Name' AND @SortDirection = 'DESC' THEN Name END DESC,
CASE WHEN @SortColumn = 'Price' AND @SortDirection = 'ASC' THEN Price END ASC,
CASE WHEN @SortColumn = 'Price' AND @SortDirection = 'DESC' THEN Price END DESC
OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY;
END
This stored procedure supports:
Setting Up ASP.NET Core with EF Core
Step 1: Create Model
Models/Product.cs
public class Product
{
public int Id { get; set; }
public string Name { get; set; }
public decimal Price { get; set; }
public int Quantity { get; set; }
}
Step 2: Create AppDbContext
using Microsoft.EntityFrameworkCore;
using System.Data;
public class AppDbContext : DbContext
{
public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) {}
public DbSet<Product> Products { get; set; }
public DataTable ExecuteStoredProcedure(string procedureName, params object[] parameters)
{
using var cmd = this.Database.GetDbConnection().CreateCommand();
cmd.CommandText = procedureName;
cmd.CommandType = CommandType.StoredProcedure;
foreach (var p in parameters)
{
cmd.Parameters.Add(p);
}
this.Database.OpenConnection();
using var reader = cmd.ExecuteReader();
var dt = new DataTable();
dt.Load(reader);
return dt;
}
}
Implementing Repository Pattern
Step 3: Create Generic Repository Interface
Repositories/IGenericRepository.cs
public interface IGenericRepository<T> where T : class
{
Task<IEnumerable<T>> GetAllAsync();
Task<T> GetByIdAsync(int id);
Task AddAsync(T entity);
void Update(T entity);
void Delete(T entity);
}
Step 4: Implement Generic Repository
Repositories/GenericRepository.cs
public class GenericRepository<T> : IGenericRepository<T> where T : class
{
protected readonly AppDbContext _context;
public GenericRepository(AppDbContext context)
{
_context = context;
}
public async Task<IEnumerable<T>> GetAllAsync()
{
return await _context.Set<T>().ToListAsync();
}
public async Task<T> GetByIdAsync(int id)
{
return await _context.Set<T>().FindAsync(id);
}
public async Task AddAsync(T entity)
{
await _context.Set<T>().AddAsync(entity);
}
public void Update(T entity)
{
_context.Set<T>().Update(entity);
}
public void Delete(T entity)
{
_context.Set<T>().Remove(entity);
}
}
Create Product Repository (Custom Repository)
Repositories/IProductRepository.cs
public interface IProductRepository : IGenericRepository<Product>
{
Task<IEnumerable<Product>> GetPagedProducts(
int pageNumber,
int pageSize,
string searchTerm,
string sortColumn,
string sortDirection);
}
Repositories/ProductRepository.cs
using Microsoft.Data.SqlClient;
public class ProductRepository : GenericRepository<Product>, IProductRepository
{
private readonly AppDbContext _context;
public ProductRepository(AppDbContext context) : base(context)
{
_context = context;
}
public async Task<IEnumerable<Product>> GetPagedProducts(
int pageNumber, int pageSize, string searchTerm,
string sortColumn, string sortDirection)
{
var p1 = new SqlParameter("@PageNumber", pageNumber);
var p2 = new SqlParameter("@PageSize", pageSize);
var p3 = new SqlParameter("@SearchTerm", (object)searchTerm ?? DBNull.Value);
var p4 = new SqlParameter("@SortColumn", sortColumn);
var p5 = new SqlParameter("@SortDirection", sortDirection);
var table = _context.ExecuteStoredProcedure("GetProductsPaged",
p1, p2, p3, p4, p5);
var list = new List<Product>();
foreach (DataRow row in table.Rows)
{
list.Add(new Product
{
Id = Convert.ToInt32(row["Id"]),
Name = row["Name"].ToString(),
Price = Convert.ToDecimal(row["Price"]),
Quantity = Convert.ToInt32(row["Quantity"])
});
}
return list;
}
}
Implementing Unit of Work
UnitOfWork/IUnitOfWork.cs
public interface IUnitOfWork
{
IProductRepository Products { get; }
Task<int> SaveAsync();
}
UnitOfWork/UnitOfWork.cs
public class UnitOfWork : IUnitOfWork
{
private readonly AppDbContext _context;
public IProductRepository Products { get; }
public UnitOfWork(AppDbContext context, IProductRepository products)
{
_context = context;
Products = products;
}
public async Task<int> SaveAsync()
{
return await _context.SaveChangesAsync();
}
}
Setting Up Dependency Injection
In Program.cs:
builder.Services.AddScoped<IUnitOfWork, UnitOfWork>();
builder.Services.AddScoped<IProductRepository, ProductRepository>();
builder.Services.AddScoped(typeof(IGenericRepository<>), typeof(GenericRepository<>));
Creating the Products Controller
Controllers/ProductsController.cs
[ApiController]
[Route("api/[controller]")]
public class ProductsController : ControllerBase
{
private readonly IUnitOfWork _unitOfWork;
public ProductsController(IUnitOfWork unitOfWork)
{
_unitOfWork = unitOfWork;
}
[HttpGet("paged")]
public async Task<IActionResult> GetPaged(
int pageNumber = 1,
int pageSize = 10,
string search = null,
string sortColumn = "Name",
string sortDirection = "ASC")
{
var result = await _unitOfWork.Products.GetPagedProducts(
pageNumber, pageSize, search, sortColumn, sortDirection);
return Ok(result);
}
[HttpPost]
public async Task<IActionResult> Add(Product product)
{
await _unitOfWork.Products.AddAsync(product);
await _unitOfWork.SaveAsync();
return Ok();
}
[HttpPut]
public async Task<IActionResult> Update(Product product)
{
_unitOfWork.Products.Update(product);
await _unitOfWork.SaveAsync();
return Ok();
}
[HttpDelete("{id}")]
public async Task<IActionResult> Delete(int id)
{
var p = await _unitOfWork.Products.GetByIdAsync(id);
_unitOfWork.Products.Delete(p);
await _unitOfWork.SaveAsync();
return Ok();
}
}
Angular Frontend for Pagination, Sorting, and Searching
Step 1: Product Service
getPagedProducts(page: number, size: number, search: string, sortColumn: string, sortDirection: string) {
return this.http.get(`${this.apiUrl}/paged`, {
params: {
pageNumber: page,
pageSize: size,
search: search,
sortColumn: sortColumn,
sortDirection: sortDirection
}
});
}
Step 2: Component with Pagination
loadProducts() {
this.service.getPagedProducts(
this.pageNumber,
this.pageSize,
this.searchTerm,
this.sortColumn,
this.sortDirection
).subscribe((data: any) => {
this.products = data;
});
}
Conclusion
In this complete guide, you learned how to create a fully professional backend architecture using:
This structure is extremely scalable and used in real enterprise-grade systems.
You now have the foundation required to build serious, long-term production applications.