kubsu-sm4-db-lab-win-forms/db_lab/RoomRepository.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Npgsql;

namespace db_lab
{
    public class RoomRepository
    {
        private NpgsqlConnection connection;

        public RoomRepository(string connectionString)
        {
            this.connection = new NpgsqlConnection(connectionString);
        }

        public DataTable ReadRooms(int pageSize, int pageNumber)
        {
            string query = $"SELECT * FROM Rooms ORDER BY id LIMIT {pageSize} OFFSET {(pageNumber - 1) * pageSize}";
            NpgsqlDataAdapter da = new NpgsqlDataAdapter(query, connection);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }

        public void DeleteRoom(int id)
        {
            string query = $"DELETE FROM Rooms WHERE id = {id}";
            using (var cmd = new NpgsqlCommand(query, connection))
            {
                connection.Open();
                cmd.ExecuteNonQuery();
                connection.Close();
            }
        }

        public void AddRoom(string roomType, int roomNumber, int floor, int creatingUserId, int buildingId, string roomName)
        {
            string query = "INSERT INTO Rooms (room_type, room_number, floor, creating_user_id, building_id, room_name) VALUES (@room_type, @room_number, @floor, @creating_user_id, @building_id, @room_name)";
            using (var cmd = new NpgsqlCommand(query, connection))
            {
                cmd.Parameters.AddWithValue("room_type", roomType);
                cmd.Parameters.AddWithValue("room_number", roomNumber);
                cmd.Parameters.AddWithValue("floor", floor);
                cmd.Parameters.AddWithValue("creating_user_id", creatingUserId);
                cmd.Parameters.AddWithValue("building_id", buildingId);
                cmd.Parameters.AddWithValue("room_name", roomName);

                connection.Open();
                cmd.ExecuteNonQuery();
                connection.Close();
            }
        }

        public void UpdateRoom(int id, string roomType, int roomNumber, int floor, int creatingUserId, int buildingId, string roomName)
        {
            string query = "UPDATE Rooms SET room_type = @room_type, room_number = @room_number, floor = @floor, creating_user_id = @creating_user_id, building_id = @building_id, room_name = @room_name WHERE id = @id";
            using (var cmd = new NpgsqlCommand(query, connection))
            {
                cmd.Parameters.AddWithValue("room_type", roomType);
                cmd.Parameters.AddWithValue("room_number", roomNumber);
                cmd.Parameters.AddWithValue("floor", floor);
                cmd.Parameters.AddWithValue("creating_user_id", creatingUserId);
                cmd.Parameters.AddWithValue("building_id", buildingId);
                cmd.Parameters.AddWithValue("room_name", roomName);
                cmd.Parameters.AddWithValue("id", id);

                connection.Open();
                cmd.ExecuteNonQuery();
                connection.Close();
            }
        }

        public DataRow GetRoomById(int id)
        {
            string query = $"SELECT * FROM Rooms WHERE id = {id}";
            NpgsqlDataAdapter da = new NpgsqlDataAdapter(query, connection);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt.Rows.Count > 0 ? dt.Rows[0] : null;
        }
    }
}