RestaurantApp/ClientRepository.cs

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

namespace RestaurantApp.DAL
{
    public class ClientRepository
    {
        public List<Client> GetAll(string search = "")
        {
            var list = new List<Client>();
            using (var conn = Database.GetConnection())
            {
                var sql = @"SELECT client_id, first_name, last_name, phone, email,
                                   birth_date, bonus_points, registered_at
                            FROM client WHERE 1=1 ";
                if (!string.IsNullOrWhiteSpace(search))
                    sql += @" AND (LOWER(first_name) LIKE LOWER(@s) OR LOWER(last_name) LIKE LOWER(@s)
                              OR phone LIKE @s OR email LIKE @s)";
                sql += " ORDER BY last_name, first_name";

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

        public Client GetById(int id)
        {
            using (var conn = Database.GetConnection())
            using (var cmd = new NpgsqlCommand(
                "SELECT client_id, first_name, last_name, phone, email, birth_date, bonus_points, registered_at FROM client WHERE client_id=@id", conn))
            {
                cmd.Parameters.AddWithValue("id", id);
                using (var r = cmd.ExecuteReader())
                    if (r.Read()) return MapClient(r);
            }
            return null;
        }

        public void Insert(Client c)
        {
            using (var conn = Database.GetConnection())
            using (var cmd = new NpgsqlCommand(
                @"INSERT INTO client(first_name, last_name, phone, email, birth_date, bonus_points)
                  VALUES(@fn, @ln, @ph, @em, @bd, @bp)", conn))
            {
                cmd.Parameters.AddWithValue("fn", c.FirstName);
                cmd.Parameters.AddWithValue("ln", c.LastName);
                cmd.Parameters.AddWithValue("ph", string.IsNullOrEmpty(c.Phone) ? (object)DBNull.Value : c.Phone);
                cmd.Parameters.AddWithValue("em", string.IsNullOrEmpty(c.Email) ? (object)DBNull.Value : c.Email);
                cmd.Parameters.AddWithValue("bd", c.BirthDate.HasValue ? (object)c.BirthDate.Value : DBNull.Value);
                cmd.Parameters.AddWithValue("bp", c.BonusPoints);
                cmd.ExecuteNonQuery();
            }
        }

        public void Update(Client c)
        {
            using (var conn = Database.GetConnection())
            using (var cmd = new NpgsqlCommand(
                @"UPDATE client SET first_name=@fn, last_name=@ln, phone=@ph, email=@em,
                  birth_date=@bd, bonus_points=@bp WHERE client_id=@id", conn))
            {
                cmd.Parameters.AddWithValue("id", c.ClientId);
                cmd.Parameters.AddWithValue("fn", c.FirstName);
                cmd.Parameters.AddWithValue("ln", c.LastName);
                cmd.Parameters.AddWithValue("ph", string.IsNullOrEmpty(c.Phone) ? (object)DBNull.Value : c.Phone);
                cmd.Parameters.AddWithValue("em", string.IsNullOrEmpty(c.Email) ? (object)DBNull.Value : c.Email);
                cmd.Parameters.AddWithValue("bd", c.BirthDate.HasValue ? (object)c.BirthDate.Value : DBNull.Value);
                cmd.Parameters.AddWithValue("bp", c.BonusPoints);
                cmd.ExecuteNonQuery();
            }
        }

        public void Delete(int id)
        {
            using (var conn = Database.GetConnection())
            {
                using (var checkCmd = new NpgsqlCommand(
                    "SELECT COUNT(*) FROM reservation WHERE client_id = @id", conn))
                {
                    checkCmd.Parameters.AddWithValue("id", id);
                    long count = (long)checkCmd.ExecuteScalar();

                    if (count > 0)
                    {
                        throw new Exception("Íåâîçìîæíî óäàëèòü êëèåíòà: ó íåãî åñòü áðîíèðîâàíèÿ");
                    }
                    else
                    {
                        using (var cmd = new NpgsqlCommand("DELETE FROM client WHERE client_id = @id", conn))
                        {
                            cmd.Parameters.AddWithValue("id", id);
                            cmd.ExecuteNonQuery();
                        }
                    }
                }
            }
        }

        private Client MapClient(NpgsqlDataReader r) => new Client
        {
            ClientId = r.GetInt32(0),
            FirstName = r.GetString(1),
            LastName = r.GetString(2),
            Phone = r.IsDBNull(3) ? "" : r.GetString(3),
            Email = r.IsDBNull(4) ? "" : r.GetString(4),
            BirthDate = r.IsDBNull(5) ? (DateTime?)null : r.GetDateTime(5),
            BonusPoints = r.GetInt32(6),
            RegisteredAt = r.GetDateTime(7)
        };
    }
}