public List<Room> GetRooms()
{
using (var connection = new SqlConnection(this._connectionSting))
{
connection.Open();
using (var command = new SqlCommand())
{
command.Connection = connection;
command.CommandText = SELECT_ROOMS;
using (var reader = command.ExecuteReader())
{
List<Room> rooms = new List<Room>();
while(reader.Read())
{
Room room = new Room();
room.Id = (int)reader["Id"];
room.Number = (int)reader["Number"];
room.Quantity = (int)reader["RoomQuantity"];
room.Reserved = (bool)reader["Reserved"];
rooms.Add(room);
}
return rooms;
}
}
}
}
public List<Room> GetRooms(int hotelId)
{
using (var connection = new SqlConnection(this._connectionString))
{
connection.Open();
using (var command = new SqlCommand())
{
command.Connection = connection;
command.CommandText = "SELECT Id, ClientId, HotelId, Name, ReservationStatus FROM Room"
+ " WHERE HotelId = " + hotelId;
using (var reader = command.ExecuteReader())
{
List<Room> sections = new List<Room>();
while (reader.Read())
{
Room room = new Room();
room.Id = (int)reader["Id"];
room.ClientId = (int)reader["ClientId"];
room.HotelId = (int)reader["HotelId"];
room.Name = (string)reader["Name"];
room.Approved = (bool)reader["ReservationStatus"];
//room.Approved = reader.GetBoolean(reader.GetOrdinal("ReservationStatus")); //invalid cast exeption
sections.Add(room);
}
return sections;
}
}
}
}
public List<Room> GetRooms()
{
using (var connection = new SqlConnection(this._connectionString))
{
connection.Open();
using (var command = new SqlCommand())
{
command.Connection = connection;
command.CommandText = SELECT_ROOM;
using (var reader = command.ExecuteReader())
{
List<Room> sections = new List<Room>();
while (reader.Read())
{
Room room = new Room();
room.Id = (int)reader["Id"];
room.ClientId = (int)reader["ClientId"];
room.HotelId = (int)reader["HotelId"];
room.Name = (string)reader["Name"];
room.Approved = (bool)reader["ReservationStatus"];
sections.Add(room);
}
return sections;
}
}
}
}
public Room GetRowById(int id)
{
using (SqlConnection connection = new SqlConnection(this._connectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand(queryGetAllId, connection))
{
command.Parameters.AddWithValue("id", id);
using (SqlDataReader reader = command.ExecuteReader())
{
Room room = new Room();
if (reader.Read())
{
room = (new Room()
{
Id = (int)reader["id"],
Name = (string)reader["Name"],
Price = (int)reader["Price"]
});
return room;
}
else
{
return null;
}
}
}
}
}
public void InsertRoom(RoomDTO roomDTO)
{
var room = new Room
{
ClientId = roomDTO.ClientId,
HotelId = roomDTO.HotelId,
Name = roomDTO.Name,
Approved = roomDTO.Approved
};
string connectionString = ConfigurationManager.ConnectionStrings["HotelServiceDB"].ConnectionString;
IRoomRepository repository = new RoomRepository(connectionString);
repository.Insert(room);
}
public void Insert(Room room)
{
using (var connection = new SqlConnection(this._connectionString))
{
connection.Open();
string commandText =
"INSERT INTO Room(ClientId, HotelId, Name, ReservationStatus) VALUES ("
+ room.ClientId + "," + room.HotelId + ", '"
+ room.Name + "', '" + room.Approved + "')";
using (var command = new SqlCommand(commandText, connection))
{
command.ExecuteNonQuery();
}
}
}