End-to-End CRUD App with Paging, Sorting & Filtering | Angular + Asp.Net Core

Vishal Gami
Nov 27
403
0
0

100

Article

Project Overview

Tech Stack

Frontend: Angular 17+
Backend: ASP.NET Core 7 Web API
Database: SQL Server
ORM: Entity Framework Core

Features

Display employees in a table
Add, update, delete employees (CRUD)
Paging
Sorting by columns
Filtering/searching

Step 1: Database Setup (SQL Server)

Create a database called EmployeeDb. Then create a table:

CREATE TABLE Employees (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(100),
    Email NVARCHAR(100),
    Department NVARCHAR(50)
);

Insert some sample data:

INSERT INTO Employees (Name, Email, Department)
VALUES 
('Alice','[email protected]','HR'),
('Bob','[email protected]','IT'),
('Charlie','[email protected]','Finance'),
('David','[email protected]','IT'),
('Eva','[email protected]','HR');

Step 2: ASP.NET Core API Setup

2.1 Create the Web API Project

dotnet new webapi -n EmployeeApi
cd EmployeeApi

2.2 Install EF Core Packages

dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Tools

2.3 Create Employee Model

Models/Employee.cs:

namespace EmployeeApi.Models
{
    public class Employee
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Email { get; set; }
        public string Department { get; set; }
    }
}

2.4 Create DbContext

Data/AppDbContext.cs:

using EmployeeApi.Models;
using Microsoft.EntityFrameworkCore;

namespace EmployeeApi.Data
{
    public class AppDbContext : DbContext
    {
        public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) {}
        public DbSet<Employee> Employees { get; set; }
    }
}

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

builder.Services.AddControllers();
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

Add connection string in appsettings.json:

"ConnectionStrings": {
  "DefaultConnection": "Server=YOUR_SERVER_NAME;Database=EmployeeDb;Trusted_Connection=True;TrustServerCertificate=True;"
}

2.5 Implement EmployeesController

Controllers/EmployeesController.cs:

using EmployeeApi.Data;
using EmployeeApi.Models;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using System.Linq.Expressions;

namespace EmployeeApi.Controllers
{
    [ApiController]
    [Route("api/[controller]")]
    public class EmployeesController : ControllerBase
    {
        private readonly AppDbContext _context;

        public EmployeesController(AppDbContext context)
        {
            _context = context;
        }

        // GET: api/employees
        [HttpGet]
        public async Task<IActionResult> Get(
            int page = 1,
            int pageSize = 5,
            string sortColumn = "Name",
            string sortDirection = "asc",
            string search = "")
        {
            var query = _context.Employees.AsQueryable();

            // Filtering
            if (!string.IsNullOrEmpty(search))
            {
                query = query.Where(e => e.Name.Contains(search) || e.Email.Contains(search) || e.Department.Contains(search));
            }

            // Sorting
            query = sortDirection.ToLower() == "asc" ? query.OrderByDynamic(sortColumn, true) : query.OrderByDynamic(sortColumn, false);

            var total = await query.CountAsync();

            var data = await query.Skip((page - 1) * pageSize).Take(pageSize).ToListAsync();

            return Ok(new { total, data });
        }

        // POST
        [HttpPost]
        public async Task<IActionResult> Post(Employee emp)
        {
            _context.Employees.Add(emp);
            await _context.SaveChangesAsync();
            return Ok(emp);
        }

        // PUT
        [HttpPut("{id}")]
        public async Task<IActionResult> Put(int id, Employee emp)
        {
            var dbEmp = await _context.Employees.FindAsync(id);
            if (dbEmp == null) return NotFound();

            dbEmp.Name = emp.Name;
            dbEmp.Email = emp.Email;
            dbEmp.Department = emp.Department;

            await _context.SaveChangesAsync();
            return Ok(dbEmp);
        }

        // DELETE
        [HttpDelete("{id}")]
        public async Task<IActionResult> Delete(int id)
        {
            var emp = await _context.Employees.FindAsync(id);
            if (emp == null) return NotFound();

            _context.Employees.Remove(emp);
            await _context.SaveChangesAsync();
            return Ok();
        }
    }

    public static class IQueryableExtensions
    {
        public static IQueryable<T> OrderByDynamic<T>(this IQueryable<T> source, string column, bool ascending)
        {
            if (string.IsNullOrEmpty(column)) return source;

            var parameter = Expression.Parameter(typeof(T), "p");
            var property = Expression.Property(parameter, column);
            var lambda = Expression.Lambda(property, parameter);

            string methodName = ascending ? "OrderBy" : "OrderByDescending";

            var result = typeof(Queryable).GetMethods()
                .Single(method => method.Name == methodName && method.GetParameters().Length == 2)
                .MakeGenericMethod(typeof(T), property.Type)
                .Invoke(null, new object[] { source, lambda });

            return (IQueryable<T>)result;
        }
    }
}

Step 3: Angular Frontend Setup

3.1 Create Angular Project

ng new employee-ui
cd employee-ui
ng add @angular/material

3.2 Create Employee Model

src/app/models/employee.model.ts:

export interface Employee {
  id?: number;
  name: string;
  email: string;
  department: string;
}

3.3 Create Employee Service

src/app/services/employee.service.ts:

import { Injectable } from '@angular/core';
import { HttpClient, HttpParams } from '@angular/common/http';
import { Employee } from '../models/employee.model';
import { Observable } from 'rxjs';

@Injectable({
  providedIn: 'root'
})
export class EmployeeService {
  apiUrl = 'https://localhost:5001/api/employees';

  constructor(private http: HttpClient) {}

  getEmployees(page: number, pageSize: number, sortColumn: string, sortDir: string, search: string): Observable<{total: number, data: Employee[]}> {
    let params = new HttpParams()
      .set('page', page)
      .set('pageSize', pageSize)
      .set('sortColumn', sortColumn)
      .set('sortDirection', sortDir)
      .set('search', search);

    return this.http.get<{total: number, data: Employee[]}>(this.apiUrl, { params });
  }

  addEmployee(emp: Employee) {
    return this.http.post<Employee>(this.apiUrl, emp);
  }

  updateEmployee(emp: Employee) {
    return this.http.put<Employee>(`${this.apiUrl}/${emp.id}`, emp);
  }

  deleteEmployee(id: number) {
    return this.http.delete(`${this.apiUrl}/${id}`);
  }
}

3.4 Employee List Component

employee-list.component.ts:

import { Component, OnInit } from '@angular/core';
import { EmployeeService } from '../services/employee.service';
import { Employee } from '../models/employee.model';

@Component({
  selector: 'app-employee-list',
  templateUrl: './employee-list.component.html'
})
export class EmployeeListComponent implements OnInit {

  employees: Employee[] = [];
  totalRecords = 0;

  page = 1;
  pageSize = 5;
  sortColumn = 'name';
  sortDirection = 'asc';
  search = '';

  constructor(private service: EmployeeService) {}

  ngOnInit(): void {
    this.loadEmployees();
  }

  loadEmployees() {
    this.service.getEmployees(this.page, this.pageSize, this.sortColumn, this.sortDirection, this.search)
      .subscribe(res => {
        this.employees = res.data;
        this.totalRecords = res.total;
      });
  }

  onPageChange(newPage: number) {
    this.page = newPage;
    this.loadEmployees();
  }

  onSort(column: string) {
    if(this.sortColumn === column) {
      this.sortDirection = this.sortDirection === 'asc' ? 'desc' : 'asc';
    } else {
      this.sortColumn = column;
      this.sortDirection = 'asc';
    }
    this.loadEmployees();
  }

  onSearchChange(searchValue: string) {
    this.search = searchValue;
    this.page = 1;
    this.loadEmployees();
  }

  delete(id: number) {
    this.service.deleteEmployee(id).subscribe(() => this.loadEmployees());
  }
}

3.5 Employee List HTML

employee-list.component.html:

<h3>Employee Management</h3>

<input type="text" placeholder="Search..." (input)="onSearchChange($event.target.value)" />

<table border="1">
  <thead>
    <tr>
      <th (click)="onSort('name')">Name</th>
      <th (click)="onSort('email')">Email</th>
      <th (click)="onSort('department')">Department</th>
      <th>Actions</th>
    </tr>
  </thead>
  <tbody>
    <tr *ngFor="let emp of employees">
      <td>{{ emp.name }}</td>
      <td>{{ emp.email }}</td>
      <td>{{ emp.department }}</td>
      <td>
        <button (click)="delete(emp.id!)">Delete</button>
      </td>
    </tr>
  </tbody>
</table>

<div style="margin-top:10px;">
  <button (click)="onPageChange(page - 1)" [disabled]="page === 1">Prev</button>
  <span>{{ page }}</span>
  <button (click)="onPageChange(page + 1)" [disabled]="page * pageSize >= totalRecords">Next</button>
</div>

3.6 Angular Routing

app-routing.module.ts:

import { NgModule } from '@angular/core';
import { RouterModule, Routes } from '@angular/router';
import { EmployeeListComponent } from './employee-list/employee-list.component';

const routes: Routes = [
  { path: '', component: EmployeeListComponent }
];

@NgModule({
  imports: [RouterModule.forRoot(routes)],
  exports: [RouterModule]
})
export class AppRoutingModule { }

Step 4: Run the Project

Run the API:

dotnet run

Run Angular UI:

ng serve

Open http://localhost:4200/ to view:

Employee table
Search/filter
Sort by clicking headers
Paging buttons
Delete functionality

Step 5: Best Practices

Use DTOs for API instead of exposing EF models directly
Add form validation and error handling
Use Angular Material tables for nicer UI
Implement Update and Add forms for full CRUD
Use services for API calls to keep components clean

✅ This is a complete working project demonstrating:

Angular frontend
ASP.NET Core Web API backend
SQL Server database
CRUD operations + Paging + Sorting + Filtering