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 EquipmentRepository { private NpgsqlConnection connection; public EquipmentRepository(string connectionString) { this.connection = new NpgsqlConnection(connectionString); } public DataTable ReadEquipments(int pageSize, int pageNumber) { string query = $"SELECT * FROM Equipment 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 DeleteEquipment(int id) { string query = $"DELETE FROM Equipment WHERE id = {id}"; using (var cmd = new NpgsqlCommand(query, connection)) { connection.Open(); cmd.ExecuteNonQuery(); connection.Close(); } } public void AddEquipment(string ipAddress, string sshSettings, string name, int roomId) { string query = "INSERT INTO Equipment (ip_address, ssh_settings, name, room_id) VALUES (@ip_address, @ssh_settings, @name, @room_id)"; using (var cmd = new NpgsqlCommand(query, connection)) { cmd.Parameters.AddWithValue("ip_address", ipAddress); cmd.Parameters.AddWithValue("ssh_settings", sshSettings ?? (object)DBNull.Value); cmd.Parameters.AddWithValue("name", name); cmd.Parameters.AddWithValue("room_id", roomId); connection.Open(); cmd.ExecuteNonQuery(); connection.Close(); } } public void UpdateEquipment(int id, string ipAddress, string sshSettings, string name, int roomId) { string query = "UPDATE Equipment SET ip_address = @ip_address, ssh_settings = @ssh_settings, name = @name, room_id = @room_id WHERE id = @id"; using (var cmd = new NpgsqlCommand(query, connection)) { cmd.Parameters.AddWithValue("ip_address", ipAddress); cmd.Parameters.AddWithValue("ssh_settings", sshSettings ?? (object)DBNull.Value); cmd.Parameters.AddWithValue("name", name); cmd.Parameters.AddWithValue("room_id", roomId); cmd.Parameters.AddWithValue("id", id); connection.Open(); cmd.ExecuteNonQuery(); connection.Close(); } } public DataRow GetEquipmentById(int id) { string query = $"SELECT * FROM Equipment 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; } } }