Пример #1
0
        //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);
        }