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

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

namespace db_lab
{
    public class DepartmentRepository
    {
        private NpgsqlConnection connection;

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

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

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

        public void AddDepartment(int code, string name, int floor, int creatingUserId, int buildingId)
        {
            string query = "INSERT INTO Department (code, name, floor, creating_user_id, building_id) VALUES (@code, @name, @floor, @creating_user_id, @building_id)";
            using (var cmd = new NpgsqlCommand(query, connection))
            {
                cmd.Parameters.AddWithValue("code", code);
                cmd.Parameters.AddWithValue("name", name);
                cmd.Parameters.AddWithValue("floor", floor);
                cmd.Parameters.AddWithValue("creating_user_id", creatingUserId);
                cmd.Parameters.AddWithValue("building_id", buildingId);

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

        public void UpdateDepartment(int code, string name, int floor, int creatingUserId, int buildingId)
        {
            string query = "UPDATE Department SET name = @name, floor = @floor, creating_user_id = @creating_user_id, building_id = @building_id WHERE code = @code";
            using (var cmd = new NpgsqlCommand(query, connection))
            {
                cmd.Parameters.AddWithValue("code", code);
                cmd.Parameters.AddWithValue("name", name);
                cmd.Parameters.AddWithValue("floor", floor);
                cmd.Parameters.AddWithValue("creating_user_id", creatingUserId);
                cmd.Parameters.AddWithValue("building_id", buildingId);

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

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