RestaurantApp/DishRepository.cs

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

namespace RestaurantApp.DAL
{
    public class DishRepository
    {
        public List<Dish> GetAll(string search = "", int categoryId = 0, bool? available = null)
        {
            var list = new List<Dish>();
            using (var conn = Database.GetConnection())
            {
                var sql = @"SELECT d.dish_id, d.category_id, dc.name as cat_name,
                                   d.name, d.description, d.price, d.weight_g,
                                   d.cook_time_min, d.is_available
                            FROM dish d
                            JOIN dish_category dc ON dc.category_id = d.category_id
                            WHERE 1=1 ";
                if (!string.IsNullOrWhiteSpace(search))
                    sql += " AND (LOWER(d.name) LIKE LOWER(@search) OR LOWER(d.description) LIKE LOWER(@search))";
                if (categoryId > 0)
                    sql += " AND d.category_id = @cat";
                if (available.HasValue)
                    sql += " AND d.is_available = @avail";
                sql += " ORDER BY dc.name, d.name";

                using (var cmd = new NpgsqlCommand(sql, conn))
                {
                    if (!string.IsNullOrWhiteSpace(search))
                        cmd.Parameters.AddWithValue("search", $"%{search}%");
                    if (categoryId > 0)
                        cmd.Parameters.AddWithValue("cat", categoryId);
                    if (available.HasValue)
                        cmd.Parameters.AddWithValue("avail", available.Value);

                    using (var r = cmd.ExecuteReader())
                    {
                        while (r.Read())
                            list.Add(MapDish(r));
                    }
                }
            }
            return list;
        }

        public Dish GetById(int id)
        {
            using (var conn = Database.GetConnection())
            using (var cmd = new NpgsqlCommand(
                @"SELECT d.dish_id, d.category_id, dc.name, d.name, d.description,
                         d.price, d.weight_g, d.cook_time_min, d.is_available
                  FROM dish d JOIN dish_category dc ON dc.category_id=d.category_id
                  WHERE d.dish_id=@id", conn))
            {
                cmd.Parameters.AddWithValue("id", id);
                using (var r = cmd.ExecuteReader())
                    if (r.Read()) return MapDish(r);
            }
            return null;
        }

        public void Insert(Dish d)
        {
            using (var conn = Database.GetConnection())
            using (var cmd = new NpgsqlCommand(
                @"INSERT INTO dish(category_id, name, description, price, weight_g, cook_time_min, is_available)
          VALUES(@cat, @name, @desc, @price, @w, @ct, @avail)", conn))
            {
                // Çäåñü ÍÅ äîëæíî áûòü ïàðàìåòðà dish_id
                cmd.Parameters.AddWithValue("cat", d.CategoryId);
                cmd.Parameters.AddWithValue("name", d.Name);
                cmd.Parameters.AddWithValue("desc", (object)d.Description ?? DBNull.Value);
                cmd.Parameters.AddWithValue("price", d.Price);
                cmd.Parameters.AddWithValue("w", d.WeightG.HasValue ? (object)d.WeightG.Value : DBNull.Value);
                cmd.Parameters.AddWithValue("ct", d.CookTimeMin.HasValue ? (object)d.CookTimeMin.Value : DBNull.Value);
                cmd.Parameters.AddWithValue("avail", d.IsAvailable);
                cmd.ExecuteNonQuery();
            }
        }

        public void Update(Dish d)
        {
            using (var conn = Database.GetConnection())
            using (var cmd = new NpgsqlCommand(
                @"UPDATE dish SET category_id=@cat,name=@name,description=@desc,price=@price,
                  weight_g=@w,cook_time_min=@ct,is_available=@avail WHERE dish_id=@id", conn))
            {
                cmd.Parameters.AddWithValue("id", d.DishId);
                cmd.Parameters.AddWithValue("cat", d.CategoryId);
                cmd.Parameters.AddWithValue("name", d.Name);
                cmd.Parameters.AddWithValue("desc", (object)d.Description ?? DBNull.Value);
                cmd.Parameters.AddWithValue("price", d.Price);
                cmd.Parameters.AddWithValue("w", d.WeightG.HasValue ? (object)d.WeightG.Value : DBNull.Value);
                cmd.Parameters.AddWithValue("ct", d.CookTimeMin.HasValue ? (object)d.CookTimeMin.Value : DBNull.Value);
                cmd.Parameters.AddWithValue("avail", d.IsAvailable);
                cmd.ExecuteNonQuery();
            }
        }

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

                    if (count > 0)
                    {
                        using (var updateCmd = new NpgsqlCommand(
                            "UPDATE dish SET is_available = false WHERE dish_id = @id", conn))
                        {
                            updateCmd.Parameters.AddWithValue("id", id);
                            updateCmd.ExecuteNonQuery();
                        }
                        MessageBox.Show("Áëþäî èñïîëüçóåòñÿ â çàêàçàõ. Îíî ïîìå÷åíî êàê íåäîñòóïíîå.",
                            "Èíôîðìàöèÿ", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                    else
                    {
                        using (var deleteCmd = new NpgsqlCommand(
                            "DELETE FROM dish WHERE dish_id = @id", conn))
                        {
                            deleteCmd.Parameters.AddWithValue("id", id);
                            deleteCmd.ExecuteNonQuery();
                        }
                    }
                }
            }
        }

        public DataTable GetCategories()
        {
            var dt = new DataTable();
            using (var conn = Database.GetConnection())
            using (var cmd = new NpgsqlCommand("SELECT category_id, name FROM dish_category ORDER BY name", conn))
            using (var da = new NpgsqlDataAdapter(cmd))
                da.Fill(dt);
            return dt;
        }

        private Dish MapDish(NpgsqlDataReader r) => new Dish
        {
            DishId = r.GetInt32(0),
            CategoryId = r.GetInt32(1),
            CategoryName = r.GetString(2),
            Name = r.GetString(3),
            Description = r.IsDBNull(4) ? "" : r.GetString(4),
            Price = r.GetDecimal(5),
            WeightG = r.IsDBNull(6) ? (int?)null : r.GetInt32(6),
            CookTimeMin = r.IsDBNull(7) ? (int?)null : r.GetInt32(7),
            IsAvailable = r.GetBoolean(8)
        };
    }
}