//accessing the DB to search for the available room for the selected dates by joining tables and sub query
internal SearchListModel SearchRooms(SearchRoomsModel searchInput)
{
SearchListModel searchListModel = new SearchListModel();
// #1.. Read the value from the appsettings.json and connect to DB
string connstr = configuration.GetConnectionString("HotelLandlystDB");
SqlConnection conn = new SqlConnection(connstr);
conn.Open();
// #2.. Create command and get the hands on the customerID
string query = "SELECT [ro].[roomType], [ro].[price], [ro].[miniBar], [ro].[aircondition], [ro].[petsPosible], [ro].[golfPosible], " +
"[ro].[roomId], [ro].[imageName], [rd].[roomDescription]" +
"FROM [dbo].[Rooms] [ro] " +
"join [dbo].[RoomDescriptions] as [rd] " +
"on [ro].[descriptionId] = [rd].[descriptionId] " +
"where [ro].[roomId] not in(select [roomId] from [Reservations] " +
"where @searchInputArriving between [arriving] and [departing] " +
"and @searchInputDeparting between [arriving] and [departing]) ";
//Applying the searchinputs to parameters
SqlCommand cmd = new SqlCommand(query, conn);
cmd.Parameters.AddWithValue("@searchInputArriving", searchInput.Arriving.ToString("yyyy/MM/dd"));
cmd.Parameters.AddWithValue("@searchInputDeparting", searchInput.Departing.ToString("yyyy/MM/dd"));
// #3.. Query the DB
SqlDataReader reader = cmd.ExecuteReader();
reader.Read();
while (reader.Read())
{
RoomModel room = new RoomModel
{
RoomType = (reader["roomType"].ToString()),
ImageName = (reader["imageName"].ToString()),
Price = (reader["price"].ToString()),
MiniBar = (reader["miniBar"].ToString()),
Aircondition = (reader["aircondition"].ToString()),
PetsPossible = (reader["petsPosible"].ToString()),
GolfPossible = (reader["golfPosible"].ToString()),
RoomDescription = (reader["roomDescription"].ToString())
};
searchListModel.AccessList().Add(room);
}
// #4.. Close the connection
conn.Close();
return(searchListModel);
}