Building a Professional Full-Stack Application with Repository Pattern, Unit of Work, Stored Procedures, Pagination, Sorting, and Searching

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:

Clean separation between business logic and data access
Easy unit testing
Cleaner controllers
Reusable data logic
Better maintainability

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:

Pagination
Keyword search
Sorting by multiple columns

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:

Repository Pattern
Unit of Work Pattern
SQL Server Stored Procedures
Pagination
Sorting
Searching
ASP.NET Core Web API
EF Core
Angular Frontend

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.