107 lines
3.9 KiB
C#
107 lines
3.9 KiB
C#
|
using HotelAbstractions.Logic;
|
|||
|
|
using HotelAbstractions.Models;
|
|||
|
|
using Npgsql;
|
|||
|
|
using System;
|
|||
|
|
using System.Collections.Generic;
|
|||
|
|
using System.Linq;
|
|||
|
|
using System.Text;
|
|||
|
|
using System.Threading.Tasks;
|
|||
|
|
|
|||
|
|
namespace HotelDatabase.Implement
|
|||
|
|
{
|
|||
|
|
public class RoomImplement : IRoomLogic
|
|||
|
|
{
|
|||
|
|
public Room? Create(Room room)
|
|||
|
|
{
|
|||
|
|
using var con = SqlConnection.GetConnection();
|
|||
|
|
con.Open();
|
|||
|
|
using var cmd = new NpgsqlCommand(
|
|||
|
|
"INSERT INTO room (fk_hotel_id, category, count_place, flor, room_number, price)" +
|
|||
|
|
" VALUES (@FKHotel_id, @Category, @Count_place, @Flor, @Room_number, @Price)",
|
|||
|
|
con
|
|||
|
|
);
|
|||
|
|
cmd.Parameters.AddWithValue("@FKHotel_id", room.HotelId == 0 ? DBNull.Value : room.HotelId);
|
|||
|
|
cmd.Parameters.AddWithValue("@Category", room.Category);
|
|||
|
|
cmd.Parameters.AddWithValue("@Count_place", room.CountPlace);
|
|||
|
|
cmd.Parameters.AddWithValue("@Flor", room.Flor);
|
|||
|
|
cmd.Parameters.AddWithValue("@Room_number", room.Number);
|
|||
|
|
cmd.Parameters.AddWithValue("@Price", room.Price);
|
|||
|
|
cmd.ExecuteNonQuery();
|
|||
|
|
return room;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public Room? Delete(int id)
|
|||
|
|
{
|
|||
|
|
var element = Get(id);
|
|||
|
|
using var con = SqlConnection.GetConnection();
|
|||
|
|
con.Open();
|
|||
|
|
using var cmd = new NpgsqlCommand($"DELETE FROM room WHERE room_id = {id}", con);
|
|||
|
|
cmd.ExecuteNonQuery();
|
|||
|
|
return element;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public Room? Get(int id)
|
|||
|
|
{
|
|||
|
|
using var con = SqlConnection.GetConnection();
|
|||
|
|
con.Open();
|
|||
|
|
using var cmd = new NpgsqlCommand($"SELECT * FROM room WHERE room_id = {id}", con);
|
|||
|
|
using var reader = cmd.ExecuteReader();
|
|||
|
|
if (reader.Read())
|
|||
|
|
{
|
|||
|
|
return new Room
|
|||
|
|
{
|
|||
|
|
Id = reader.GetInt32(0),
|
|||
|
|
HotelId = !reader.IsDBNull(1) ? reader.GetInt32(1) : 0,
|
|||
|
|
Category = reader.GetString(2),
|
|||
|
|
CountPlace = reader.GetInt32(3),
|
|||
|
|
Flor = reader.GetInt32(4),
|
|||
|
|
Number = reader.GetString(5),
|
|||
|
|
Price = reader.GetDouble(6),
|
|||
|
|
};
|
|||
|
|
}
|
|||
|
|
return null;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public List<Room> GetAll()
|
|||
|
|
{
|
|||
|
|
var rooms = new List<Room>();
|
|||
|
|
using var con = SqlConnection.GetConnection();
|
|||
|
|
con.Open();
|
|||
|
|
using var cmd = new NpgsqlCommand("SELECT * FROM room order by room_id", con);
|
|||
|
|
using var reader = cmd.ExecuteReader();
|
|||
|
|
while (reader.Read())
|
|||
|
|
{
|
|||
|
|
rooms.Add(new Room
|
|||
|
|
{
|
|||
|
|
Id = reader.GetInt32(0),
|
|||
|
|
HotelId = !reader.IsDBNull(1) ? reader.GetInt32(1) : 0,
|
|||
|
|
Category = reader.GetString(2),
|
|||
|
|
CountPlace = reader.GetInt32(3),
|
|||
|
|
Flor = reader.GetInt32(4),
|
|||
|
|
Number = reader.GetString(5),
|
|||
|
|
Price = reader.GetDouble(6),
|
|||
|
|
});
|
|||
|
|
}
|
|||
|
|
return rooms;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public Room? Update(Room room)
|
|||
|
|
{
|
|||
|
|
using var con = SqlConnection.GetConnection();
|
|||
|
|
con.Open();
|
|||
|
|
using var cmd = new NpgsqlCommand($"UPDATE room SET " +
|
|||
|
|
$"fk_hotel_id = @FKHotel_Id, " +
|
|||
|
|
$"category = '{room.Category}', " +
|
|||
|
|
$"count_place = '{room.CountPlace}', " +
|
|||
|
|
$"flor = '{room.Flor}', " +
|
|||
|
|
$"room_number = '{room.Number}', " +
|
|||
|
|
$"price = '{room.Price}' " +
|
|||
|
|
$"WHERE room_id = {room.Id}", con);
|
|||
|
|
cmd.Parameters.AddWithValue("@FKHotel_Id", room.HotelId == 0 ? DBNull.Value : room.HotelId);
|
|||
|
|
cmd.ExecuteNonQuery();
|
|||
|
|
var element = Get(room.Id);
|
|||
|
|
return element;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|