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

using Npgsql;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace db_lab
{
    public partial class BuildingRepository : Form
    {
        private NpgsqlConnection connection;

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

        public DataTable ReadBuildings(int pageSize, int pageNumber)
        {
            string query = $"SELECT * FROM Building 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 DeleteBuilding(int id)
        {
            string query = $"DELETE FROM Building WHERE id = {id}";
            using (var cmd = new NpgsqlCommand(query, connection))
            {
                connection.Open();
                cmd.ExecuteNonQuery();
                connection.Close();
            }
        }

        public void AddBuilding(int floorCount, string address, int creatingUserId, string buildingType)
        {
            string query = "INSERT INTO Building (floor_count, address, creating_user_id, building_type) VALUES (@floor_count, @address, @creating_user_id, @building_type)";
            using (var cmd = new NpgsqlCommand(query, connection))
            {
                cmd.Parameters.AddWithValue("floor_count", floorCount);
                cmd.Parameters.AddWithValue("address", address);
                cmd.Parameters.AddWithValue("creating_user_id", creatingUserId);
                cmd.Parameters.AddWithValue("building_type", buildingType);

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

        public void UpdateBuilding(int id, int floorCount, string address, int creatingUserId, string buildingType)
        {
            string query = "UPDATE Building SET floor_count = @floor_count, address = @address, creating_user_id = @creating_user_id, building_type = @building_type WHERE id = @id";
            using (var cmd = new NpgsqlCommand(query, connection))
            {
                cmd.Parameters.AddWithValue("floor_count", floorCount);
                cmd.Parameters.AddWithValue("address", address);
                cmd.Parameters.AddWithValue("creating_user_id", creatingUserId);
                cmd.Parameters.AddWithValue("building_type", buildingType);
                cmd.Parameters.AddWithValue("id", id);

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

        public DataRow GetBuildingById(int id)
        {
            string query = $"SELECT * FROM Building 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;
        }
    }
}