Project Introduction
The Employee Management System is designed to streamline the management of employee data, attendance, payroll, and performance within an organization. Built using ASP.NET and SQL Server, this application aims to provide a comprehensive solution for HR departments to manage employee information efficiently. The system will facilitate various HR functions, including tracking attendance, processing payroll, managing job postings, and handling leave requests, thereby enhancing overall productivity and organization within the workplace.
Project Objectives
- To create a user-friendly interface for HR personnel to manage employee records and related activities.
- To implement a secure authentication system for users with different roles (e.g., admin, HR staff).
- To manage employee details, including personal information, job titles, and salary data.
- To track employee attendance and generate reports on attendance status.
- To handle payroll processing, including gross salary, deductions, and net salary calculations.
- To evaluate employee performance through reviews and ratings.
- To manage job postings and training programs for employee development.
- To facilitate leave requests and track their approval status.
- To maintain important documents and policies related to employees.
- To provide a platform for announcements and communication within the organization.
Project Modules
- User Management Module: Handles user registration, login, and role management.
- Employee Management Module: Manages employee records, including personal and job-related information.
- Attendance Management Module: Tracks employee attendance and generates attendance reports.
- Payroll Management Module: Processes payroll, including salary calculations and deductions.
- Performance Management Module: Evaluates employee performance through reviews and feedback.
- Job Posting Module: Manages job postings and applications for open positions.
- Training Management Module: Facilitates the creation and management of training programs for employees.
- Leave Management Module: Handles leave requests and tracks their approval status.
- Document Management Module: Manages important employee documents and files.
- Policy Management Module: Maintains organizational policies and procedures.
- Announcement Module: Allows for the creation and dissemination of announcements within the organization.
SQL Server Database Tables
-- Create Users Table
CREATE TABLE Users (
UserId INT PRIMARY KEY IDENTITY(1,1),
Username NVARCHAR(50) NOT NULL UNIQUE,
PasswordHash NVARCHAR(256) NOT NULL,
Email NVARCHAR(100) NOT NULL UNIQUE,
RoleId INT,
CreatedAt DATETIME DEFAULT GETDATE(),
FOREIGN KEY (RoleId) REFERENCES Roles(RoleId)
);
-- Create Roles Table
CREATE TABLE Roles (
RoleId INT PRIMARY KEY IDENTITY(1,1),
RoleName NVARCHAR(50) NOT NULL UNIQUE
);
-- Create Employees Table
CREATE TABLE Employees (
EmployeeId INT PRIMARY KEY IDENTITY(1,1),
FirstName NVARCHAR(50) NOT NULL,
LastName NVARCHAR(50) NOT NULL,
Email NVARCHAR(100) NOT NULL UNIQUE,
Phone NVARCHAR(15),
HireDate DATETIME NOT NULL,
JobTitle NVARCHAR(100),
Department NVARCHAR(100),
Salary DECIMAL(18, 2),
CreatedAt DATETIME DEFAULT GETDATE(),
UserId INT,
FOREIGN KEY (User Id) REFERENCES Users(UserId)
);
-- Create Attendance Table
CREATE TABLE Attendance (
AttendanceId INT PRIMARY KEY IDENTITY(1,1),
EmployeeId INT,
Date DATE NOT NULL,
Status NVARCHAR(20) NOT NULL, -- e.g., Present, Absent, Leave
CreatedAt DATETIME DEFAULT GETDATE(),
FOREIGN KEY (EmployeeId) REFERENCES Employees(EmployeeId)
);
-- Create Payroll Table
CREATE TABLE Payroll (
PayrollId INT PRIMARY KEY IDENTITY(1,1),
EmployeeId INT,
PayPeriodStart DATE NOT NULL,
PayPeriodEnd DATE NOT NULL,
GrossSalary DECIMAL(18, 2) NOT NULL,
Deductions DECIMAL(18, 2) NOT NULL,
NetSalary DECIMAL(18, 2) NOT NULL,
CreatedAt DATETIME DEFAULT GETDATE(),
FOREIGN KEY (EmployeeId) REFERENCES Employees(EmployeeId)
);
-- Create Performance Table
CREATE TABLE Performance (
PerformanceId INT PRIMARY KEY IDENTITY(1,1),
EmployeeId INT,
ReviewDate DATE NOT NULL,
Rating INT CHECK (Rating >= 1 AND Rating <= 5),
Comments NVARCHAR(MAX),
CreatedAt DATETIME DEFAULT GETDATE(),
FOREIGN KEY (EmployeeId) REFERENCES Employees(EmployeeId)
);
-- Create JobPostings Table
CREATE TABLE JobPostings (
JobPostingId INT PRIMARY KEY IDENTITY(1,1),
JobTitle NVARCHAR(100) NOT NULL,
Description NVARCHAR(MAX) NOT NULL,
Requirements NVARCHAR(MAX),
CreatedAt DATETIME DEFAULT GETDATE(),
IsActive BIT DEFAULT 1
);
-- Create TrainingPrograms Table
CREATE TABLE TrainingPrograms (
TrainingProgramId INT PRIMARY KEY IDENTITY(1,1),
Title NVARCHAR(100) NOT NULL,
Description NVARCHAR(MAX),
StartDate DATE NOT NULL,
EndDate DATE NOT NULL,
CreatedAt DATETIME DEFAULT GETDATE()
);
-- Create LeaveRequests Table
CREATE TABLE LeaveRequests (
LeaveRequestId INT PRIMARY KEY IDENTITY(1,1),
EmployeeId INT,
StartDate DATE NOT NULL,
EndDate DATE NOT NULL,
Reason NVARCHAR(MAX),
Status NVARCHAR(20) NOT NULL, -- e.g., Pending, Approved, Rejected
CreatedAt DATETIME DEFAULT GETDATE(),
FOREIGN KEY (EmployeeId) REFERENCES Employees(EmployeeId)
);
-- Create Documents Table
CREATE TABLE Documents (
DocumentId INT PRIMARY KEY IDENTITY(1,1),
EmployeeId INT,
DocumentType NVARCHAR(100) NOT NULL,
FilePath NVARCHAR(256) NOT NULL,
CreatedAt DATETIME DEFAULT GETDATE(),
FOREIGN KEY (EmployeeId) REFERENCES Employees(EmployeeId)
);
-- Create Policies Table
CREATE TABLE Policies (
PolicyId INT PRIMARY KEY IDENTITY(1,1),
Title NVARCHAR(100) NOT NULL,
Description NVARCHAR(MAX),
CreatedAt DATETIME DEFAULT GETDATE()
);
-- Create Announcements Table
CREATE TABLE Announcements (
AnnouncementId INT PRIMARY KEY IDENTITY(1,1),
Title NVARCHAR(100) NOT NULL,
Message NVARCHAR(MAX) NOT NULL,
CreatedAt DATETIME DEFAULT GETDATE()
);
Explanation of the Tables
Users: Stores user information, including username, password hash, email, and role.
Roles: Defines user roles (e.g., Admin, Employee).
Employees: Contains employee details, including first name, last name, email, phone, hire date, job title, department, salary, and a reference to the user account.
Attendance: Tracks employee attendance records, including date and status.
Payroll: Manages payroll information for employees, including salary details for each pay period.
Performance: Stores performance review data for employees, including ratings and comments.
JobPostings: Contains job postings for recruitment purposes, detailing job titles and descriptions.
TrainingPrograms: Manages training programs available for employees, including schedules and descriptions.
LeaveRequests: Tracks leave requests made by employees, including start and end dates and status.
Documents: Stores documents related to employees, such as contracts or certifications.
Policies: Contains company policies that employees need to be aware of.
Announcements: Manages announcements made within the organization, such as important updates or events.
Creating Models and Repositories
To create models and repositories using ADO.NET for the provided SQL Server schema in an ASP.NET application, we will follow these steps:
Step 1: Create Models
Here are the C# model classes for each of the tables:
public class User
{
public int UserId { get; set; }
public string Username { get; set; }
public string PasswordHash { get; set; }
public string Email { get; set; }
public int? RoleId { get; set; }
public DateTime CreatedAt { get; set; }
}
public class Role
{
public int RoleId { get; set; }
public string RoleName { get; set; }
}
public class Employee
{
public int EmployeeId { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string Email { get; set; }
public string Phone { get; set; }
public DateTime HireDate { get; set; }
public string JobTitle { get; set; }
public string Department { get; set; }
public decimal Salary { get; set; }
public DateTime CreatedAt { get; set; }
public int? UserId { get; set; }
}
public class Attendance
{
public int AttendanceId { get; set; }
public int EmployeeId { get; set; }
public DateTime Date { get; set; }
public string Status { get; set; }
public DateTime CreatedAt { get; set; }
}
public class Payroll
{
public int PayrollId { get; set; }
public int EmployeeId { get; set; }
public DateTime PayPeriodStart { get; set; }
public DateTime PayPeriodEnd { get; set; }
public decimal GrossSalary { get; set; }
public decimal Deductions { get; set; }
public decimal NetSalary { get; set; }
public DateTime CreatedAt { get; set; }
}
public class Performance
{
public int PerformanceId { get; set; }
public int EmployeeId { get; set; }
public DateTime ReviewDate { get; set; }
public int Rating { get; set; }
public string Comments { get; set; }
public DateTime CreatedAt { get; set; }
}
public class JobPosting
{
public int JobPostingId { get; set; }
public string JobTitle { get; set; }
public string Description { get; set; }
public string Requirements { get; set; }
public DateTime CreatedAt { get; set; }
public bool IsActive { get; set; }
}
public class TrainingProgram
{
public int TrainingProgramId { get; set; }
public string Title { get; set; }
public string Description { get; set; }
public DateTime StartDate { get; set; }
public DateTime EndDate { get; set; }
public DateTime CreatedAt { get; set; }
}
public class LeaveRequest
{
public int LeaveRequestId { get; set; }
public int EmployeeId { get; set; }
public DateTime StartDate { get; set; }
public DateTime EndDate { get; set; }
public string Reason { get; set; }
public string Status { get; set; }
public DateTime CreatedAt { get; set; }
}
public class Document
{
public int DocumentId { get; set; }
public int EmployeeId { get; set; }
public string DocumentType { get; set; }
public string FilePath { get; set; }
public DateTime CreatedAt { get; set; }
}
public class Policy
{
public int PolicyId { get; set; }
public string Title { get; set; }
public string Description { get; set; }
public DateTime CreatedAt { get; set; }
}
public class Announcement
{
public int AnnouncementId { get; set; }
public string Title { get; set; }
public string Message { get; set; }
public DateTime CreatedAt { get; set; }
}
Step 2: Create Repositories
Next, we will create a repository for each model. Below is an example of a generic repository pattern for the User model. You can create similar repositories for other models.
User Repository.cs
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
public class User Repository
{
private readonly string _connectionString;
public UserRepository()
{
_connectionString = ConfigurationManager.ConnectionStrings["YourConnectionStringName"].ConnectionString;
}
public IEnumerable<User> GetAllUsers()
{
var users = new List<User>();
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("SELECT * FROM Users", connection);
connection.Open();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
users.Add(new User
{
UserId = (int)reader["User Id"],
Username = reader["Username"].ToString(),
PasswordHash = reader["PasswordHash"].ToString(),
Email = reader["Email"].ToString(),
RoleId = reader["RoleId"] as int?,
CreatedAt = (DateTime)reader["CreatedAt"]
});
}
}
}
return users;
}
public User GetUser ById(int userId)
{
User user = null;
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("SELECT * FROM Users WHERE UserId = @User Id", connection);
command.Parameters.AddWithValue("@User Id", userId);
connection.Open();
using (var reader = command.ExecuteReader())
{
if (reader.Read())
{
user = new User
{
UserId = (int)reader["User Id"],
Username = reader["Username"].ToString(),
PasswordHash = reader["PasswordHash"].ToString(),
Email = reader["Email"].ToString(),
RoleId = reader["RoleId"] as int?,
CreatedAt = (DateTime)reader["CreatedAt"]
};
}
}
}
return user;
}
public void AddUser (User user)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("INSERT INTO Users (Username, PasswordHash, Email, RoleId) VALUES (@Username, @PasswordHash, @Email, @RoleId)", connection);
command.Parameters.AddWithValue("@Username", user.Username);
command.Parameters.AddWithValue("@PasswordHash", user.PasswordHash);
command.Parameters.AddWithValue("@Email", user.Email);
command.Parameters.AddWithValue("@RoleId", user.RoleId);
connection.Open();
command.ExecuteNonQuery();
}
}
public void UpdateUser (User user)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("UPDATE Users SET Username = @Username, PasswordHash = @PasswordHash, Email = @Email, RoleId = @RoleId WHERE UserId = @User Id", connection);
command.Parameters.AddWithValue("@User Id", user.UserId);
command.Parameters.AddWithValue("@Username", user.Username);
command.Parameters.AddWithValue("@PasswordHash", user.PasswordHash);
command.Parameters.AddWithValue("@Email", user.Email);
command.Parameters.AddWithValue("@RoleId", user.RoleId);
connection.Open();
command.ExecuteNonQuery();
}
}
public void DeleteUser (int userId)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("DELETE FROM Users WHERE UserId = @User Id", connection);
command.Parameters.AddWithValue("@User Id", userId);
connection.Open();
command.ExecuteNonQuery();
}
}
}
Additional Repositories
You can create similar repository classes for the other models (Role, Employee, Attendance, Payroll, Performance, JobPosting, TrainingProgram, LeaveRequest, Document, Policy, Announcement) by following the same pattern as shown in the User Repository. Each repository should implement methods for CRUD operations (Create, Read, Update, Delete) specific to its model.
This setup provides a structured way to manage data access in your ASP.NET application using ADO.NET. Each model corresponds to a table in the database, and each repository encapsulates the logic for interacting with that table, promoting separation of concerns and making your code more maintainable.
Creating Remaining Repositories
Below are the repository classes for the remaining models based on the SQL Server schema you provided. Each repository will implement basic CRUD operations (Create, Read, Update, Delete) using ADO.NET.
1. RoleRepository
public class RoleRepository
{
private readonly string _connectionString;
public RoleRepository()
{
_connectionString = ConfigurationManager.ConnectionStrings["YourConnectionStringName"].ConnectionString;
}
public IEnumerable<Role> GetAllRoles()
{
var roles = new List<Role>();
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("SELECT * FROM Roles", connection);
connection.Open();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
roles.Add(new Role
{
RoleId = (int)reader["RoleId"],
RoleName = reader["RoleName"].ToString()
});
}
}
}
return roles;
}
public Role GetRoleById(int roleId)
{
Role role = null;
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("SELECT * FROM Roles WHERE RoleId = @RoleId", connection);
command.Parameters.AddWithValue("@RoleId", roleId);
connection.Open();
using (var reader = command.ExecuteReader())
{
if (reader.Read())
{
role = new Role
{
RoleId = (int)reader["RoleId"],
RoleName = reader["RoleName"].ToString()
};
}
}
}
return role;
}
public void AddRole(Role role)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("INSERT INTO Roles (RoleName) VALUES (@RoleName)", connection);
command.Parameters.AddWithValue("@RoleName", role.RoleName);
connection.Open();
command.ExecuteNonQuery();
}
}
public void UpdateRole(Role role)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("UPDATE Roles SET RoleName = @RoleName WHERE RoleId = @RoleId", connection);
command.Parameters.AddWithValue("@RoleId", role.RoleId);
command.Parameters.AddWithValue("@RoleName", role.RoleName);
connection.Open();
command.ExecuteNonQuery();
}
}
public void DeleteRole(int roleId)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("DELETE FROM Roles WHERE RoleId = @RoleId", connection);
command.Parameters.AddWithValue("@RoleId", roleId);
connection.Open();
command.ExecuteNonQuery();
}
}
}
2. EmployeeRepository
public class EmployeeRepository
{
private readonly string _connectionString;
public EmployeeRepository()
{
_connectionString = ConfigurationManager.ConnectionStrings["YourConnectionStringName"].ConnectionString;
}
public IEnumerable<Employee> GetAllEmployees()
{
var employees = new List<Employee>();
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("SELECT * FROM Employees", connection);
connection.Open();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
employees.Add(new Employee
{
EmployeeId = (int)reader["EmployeeId"],
FirstName = reader["FirstName"].ToString(),
LastName = reader["LastName"].ToString(),
Email = reader["Email"].ToString(),
Phone = reader["Phone"].ToString(),
HireDate = (DateTime)reader["HireDate"],
JobTitle = reader["JobTitle"].ToString(),
Department = reader["Department"].ToString(),
Salary = (decimal)reader["Salary"],
CreatedAt = (DateTime)reader["CreatedAt"],
UserId = reader["User Id"] as int?
});
}
}
}
return employees;
}
public Employee GetEmployeeById(int employeeId)
{
Employee employee = null;
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("SELECT * FROM Employees WHERE EmployeeId = @EmployeeId", connection);
command.Parameters.AddWithValue("@EmployeeId", employeeId);
connection.Open();
using (var reader = command.ExecuteReader())
{
if (reader.Read())
{
employee = new Employee
{
EmployeeId = (int)reader["EmployeeId"],
FirstName = reader["FirstName"].ToString(),
LastName = reader["LastName"].ToString(),
Email = reader["Email"].ToString(),
Phone = reader["Phone"].ToString (),
HireDate = (DateTime)reader["HireDate"],
JobTitle = reader["JobTitle"].ToString(),
Department = reader["Department"].ToString(),
Salary = (decimal)reader["Salary"],
CreatedAt = (DateTime)reader["CreatedAt"],
UserId = reader["User Id"] as int?
};
}
}
}
return employee;
}
public void AddEmployee(Employee employee)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("INSERT INTO Employees (FirstName, LastName, Email, Phone, HireDate, JobTitle, Department, Salary, UserId) VALUES (@FirstName, @LastName, @Email, @Phone, @HireDate, @JobTitle, @Department, @Salary, @User Id)", connection);
command.Parameters.AddWithValue("@FirstName", employee.FirstName);
command.Parameters.AddWithValue("@LastName", employee.LastName);
command.Parameters.AddWithValue("@Email", employee.Email);
command.Parameters.AddWithValue("@Phone", employee.Phone);
command.Parameters.AddWithValue("@HireDate", employee.HireDate);
command.Parameters.AddWithValue("@JobTitle", employee.JobTitle);
command.Parameters.AddWithValue("@Department", employee.Department);
command.Parameters.AddWithValue("@Salary", employee.Salary);
command.Parameters.AddWithValue("@User Id", employee.UserId);
connection.Open();
command.ExecuteNonQuery();
}
}
public void UpdateEmployee(Employee employee)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("UPDATE Employees SET FirstName = @FirstName, LastName = @LastName, Email = @Email, Phone = @Phone, HireDate = @HireDate, JobTitle = @JobTitle, Department = @Department, Salary = @Salary, UserId = @User Id WHERE EmployeeId = @EmployeeId", connection);
command.Parameters.AddWithValue("@EmployeeId", employee.EmployeeId);
command.Parameters.AddWithValue("@FirstName", employee.FirstName);
command.Parameters.AddWithValue("@LastName", employee.LastName);
command.Parameters.AddWithValue("@Email", employee.Email);
command.Parameters.AddWithValue("@Phone", employee.Phone);
command.Parameters.AddWithValue("@HireDate", employee.HireDate);
command.Parameters.AddWithValue("@JobTitle", employee.JobTitle);
command.Parameters.AddWithValue("@Department", employee.Department);
command.Parameters.AddWithValue("@Salary", employee.Salary);
command.Parameters.AddWithValue("@User Id", employee.UserId);
connection.Open();
command.ExecuteNonQuery();
}
}
public void DeleteEmployee(int employeeId)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("DELETE FROM Employees WHERE EmployeeId = @EmployeeId", connection);
command.Parameters.AddWithValue("@EmployeeId", employeeId);
connection.Open();
command.ExecuteNonQuery();
}
}
}
3. AttendanceRepository
public class AttendanceRepository
{
private readonly string _connectionString;
public AttendanceRepository()
{
_connectionString = ConfigurationManager.ConnectionStrings["YourConnectionStringName"].ConnectionString;
}
public IEnumerable<Attendance> GetAllAttendanceRecords()
{
var attendanceRecords = new List<Attendance>();
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("SELECT * FROM Attendance", connection);
connection.Open();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
attendanceRecords.Add(new Attendance
{
AttendanceId = (int)reader["AttendanceId"],
EmployeeId = (int)reader["EmployeeId"],
Date = (DateTime)reader["Date"],
Status = reader["Status"].ToString(),
CreatedAt = (DateTime)reader["CreatedAt"]
});
}
}
}
return attendanceRecords;
}
public Attendance GetAttendanceById(int attendanceId)
{
Attendance attendance = null;
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("SELECT * FROM Attendance WHERE AttendanceId = @AttendanceId", connection);
command.Parameters.AddWithValue("@AttendanceId", attendanceId);
connection.Open();
using (var reader = command.ExecuteReader())
{
if (reader.Read())
{
attendance = new Attendance
{
AttendanceId = (int)reader["AttendanceId"],
EmployeeId = (int)reader["EmployeeId"],
Date = (DateTime)reader["Date"],
Status = reader["Status"].ToString(),
CreatedAt = (DateTime)reader["CreatedAt"]
};
}
}
}
return attendance;
}
public void AddAttendance(Attendance attendance)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("INSERT INTO Attendance (EmployeeId, Date, Status) VALUES (@EmployeeId, @Date, @Status)", connection);
command.Parameters.AddWithValue("@EmployeeId", attendance.EmployeeId);
command.Parameters.AddWithValue("@Date", attendance.Date);
command.Parameters.AddWithValue("@Status", attendance.Status);
connection.Open();
command.ExecuteNonQuery();
}
}
public void UpdateAttendance(Attendance attendance)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("UPDATE Attendance SET EmployeeId = @EmployeeId, Date = @Date, Status = @Status WHERE AttendanceId = @AttendanceId", connection);
command.Parameters.AddWithValue("@AttendanceId", attendance.AttendanceId);
command.Parameters.AddWithValue("@EmployeeId", attendance.EmployeeId);
command.Parameters.AddWithValue("@Date", attendance.Date);
command.Parameters.AddWithValue("@Status", attendance.Status);
connection.Open();
command.ExecuteNonQuery();
}
}
public void DeleteAttendance(int attendanceId)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("DELETE FROM Attendance WHERE AttendanceId = @AttendanceId", connection);
command.Parameters.AddWithValue("@AttendanceId", attendanceId);
connection.Open();
command.ExecuteNonQuery();
}
}
}
4. PayrollRepository
public class PayrollRepository
{
private readonly string _connectionString;
public PayrollRepository()
{
_connectionString = ConfigurationManager.ConnectionStrings["YourConnectionStringName"].ConnectionString;
}
public IEnumerable<Payroll> GetAllPayrolls()
{
var payrolls = new List<Payroll>();
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("SELECT * FROM Payroll", connection);
connection.Open();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
payrolls.Add(new Payroll
{
PayrollId = (int)reader["PayrollId"],
EmployeeId = (int)reader["EmployeeId"],
PayPeriodStart = (DateTime)reader["PayPeriodStart"],
PayPeriodEnd = (DateTime)reader["PayPeriodEnd"],
GrossSalary = (decimal)reader["GrossSalary"],
Deductions = (decimal)reader["Deductions"],
NetSalary = (decimal)reader["NetSalary"],
CreatedAt = (DateTime)reader["CreatedAt"]
});
}
}
}
return payrolls;
}
public Payroll GetPayrollById(int payrollId)
{
Payroll payroll = null;
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("SELECT * FROM Payroll WHERE PayrollId = @PayrollId", connection);
command.Parameters.AddWithValue("@PayrollId", payrollId);
connection.Open();
using (var reader = command.ExecuteReader())
{
if (reader.Read())
{
payroll = new Payroll
{
PayrollId = (int)reader["PayrollId"],
EmployeeId = (int)reader["EmployeeId"],
PayPeriodStart = (DateTime)reader["PayPeriodStart"],
PayPeriodEnd = (DateTime)reader["PayPeriodEnd"],
GrossSalary = (decimal)reader["GrossSalary"],
Deductions = (decimal)reader["Deductions"],
NetSalary = (decimal)reader["NetSalary"],
CreatedAt = (DateTime)reader["CreatedAt"]
};
}
}
}
return payroll;
}
public void AddPayroll(Payroll payroll)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("INSERT INTO Payroll (EmployeeId, PayPeriodStart, PayPeriodEnd, GrossSalary, Deductions, NetSalary) VALUES (@EmployeeId, @PayPeriodStart, @PayPeriodEnd, @GrossSalary, @Deductions, @NetSalary)", connection);
command.Parameters.AddWithValue("@EmployeeId", payroll.EmployeeId);
command.Parameters.AddWithValue("@PayPeriodStart", payroll.PayPeriodStart);
command.Parameters.AddWithValue("@PayPeriodEnd", payroll.PayPeriodEnd);
command.Parameters.AddWithValue("@GrossSalary", payroll.GrossSalary);
command.Parameters.AddWithValue("@Deductions", payroll.Deductions);
command.Parameters.AddWithValue("@NetSalary", payroll.NetSalary);
connection.Open();
command.ExecuteNonQuery();
}
}
public void UpdatePayroll(Payroll payroll)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("UPDATE Payroll SET EmployeeId = @EmployeeId, PayPeriodStart = @PayPeriodStart, PayPeriodEnd = @PayPeriodEnd, GrossSalary = @GrossSalary, Deductions = @Deductions, NetSalary = @NetSalary WHERE PayrollId = @PayrollId", connection);
command.Parameters.AddWithValue("@PayrollId", payroll.PayrollId);
command.Parameters.AddWithValue("@EmployeeId", payroll.EmployeeId);
command.Parameters.AddWithValue("@PayPeriodStart", payroll.PayPeriodStart);
command.Parameters.AddWithValue("@PayPeriodEnd", payroll.PayPeriodEnd);
command.Parameters.AddWithValue("@GrossSalary", payroll.GrossSalary);
command.Parameters.AddWithValue("@Deductions", payroll.Deductions);
command.Parameters.AddWithValue("@NetSalary", payroll.NetSalary);
connection.Open();
command.ExecuteNonQuery();
}
}
public void DeletePayroll(int payrollId)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("DELETE FROM Payroll WHERE PayrollId = @PayrollId", connection);
command.Parameters.AddWithValue("@PayrollId", payrollId);
connection.Open();
command.ExecuteNonQuery();
}
}
}
5. PerformanceRepository
public class PerformanceRepository
{
private readonly string _connectionString;
public PerformanceRepository()
{
_connectionString = ConfigurationManager.ConnectionStrings["YourConnectionStringName"].ConnectionString;
}
public IEnumerable<Performance> GetAllPerformances()
{
var performances = new List<Performance>();
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("SELECT * FROM Performance", connection);
connection.Open();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
performances.Add(new Performance
{
PerformanceId = (int)reader["PerformanceId"],
EmployeeId = (int)reader["EmployeeId"],
ReviewDate = (DateTime)reader["ReviewDate"],
Rating = (int)reader["Rating"],
Comments = reader["Comments"].ToString(),
CreatedAt = (DateTime)reader["CreatedAt"]
});
}
}
}
return performances;
}
public Performance GetPerformanceById(int performanceId)
{
Performance performance = null;
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("SELECT * FROM Performance WHERE PerformanceId = @PerformanceId", connection);
command.Parameters.AddWithValue("@PerformanceId", performanceId);
connection.Open();
using (var reader = command.ExecuteReader())
{
if (reader.Read())
{
performance = new Performance
{
PerformanceId = (int)reader["PerformanceId"],
EmployeeId = (int)reader["EmployeeId"],
ReviewDate = (DateTime)reader["ReviewDate"],
Rating = (int)reader["Rating"],
Comments = reader["Comments"].ToString(),
CreatedAt = (DateTime)reader["CreatedAt"]
};
}
}
}
return performance;
}
public void AddPerformance(Performance performance)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("INSERT INTO Performance (EmployeeId, ReviewDate, Rating, Comments) VALUES (@EmployeeId, @ReviewDate, @Rating, @Comments)", connection);
command.Parameters.AddWithValue("@EmployeeId", performance.EmployeeId);
command.Parameters.AddWithValue("@ReviewDate", performance.ReviewDate);
command.Parameters.AddWithValue("@Rating", performance.Rating);
command.Parameters.AddWithValue("@Comments", performance.Comments);
connection.Open();
command.ExecuteNonQuery();
}
}
public void UpdatePerformance(Performance performance)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("UPDATE Performance SET EmployeeId = @EmployeeId, ReviewDate = @ReviewDate, Rating = @Rating, Comments = @Comments WHERE PerformanceId = @PerformanceId", connection);
command.Parameters.AddWithValue("@PerformanceId", performance.PerformanceId);
command.Parameters.AddWithValue("@EmployeeId", performance.EmployeeId);
command.Parameters.AddWithValue("@ReviewDate", performance.ReviewDate);
command.Parameters.AddWithValue("@Rating", performance.Rating);
command.Parameters.AddWithValue("@Comments", performance.Comments);
connection.Open();
command.ExecuteNonQuery();
}
}
public void DeletePerformance(int performanceId)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("DELETE FROM Performance WHERE PerformanceId = @PerformanceId", connection);
command.Parameters.AddWithValue("@PerformanceId", performanceId);
connection.Open();
command.ExecuteNonQuery();
}
}
}
6. JobPostingRepository
public class JobPostingRepository
{
private readonly string _connectionString;
public JobPostingRepository()
{
_connectionString = ConfigurationManager.ConnectionStrings["YourConnectionStringName"].ConnectionString;
}
public IEnumerable<JobPosting> GetAllJobPostings()
{
var jobPostings = new List<JobPosting>();
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("SELECT * FROM JobPostings", connection);
connection.Open();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
jobPostings.Add(new JobPosting
{
JobPostingId = (int)reader["JobPostingId"],
JobTitle = reader["JobTitle"].ToString(),
Description = reader["Description"].ToString(),
Requirements = reader["Requirements"].ToString(),
CreatedAt = (DateTime)reader["CreatedAt"],
IsActive = (bool)reader["IsActive"]
});
}
}
}
return jobPostings;
}
public JobPosting GetJobPostingById(int jobPostingId)
{
JobPosting jobPosting = null;
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("SELECT * FROM JobPostings WHERE JobPostingId = @JobPostingId", connection);
command.Parameters.AddWithValue("@JobPostingId", jobPostingId);
connection.Open();
using (var reader = command.ExecuteReader())
{
if (reader.Read())
{
jobPosting = new JobPosting
{
JobPostingId = (int)reader["JobPostingId"],
JobTitle = reader["JobTitle"].ToString(),
Description = reader["Description"].ToString(),
Requirements = reader["Requirements"].ToString(),
CreatedAt = (DateTime)reader["CreatedAt"],
IsActive = (bool)reader["IsActive"]
};
}
}
}
return jobPosting;
}
public void AddJobPosting(JobPosting jobPosting)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("INSERT INTO JobPostings (JobTitle, Description, Requirements, IsActive) VALUES (@JobTitle, @Description, @Requirements, @IsActive)", connection);
command.Parameters.AddWithValue("@JobTitle", jobPosting.JobTitle);
command.Parameters.AddWithValue("@Description", jobPosting.Description);
command.Parameters.AddWithValue("@Requirements", jobPosting.Requirements);
command.Parameters.AddWithValue("@IsActive", jobPosting.IsActive);
connection.Open();
command.ExecuteNonQuery();
}
}
public void UpdateJobPosting(JobPosting jobPosting)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("UPDATE JobPostings SET JobTitle = @JobTitle, Description = @Description, Requirements = @Requirements, IsActive = @IsActive WHERE JobPostingId = @JobPostingId", connection);
command.Parameters.AddWithValue("@JobPostingId", jobPosting.JobPostingId);
command.Parameters.AddWithValue("@JobTitle", jobPosting.JobTitle);
command.Parameters.AddWithValue("@Description", jobPosting.Description);
command.Parameters.AddWithValue("@Requirements", jobPosting.Requirements);
command.Parameters.AddWithValue("@IsActive", jobPosting.IsActive);
connection.Open();
command.ExecuteNonQuery();
}
}
public void DeleteJobPosting(int jobPostingId)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("DELETE FROM JobPostings WHERE JobPostingId = @JobPostingId", connection);
command.Parameters.AddWithValue("@JobPostingId", jobPostingId);
connection.Open();
command.ExecuteNonQuery();
}
}
}
7. TrainingProgramRepository
public class TrainingProgramRepository
{
private readonly string _connectionString;
public TrainingProgramRepository()
{
_connectionString = ConfigurationManager.ConnectionStrings["YourConnectionStringName"].ConnectionString;
}
public IEnumerable<TrainingProgram> GetAllTrainingPrograms()
{
var trainingPrograms = new List<TrainingProgram>();
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("SELECT * FROM TrainingPrograms", connection);
connection.Open();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
trainingPrograms.Add(new TrainingProgram
{
TrainingProgramId = (int)reader["TrainingProgramId"],
Title = reader["Title"].ToString(),
Description = reader["Description"].ToString(),
StartDate = (DateTime)reader ["StartDate"],
EndDate = (DateTime)reader["EndDate"],
CreatedAt = (DateTime)reader["CreatedAt"]
});
}
}
}
return trainingPrograms;
}
public TrainingProgram GetTrainingProgramById(int trainingProgramId)
{
TrainingProgram trainingProgram = null;
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("SELECT * FROM TrainingPrograms WHERE TrainingProgramId = @TrainingProgramId", connection);
command.Parameters.AddWithValue("@TrainingProgramId", trainingProgramId);
connection.Open();
using (var reader = command.ExecuteReader())
{
if (reader.Read())
{
trainingProgram = new TrainingProgram
{
TrainingProgramId = (int)reader["TrainingProgramId"],
Title = reader["Title"].ToString(),
Description = reader["Description"].ToString(),
StartDate = (DateTime)reader["StartDate"],
EndDate = (DateTime)reader["EndDate"],
CreatedAt = (DateTime)reader["CreatedAt"]
};
}
}
}
return trainingProgram;
}
public void AddTrainingProgram(TrainingProgram trainingProgram)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("INSERT INTO TrainingPrograms (Title, Description, StartDate, EndDate) VALUES (@Title, @Description, @StartDate, @EndDate)", connection);
command.Parameters.AddWithValue("@Title", trainingProgram.Title);
command.Parameters.AddWithValue("@Description", trainingProgram.Description);
command.Parameters.AddWithValue("@StartDate", trainingProgram.StartDate);
command.Parameters.AddWithValue("@EndDate", trainingProgram.EndDate);
connection.Open();
command.ExecuteNonQuery();
}
}
public void UpdateTrainingProgram(TrainingProgram trainingProgram)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("UPDATE TrainingPrograms SET Title = @Title, Description = @Description, StartDate = @StartDate, EndDate = @EndDate WHERE TrainingProgramId = @TrainingProgramId", connection);
command.Parameters.AddWithValue("@TrainingProgramId", trainingProgram.TrainingProgramId);
command.Parameters.AddWithValue("@Title", trainingProgram.Title);
command.Parameters.AddWithValue("@Description", trainingProgram.Description);
command.Parameters.AddWithValue("@StartDate", trainingProgram.StartDate);
command.Parameters.AddWithValue("@EndDate", trainingProgram.EndDate);
connection.Open();
command.ExecuteNonQuery();
}
}
public void DeleteTrainingProgram(int trainingProgramId)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("DELETE FROM TrainingPrograms WHERE TrainingProgramId = @TrainingProgramId", connection);
command.Parameters.AddWithValue("@TrainingProgramId", trainingProgramId);
connection.Open();
command.ExecuteNonQuery();
}
}
}
8. LeaveRequestRepository
public class LeaveRequestRepository
{
private readonly string _connectionString;
public LeaveRequestRepository()
{
_connectionString = ConfigurationManager.ConnectionStrings["YourConnectionStringName"].ConnectionString;
}
public IEnumerable<LeaveRequest> GetAllLeaveRequests()
{
var leaveRequests = new List<LeaveRequest>();
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("SELECT * FROM LeaveRequests", connection);
connection.Open();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
leaveRequests.Add(new LeaveRequest
{
LeaveRequestId = (int)reader["LeaveRequestId"],
EmployeeId = (int)reader["EmployeeId"],
StartDate = (DateTime)reader["StartDate"],
EndDate = (DateTime)reader["EndDate"],
Reason = reader["Reason"].ToString(),
Status = reader["Status"].ToString(),
CreatedAt = (DateTime)reader["CreatedAt"]
});
}
}
}
return leaveRequests;
}
public LeaveRequest GetLeaveRequestById(int leaveRequestId)
{
LeaveRequest leaveRequest = null;
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("SELECT * FROM LeaveRequests WHERE LeaveRequestId = @LeaveRequestId", connection);
command.Parameters.AddWithValue("@LeaveRequestId", leaveRequestId);
connection.Open();
using (var reader = command.ExecuteReader())
{
if (reader.Read())
{
leaveRequest = new LeaveRequest
{
LeaveRequestId = (int)reader["LeaveRequestId"],
EmployeeId = (int)reader["EmployeeId"],
StartDate = (DateTime reader["StartDate"],
EndDate = (DateTime)reader["EndDate"],
Reason = reader["Reason"].ToString(),
Status = reader["Status"].ToString(),
CreatedAt = (DateTime)reader["CreatedAt"]
};
}
}
}
return leaveRequest;
}
public void AddLeaveRequest(LeaveRequest leaveRequest)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("INSERT INTO LeaveRequests (EmployeeId, StartDate, EndDate, Reason, Status) VALUES (@EmployeeId, @StartDate, @EndDate, @Reason, @Status)", connection);
command.Parameters.AddWithValue("@EmployeeId", leaveRequest.EmployeeId);
command.Parameters.AddWithValue("@StartDate", leaveRequest.StartDate);
command.Parameters.AddWithValue("@EndDate", leaveRequest.EndDate);
command.Parameters.AddWithValue("@Reason", leaveRequest.Reason);
command.Parameters.AddWithValue("@Status", leaveRequest.Status);
connection.Open();
command.ExecuteNonQuery();
}
}
public void UpdateLeaveRequest(LeaveRequest leaveRequest)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("UPDATE LeaveRequests SET EmployeeId = @EmployeeId, StartDate = @StartDate, EndDate = @EndDate, Reason = @Reason, Status = @Status WHERE LeaveRequestId = @LeaveRequestId", connection);
command.Parameters.AddWithValue("@LeaveRequestId", leaveRequest.LeaveRequestId);
command.Parameters.AddWithValue("@EmployeeId", leaveRequest.EmployeeId);
command.Parameters.AddWithValue("@StartDate", leaveRequest.StartDate);
command.Parameters.AddWithValue("@EndDate", leaveRequest.EndDate);
command.Parameters.AddWithValue("@Reason", leaveRequest.Reason);
command.Parameters.AddWithValue("@Status", leaveRequest.Status);
connection.Open();
command.ExecuteNonQuery();
}
}
public void DeleteLeaveRequest(int leaveRequestId)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("DELETE FROM LeaveRequests WHERE LeaveRequestId = @LeaveRequestId", connection);
command.Parameters.AddWithValue("@LeaveRequestId", leaveRequestId);
connection.Open();
command.ExecuteNonQuery();
}
}
}
9. DocumentRepository
public class DocumentRepository
{
private readonly string _connectionString;
public DocumentRepository()
{
_connectionString = ConfigurationManager.ConnectionStrings["YourConnectionStringName"].ConnectionString;
}
public IEnumerable<Document> GetAllDocuments()
{
var documents = new List<Document>();
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("SELECT * FROM Documents", connection);
connection.Open();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
documents.Add(new Document
{
DocumentId = (int)reader["DocumentId"],
EmployeeId = (int)reader["EmployeeId"],
DocumentType = reader["DocumentType"].ToString(),
FilePath = reader["FilePath"].ToString(),
CreatedAt = (DateTime)reader["CreatedAt"]
});
}
}
}
return documents;
}
public Document GetDocumentById(int documentId)
{
Document document = null;
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("SELECT * FROM Documents WHERE DocumentId = @DocumentId", connection);
command.Parameters.AddWithValue("@DocumentId", documentId);
connection.Open();
using (var reader = command.ExecuteReader())
{
if (reader.Read())
{
document = new Document
{
DocumentId = (int)reader["DocumentId"],
EmployeeId = (int)reader["EmployeeId"],
DocumentType = reader["DocumentType"].ToString(),
FilePath = reader["FilePath"].ToString(),
CreatedAt = (DateTime)reader["CreatedAt"]
};
}
}
}
return document;
}
public void AddDocument(Document document)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("INSERT INTO Documents (EmployeeId, DocumentType, FilePath) VALUES (@EmployeeId, @DocumentType, @FilePath)", connection);
command.Parameters.AddWithValue("@EmployeeId", document.EmployeeId);
command.Parameters.AddWithValue("@DocumentType", document.DocumentType);
command.Parameters.AddWithValue("@FilePath", document.FilePath);
connection.Open();
command.ExecuteNonQuery();
}
}
public void UpdateDocument(Document document)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("UPDATE Documents SET EmployeeId = @EmployeeId, DocumentType = @DocumentType, FilePath = @FilePath WHERE DocumentId = @DocumentId", connection);
command.Parameters.AddWithValue("@DocumentId", document.DocumentId);
command.Parameters.AddWithValue("@EmployeeId", document.EmployeeId);
command.Parameters.AddWithValue("@DocumentType", document.DocumentType);
command.Parameters.AddWithValue("@FilePath", document.FilePath);
connection.Open();
command.ExecuteNonQuery();
}
}
public void DeleteDocument(int documentId)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("DELETE FROM Documents WHERE DocumentId = @DocumentId", connection);
command.Parameters.AddWithValue("@DocumentId", documentId);
connection.Open();
command.ExecuteNonQuery();
}
}
}
10. PolicyRepository
public class PolicyRepository
{
private readonly string _connectionString;
public PolicyRepository()
{
_connectionString = ConfigurationManager.ConnectionStrings["YourConnectionStringName"].ConnectionString;
}
public IEnumerable<Policy> GetAllPolicies()
{
var policies = new List<Policy>();
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("SELECT * FROM Policies", connection);
connection.Open();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
policies.Add(new Policy
{
PolicyId = (int)reader["PolicyId"],
Title = reader["Title"].ToString(),
Description = reader["Description"].ToString(),
CreatedAt = (DateTime)reader["CreatedAt"]
});
}
}
}
return policies;
}
public Policy GetPolicyById(int policyId)
{
Policy policy = null;
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("SELECT * FROM Policies WHERE PolicyId = @PolicyId", connection);
command.Parameters.AddWithValue("@PolicyId", policyId);
connection.Open();
using (var reader = command.ExecuteReader())
{
if (reader.Read())
{
policy = new Policy
{
PolicyId = (int)reader["PolicyId"],
Title = reader["Title"].ToString(),
Description = reader["Description"].ToString(),
CreatedAt = (DateTime)reader["CreatedAt"]
};
}
}
}
return policy;
}
public void AddPolicy(Policy policy)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("INSERT INTO Policies (Title, Description) VALUES (@Title, @Description)", connection);
command.Parameters.AddWithValue("@Title", policy.Title);
command.Parameters.AddWithValue("@Description", policy.Description);
connection.Open();
command.ExecuteNonQuery();
}
}
public void UpdatePolicy(Policy policy)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("UPDATE Policies SET Title = @Title, Description = @Description WHERE PolicyId = @PolicyId", connection);
command.Parameters.AddWithValue("@PolicyId", policy.PolicyId);
command.Parameters.AddWithValue("@Title", policy.Title);
command.Parameters.AddWithValue("@Description", policy.Description);
connection.Open();
command.ExecuteNonQuery();
}
}
public void DeletePolicy(int policyId)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("DELETE FROM Policies WHERE PolicyId = @PolicyId", connection);
command.Parameters.AddWithValue("@PolicyId", policyId);
connection.Open();
command.ExecuteNonQuery();
}
}
}
11. AnnouncementRepository
public class AnnouncementRepository
{
private readonly string _connectionString;
public AnnouncementRepository()
{
_connectionString = ConfigurationManager.ConnectionStrings["YourConnectionStringName"].ConnectionString;
}
public IEnumerable<Announcement> GetAllAnnouncements()
{
var announcements = new List<Announcement>();
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("SELECT * FROM Announcements", connection);
connection.Open();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
announcements.Add(new Announcement
{
AnnouncementId = (int)reader["AnnouncementId"],
Title = reader["Title"].ToString(),
Message = reader["Message"].ToString(),
CreatedAt = (DateTime)reader["CreatedAt"]
});
}
}
}
return announcements;
}
public Announcement GetAnnouncementById (int announcementId)
{
Announcement announcement = null;
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("SELECT * FROM Announcements WHERE AnnouncementId = @AnnouncementId", connection);
command.Parameters.AddWithValue("@AnnouncementId", announcementId);
connection.Open();
using (var reader = command.ExecuteReader())
{
if (reader.Read())
{
announcement = new Announcement
{
AnnouncementId = (int)reader["AnnouncementId"],
Title = reader["Title"].ToString(),
Message = reader["Message"].ToString(),
CreatedAt = (DateTime)reader["CreatedAt"]
};
}
}
}
return announcement;
}
public void AddAnnouncement(Announcement announcement)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("INSERT INTO Announcements (Title, Message) VALUES (@Title, @Message)", connection);
command.Parameters.AddWithValue("@Title", announcement.Title);
command.Parameters.AddWithValue("@Message", announcement.Message);
connection.Open();
command.ExecuteNonQuery();
}
}
public void UpdateAnnouncement(Announcement announcement)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("UPDATE Announcements SET Title = @Title, Message = @Message WHERE AnnouncementId = @AnnouncementId", connection);
command.Parameters.AddWithValue("@AnnouncementId", announcement.AnnouncementId);
command.Parameters.AddWithValue("@Title", announcement.Title);
command.Parameters.AddWithValue("@Message", announcement.Message);
connection.Open();
command.ExecuteNonQuery();
}
}
public void DeleteAnnouncement(int announcementId)
{
using (var connection = new SqlConnection(_connectionString))
{
var command = new SqlCommand("DELETE FROM Announcements WHERE AnnouncementId = @AnnouncementId", connection);
command.Parameters.AddWithValue("@AnnouncementId", announcementId);
connection.Open();
command.ExecuteNonQuery();
}
}
}
With these repository classes, you now have a complete set of data access layers for your ASP.NET application using ADO.NET. Each repository provides methods for interacting with the corresponding database table, allowing for easy management of data while adhering to the principles of separation of concerns and maintainability.
Creating Controllers
Now create a controller for each repository.
Below are example controllers for each of the repositories created earlier. Each controller will handle HTTP requests, interact with the corresponding repository, and return appropriate responses. The controllers will follow the ASP.NET MVC pattern.
1. UserController
using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
[Route("api/[controller]")]
[ApiController]
public class UserController : ControllerBase
{
private readonly UserRepository _userRepository;
public UserController()
{
_userRepository = new UserRepository(); // Ideally, use Dependency Injection
}
[HttpGet]
public ActionResult<IEnumerable<User>> GetAllUsers()
{
var users = _userRepository.GetAllUsers();
return Ok(users);
}
[HttpGet("{id}")]
public ActionResult<User> GetUser ById(int id)
{
var user = _userRepository.GetUser ById(id);
if (user == null)
{
return NotFound();
}
return Ok(user);
}
[HttpPost]
public ActionResult<User> CreateUser ([FromBody] User user)
{
_userRepository.AddUser (user);
return CreatedAtAction(nameof(GetUser ById), new { id = user.UserId }, user);
}
[HttpPut("{id}")]
public IActionResult UpdateUser (int id, [FromBody] User user)
{
if (id != user.UserId)
{
return BadRequest();
}
_userRepository.UpdateUser (user);
return NoContent();
}
[HttpDelete("{id}")]
public IActionResult DeleteUser (int id)
{
_userRepository.DeleteUser (id);
return NoContent();
}
}
2. RoleController
using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
[Route("api/[controller]")]
[ApiController]
public class RoleController : ControllerBase
{
private readonly RoleRepository _roleRepository;
public RoleController()
{
_roleRepository = new RoleRepository(); // Ideally, use Dependency Injection
}
[HttpGet]
public ActionResult<IEnumerable<Role>> GetAllRoles()
{
var roles = _roleRepository.GetAllRoles();
return Ok(roles);
}
[HttpGet("{id}")]
public ActionResult<Role> GetRoleById(int id)
{
var role = _roleRepository.GetRoleById(id);
if (role == null)
{
return NotFound();
}
return Ok(role);
}
[HttpPost]
public ActionResult<Role> CreateRole([FromBody] Role role)
{
_roleRepository.AddRole(role);
return CreatedAtAction(nameof(GetRoleById), new { id = role.RoleId }, role);
}
[HttpPut("{id}")]
public IActionResult UpdateRole(int id, [FromBody] Role role)
{
if (id != role.RoleId)
{
return BadRequest();
}
_roleRepository.UpdateRole(role);
return NoContent();
}
[HttpDelete("{id}")]
public IActionResult DeleteRole(int id)
{
_roleRepository.DeleteRole(id);
return NoContent();
}
}
3. EmployeeController
using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
[Route("api/[controller]")]
[ApiController]
public class EmployeeController : ControllerBase
{
private readonly EmployeeRepository _employeeRepository;
public EmployeeController()
{
_employeeRepository = new EmployeeRepository(); // Ideally, use Dependency Injection
}
[HttpGet]
public ActionResult<IEnumerable<Employee>> GetAllEmployees()
{
var employees = _employeeRepository.GetAllEmployees();
return Ok(employees);
}
[HttpGet("{id}")]
public ActionResult<Employee> GetEmployeeById(int id)
{
var employee = _employeeRepository.GetEmployeeById(id);
if (employee == null)
{
return NotFound();
}
return Ok(employee);
}
[HttpPost]
public ActionResult<Employee> CreateEmployee([FromBody] Employee employee)
{
_employeeRepository.AddEmployee(employee);
return CreatedAtAction(nameof(GetEmployeeById), new { id = employee.EmployeeId }, employee);
}
[HttpPut("{id}")]
public IActionResult UpdateEmployee(int id, [FromBody] Employee employee)
{
if (id != employee.EmployeeId)
{
return BadRequest();
}
_employeeRepository.UpdateEmployee(employee);
return NoContent();
}
[HttpDelete("{id}")]
public IActionResult DeleteEmployee(int id)
{
_employeeRepository.DeleteEmployee(id);
return NoContent();
}
}
4. AttendanceController
using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
[Route("api/[ controller]")]
[ApiController]
public class AttendanceController : ControllerBase
{
private readonly AttendanceRepository _attendanceRepository;
public AttendanceController()
{
_attendanceRepository = new AttendanceRepository(); // Ideally, use Dependency Injection
}
[HttpGet]
public ActionResult<IEnumerable<Attendance>> GetAllAttendanceRecords()
{
var attendanceRecords = _attendanceRepository.GetAllAttendanceRecords();
return Ok(attendanceRecords);
}
[HttpGet("{id}")]
public ActionResult<Attendance> GetAttendanceById(int id)
{
var attendance = _attendanceRepository.GetAttendanceById(id);
if (attendance == null)
{
return NotFound();
}
return Ok(attendance);
}
[HttpPost]
public ActionResult<Attendance> CreateAttendance([FromBody] Attendance attendance)
{
_attendanceRepository.AddAttendance(attendance);
return CreatedAtAction(nameof(GetAttendanceById), new { id = attendance.AttendanceId }, attendance);
}
[HttpPut("{id}")]
public IActionResult UpdateAttendance(int id, [FromBody] Attendance attendance)
{
if (id != attendance.AttendanceId)
{
return BadRequest();
}
_attendanceRepository.UpdateAttendance(attendance);
return NoContent();
}
[HttpDelete("{id}")]
public IActionResult DeleteAttendance(int id)
{
_attendanceRepository.DeleteAttendance(id);
return NoContent();
}
}
5. PayrollController
using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
[Route("api/[controller]")]
[ApiController]
public class PayrollController : ControllerBase
{
private readonly PayrollRepository _payrollRepository;
public PayrollController()
{
_payrollRepository = new PayrollRepository(); // Ideally, use Dependency Injection
}
[HttpGet]
public ActionResult<IEnumerable<Payroll>> GetAllPayrolls()
{
var payrolls = _payrollRepository.GetAllPayrolls();
return Ok(payrolls);
}
[HttpGet("{id}")]
public ActionResult<Payroll> GetPayrollById(int id)
{
var payroll = _payrollRepository.GetPayrollById(id);
if (payroll == null)
{
return NotFound();
}
return Ok(payroll);
}
[HttpPost]
public ActionResult<Payroll> CreatePayroll([FromBody] Payroll payroll)
{
_payrollRepository.AddPayroll(payroll);
return CreatedAtAction(nameof(GetPayrollById), new { id = payroll.PayrollId }, payroll);
}
[HttpPut("{id}")]
public IActionResult UpdatePayroll(int id, [FromBody] Payroll payroll)
{
if (id != payroll.PayrollId)
{
return BadRequest();
}
_payrollRepository.UpdatePayroll(payroll);
return NoContent();
}
[HttpDelete("{id}")]
public IActionResult DeletePayroll(int id)
{
_payrollRepository.DeletePayroll(id);
return NoContent();
}
}
6. PerformanceController
using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
[Route("api/[controller]")]
[ApiController]
public class PerformanceController : ControllerBase
{
private readonly PerformanceRepository _performanceRepository;
public PerformanceController()
{
_performanceRepository = new PerformanceRepository(); // Ideally, use Dependency Injection
}
[HttpGet]
public ActionResult<IEnumerable<Performance>> GetAllPerformances()
{
var performances = _performanceRepository.GetAllPerformances();
return Ok(performances);
}
[HttpGet("{id}")]
public ActionResult<Performance> GetPerformanceById(int id)
{
var performance = _performanceRepository.GetPerformanceById(id);
if (performance == null)
{
return NotFound();
}
return Ok(performance);
}
[HttpPost]
public ActionResult<Performance> CreatePerformance([FromBody] Performance performance)
{
_performanceRepository.AddPerformance(performance);
return CreatedAtAction(nameof(GetPerformanceById), new { id = performance.PerformanceId }, performance);
}
[HttpPut("{id}")]
public IActionResult UpdatePerformance(int id, [FromBody] Performance performance)
{
if (id != performance.PerformanceId)
{
return BadRequest();
}
_performanceRepository.UpdatePerformance(performance);
return NoContent();
}
[HttpDelete("{id}")]
public IActionResult DeletePerformance(int id)
{
_performanceRepository.DeletePerformance(id);
return NoContent();
}
}
7. JobPostingController
using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
[Route("api/[controller]")]
[ApiController]
public class JobPostingController : ControllerBase
{
private readonly JobPostingRepository _ JobPostingRepository;
public JobPostingController()
{
_jobPostingRepository = new JobPostingRepository(); // Ideally, use Dependency Injection
}
[HttpGet]
public ActionResult<IEnumerable<JobPosting>> GetAllJobPostings()
{
var jobPostings = _jobPostingRepository.GetAllJobPostings();
return Ok(jobPostings);
}
[HttpGet("{id}")]
public ActionResult<JobPosting> GetJobPostingById(int id)
{
var jobPosting = _jobPostingRepository.GetJobPostingById(id);
if (jobPosting == null)
{
return NotFound();
}
return Ok(jobPosting);
}
[HttpPost]
public ActionResult<JobPosting> CreateJobPosting([FromBody] JobPosting jobPosting)
{
_jobPostingRepository.AddJobPosting(jobPosting);
return CreatedAtAction(nameof(GetJobPostingById), new { id = jobPosting.JobPostingId }, jobPosting);
}
[HttpPut("{id}")]
public IActionResult UpdateJobPosting(int id, [FromBody] JobPosting jobPosting)
{
if (id != jobPosting.JobPostingId)
{
return BadRequest();
}
_jobPostingRepository.UpdateJobPosting(jobPosting);
return NoContent();
}
[HttpDelete("{id}")]
public IActionResult DeleteJobPosting(int id)
{
_jobPostingRepository.DeleteJobPosting(id);
return NoContent();
}
}
8. TrainingProgramController
using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
[Route("api/[controller]")]
[ApiController]
public class TrainingProgramController : ControllerBase
{
private readonly TrainingProgramRepository _trainingProgramRepository;
public TrainingProgramController()
{
_trainingProgramRepository = new TrainingProgramRepository(); // Ideally, use Dependency Injection
}
[HttpGet]
public ActionResult<IEnumerable<TrainingProgram>> GetAllTrainingPrograms()
{
var trainingPrograms = _trainingProgramRepository.GetAllTrainingPrograms();
return Ok(trainingPrograms);
}
[HttpGet("{id}")]
public ActionResult<TrainingProgram> GetTrainingProgramById(int id)
{
var trainingProgram = _trainingProgramRepository.GetTrainingProgramById(id);
if (trainingProgram == null)
{
return NotFound();
}
return Ok(trainingProgram);
}
[HttpPost]
public ActionResult<TrainingProgram> CreateTrainingProgram([FromBody] TrainingProgram trainingProgram)
{
_trainingProgramRepository.AddTrainingProgram(trainingProgram);
return CreatedAtAction(nameof(GetTrainingProgramById), new { id = trainingProgram.TrainingProgramId }, trainingProgram);
}
[HttpPut("{id}")]
public IActionResult UpdateTrainingProgram(int id, [FromBody] TrainingProgram trainingProgram)
{
if (id != trainingProgram.TrainingProgramId)
{
return BadRequest();
}
_trainingProgramRepository.UpdateTrainingProgram(trainingProgram);
return NoContent();
}
[HttpDelete("{id}")]
public IActionResult DeleteTrainingProgram(int id)
{
_trainingProgramRepository.DeleteTrainingProgram(id);
return NoContent();
}
}
9. LeaveRequestController
using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
[Route("api/[controller]")]
[ApiController]
public class LeaveRequestController : ControllerBase
{
private readonly LeaveRequestRepository _leaveRequestRepository;
public LeaveRequestController()
{
_leaveRequestRepository = new LeaveRequestRepository(); // Ideally, use Dependency Injection
}
[HttpGet]
public ActionResult<IEnumerable<LeaveRequest>> GetAllLeaveRequests()
{
var leaveRequests = _leaveRequestRepository.GetAllLeaveRequests();
return Ok(leaveRequests);
}
[HttpGet("{id}")]
public ActionResult<LeaveRequest> GetLeaveRequestById(int id)
{
var leaveRequest = _leaveRequestRepository.GetLeaveRequestById(id);
if (leaveRequest == null)
{
return NotFound();
}
return Ok(leaveRequest);
}
[HttpPost]
public ActionResult<LeaveRequest> CreateLeaveRequest([FromBody] LeaveRequest leaveRequest)
{
_leaveRequestRepository.AddLeaveRequest(leaveRequest);
return CreatedAtAction(nameof(GetLeaveRequestById), new { id = leaveRequest.LeaveRequestId }, leaveRequest);
}
[HttpPut("{id}")]
public IActionResult UpdateLeaveRequest(int id, [FromBody] LeaveRequest leaveRequest)
{
if (id != leaveRequest.LeaveRequestId)
{
return BadRequest();
}
_leaveRequestRepository.UpdateLeaveRequest(leaveRequest);
return NoContent();
}
[HttpDelete("{id}")]
public IActionResult DeleteLeaveRequest(int id)
{
_leaveRequestRepository.DeleteLeaveRequest(id);
return NoContent();
}
}
10. DocumentController
using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
[Route("api/[controller]")]
[ApiController]
public class DocumentController : ControllerBase
{
private readonly DocumentRepository _documentRepository;
public DocumentController()
{
_documentRepository = new DocumentRepository(); // Ideally, use Dependency Injection
}
[HttpGet]
public ActionResult<IEnumerable<Document>> GetAllDocuments()
{
var documents = _documentRepository.GetAllDocuments();
return Ok(documents);
}
[HttpGet("{id}")]
public ActionResult<Document> GetDocumentById(int id)
{
var document = _documentRepository.GetDocumentById(id);
if (document == null)
{
return NotFound();
}
return Ok(document);
}
[HttpPost]
public ActionResult<Document> CreateDocument([FromBody] Document document)
{
_documentRepository.AddDocument(document);
return CreatedAtAction(nameof(GetDocumentById), new { id = document.DocumentId }, document);
}
[HttpPut("{id}")]
public IActionResult UpdateDocument(int id, [FromBody] Document document)
{
if (id != document.DocumentId)
{
return BadRequest();
}
_documentRepository.UpdateDocument(document);
return NoContent();
}
[HttpDelete("{id}")]
public IActionResult DeleteDocument(int id)
{
_documentRepository.DeleteDocument(id);
return NoContent();
}
}
11. PolicyController
using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
[Route("api/[controller]")]
[ApiController]
public class PolicyController : ControllerBase
{
private readonly PolicyRepository _policyRepository;
public PolicyController()
{
_policyRepository = new PolicyRepository(); // Ideally, use Dependency Injection
}
[HttpGet]
public ActionResult<IEnumerable<Policy>> GetAllPolicies()
{
var policies = _policyRepository.GetAllPolicies();
return Ok(policies);
}
[HttpGet("{id}")]
public ActionResult<Policy> GetPolicyById(int id)
{
var policy = _policyRepository.GetPolicyById(id);
if (policy == null)
{
return NotFound();
}
return Ok(policy);
}
[HttpPost]
public ActionResult<Policy> CreatePolicy([FromBody] Policy policy)
{
_policyRepository.AddPolicy(policy);
return CreatedAtAction(nameof(GetPolicyById), new { id = policy.PolicyId }, policy);
}
[HttpPut("{id}")]
public IActionResult UpdatePolicy(int id, [FromBody] Policy policy)
{
if (id != policy.PolicyId)
{
return BadRequest();
}
_policyRepository.UpdatePolicy(policy);
return NoContent();
}
[HttpDelete("{id}")]
public IActionResult DeletePolicy(int id)
{
_policyRepository.DeletePolicy(id);
return NoContent();
}
}
12. AnnouncementController
using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
[Route("api/[controller]")]
[ApiController]
public class AnnouncementController : ControllerBase
{
private readonly AnnouncementRepository _announcementRepository;
public AnnouncementController()
{
_announcementRepository = new AnnouncementRepository(); // Ideally, use Dependency Injection
}
[HttpGet]
public ActionResult<IEnumerable<Announcement>> GetAllAnnouncements()
{
var announcements = _announcementRepository.GetAllAnnouncements();
return Ok(announcements);
}
[HttpGet("{id}")]
public ActionResult<Announcement> GetAnnouncementById(int id)
{
var announcement = _announcementRepository.GetAnnouncementById(id);
if (announcement == null)
{
return NotFound();
}
return Ok(announcement);
}
[HttpPost]
public ActionResult<Announcement> CreateAnnouncement([FromBody] Announcement announcement)
{
_announcementRepository.AddAnnouncement(announcement);
return CreatedAtAction(nameof(GetAnnouncementById), new { id = announcement.AnnouncementId }, announcement);
}
[HttpPut("{id}")]
public IActionResult UpdateAnnouncement(int id, [FromBody] Announcement announcement)
{
if (id != announcement.AnnouncementId)
{
return BadRequest();
}
_announcementRepository.UpdateAnnouncement(announcement);
return NoContent();
}
[HttpDelete("{id}")]
public IActionResult DeleteAnnouncement(int id)
{
_announcementRepository.DeleteAnnouncement(id);
return NoContent();
}
}
With these controllers, you now have a complete set of API endpoints for managing the various entities in your application. Each controller provides a complete set of actions for managing the respective models, allowing for the creation, reading, updating, and deletion of records.
Creating Views
Creating view files for each of the controllers in an ASP.NET MVC application involves creating Razor views that correspond to the actions in the controllers. Below, I will provide a basic structure for the views associated with each controller. These views will focus on displaying data and providing forms for creating and editing records.
User Views
Views/User/Index.cshtml
@model IEnumerable<YourNamespace.Models.User>
<h2>Users</h2>
<a href='@Url.Action("Create")'>Create New User</a>
<table>
<thead>
<tr>
<th>Username</th>
<th>Email</th>
<th>Actions</th>
</tr>
</thead>
<tbody>
@foreach (var user in Model)
{
<tr>
<td>@user.Username</td>
<td>@user.Email</td>
<td>
<a href='@Url.Action("Edit", new { id = user.UserId })'>Edit</a> |
<a href='@Url.Action("Delete", new { id = user.UserId })'>Delete</a>
</td>
</tr>
}
</tbody>
</table>
Views/User/Create.cshtml
@model YourNamespace.Models.User
<h2>Create User</h2>
<form asp-action="Create">
<div>
<label>Username</label>
<input asp-for="Username" />
</div>
<div>
<label>Password</label>
<input asp-for="PasswordHash" type="password" />
</div>
<div>
<label>Email</label>
<input asp-for="Email" />
</div>
<div>
<label>Role ID</label>
<input asp-for="RoleId" />
</div>
<button type="submit">Create</button>
</form>
Views/User/Edit.cshtml
@model YourNamespace.Models.User
<h2>Edit User</h2>
<form asp-action="Edit">
<input type="hidden" asp-for="User Id" />
<div>
<label>Username</label>
<input asp-for="Username" />
</div>
<div>
<label>Password</label>
<input asp-for="PasswordHash" type="password" />
</div>
<div>
<label>Email</label>
<input asp-for="Email" />
</div>
<div>
<label>Role ID</label>
<input asp-for="RoleId" />
</div>
<button type="submit">Save</button>
</form>
Role Views
Views/Role/Index.cshtml
@model IEnumerable<YourNamespace.Models.Role>
<h2>Roles</h2>
<a href='@Url.Action("Create")'>Create New Role</a>
<table>
<thead>
<tr>
<th>Role Name</th>
<th>Actions</th>
</tr>
</thead>
<tbody>
@foreach (var role in Model)
{
<tr>
<td>@role.RoleName</td>
<td>
<a href='@Url.Action("Edit", new { id = role.RoleId })'>Edit</a> |
<a href='@Url.Action("Delete", new { id = role.RoleId })'>Delete</a>
</td>
</tr>
}
</tbody>
</table>
Views/Role/Create.cshtml
@model YourNamespace.Models.Role
<h2>Create Role</h2>
<form asp-action="Create">
<div>
<label>Role Name</label>
<input asp-for="RoleName" />
</div>
<button type="submit">Create</button>
</form>
Views/Role/Edit.cshtml
@model YourNamespace.Models.Role
<h2>Edit Role</h2>
<form asp-action="Edit">
<input type="hidden" asp-for="RoleId" />
<div>
<label>Role Name</label>
<input asp-for="RoleName" />
</div>
<button type="submit">Save</button>
</form>
Employee Views
Views/Employee/Index.cshtml
@model IEnumerable<YourNamespace.Models.Employee>
<h2>Employees</h2>
<a href='@Url.Action("Create")'>Create New Employee</a>
<table>
<thead>
<tr>
<th>First Name</th>
<th>Last Name</th>
<th>Email</th>
<th>Actions</th>
</tr>
</thead>
<tbody>
@foreach (var employee in Model)
{
<tr>
<td>@employee.FirstName</td>
<td>@employee.LastName</td>
<td>@employee.Email</td>
<td>
<a href='@Url.Action("Edit", new { id = employee.EmployeeId })'>Edit</a> |
<a href='@Url.Action("Delete", new { id = employee.EmployeeId })'>Delete</a>
</td>
</tr>
}
</tbody>
</table>
Views/Employee/Create.cshtml
@model YourNamespace.Models.Employee
<h2>Create Employee</h2>
<form asp-action="Create">
<div>
<label>First Name</label>
<input asp-for="FirstName" />
</div>
<div>
<label>Last Name</label>
<input asp-for="LastName" />
</div>
<div>
<label>Email</label>
<input asp-for="Email" />
</div>
<div>
<label>Role ID</label>
<input asp-for="RoleId" />
</div>
<button type="submit">Create</button>
</form>
Views/Employee/Edit.cshtml
@model YourNamespace.Models.Employee
<h2>Edit Employee</h2>
<form asp-action="Edit">
<input type="hidden" asp-for="EmployeeId" />
<div>
<label>First Name</label>
<input asp-for="FirstName" />
</div>
<div>
<label>Last Name</label>
<input asp-for="LastName" />
</div>
<div>
<label>Email</label>
<input asp-for="Email" />
</div>
<div>
<label>Role ID</label>
<input asp-for="RoleId" />
</div>
<button type="submit">Save</button>
</form>
Attendance Views
Views/Attendance/Index.cshtml
@model IEnumerable<YourNamespace.Models.Attendance>
<h2>Attendance Records</h2>
<a href='@Url.Action("Create")'>Create New Attendance Record</a>
<table>
<thead>
<tr>
<th>Employee ID</th>
<th>Date</th>
<th>Status</th>
<th>Actions</th>
</tr>
</thead>
<tbody>
@foreach (var attendance in Model)
{
<tr>
<td>@attendance.EmployeeId</td>
<td>@attendance.Date.ToShortDateString()</td>
<td>@attendance.Status</td>
<td>
<a href='@Url.Action("Edit", new { id = attendance.AttendanceId })'>Edit</a> |
<a href='@Url.Action("Delete", new { id = attendance.AttendanceId })'>Delete</a>
</td>
</tr>
}
</tbody>
</table>
Views/Attendance/Create.cshtml
@model YourNamespace.Models.Attendance
<h2>Create Attendance Record</h2>
<form asp-action="Create">
<div>
<label>Employee ID</label>
<input asp-for="EmployeeId" />
</div>
<div>
<label>Date</label>
<input asp-for="Date" type="date" />
</div>
<div>
<label>Status</label>
<input asp-for="Status" />
</div>
<button type="submit">Create</button>
</form>
Views/Attendance/Edit.cshtml
@model YourNamespace.Models.Attendance
<h2>Edit Attendance Record</h2>
<form asp-action="Edit">
<input type="hidden" asp-for="AttendanceId" />
<div>
<label>Employee ID</label>
<input asp-for="EmployeeId" />
</div>
<div>
<label>Date</label>
<input asp-for="Date" type="date" />
</div>
<div>
<label>Status</label>
<input asp-for="Status" />
</div>
<button type="submit">Save</button>
</form>
Payroll Views
Views/Payroll/Index.cshtml
@model IEnumerable<YourNamespace.Models.Payroll>
<h2>Payroll Records</h2>
<a href='@Url.Action("Create")'>Create New Payroll Record</a>
<table>
<thead>
<tr>
<th>Employee ID</th>
<th>Salary</th>
<th>Pay Date</th>
<th>Actions</th>
</tr>
</thead>
<tbody>
@foreach (var payroll in Model)
{
<tr>
<td>@payroll.EmployeeId</td>
<td>@payroll.Salary</td>
<td>@payroll.PayDate.ToShortDateString()</td>
<td>
<a href='@Url.Action("Edit", new { id = payroll.PayrollId })'>Edit</a> |
<a href='@Url.Action("Delete", new { id = payroll.PayrollId })'>Delete</a>
</td>
</tr>
}
</tbody>
</table>
Views/Payroll/Create.cshtml
@model YourNamespace.Models.Payroll
<h2>Create Payroll Record</h2>
<form asp-action="Create">
<div>
<label>Employee ID</label>
<input asp-for="EmployeeId" />
</div>
<div>
<label>Salary</label>
<input asp-for="Salary" />
</div>
<div>
<label>Pay Date</label>
<input asp-for="PayDate" type="date" />
</div>
<button type="submit">Create</button>
</form>
Views/Payroll/Edit.cshtml
@model YourNamespace.Models.Payroll
<h2>Edit Payroll Record</h2>
<form asp-action="Edit">
<input type="hidden" asp-for="PayrollId" />
<div>
<label>Employee ID</label>
<input asp-for="EmployeeId" />
</div>
<div>
<label>Salary</label>
<input asp-for="Salary" />
</div>
<div>
<label>Pay Date</label>
<input asp-for="PayDate" type="date" />
</div>
<button type="submit">Save</button>
</form>
Performance Views
Views/Performance/Index.cshtml
@model IEnumerable<YourNamespace.Models.Performance>
<h2>Performance Records</h2>
<a href='@Url.Action("Create")'>Create New Performance Record</a>
<table>
<thead>
<tr>
<th>Employee ID</th>
<th>Score</th>
<th>Review Date</th>
<th>Actions</th>
</tr>
</thead>
<tbody>
@foreach (var performance in Model)
{
<tr>
<td>@performance.EmployeeId</td>
<td>@performance.Score</td>
<td>@performance.ReviewDate.ToShortDateString()</td>
<td>
<a href='@Url.Action("Edit", new { id = performance.PerformanceId })'>Edit</a> |
<a href='@Url.Action("Delete", new { id = performance.PerformanceId })'>Delete</a>
</td>
</tr>
}
</tbody>
</table>
Views/Performance/Create.cshtml
@model YourNamespace.Models.Performance
<h2>Create Performance Record</h2>
<form asp-action="Create">
<div>
<label>Employee ID</label>
<input asp-for="EmployeeId" />
</div>
<div>
<label>Score</label>
<input asp-for="Score" />
</div>
<div>
<label>Review Date</label>
<input asp-for="ReviewDate" type="date" />
</div>
<button type="submit">Create</button>
</form>
Views/Performance/Edit.cshtml
@model YourNamespace.Models.Performance
<h2>Edit Performance Record</h2>
<form asp-action="Edit">
<input type="hidden" asp-for="PerformanceId" />
<div>
<label>Employee ID</label>
<input asp-for="EmployeeId" />
</div>
<div>
<label>Score</label>
<input asp-for="Score" />
</div>
<div>
<label>Review Date</label>
<input asp-for="ReviewDate" type="date" />
</div>
<button type="submit">Save</button>
</form>
Job Posting Views
Views/JobPosting/Index.cshtml
@model IEnumerable<YourNamespace.Models.JobPosting>
<h2>Job Postings</h2>
<a href='@Url.Action("Create")'>Create New Job Posting</a>
<table>
<thead>
<tr>
<th>Title</th>
<th>Description</ th>
<th>Actions</th>
</tr>
</thead>
<tbody>
@foreach (var jobPosting in Model)
{
<tr>
<td>@jobPosting.Title</td>
<td>@jobPosting.Description</td>
<td>
<a href='@Url.Action("Edit", new { id = jobPosting.JobPostingId })'>Edit</a> |
<a href='@Url.Action("Delete", new { id = jobPosting.JobPostingId })'>Delete</a>
</td>
</tr>
}
</tbody>
</table>
Views/JobPosting/Create.cshtml
@model YourNamespace.Models.JobPosting
<h2>Create Job Posting</h2>
<form asp-action="Create">
<div>
<label>Title</label>
<input asp-for="Title" />
</div>
<div>
<label>Description</label>
<textarea asp-for="Description"></textarea>
</div>
<button type="submit">Create</button>
</form>
Views/JobPosting/Edit.cshtml
@model YourNamespace.Models.JobPosting
<h2>Edit Job Posting</h2>
<form asp-action="Edit">
<input type="hidden" asp-for="JobPostingId" />
<div>
<label>Title</label>
<input asp-for="Title" />
</div>
<div>
<label>Description</label>
<textarea asp-for="Description"></textarea>
</div>
<button type="submit">Save</button>
</form>
Training Program Views
Views/TrainingProgram/Index.cshtml
@model IEnumerable<YourNamespace.Models.TrainingProgram>
<h2>Training Programs</h2>
<a href='@Url.Action("Create")'>Create New Training Program</a>
<table>
<thead>
<tr>
<th>Program Name</th>
<th>Description</th>
<th>Actions</th>
</tr>
</thead>
<tbody>
@foreach (var trainingProgram in Model)
{
<tr>
<td>@trainingProgram.ProgramName</td>
<td>@trainingProgram.Description</td>
<td>
<a href='@Url.Action("Edit", new { id = trainingProgram.TrainingProgramId })'>Edit</a> |
<a href='@Url.Action("Delete", new { id = trainingProgram.TrainingProgramId })'>Delete</a>
</td>
</tr>
}
</tbody>
</table>
Views/TrainingProgram/Create.cshtml
@model YourNamespace.Models.TrainingProgram
<h2>Create Training Program</h2>
<form asp-action="Create">
<div>
<label>Program Name</label>
<input asp-for="ProgramName" />
</div>
<div>
<label>Description</label>
<textarea asp-for="Description"></textarea>
</div>
<button type="submit">Create</button>
</form>
Views/TrainingProgram/Edit.cshtml
@model YourNamespace.Models.TrainingProgram
<h2>Edit Training Program</h2>
<form asp-action="Edit">
<input type="hidden" asp-for="TrainingProgramId" />
<div>
<label>Program Name</label>
<input asp-for="ProgramName" />
</div>
<div>
<label>Description</label>
<textarea asp-for="Description"></textarea>
</div>
<button type="submit">Save</button>
</form>
Leave Request Views
Views/LeaveRequest/Index.cshtml
@model IEnumerable<YourNamespace.Models.LeaveRequest>
<h2>Leave Requests</h2>
<a href='@Url.Action("Create")'>Create New Leave Request</a>
<table>
<thead>
<tr>
<th>Employee ID</th>
<th>Start Date</th>
<th>End Date</th>
<th>Status</th>
<th>Actions</th>
</tr>
</thead>
<tbody>
@foreach (var leaveRequest in Model)
{
<tr>
<td>@leaveRequest.EmployeeId</td>
<td>@leaveRequest.StartDate.ToShortDateString()</td>
<td>@leaveRequest.EndDate.ToShortDateString()</td>
<td>@leaveRequest.Status</td>
<td>
<a href='@Url.Action("Edit", new { id = leaveRequest.LeaveRequestId })'>Edit</a> |
<a href='@Url.Action("Delete", new { id = leaveRequest.Le aveRequestId })'>Delete</a>
</td>
</tr>
}
</tbody>
</table>
Views/LeaveRequest/Create.cshtml
@model YourNamespace.Models.LeaveRequest
<h2>Create Leave Request</h2>
<form asp-action="Create">
<div>
<label>Employee ID</label>
<input asp-for="EmployeeId" />
</div>
<div>
<label>Start Date</label>
<input asp-for="StartDate" type="date" />
</div>
<div>
<label>End Date</label>
<input asp-for="EndDate" type="date" />
</div>
<div>
<label>Status</label>
<input asp-for="Status" />
</div>
<button type="submit">Create</button>
</form>
Views/LeaveRequest/Edit.cshtml
@model YourNamespace.Models.LeaveRequest
<h2>Edit Leave Request</h2>
<form asp-action="Edit">
<input type="hidden" asp-for="LeaveRequestId" />
<div>
<label>Employee ID</label>
<input asp-for="EmployeeId" />
</div>
<div>
<label>Start Date</label>
<input asp-for="StartDate" type="date" />
</div>
<div>
<label>End Date</label>
<input asp-for="EndDate" type="date" />
</div>
<div>
<label>Status</label>
<input asp-for="Status" />
</div>
<button type="submit">Save</button>
</form>
Document Views
Views/Document/Index.cshtml
@model IEnumerable<YourNamespace.Models.Document>
<h2>Documents</h2>
<a href='@Url.Action("Create")'>Create New Document</a>
<table>
<thead>
<tr>
<th>Title</th>
<th>Upload Date</th>
<th>Actions</th>
</tr>
</thead>
<tbody>
@foreach (var document in Model)
{
<tr>
<td>@document.Title</td>
<td>@document.UploadDate.ToShortDateString()</td>
<td>
<a href='@Url.Action("Edit", new { id = document.DocumentId })'>Edit</a> |
<a href='@Url.Action("Delete", new { id = document.DocumentId })'>Delete</a>
</td>
</tr>
}
</tbody>
</table>
Views/Document/Create.cshtml
@model YourNamespace.Models.Document
<h2>Create Document</h2>
<form asp-action="Create">
<div>
<label>Title</label>
<input asp-for="Title" />
</div>
<div>
<label>Upload Date</label>
<input asp-for="UploadDate" type="date" />
</div>
<button type="submit">Create</button>
</form>
Views/Document/Edit.cshtml
@model YourNamespace.Models.Document
<h2>Edit Document</h2>
<form asp-action="Edit">
<input type="hidden" asp-for="DocumentId" />
<div>
<label>Title</label>
<input asp-for="Title" />
</div>
<div>
<label>Upload Date</label>
<input asp-for="UploadDate" type="date" />
</div>
<button type="submit">Save</button>
</form>
Policy Views
Views/Policy/Index.cshtml
@model IEnumerable<YourNamespace.Models.Policy>
<h2>Policies</h2>
<a href='@Url.Action("Create")'>Create New Policy</a>
<table>
<thead>
<tr>
<th>Policy Name</th>
<th>Description</th>
<th>Actions</th>
</tr>
</thead>
<tbody>
@foreach (var policy in Model)
{
<tr>
<td>@policy.PolicyName</td>
<td>@policy.Description</td>
<td>
<a href='@Url.Action("Edit", new { id = policy.PolicyId })'>Edit</a> |
<a href='@Url.Action("Delete", new { id = policy.PolicyId })'>Delete</a>
</td>
</tr>
}
</tbody>
</table>
Views/Policy/Create.cshtml
@model YourNamespace.Models.Policy
<h2>Create Policy </h2>
<form asp-action="Create">
<div>
<label>Policy Name</label>
<input asp-for="PolicyName" />
</div>
<div>
<label>Description</label>
<textarea asp-for="Description"></textarea>
</div>
<button type="submit">Create</button>
</form>
Views/Policy/Edit.cshtml
@model YourNamespace.Models.Policy
<h2>Edit Policy</h2>
<form asp-action="Edit">
<input type="hidden" asp-for="PolicyId" />
<div>
<label>Policy Name</label>
<input asp-for="PolicyName" />
</div>
<div>
<label>Description</label>
<textarea asp-for="Description"></textarea>
</div>
<button type="submit">Save</button>
</form>
Announcement Views
Views/Announcement/Index.cshtml
@model IEnumerable<YourNamespace.Models.Announcement>
<h2>Announcements</h2>
<a href='@Url.Action("Create")'>Create New Announcement</a>
<table>
<thead>
<tr>
<th>Title</th>
<th>Content</th>
<th>Actions</th>
</tr>
</thead>
<tbody>
@foreach (var announcement in Model)
{
<tr>
<td>@announcement.Title</td>
<td>@announcement.Content</td>
<td>
<a href='@Url.Action("Edit", new { id = announcement.AnnouncementId })'>Edit</a> |
<a href='@Url.Action("Delete", new { id = announcement.AnnouncementId })'>Delete</a>
</td>
</tr>
}
</tbody>
</table>
Views/Announcement/Create.cshtml
@model YourNamespace.Models.Announcement
<h2>Create Announcement</h2>
<form asp-action="Create">
<div>
<label>Title</label>
<input asp-for="Title" />
</div>
<div>
<label>Content</label>
<textarea asp-for="Content"></textarea>
</div>
<button type="submit">Create</button>
</form>
Views/Announcement/Edit.cshtml
@model YourNamespace.Models.Announcement
<h2>Edit Announcement</h2>
<form asp-action="Edit">
<input type="hidden" asp-for="AnnouncementId" />
<div>
<label>Title</label>
<input asp-for="Title" />
</div>
<div>
<label>Content</label>
<textarea asp-for="Content"></textarea>
</div>
<button type="submit">Save</button>
</form>
With these views, you now have a complete set of Razor views for managing the various entities in your application. Each view corresponds to the actions defined in the controllers, allowing users to create, read, update, and delete records effectively.
Creating a Dashboard Page
To create a dashboard page that displays consolidated data related to your project, we will follow these steps:
Step 1: Create a Dashboard ViewModel
First, we need to create a ViewModel that will hold the consolidated data for the dashboard.
Models/DashboardViewModel.cs
public class DashboardViewModel
{
public int TotalUsers { get; set; }
public int TotalRoles { get; set; }
public int TotalEmployees { get; set; }
public int TotalAttendanceRecords { get; set; }
public int TotalPayrollRecords { get; set; }
public int TotalPerformanceRecords { get; set; }
public int TotalJobPostings { get; set; }
public int TotalTrainingPrograms { get; set; }
public int TotalLeaveRequests { get; set; }
public int TotalDocuments { get; set; }
public int TotalPolicies { get; set; }
public int TotalAnnouncements { get; set; }
}
Step 2: Create a Dashboard Controller
Next, we will create a controller that will fetch the necessary data and pass it to the dashboard view.
Controllers/DashboardController.cs
using Microsoft.AspNetCore.Mvc;
public class DashboardController : Controller
{
private readonly UserRepository _userRepository;
private readonly RoleRepository _roleRepository;
private readonly EmployeeRepository _employeeRepository;
private readonly AttendanceRepository _attendanceRepository;
private readonly PayrollRepository _payrollRepository;
private readonly PerformanceRepository _performanceRepository;
private readonly JobPostingRepository _jobPostingRepository;
private readonly TrainingProgramRepository _trainingProgramRepository;
private readonly LeaveRequestRepository _leaveRequestRepository;
private readonly DocumentRepository _documentRepository;
private readonly PolicyRepository _policyRepository;
private readonly AnnouncementRepository _announcementRepository;
public DashboardController()
{
_userRepository = new UserRepository();
_roleRepository = new RoleRepository();
_employeeRepository = new EmployeeRepository();
_attendanceRepository = new AttendanceRepository();
_payrollRepository = new PayrollRepository();
_performanceRepository = new PerformanceRepository();
_jobPostingRepository = new JobPostingRepository();
_trainingProgramRepository = new TrainingProgramRepository();
_leaveRequestRepository = new LeaveRequestRepository();
_documentRepository = new DocumentRepository();
_policyRepository = new PolicyRepository();
_announcementRepository = new AnnouncementRepository();
}
public IActionResult Index()
{
var model = new DashboardViewModel
{
TotalUsers = _userRepository.GetAllUsers().Count(),
TotalRoles = _roleRepository.GetAllRoles().Count(),
TotalEmployees = _employeeRepository.GetAllEmployees().Count(),
TotalAttendanceRecords = _attendanceRepository.GetAllAttendanceRecords().Count(),
TotalPayrollRecords = _payrollRepository.GetAllPayrolls().Count(),
TotalPerformanceRecords = _performanceRepository.GetAllPerformances().Count(),
TotalJobPostings = _jobPostingRepository.GetAllJobPostings().Count(),
TotalTrainingPrograms = _trainingProgramRepository.GetAllTrainingPrograms().Count(),
TotalLeaveRequests = _leaveRequestRepository.GetAllLeaveRequests().Count(),
TotalDocuments = _documentRepository.GetAllDocuments().Count(),
TotalPolicies = _policyRepository.GetAllPolicies().Count(),
TotalAnnouncements = _announcementRepository.GetAllAnnouncements().Count()
};
return View(model);
}
}
Step 3: Create the Dashboard View
Now, we will create the view that will display the dashboard data.
Views/Dashboard/Index.cshtml
@model YourNamespace.Models.DashboardViewModel
@{
ViewData["Title"] = "Dashboard";
}
<h2>Dashboard</h2>
<div class="dashboard">
<div class="card">
<h3>Total Users</h3>
<p>@Model.TotalUsers</p>
</div>
<div class="card">
<h3>Total Roles</h3>
<p>@Model.TotalRoles</p>
</div>
<div class="card">
<h3>Total Employees</h3>
<p>@Model.TotalEmployees</p>
</div>
<div class="card">
<h3>Total Attendance Records</h3>
<p>@Model.TotalAttendanceRecords</p>
</div>
<div class="card">
<h3>Total Payroll Records</h3>
<p>@Model.TotalPayrollRecords</p>
</div>
<div class="card">
<h3>Total Performance Records</h3 <p>@Model.TotalPerformanceRecords</p>
</div>
<div class="card">
<h3>Total Job Postings</h3>
<p>@Model.TotalJobPostings</p>
</div>
<div class="card">
<h3>Total Training Programs</h3>
<p>@Model.TotalTrainingPrograms</p>
</div>
<div class="card">
<h3>Total Leave Requests</h3>
<p>@Model.TotalLeaveRequests</p>
</div>
<div class="card">
<h3>Total Documents</h3>
<p>@Model.TotalDocuments</p>
</div>
<div class="card">
<h3>Total Policies</h3>
<p>@Model.TotalPolicies</p>
</div>
<div class="card">
<h3>Total Announcements</h3>
<p>@Model.TotalAnnouncements</p>
</div>
</div>
<style>
.dashboard {
display: grid;
grid-template-columns: repeat(auto-fill, minmax(200px, 1fr));
gap: 20px;
}
.card {
border: 1px solid #ccc;
border-radius: 5px;
padding: 20px;
text-align: center;
background-color: #f9f9f9;
}
</style>
AI 
ASP.NET API 
ASP.NET CLI 
ASP.NET Core 
ASP.NET MVC 
ASP.NET Projects With Source Code 
ASP.NET Web Forms 
ASP.NET Web Pages 
AWS 
Angular 10 
Bash 
Bitcoin 
Blockchain 
C Tutorial 
C# 
CPP 
Chat GPT 
Cheat Sheet 
Cyber Security 
Dart 
Django 
Docker 
Ether.js 
Ethereum 
Flask 
Flask Projects With Source Code 
GEN AI 
Git 
Go Language 
Google Cloud Platform 
GraphQL 
Hardhat 
INI 
JSON 
Java 
JavaScript 
Kotlin 
Kubernetes 
LaTeX 
Laravel 
Laravel 10 E-Commerce Project 
Laravel 11 E-Commerce Project 
Laravel 9 E-Commerce Project 
Laravel 9 React Tutorial 
Laravel E-Commerce Project 
Laravel Home Services Project 
Laravel Projects With Source Code 
Laravel Tutorial Advanced 
Laravel Tutorial Beginners 
Livewire 
Markdown 
Matlab 
Metamask 
Microsoft Azure 
MongoDB Tutorial Advanced 
MongoDB Tutorial Beginners 
MySql Tutorial Advanced 
MySql Tutorial Beginners 
NextJS 
Node JS 
Node JS Projects With Source Code 
PHP Projects With Source Code 
PHP Tutorial Advanced 
PHP Tutorial Beginners 
Project Database Schema 
Python 
React JS 
Ruby 
Rust 
SQL Server Queries 
SQL Server Tutorial Advanced 
SQL Server Tutorial Beginners 
Sass 
Solidity 
Spring Boot 
Truffle 
TypeScript 
VueJS 
Web3.js 
WordPress Tutorial Advanced 
WordPress Tutorial Beginners 
YAML