RestaurantApp/EmployeeRepository.cs

using System;
using System.Collections.Generic;
using System.Data;
using Npgsql;
using RestaurantApp.Models;

namespace RestaurantApp.DAL
{
    public class EmployeeRepository
    {
        public List<Employee> GetAll(string search = "", bool? active = null)
        {
            var list = new List<Employee>();
            using (var conn = Database.GetConnection())
            {
                var sql = @"SELECT e.employee_id,e.position_id,p.title,
                                   e.first_name,e.last_name,e.middle_name,
                                   e.phone,e.email,e.hire_date,e.salary,e.is_active
                            FROM employee e JOIN position p ON p.position_id=e.position_id
                            WHERE 1=1 ";
                if (!string.IsNullOrWhiteSpace(search))
                    sql += " AND (LOWER(e.last_name) LIKE LOWER(@s) OR LOWER(e.first_name) LIKE LOWER(@s) OR e.phone LIKE @s)";
                if (active.HasValue)
                    sql += " AND e.is_active=@a";
                sql += " ORDER BY e.last_name, e.first_name";

                using (var cmd = new NpgsqlCommand(sql, conn))
                {
                    if (!string.IsNullOrWhiteSpace(search))
                        cmd.Parameters.AddWithValue("s", $"%{search}%");
                    if (active.HasValue)
                        cmd.Parameters.AddWithValue("a", active.Value);
                    using (var r = cmd.ExecuteReader())
                        while (r.Read()) list.Add(MapEmployee(r));
                }
            }
            return list;
        }

        public void Insert(Employee e)
        {
            using (var conn = Database.GetConnection())
            using (var cmd = new NpgsqlCommand(
                @"INSERT INTO employee(position_id,first_name,last_name,middle_name,phone,email,hire_date,salary,is_active)
                  VALUES(@p,@fn,@ln,@mn,@ph,@em,@hd,@sal,@act)", conn))
            {
                SetParams(cmd, e);
                cmd.ExecuteNonQuery();
            }
        }

        public void Update(Employee e)
        {
            using (var conn = Database.GetConnection())
            using (var cmd = new NpgsqlCommand(
                @"UPDATE employee SET position_id=@p,first_name=@fn,last_name=@ln,middle_name=@mn,
                  phone=@ph,email=@em,hire_date=@hd,salary=@sal,is_active=@act
                  WHERE employee_id=@id", conn))
            {
                cmd.Parameters.AddWithValue("id", e.EmployeeId);
                SetParams(cmd, e);
                cmd.ExecuteNonQuery();
            }
        }

        public void Delete(int id)
        {
            using (var conn = Database.GetConnection())
            using (var cmd = new NpgsqlCommand("UPDATE employee SET is_active=false WHERE employee_id=@id", conn))
            {
                cmd.Parameters.AddWithValue("id", id);
                cmd.ExecuteNonQuery();
            }
        }

        public DataTable GetPositions()
        {
            var dt = new DataTable();
            using (var conn = Database.GetConnection())
            using (var cmd = new NpgsqlCommand("SELECT position_id, title FROM position ORDER BY title", conn))
            using (var da = new NpgsqlDataAdapter(cmd))
                da.Fill(dt);
            return dt;
        }

        public List<Employee> GetWaiters()
        {
            var list = new List<Employee>();
            using (var conn = Database.GetConnection())
            using (var cmd = new NpgsqlCommand(
                @"SELECT e.employee_id,e.position_id,p.title,e.first_name,e.last_name,e.middle_name,
                         e.phone,e.email,e.hire_date,e.salary,e.is_active
                  FROM employee e JOIN position p ON p.position_id=e.position_id
                  WHERE e.is_active=true ORDER BY e.last_name", conn))
            using (var r = cmd.ExecuteReader())
                while (r.Read()) list.Add(MapEmployee(r));
            return list;
        }

        private void SetParams(NpgsqlCommand cmd, Employee e)
        {
            cmd.Parameters.AddWithValue("p", e.PositionId);
            cmd.Parameters.AddWithValue("fn", e.FirstName);
            cmd.Parameters.AddWithValue("ln", e.LastName);
            cmd.Parameters.AddWithValue("mn", string.IsNullOrEmpty(e.MiddleName) ? (object)DBNull.Value : e.MiddleName);
            cmd.Parameters.AddWithValue("ph", string.IsNullOrEmpty(e.Phone) ? (object)DBNull.Value : e.Phone);
            cmd.Parameters.AddWithValue("em", string.IsNullOrEmpty(e.Email) ? (object)DBNull.Value : e.Email);
            cmd.Parameters.AddWithValue("hd", e.HireDate);
            cmd.Parameters.AddWithValue("sal", e.Salary);
            cmd.Parameters.AddWithValue("act", e.IsActive);
        }

        private Employee MapEmployee(NpgsqlDataReader r) => new Employee
        {
            EmployeeId = r.GetInt32(0),
            PositionId = r.GetInt32(1),
            PositionTitle = r.GetString(2),
            FirstName = r.GetString(3),
            LastName = r.GetString(4),
            MiddleName = r.IsDBNull(5) ? "" : r.GetString(5),
            Phone = r.IsDBNull(6) ? "" : r.GetString(6),
            Email = r.IsDBNull(7) ? "" : r.GetString(7),
            HireDate = r.GetDateTime(8),
            Salary = r.GetDecimal(9),
            IsActive = r.GetBoolean(10)
        };
    }
}