🚀 Employee Department CRUD API

A Production-Ready ASP.NET Core 8 Web API demonstrating Department & Employee CRUD Operations using Dapper, Stored Procedures, JWT Authentication, Serilog, FluentValidation, Caching, and Rate Limiting.

📥 Getting Started

1️⃣ Clone the Repository

git clone <YOUR_GITHUB_REPOSITORY_URL>

Navigate into the project folder:

cd EmployeeDepartmentCrud

2️⃣ Setup SQL Server Database

Open SQL Server Management Studio (SSMS) or Azure Data Studio.

Execute the SQL scripts in the following order:

Paste your stored procedure below.

-- ============================================
-- Scripts To Execute in MSSQL
-- ============================================

Create Database EmployeeDepartmentMaster

Use EmployeeDepartmentMaster


 CREATE TABLE Department (
        DepartmentId INT IDENTITY(1,1) PRIMARY KEY,
        DepartmentName NVARCHAR(100) NOT NULL,
        DepartmentCode NVARCHAR(20) NOT NULL,
        Description NVARCHAR(250) NULL,
        IsActive BIT NOT NULL DEFAULT 1,
        CreatedDate DATETIME NOT NULL DEFAULT GETDATE(),
        ModifiedDate DATETIME NULL
    );


CREATE TABLE Employee (
        EmployeeId INT IDENTITY(1,1) PRIMARY KEY,
        DepartmentId INT NOT NULL,
        FirstName NVARCHAR(50) NOT NULL,
        LastName NVARCHAR(50) NOT NULL,
        Email NVARCHAR(100) NOT NULL UNIQUE,
        Phone NVARCHAR(20) NULL,
        Salary DECIMAL(18,2) NOT NULL,
        Gender NVARCHAR(10) NOT NULL,
        DOB DATE NOT NULL,
        JoiningDate DATE NOT NULL,
        Address NVARCHAR(500) NULL,
        IsActive BIT NOT NULL DEFAULT 1,
        CreatedDate DATETIME NOT NULL DEFAULT GETDATE(),
        ModifiedDate DATETIME NULL,
        
        -- Foreign Key Constraint
        CONSTRAINT FK_Employee_Department FOREIGN KEY (DepartmentId) 
        REFERENCES Department(DepartmentId)
    );


Select * from Department
Select * from Employee


-- =============================================
-- Author:      System
-- Create date: 2024
-- Description: Single SP for Department CRUD returning JSON
-- Operations:  1=Insert, 2=Update, 3=GetById, 4=GetAll, 5=Delete
-- =============================================
CREATE OR ALTER PROCEDURE sp_Department_CRUD
    @OperationId INT,
    @DepartmentId INT = NULL,
    @DepartmentName NVARCHAR(100) = NULL,
    @DepartmentCode NVARCHAR(20) = NULL,
    @Description NVARCHAR(250) = NULL,
    @IsActive BIT = 1,
    @PageSize INT = 10,
    @PageIndex INT = 1
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        IF @OperationId = 1 -- Insert
        BEGIN
            INSERT INTO Department (DepartmentName, DepartmentCode, Description, IsActive, CreatedDate)
            VALUES (@DepartmentName, @DepartmentCode, @Description, @IsActive, GETDATE());
            
            DECLARE @NewId INT = SCOPE_IDENTITY();
            SELECT (
                SELECT 1 AS Status, 'Department created successfully' AS Message, @NewId AS Id
                FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
            ) AS JsonOutput;
        END
        ELSE IF @OperationId = 2 -- Update
        BEGIN
            UPDATE Department
            SET DepartmentName = @DepartmentName,
                DepartmentCode = @DepartmentCode,
                Description = @Description,
                IsActive = @IsActive,
                ModifiedDate = GETDATE()
            WHERE DepartmentId = @DepartmentId;
            
            SELECT (
                SELECT 1 AS Status, 'Department updated successfully' AS Message, @DepartmentId AS Id
                FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
            ) AS JsonOutput;
        END
        ELSE IF @OperationId = 3 -- Get By Id
        BEGIN
            SELECT (
                SELECT * FROM Department WHERE DepartmentId = @DepartmentId
                FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
            ) AS JsonOutput;
        END
        ELSE IF @OperationId = 4 -- Get All
        BEGIN
            SELECT (
                SELECT *, COUNT(*) OVER() AS TotalRecords 
                FROM Department 
                ORDER BY DepartmentName
                OFFSET (@PageIndex - 1) * @PageSize ROWS
                FETCH NEXT @PageSize ROWS ONLY
                FOR JSON PATH
            ) AS JsonOutput;
        END
        ELSE IF @OperationId = 5 -- Delete (Soft Delete)
        BEGIN
            UPDATE Department SET IsActive = 0, ModifiedDate = GETDATE() WHERE DepartmentId = @DepartmentId;
            
            SELECT (
                SELECT 1 AS Status, 'Department deleted successfully' AS Message, @DepartmentId AS Id
                FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
            ) AS JsonOutput;
        END
    END TRY
    BEGIN CATCH
        SELECT (
            SELECT 0 AS Status, ERROR_MESSAGE() AS Message, @DepartmentId AS Id
            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        ) AS JsonOutput;
    END CATCH
END
GO

-- ============================================
-- =============================================
-- Author:      System
-- Create date: 2024
-- Description: Single SP for Employee CRUD returning JSON
-- Operations:  1=Insert, 2=Update, 3=GetById, 4=GetAll, 5=Delete
-- =============================================
CREATE OR ALTER PROCEDURE sp_Employee_CRUD
    @OperationId INT,
    @EmployeeId INT = NULL,
    @DepartmentId INT = NULL,
    @FirstName NVARCHAR(50) = NULL,
    @LastName NVARCHAR(50) = NULL,
    @Email NVARCHAR(100) = NULL,
    @Phone NVARCHAR(20) = NULL,
    @Salary DECIMAL(18,2) = NULL,
    @Gender NVARCHAR(10) = NULL,
    @DOB DATE = NULL,
    @JoiningDate DATE = NULL,
    @Address NVARCHAR(500) = NULL,
    @IsActive BIT = 1,
    @PageSize INT = 10,
    @PageIndex INT = 1,
    @SearchDepartmentName NVARCHAR(100) = NULL,
    @SearchDepartmentCode NVARCHAR(20) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        IF @OperationId = 1 -- Insert
        BEGIN
            INSERT INTO Employee (DepartmentId, FirstName, LastName, Email, Phone, Salary, Gender, DOB, JoiningDate, Address, IsActive, CreatedDate)
            VALUES (@DepartmentId, @FirstName, @LastName, @Email, @Phone, @Salary, @Gender, @DOB, @JoiningDate, @Address, @IsActive, GETDATE());
            
            DECLARE @NewId INT = SCOPE_IDENTITY();
            SELECT (
                SELECT 1 AS Status, 'Employee created successfully' AS Message, @NewId AS Id
                FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
            ) AS JsonOutput;
        END
        ELSE IF @OperationId = 2 -- Update
        BEGIN
            UPDATE Employee
            SET DepartmentId = @DepartmentId,
                FirstName = @FirstName,
                LastName = @LastName,
                Email = @Email,
                Phone = @Phone,
                Salary = @Salary,
                Gender = @Gender,
                DOB = @DOB,
                JoiningDate = @JoiningDate,
                Address = @Address,
                IsActive = @IsActive,
                ModifiedDate = GETDATE()
            WHERE EmployeeId = @EmployeeId;
            
            SELECT (
                SELECT 1 AS Status, 'Employee updated successfully' AS Message, @EmployeeId AS Id
                FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
            ) AS JsonOutput;
        END
        ELSE IF @OperationId = 3 -- Get By Id
        BEGIN
            SELECT (
                SELECT * FROM Employee WHERE EmployeeId = @EmployeeId
                FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
            ) AS JsonOutput;
        END
        ELSE IF @OperationId = 4 -- Get All
        BEGIN
            SELECT (
                SELECT *, COUNT(*) OVER() AS TotalRecords 
                FROM Employee 
                ORDER BY FirstName
                OFFSET (@PageIndex - 1) * @PageSize ROWS
                FETCH NEXT @PageSize ROWS ONLY
                FOR JSON PATH
            ) AS JsonOutput;
        END
        ELSE IF @OperationId = 6 -- Get By Department Id
        BEGIN
            SELECT (
                SELECT *, COUNT(*) OVER() AS TotalRecords
                FROM Employee
                WHERE DepartmentId = @DepartmentId
                ORDER BY FirstName
                OFFSET (@PageIndex - 1) * @PageSize ROWS
                FETCH NEXT @PageSize ROWS ONLY
                FOR JSON PATH
            ) AS JsonOutput;
        END
        ELSE IF @OperationId = 7 -- Get By Department Name
        BEGIN
            SELECT (
                SELECT e.*, COUNT(*) OVER() AS TotalRecords
                FROM Employee e
                INNER JOIN Department d ON e.DepartmentId = d.DepartmentId
                WHERE d.DepartmentName LIKE '%' + @SearchDepartmentName + '%'
                ORDER BY e.FirstName
                OFFSET (@PageIndex - 1) * @PageSize ROWS
                FETCH NEXT @PageSize ROWS ONLY
                FOR JSON PATH
            ) AS JsonOutput;
        END
        ELSE IF @OperationId = 8 -- Get By Department Code
        BEGIN
            SELECT (
                SELECT e.*, COUNT(*) OVER() AS TotalRecords
                FROM Employee e
                INNER JOIN Department d ON e.DepartmentId = d.DepartmentId
                WHERE d.DepartmentCode = @SearchDepartmentCode
                ORDER BY e.FirstName
                OFFSET (@PageIndex - 1) * @PageSize ROWS
                FETCH NEXT @PageSize ROWS ONLY
                FOR JSON PATH
            ) AS JsonOutput;
        END
        ELSE IF @OperationId = 5 -- Delete (Soft Delete)
        BEGIN
            UPDATE Employee SET IsActive = 0, ModifiedDate = GETDATE() WHERE EmployeeId = @EmployeeId;
            
            SELECT (
                SELECT 1 AS Status, 'Employee deleted successfully' AS Message, @EmployeeId AS Id
                FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
            ) AS JsonOutput;
        END
    END TRY
    BEGIN CATCH
        SELECT (
            SELECT 0 AS Status, ERROR_MESSAGE() AS Message, @EmployeeId AS Id
            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        ) AS JsonOutput;
    END CATCH
END
GO

3️⃣ Update Connection String

Update your appsettings.json

"ConnectionStrings": {
  "DefaultConnection": "Server=localhost;Database=YourDatabaseName;Trusted_Connection=True;Encrypt=False;"
}

4️⃣ Install Required NuGet Packages

Microsoft.AspNetCore.Authentication.JwtBearer

Dapper

FluentValidation.AspNetCore

FluentValidation.DependencyInjectionExtensions

Serilog.AspNetCore

Serilog.Sinks.Console

Serilog.Sinks.File

Serilog.Sinks.Async

System.Data.SqlClient

5️⃣ Build the Project

dotnet build

6️⃣ Run the Project

dotnet run

7️⃣ Open Swagger

https://localhost:5001/swagger

🔑 Authentication

Login

POST /api/Auth/login

Request

{
  "username": "admin",
  "password": "admin"
}

Copy the generated JWT token and click Authorize in Swagger.

🏢 Department API

Create Department

{
  "departmentName": "FrontDesk",
  "departmentCode": "FrontDesk Service",
  "description": "The first point of contact"
}

👨‍💼 Employee API

Create Employee

{
  "departmentId": 1,
  "firstName": "Paras",
  "lastName": "Panchal",
  "email": "paraspanchal5555@gmail.com",
  "phone": "+971502877414",
  "salary": 120000,
  "gender": "Male",
  "dob": "2001-06-01T09:45:32.739Z",
  "joiningDate": "2026-08-04T09:45:32.739Z",
  "address": "Hor Al Naz East"
}

📂 Project Structure

EmployeeDepartmentCrud
│
├── Authentication
├── Caching
├── Controllers
├── Database
│   ├── Scripts
│   └── StoredProcedures
├── DTOs
├── Entities
├── Extensions
├── Helpers
├── Interfaces
├── Middleware
├── Responses
├── Services
├── Validators
├── appsettings.json
└── Program.cs

✨ Features

🔐 JWT Authentication
🏢 Department CRUD
👨‍💼 Employee CRUD
⚡ Async/Await
📦 Dapper ORM
📄 SQL Server Stored Procedures
📝 Serilog Logging
🚦 Rate Limiting
💾 Memory Caching
✅ FluentValidation
🌍 Global Exception Middleware
📖 Swagger Documentation
🎯 Clean Service & Repository Pattern

🚀 Implementation Plan

🧹 Remove default WeatherForecast files.
📦 Install all required NuGet packages.
🗄️ Create SQL schema and stored procedures.
🏗️ Build Entities, DTOs, and Response wrappers.
✅ Add FluentValidation validators.
⚙️ Implement reusable Dapper Helper.
🏢 Create Department Service.
👨‍💼 Create Employee Service.
🔐 Implement JWT Authentication.
🌍 Add Global Exception Middleware.
🚦 Configure Rate Limiting.
💾 Configure Memory Cache.
📝 Configure Serilog.
📖 Configure Swagger.
🧩 Register Dependency Injection.
🚀 Configure Program.cs.

🧪 Verification Steps

✅ Clone the repository.

✅ Execute SQL Schema Script.

✅ Execute Department Stored Procedure.

✅ Execute Employee Stored Procedure.

✅ Update Connection String.

✅ Restore NuGet Packages.

✅ Build Project.

dotnet build

✅ Run Project.

dotnet run

✅ Open Swagger.

https://localhost:5001/swagger

✅ Login.

✅ Authorize JWT.

✅ Test all CRUD APIs.

🎯 CRUD Operations

Operation	Department	Employee
Create	✅	✅
Update	✅	✅
Get By Id	✅	✅
Get All	✅	✅
Soft Delete	✅	✅

🔮 Future Enhancements

The following improvements are planned for future releases:

📄 Server-Side Pagination

Pagination will be added for Get All APIs to improve performance when handling large datasets.

The stored procedures and API endpoints will be updated to accept:

PageIndex
PageSize

The response will also include:

✅ Total Records
✅ Total Pages
✅ Current Page
✅ Page Size
✅ Has Previous Page
✅ Has Next Page

Example Response:

{
  "pageIndex": 1,
  "pageSize": 10,
  "totalRecords": 245,
  "totalPages": 25,
  "data": []
}

Both the Stored Procedures and the ASP.NET Core codebase will be enhanced to support this pagination model while maintaining backward compatibility.

Additional future improvements may include:

🔍 Advanced Search
🎯 Dynamic Filtering
📊 Sorting
📤 Excel Export
📥 Bulk Import
📈 Performance Monitoring
🧪 Unit Testing
🐳 Docker Support
☁️ Azure Deployment
🔄 CI/CD Pipeline

Navigation Menu

Repository files navigation

🚀 Employee Department CRUD API

📥 Getting Started

1️⃣ Clone the Repository

2️⃣ Setup SQL Server Database

3️⃣ Update Connection String

4️⃣ Install Required NuGet Packages

5️⃣ Build the Project

6️⃣ Run the Project

7️⃣ Open Swagger

🔑 Authentication

Login

Request

🏢 Department API

Create Department

👨‍💼 Employee API

Create Employee

📂 Project Structure

✨ Features

🚀 Implementation Plan

🧪 Verification Steps

🎯 CRUD Operations

🔮 Future Enhancements

📄 Server-Side Pagination

🎉 Happy Coding!

About

Releases

Packages

Contributors

Languages

Name		Name	Last commit message	Last commit date
Authentication		Authentication
Caching		Caching
Controllers		Controllers
DTOs		DTOs
Database/Scripts		Database/Scripts
Entities		Entities
Enums		Enums
Extensions		Extensions
Helpers		Helpers
Interfaces		Interfaces
Middleware		Middleware
Properties		Properties
Responses		Responses
Services		Services
StoredProcedures		StoredProcedures
Validators		Validators
.gitattributes		.gitattributes
.gitignore		.gitignore
EmployeeDeparmtmentCrud.csproj		EmployeeDeparmtmentCrud.csproj
EmployeeDeparmtmentCrud.http		EmployeeDeparmtmentCrud.http
EmployeeDeparmtmentCrud.sln		EmployeeDeparmtmentCrud.sln
Program.cs		Program.cs
README.md		README.md
appsettings.Development.json		appsettings.Development.json
appsettings.json		appsettings.json

Latest commit

History

Folders and files

Repository files navigation

🚀 Employee Department CRUD API

📥 Getting Started

1️⃣ Clone the Repository

2️⃣ Setup SQL Server Database

3️⃣ Update Connection String

4️⃣ Install Required NuGet Packages

5️⃣ Build the Project

6️⃣ Run the Project

7️⃣ Open Swagger

🔑 Authentication

Login

Request

🏢 Department API

Create Department

👨‍💼 Employee API

Create Employee

📂 Project Structure

✨ Features

🚀 Implementation Plan

🧪 Verification Steps

🎯 CRUD Operations

🔮 Future Enhancements

📄 Server-Side Pagination

🎉 Happy Coding!

About

Resources

Stars

Watchers

Forks

Releases

Packages

Contributors

Languages