Back to: ASP.NET Core Web API Tutorials
Implementing Hotel Search Module of Hotel Booking Application
In this article, I will discuss implementing the Hotel Search Module of our Hotel Booking Application using our ASP.NET Core Web API Application. Please read our previous article discussing How to Implement the Room Amenities Module in our Hotel Booking Application.
Let us implement the Hotel Search Module. Let us create the Stored Procedures, DTOs, a Repository class, and the Hotel Search Controller to provide different options for the user to search for hotels, such as by Dates, Price Range, Hotel Type, View Type, Amenities, etc.
Create Stored Procedures for Hotel Search
We will start by creating the necessary stored procedures in SQL Server to Manage the Hotel Search Functionalities in the database. Please execute the following Script on the HotelDB database that we are working with so far.
-- Search by Availability Dates
-- Searches rooms that are available between specified check-in and check-out dates.
-- Inputs: @CheckInDate - Desired check-in date, @CheckOutDate - Desired check-out date
-- Returns: List of rooms that are available along with their type details
CREATE OR ALTER PROCEDURE spSearchByAvailability
@CheckInDate DATE,
@CheckOutDate DATE
AS
BEGIN
SET NOCOUNT ON; -- Suppresses the 'rows affected' message
SELECT r.RoomID, r.RoomNumber, r.RoomTypeID, r.Price, r.BedType, r.ViewType, r.Status,
rt.TypeName, rt.AccessibilityFeatures, rt.Description
FROM Rooms r
JOIN RoomTypes rt ON r.RoomTypeID = rt.RoomTypeID
LEFT JOIN Reservations res ON r.RoomID = res.RoomID
AND res.Status NOT IN ('Cancelled')
AND (
(res.CheckInDate <= @CheckOutDate AND res.CheckOutDate >= @CheckInDate)
)
WHERE res.RoomID IS NULL AND r.Status NOT IN ('Under Maintenance')
AND r.IsActive = 1;
END
GO
-- Search by Price Range
-- Searches rooms within a specified price range.
-- Inputs: @MinPrice - Minimum room price, @MaxPrice - Maximum room price
-- Returns: List of rooms within the price range along with their type details
CREATE OR ALTER PROCEDURE spSearchByPriceRange
@MinPrice DECIMAL(10,2),
@MaxPrice DECIMAL(10,2)
AS
BEGIN
SET NOCOUNT ON; -- Avoids sending row count information
SELECT r.RoomID, r.RoomNumber, r.Price, r.BedType, r.ViewType, r.Status,
rt.RoomTypeID, rt.TypeName, rt.AccessibilityFeatures, rt.Description
FROM Rooms r
JOIN RoomTypes rt ON r.RoomTypeID = rt.RoomTypeID
WHERE r.Price BETWEEN @MinPrice AND @MaxPrice
AND r.IsActive = 1 AND rt.IsActive = 1
END
GO
-- Search by Room Type
-- Searches rooms based on room type name.
-- Inputs: @RoomTypeName - Name of the room type
-- Returns: List of rooms matching the room type name along with type details
CREATE OR ALTER PROCEDURE spSearchByRoomType
@RoomTypeName NVARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;
SELECT r.RoomID, r.RoomNumber, r.Price, r.BedType, r.ViewType, r.Status,
rt.RoomTypeID, rt.TypeName, rt.AccessibilityFeatures, rt.Description
FROM Rooms r
JOIN RoomTypes rt ON r.RoomTypeID = rt.RoomTypeID
WHERE rt.TypeName = @RoomTypeName
AND r.IsActive = 1
END
GO
-- Search by View Type
-- Searches rooms by specific view type.
-- Inputs: @ViewType - Type of view from the room (e.g., sea, city)
-- Returns: List of rooms with the specified view along with their type details
CREATE OR ALTER PROCEDURE spSearchByViewType
@ViewType NVARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;
SELECT r.RoomID, r.RoomNumber, r.RoomTypeID, r.Price, r.BedType, r.Status, r.ViewType,
rt.TypeName, rt.AccessibilityFeatures, rt.Description
FROM Rooms r
JOIN RoomTypes rt ON r.RoomTypeID = rt.RoomTypeID
WHERE r.ViewType = @ViewType
AND r.IsActive = 1
END
GO
-- Search by Amenities
-- Searches rooms offering a specific amenity.
-- Inputs: @AmenityName - Name of the amenity
-- Returns: List of rooms offering the specified amenity along with their type details
CREATE OR ALTER PROCEDURE spSearchByAmenities
@AmenityName NVARCHAR(100)
AS
BEGIN
SET NOCOUNT ON;
SELECT DISTINCT r.RoomID, r.RoomNumber, r.RoomTypeID, r.Price, r.BedType, r.ViewType, r.Status,
rt.TypeName, rt.AccessibilityFeatures, rt.Description
FROM Rooms r
JOIN RoomTypes rt ON r.RoomTypeID = rt.RoomTypeID
JOIN RoomAmenities ra ON rt.RoomTypeID = ra.RoomTypeID
JOIN Amenities a ON ra.AmenityID = a.AmenityID
WHERE a.Name = @AmenityName
AND r.IsActive = 1
END
GO
-- Search All Rooms by RoomTypeID
-- Searches all rooms based on a specific RoomTypeID.
-- Inputs: @RoomTypeID - The ID of the room type
-- Returns: List of all rooms associated with the specified RoomTypeID along with type details
CREATE OR ALTER PROCEDURE spSearchRoomsByRoomTypeID
@RoomTypeID INT
AS
BEGIN
SET NOCOUNT ON;
SELECT r.RoomID, r.RoomNumber, r.Price, r.BedType, r.ViewType, r.Status,
rt.RoomTypeID, rt.TypeName, rt.AccessibilityFeatures, rt.Description
FROM Rooms r
JOIN RoomTypes rt ON r.RoomTypeID = rt.RoomTypeID
WHERE rt.RoomTypeID = @RoomTypeID
AND r.IsActive = 1
END
GO
-- Stored Procedure to Fetch Room, Room Type, and Amenities Details
-- Retrieves details of a room by its RoomID, including room type and amenities.
-- Inputs: @RoomID - The ID of the room
-- Returns: Details of the room, its room type, and associated amenities
CREATE OR ALTER PROCEDURE spGetRoomDetailsWithAmenitiesByRoomID
@RoomID INT
AS
BEGIN
SET NOCOUNT ON; -- Suppresses the 'rows affected' message
-- First, retrieve the basic details of the room along with its room type information
SELECT
r.RoomID,
r.RoomNumber,
r.Price,
r.BedType,
r.ViewType,
r.Status,
rt.RoomTypeID,
rt.TypeName,
rt.AccessibilityFeatures,
rt.Description
FROM Rooms r
JOIN RoomTypes rt ON r.RoomTypeID = rt.RoomTypeID
WHERE r.RoomID = @RoomID
AND r.IsActive = 1;
-- Next, retrieve the amenities associated with the room type of the specified room
SELECT
a.AmenityID,
a.Name,
a.Description
FROM RoomAmenities ra
JOIN Amenities a ON ra.AmenityID = a.AmenityID
WHERE ra.RoomTypeID IN (SELECT RoomTypeID FROM Rooms WHERE RoomID = @RoomID)
AND a.IsActive = 1;
END
GO
-- Fetch Amenities for a Specific Room
-- Retrieves all amenities associated with a specific room by its RoomID.
-- Inputs: @RoomID - The ID of the room
-- Returns: List of amenities associated with the room type of the specified room
CREATE OR ALTER PROCEDURE spGetRoomAmenitiesByRoomID
@RoomID INT
AS
BEGIN
SET NOCOUNT ON; -- Suppresses the 'rows affected' message
SELECT
a.AmenityID,
a.Name,
a.Description
FROM RoomAmenities ra
JOIN Amenities a ON ra.AmenityID = a.AmenityID
JOIN Rooms r ON ra.RoomTypeID = r.RoomTypeID
WHERE r.RoomID = @RoomID
AND a.IsActive = 1;
END
GO
-- Search by Rating
-- Searches rooms based on a minimum average guest rating.
-- Inputs: @MinRating - Minimum average rating required
-- Searches rooms based on a minimum average guest rating.
-- Inputs: @MinRating - Minimum average rating required
CREATE OR ALTER PROCEDURE spSearchByMinRating
@MinRating FLOAT
AS
BEGIN
SET NOCOUNT ON;
-- A subquery to calculate average ratings for each room via their reservations
WITH RatedRooms AS (
SELECT
res.RoomID,
AVG(CAST(fb.Rating AS FLOAT)) AS AvgRating -- Calculate average rating per room
FROM Feedbacks fb
JOIN Reservations res ON fb.ReservationID = res.ReservationID
GROUP BY res.RoomID
HAVING AVG(CAST(fb.Rating AS FLOAT)) >= @MinRating -- Filter rooms by minimum rating
)
SELECT
r.RoomID,
r.RoomNumber,
r.Price,
r.BedType,
r.ViewType,
r.Status,
rt.RoomTypeID,
rt.TypeName,
rt.AccessibilityFeatures,
rt.Description
FROM Rooms r
JOIN RoomTypes rt ON r.RoomTypeID = rt.RoomTypeID
JOIN RatedRooms rr ON r.RoomID = rr.RoomID -- Join with the subquery of rated rooms
WHERE r.IsActive = 1;
END
GO
-- Custom Combination Searches with Dynamic SQL
-- Searches for rooms based on a combination of criteria including price range, room type, and amenities.
-- Inputs:
-- @MinPrice DECIMAL(10,2) = NULL: Minimum price filter (optional)
-- @MaxPrice DECIMAL(10,2) = NULL: Maximum price filter (optional)
-- @RoomTypeName NVARCHAR(50) = NULL: Room type Name filter (optional)
-- @AmenityName NVARCHAR(100) = NULL: Amenity Name filter (optional)
-- @@ViewType NVARCHAR(50) = NULL: View Type filter (optional)
-- Returns: List of rooms matching the combination of specified criteria along with their type details
-- Note: Based on the Requirements you can use AND or OR Conditions
CREATE OR ALTER PROCEDURE spSearchCustomCombination
@MinPrice DECIMAL(10,2) = NULL,
@MaxPrice DECIMAL(10,2) = NULL,
@RoomTypeName NVARCHAR(50) = NULL,
@AmenityName NVARCHAR(100) = NULL,
@ViewType NVARCHAR(50) = NULL
AS
BEGIN
SET NOCOUNT ON; -- Suppresses the 'rows affected' message
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = 'SELECT DISTINCT r.RoomID, r.RoomNumber, r.Price, r.BedType, r.ViewType, r.Status,
rt.RoomTypeID, rt.TypeName, rt.AccessibilityFeatures, rt.Description
FROM Rooms r
JOIN RoomTypes rt ON r.RoomTypeID = rt.RoomTypeID
LEFT JOIN RoomAmenities ra ON rt.RoomTypeID = ra.RoomTypeID
LEFT JOIN Amenities a ON ra.AmenityID = a.AmenityID
WHERE r.IsActive = 1 '
DECLARE @Conditions NVARCHAR(MAX) = ''
-- Dynamic conditions based on input parameters
IF @MinPrice IS NOT NULL
SET @Conditions = @Conditions + 'AND r.Price >= @MinPrice '
IF @MaxPrice IS NOT NULL
SET @Conditions = @Conditions + 'AND r.Price <= @MaxPrice '
IF @RoomTypeName IS NOT NULL
SET @Conditions = @Conditions + 'AND rt.TypeName LIKE ''%' + @RoomTypeName + '%'' '
IF @AmenityName IS NOT NULL
SET @Conditions = @Conditions + 'AND a.Name LIKE ''%' + @AmenityName + '%'' '
IF @ViewType IS NOT NULL
SET @Conditions = @Conditions + 'AND r.ViewType = @ViewType '
-- Remove the first OR if any conditions were added
IF LEN(@Conditions) > 0
SET @SQL = @SQL + ' AND (' + STUFF(@Conditions, 1, 3, '') + ')'
-- Execute the dynamic SQL
EXEC sp_executesql @SQL,
N'@MinPrice DECIMAL(10,2), @MaxPrice DECIMAL(10,2), @RoomTypeName NVARCHAR(50), @AmenityName NVARCHAR(100), @ViewType NVARCHAR(50)',
@MinPrice, @MaxPrice, @RoomTypeName, @AmenityName, @ViewType
END
GO
Explanation of Stored Procedures:
Here are the objectives of each stored procedure we created above, designed to effectively manage the Hotel Search Functionalities.
- spSearchByAvailability: This stored procedure finds all rooms available during a specified date range and ensures that there are no non-canceled overlapping reservations.
- spSearchByPriceRange: Retrieves rooms that are priced within a given range, allowing for budget-based room searches.
- spSearchByRoomType: Filters rooms based on their type, facilitating searches for guests who have preferences for specific room configurations or features.
- spSearchByViewType: Lists rooms that offer specific views (e.g., sea or city), catering to guests who desire rooms with particular scenic views.
- spSearchByAmenities: Identifies rooms that provide certain amenities, helping guests choose rooms based on specific needs or desires, such as gym access or spa facilities.
- spSearchRoomsByRoomTypeID: This stored procedure searches all rooms associated with a particular room type ID, which is useful for detailed queries about room categorizations.
- spGetRoomDetailsWithAmenitiesByRoomID: This stored procedure provides detailed information about a specific room, including its type and associated amenities, offering a comprehensive overview for guest decision-making.
- spGetRoomAmenitiesByRoomID: Fetches a list of all amenities available in a particular room, aiding guests in understanding what features are included in their potential accommodation.
- spSearchByMinRating: This stored procedure filtering selects rooms that meet or exceed a specified minimum guest rating, ensuring guests can select rooms that uphold a certain standard of quality.
- spSearchCustomCombination: This stored procedure provides a flexible search based on various optional criteria, including price range, room type, amenities, and view type.
Creating Custom Validation Attributes:
In our Hotel Search, we are going to provide different search criteria based on the Date Range and Price Range. So, let us create some custom Data Annotation Attributes to validate the data when received from the Clients. Create a folder in the Project Root Directory named CustomValidator, where we will create all our Custom Data Annotation Attributes.
FutureDateValidationAttribute
Create a class file named FutureDateValidationAttribute.cs within the CustomValidator folder, and copy and paste the following code. This Custom Data Annotation Attribute will be used to check the future date, i.e., the check-in and check-out dates must be on a future date.
using System.ComponentModel.DataAnnotations;
namespace HotelBookingAPI.CustomValidator
{
public class FutureDateValidationAttribute : ValidationAttribute
{
public FutureDateValidationAttribute()
{
ErrorMessage = "The date must be in the future.";
}
protected override ValidationResult IsValid(object value, ValidationContext validationContext)
{
var date = value as DateTime?;
// Check if the date is in the future
if (!date.HasValue || date.Value.Date <= DateTime.Today)
{
return new ValidationResult(ErrorMessage);
}
return ValidationResult.Success;
}
}
}
DateGreaterThanValidationAttribute
Create a class file named DateGreaterThanValidationAttribute.cs within the CustomValidator folder and then copy and paste the following code. This Custom Data Annotation Attribute will be used to check whether the Check-out Date is greater than the Check-in Date.
using System.ComponentModel.DataAnnotations;
namespace HotelBookingAPI.CustomValidator
{
public class DateGreaterThanValidationAttribute : ValidationAttribute
{
private readonly string _comparisonPropertyName;
public DateGreaterThanValidationAttribute(string comparisonPropertyName)
{
_comparisonPropertyName = comparisonPropertyName;
ErrorMessage = "The date must be greater than the comparison date.";
}
protected override ValidationResult IsValid(object value, ValidationContext validationContext)
{
var currentDate = value as DateTime?;
var comparisonProperty = validationContext.ObjectType.GetProperty(_comparisonPropertyName);
var comparisonDate = comparisonProperty?.GetValue(validationContext.ObjectInstance, null) as DateTime?;
// Check if the current date is greater than the comparison date
if (currentDate.HasValue && comparisonDate.HasValue && currentDate.Value <= comparisonDate.Value)
{
return new ValidationResult(ErrorMessage);
}
return ValidationResult.Success;
}
}
}
PriceRangeValidationAttribute
Create a class file named FutureDateValidationAttribute.cs within the CustomValidator folder, and copy and paste the following code. This Custom Data Annotation Attribute will be used to check the price range, i.e., the Max Price should be greater than or equal to the Min Price.
using System.ComponentModel.DataAnnotations;
namespace HotelBookingAPI.CustomValidator
{
public class PriceRangeValidationAttribute : ValidationAttribute
{
private readonly string _minPricePropertyName;
private readonly string _maxPricePropertyName;
public PriceRangeValidationAttribute(string minPricePropertyName, string maxPricePropertyName)
{
_minPricePropertyName = minPricePropertyName;
_maxPricePropertyName = maxPricePropertyName;
ErrorMessage = "The maximum price must be greater than or equal to the minimum price.";
}
protected override ValidationResult IsValid(object value, ValidationContext validationContext)
{
var minPriceProperty = validationContext.ObjectType.GetProperty(_minPricePropertyName);
var maxPriceProperty = validationContext.ObjectType.GetProperty(_maxPricePropertyName);
if (minPriceProperty == null || maxPriceProperty == null)
{
throw new ArgumentException("Property not found.");
}
var minPrice = minPriceProperty.GetValue(validationContext.ObjectInstance, null) as decimal?;
var maxPrice = maxPriceProperty.GetValue(validationContext.ObjectInstance, null) as decimal?;
if (!minPrice.HasValue || !maxPrice.HasValue)
{
return ValidationResult.Success; // Consider how to handle nulls, potentially invalid state
}
if (minPrice.Value > maxPrice.Value)
{
return new ValidationResult(ErrorMessage);
}
return ValidationResult.Success;
}
}
}
Creating Hotel Search DTOs:
Next, we need to create the DTOs required to perform Hotel Search operations. Please create a folder called HotelSearchDTOs inside the DTOs folder, where we will add all the DTOs related to Hotel Search Functionalities.
AvailabilityHotelSearchRequestDTO
Create a class file named AvailabilityHotelSearchRequestDTO.cs within the HotelSearchDTOs folder and then copy and paste the following code. This DTO will contain the information we need whenever we search the hotels based on the check-in and check-out dates.
using HotelBookingAPI.CustomValidator;
using System.ComponentModel.DataAnnotations;
namespace HotelBookingAPI.DTOs.HotelSearchDTOs
{
public class AvailabilityHotelSearchRequestDTO
{
[Required]
[DataType(DataType.Date)]
[FutureDateValidation(ErrorMessage = "Check-in date must be in the future.")]
public DateTime CheckInDate { get; set; }
[Required]
[DataType(DataType.Date)]
[FutureDateValidation(ErrorMessage = "Check-out date must be in the future.")]
[DateGreaterThanValidation("CheckInDate", ErrorMessage = "Check-out date must be after check-in date.")]
public DateTime CheckOutDate { get; set; }
}
}
PriceRangeHotelSearchRequestDTO
Create a class file named PriceRangeHotelSearchRequestDTO.cs within the HotelSearchDTOs folder, and then copy and paste the following code. This DTO will contain the information whenever we need to search the Hotels based on Price Range, i.e., Minimum and Maximum Price.
using HotelBookingAPI.CustomValidator;
using System.ComponentModel.DataAnnotations;
namespace HotelBookingAPI.DTOs.HotelSearchDTOs
{
public class PriceRangeHotelSearchRequestDTO
{
[Required]
[Range(0, double.MaxValue, ErrorMessage = "Minimum price must be greater than or equal to 0.")]
public decimal MinPrice { get; set; }
[Required]
[PriceRangeValidation("MinPrice", "MaxPrice")]
public decimal MaxPrice { get; set; }
}
}
CustomHotelSearchCriteriaDTO
Create a class file named CustomHotelSearchCriteriaDTO.cs within the HotelSearchDTOs folder, then copy and paste the following code. This DTO will contain the information whenever we need to search the Hotels based on custom search criteria such as Price Range, Hotel Type, and Amenity Type.
using HotelBookingAPI.CustomValidator;
using System.ComponentModel.DataAnnotations;
namespace HotelBookingAPI.DTOs.HotelSearchDTOs
{
public class CustomHotelSearchCriteriaDTO
{
[Range(0, double.MaxValue, ErrorMessage = "Minimum price must be greater than or equal to 0.")]
public decimal? MinPrice { get; set; }
[PriceRangeValidation("MinPrice", "MaxPrice")]
public decimal? MaxPrice { get; set; }
[StringLength(50, ErrorMessage = "Room type name length cannot exceed 50 characters.")]
public string? RoomTypeName { get; set; }
[StringLength(100, ErrorMessage = "Amenity name length cannot exceed 100 characters.")]
public string? AmenityName { get; set; }
[StringLength(50, ErrorMessage = "View type name length cannot exceed 50 characters.")]
public string? ViewType { get; set; }
}
}
RoomTypeSearchDTO
Create a class file named RoomTypeSearchDTO.cs within the HotelSearchDTOs folder, and then copy and paste the following code. This DTO will contain the Room Type information, which is basically used within other DTOs to return the Room Type information.
{
public class RoomTypeSearchDTO
{
public int RoomTypeID { get; set; }
public string TypeName { get; set; }
public string AccessibilityFeatures { get; set; }
public string Description { get; set; }
}
}
RoomSearchDTO
Create a class file named RoomSearchDTO.cs within the HotelSearchDTOs folder, and then copy and paste the following code. This DTO will contain all the room information and the corresponding room type information. In most of the cases we will use this DTO to return the search result.
namespace HotelBookingAPI.DTOs.HotelSearchDTOs
{
public class RoomSearchDTO
{
public int RoomID { get; set; }
public string RoomNumber { get; set; }
public decimal Price { get; set; }
public string BedType { get; set; }
public string ViewType { get; set; }
public string Status { get; set; }
public RoomTypeSearchDTO RoomType { get; set; }
}
}
AmenitySearchDTO
Create a class file named AmenitySearchDTO.cs within the HotelSearchDTOs folder, and then copy and paste the following code. This DTO allows us to return all the Amenities based on a Room ID.
namespace HotelBookingAPI.DTOs.HotelSearchDTOs
{
public class AmenitySearchDTO
{
public int AmenityID { get; set; }
public string Name { get; set; }
public string Description { get; set; }
}
}
RoomDetailsWithAmenitiesSearchDTO
Create a class file named RoomDetailsWithAmenitiesSearchDTO.cs within the HotelSearchDTOs folder, and then copy and paste the following code. We will use the following DTO whenever we fetch a particular Room’s Details. This will include the Room Information, Room Type information, and available Amenities.
namespace HotelBookingAPI.DTOs.HotelSearchDTOs
{
public class RoomDetailsWithAmenitiesSearchDTO
{
public RoomSearchDTO Room { get; set; }
public List<AmenitySearchDTO> Amenities { get; set; }
}
}
Creating Hotel Search Repository:
Next, we need to create the Hotel Search Repository class to implement the business and data access logic. This class will consume the Hotel Search Related Stored Procedures and DTOs for the operations that we have created so far. So, create a class file named HotelSearchRepository.cs within the Repository folder and copy and paste the following code.
using System.Data;
using HotelBookingAPI.Connection;
using HotelBookingAPI.DTOs.HotelSearchDTOs;
using HotelBookingAPI.Extensions;
using Microsoft.Data.SqlClient;
namespace HotelBookingAPI.Repository
{
public class HotelSearchRepository
{
private readonly SqlConnectionFactory _connectionFactory;
public HotelSearchRepository(SqlConnectionFactory connectionFactory)
{
_connectionFactory = connectionFactory;
}
// Search by Availability Dates
// spSearchByAvailability
public async Task<List<RoomSearchDTO>> SearchByAvailabilityAsync(DateTime checkInDate, DateTime checkOutDate)
{
var rooms = new List<RoomSearchDTO>();
using (var connection = _connectionFactory.CreateConnection())
{
using (var command = new SqlCommand("spSearchByAvailability", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@CheckInDate", checkInDate));
command.Parameters.Add(new SqlParameter("@CheckOutDate", checkOutDate));
connection.Open();
using (var reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
rooms.Add(CreateRoomSearchDTO(reader));
}
}
}
}
return rooms;
}
// Search by Price Range
// spSearchByPriceRange
public async Task<List<RoomSearchDTO>> SearchByPriceRangeAsync(decimal minPrice, decimal maxPrice)
{
var rooms = new List<RoomSearchDTO>();
using (var connection = _connectionFactory.CreateConnection())
{
using (var command = new SqlCommand("spSearchByPriceRange", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@MinPrice", minPrice));
command.Parameters.Add(new SqlParameter("@MaxPrice", maxPrice));
connection.Open();
using (var reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
rooms.Add(CreateRoomSearchDTO(reader));
}
}
}
}
return rooms;
}
// Search by Room Type
// spSearchByRoomType
public async Task<List<RoomSearchDTO>> SearchByRoomTypeAsync(string roomTypeName)
{
var rooms = new List<RoomSearchDTO>();
using (var connection = _connectionFactory.CreateConnection())
{
using (var command = new SqlCommand("spSearchByRoomType", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@RoomTypeName", roomTypeName));
connection.Open();
using (var reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
rooms.Add(CreateRoomSearchDTO(reader));
}
}
}
}
return rooms;
}
// Search by View Type
// spSearchByViewType
public async Task<List<RoomSearchDTO>> SearchByViewTypeAsync(string viewType)
{
var rooms = new List<RoomSearchDTO>();
using (var connection = _connectionFactory.CreateConnection())
{
using (var command = new SqlCommand("spSearchByViewType", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@ViewType", viewType));
connection.Open();
using (var reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
rooms.Add(CreateRoomSearchDTO(reader));
}
}
}
}
return rooms;
}
// Search by Amenities
// spSearchByAmenities
public async Task<List<RoomSearchDTO>> SearchByAmenitiesAsync(string amenityName)
{
var rooms = new List<RoomSearchDTO>();
using (var connection = _connectionFactory.CreateConnection())
{
using (var command = new SqlCommand("spSearchByAmenities", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@AmenityName", amenityName));
connection.Open();
using (var reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
rooms.Add(CreateRoomSearchDTO(reader));
}
}
}
}
return rooms;
}
// Search All Rooms by RoomTypeID
// spSearchRoomsByRoomTypeID
public async Task<List<RoomSearchDTO>> SearchRoomsByRoomTypeIDAsync(int roomTypeID)
{
var rooms = new List<RoomSearchDTO>();
using (var connection = _connectionFactory.CreateConnection())
{
using (var command = new SqlCommand("spSearchRoomsByRoomTypeID", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@RoomTypeID", roomTypeID));
connection.Open();
using (var reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
rooms.Add(CreateRoomSearchDTO(reader));
}
}
}
}
return rooms;
}
// Fetch Room Details with Amenities by RoomID
// spGetRoomDetailsWithAmenitiesByRoomID
public async Task<RoomDetailsWithAmenitiesSearchDTO> GetRoomDetailsWithAmenitiesByRoomIDAsync(int roomID)
{
RoomDetailsWithAmenitiesSearchDTO roomDetails = new RoomDetailsWithAmenitiesSearchDTO();
//List<AmenitySearchDTO> amenities = new List<AmenitySearchDTO>();
using (var connection = _connectionFactory.CreateConnection())
{
using (var command = new SqlCommand("spGetRoomDetailsWithAmenitiesByRoomID", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@RoomID", roomID));
connection.Open();
using (var reader = await command.ExecuteReaderAsync())
{
if (await reader.ReadAsync())
{
roomDetails.Room = CreateRoomSearchDTO(reader);
roomDetails.Amenities = new List<AmenitySearchDTO>();
if (await reader.NextResultAsync())
{
while (await reader.ReadAsync())
{
roomDetails.Amenities.Add(new AmenitySearchDTO
{
AmenityID = reader.GetInt32(reader.GetOrdinal("AmenityID")),
Name = reader.GetString(reader.GetOrdinal("Name")),
Description = reader.GetString(reader.GetOrdinal("Description"))
});
}
}
}
}
}
}
return roomDetails;
}
// Method to retrieve all amenities for a specific room by its RoomID
// spGetRoomAmenitiesByRoomID
public async Task<List<AmenitySearchDTO>> GetRoomAmenitiesByRoomIDAsync(int roomID)
{
var amenities = new List<AmenitySearchDTO>();
using (var connection = _connectionFactory.CreateConnection())
{
using (var command = new SqlCommand("spGetRoomAmenitiesByRoomID", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@RoomID", roomID));
connection.Open();
using (var reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
amenities.Add(new AmenitySearchDTO
{
AmenityID = reader.GetInt32(reader.GetOrdinal("AmenityID")),
Name = reader.GetString(reader.GetOrdinal("Name")),
Description = reader.GetValueByColumn<string>("Description")
});
}
}
}
}
return amenities;
}
// Search by Minimum Rating
// spSearchByMinRating
public async Task<List<RoomSearchDTO>> SearchByMinRatingAsync(float minRating)
{
var rooms = new List<RoomSearchDTO>();
using (var connection = _connectionFactory.CreateConnection())
{
using (var command = new SqlCommand("spSearchByMinRating", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@MinRating", minRating));
connection.Open();
using (var reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
while (await reader.ReadAsync())
{
rooms.Add(CreateRoomSearchDTO(reader));
}
}
}
}
}
return rooms;
}
// Search Custom Combination
public async Task<List<RoomSearchDTO>> SearchCustomCombinationAsync(CustomHotelSearchCriteriaDTO criteria)
{
var rooms = new List<RoomSearchDTO>();
using (var connection = _connectionFactory.CreateConnection())
{
var command = new SqlCommand("spSearchCustomCombination", connection)
{
CommandType = CommandType.StoredProcedure
};
command.Parameters.AddWithValue("@MinPrice", (object)criteria.MinPrice ?? DBNull.Value);
command.Parameters.AddWithValue("@MaxPrice", (object)criteria.MaxPrice ?? DBNull.Value);
command.Parameters.AddWithValue("@RoomTypeName", string.IsNullOrEmpty(criteria.RoomTypeName) ? DBNull.Value : criteria.RoomTypeName);
command.Parameters.AddWithValue("@AmenityName", string.IsNullOrEmpty(criteria.AmenityName) ? DBNull.Value : criteria.AmenityName);
command.Parameters.AddWithValue("@ViewType", string.IsNullOrEmpty(criteria.ViewType) ? DBNull.Value : criteria.ViewType);
connection.Open();
using (var reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
rooms.Add(CreateRoomSearchDTO(reader));
}
}
}
return rooms;
}
private RoomSearchDTO CreateRoomSearchDTO(SqlDataReader reader)
{
return new RoomSearchDTO
{
RoomID = reader.GetInt32(reader.GetOrdinal("RoomID")),
RoomNumber = reader.GetString(reader.GetOrdinal("RoomNumber")),
Price = reader.GetDecimal(reader.GetOrdinal("Price")),
BedType = reader.GetString(reader.GetOrdinal("BedType")),
ViewType = reader.GetString(reader.GetOrdinal("ViewType")),
Status = reader.GetString(reader.GetOrdinal("Status")),
RoomType = new RoomTypeSearchDTO
{
RoomTypeID = reader.GetInt32(reader.GetOrdinal("RoomTypeID")),
TypeName = reader.GetString(reader.GetOrdinal("TypeName")),
AccessibilityFeatures = reader.GetString(reader.GetOrdinal("AccessibilityFeatures")),
Description = reader.GetString(reader.GetOrdinal("Description"))
}
};
}
}
}
Explanation of Each Method
- SearchByAvailabilityAsync: Retrieves rooms available during the specified check-in and check-out dates, ensuring they don’t overlap with existing reservations except those canceled.
- SearchByPriceRangeAsync: Finds rooms within a specified price range, helping users find accommodations that fit their budget.
- SearchByRoomTypeAsync: Filters and retrieves rooms based on a specific room type name, facilitating user searches for rooms that meet particular type requirements.
- SearchByViewTypeAsync: Selects and lists rooms based on their view type (e.g., sea, city), catering to preferences for specific scenic outlooks.
- SearchByAmenitiesAsync: Identifies and lists rooms equipped with certain specified amenities, aiding in the customization of user preferences for room features.
- SearchRoomsByRoomTypeIDAsync: Retrieves all rooms associated with a given room type ID, useful for detailed and specific queries related to room categorization.
- GetRoomDetailsWithAmenitiesByRoomIDAsync: This method provides comprehensive details about a specific room, including information about the room type and the amenities it offers. Thus, it gives a full overview for guest decision-making or administrative purposes.
- GetRoomAmenitiesByRoomIDAsync: This function fetches all amenities available in a specified room, which helps provide detailed amenities information to guests or for service quality assessments.
- SearchByMinRatingAsync: This method filters and returns rooms that have a minimum average guest rating, assisting in ensuring quality standards or preferences are met.
- SearchCustomCombinationAsync: This function conducts a flexible search based on multiple optional criteria, such as price, room type, amenities, and view type, using a dynamic approach to match various user-defined filters.
Register Hotel Search Repository:
Next, we need to register the HotelSearchRepository into the dependency injection container so that the framework can inject the object wherever we need this repository instance, mostly in the Controller class. So, please add the following code to the Program.cs class file:
builder.Services.AddScoped<HotelSearchRepository>();
Creating Hotel Search Controller:
Let us create the Hotel Search Controller and use the methods defined in the above HotelSearchRepository class. So, create a new Empty API Controller named HotelSearchController within the Controllers folder and copy and paste the following code.
using HotelBookingAPI.DTOs.HotelSearchDTOs;
using HotelBookingAPI.Models;
using HotelBookingAPI.Repository;
using Microsoft.AspNetCore.Mvc;
using System.Net;
namespace HotelBookingAPI.Controllers
{
[ApiController]
[Route("[controller]")]
public class HotelSearchController : ControllerBase
{
private readonly HotelSearchRepository _hotelSearchRepository;
private readonly ILogger<HotelSearchController> _logger;
public HotelSearchController(HotelSearchRepository hotelSearchRepository, ILogger<HotelSearchController> logger)
{
_hotelSearchRepository = hotelSearchRepository;
_logger = logger;
}
[HttpGet("Availability")]
//checkInDate=2024-05-15
//checkOutDate=2024-05-18
public async Task<APIResponse<List<RoomSearchDTO>>> SearchByAvailability([FromQuery] AvailabilityHotelSearchRequestDTO request)
{
try
{
if (!ModelState.IsValid)
{
_logger.LogInformation("Invalid Data in the Request Body");
return new APIResponse<List<RoomSearchDTO>>(HttpStatusCode.BadRequest, "Invalid Data in the Request Body");
}
var rooms = await _hotelSearchRepository.SearchByAvailabilityAsync(request.CheckInDate, request.CheckOutDate);
if (rooms != null && rooms.Count > 0)
{
return new APIResponse<List<RoomSearchDTO>>(rooms, "Fetch Available Room Successful");
}
return new APIResponse<List<RoomSearchDTO>>(HttpStatusCode.BadRequest, "No Record Found");
}
catch (Exception ex)
{
_logger.LogError(ex, "Failed to get available rooms");
return new APIResponse<List<RoomSearchDTO>>(HttpStatusCode.InternalServerError, "Failed to get available rooms", ex.Message);
}
}
[HttpGet("PriceRange")]
public async Task<APIResponse<List<RoomSearchDTO>>> SearchByPriceRange([FromQuery] PriceRangeHotelSearchRequestDTO request)
{
try
{
if (!ModelState.IsValid)
{
_logger.LogInformation("Invalid Price Range in the Request Body");
return new APIResponse<List<RoomSearchDTO>>(HttpStatusCode.BadRequest, "Invalid Data in the Request Body");
}
var rooms = await _hotelSearchRepository.SearchByPriceRangeAsync(request.MinPrice, request.MaxPrice);
if (rooms != null && rooms.Count > 0)
{
return new APIResponse<List<RoomSearchDTO>>(rooms, "Fetch rooms by price range Successful");
}
return new APIResponse<List<RoomSearchDTO>>(HttpStatusCode.BadRequest, "No Record Found");
}
catch (Exception ex)
{
_logger.LogError(ex, "Failed to get rooms by price range");
return new APIResponse<List<RoomSearchDTO>>(HttpStatusCode.InternalServerError, "An error occurred while fetching rooms by price range.", ex.Message);
}
}
[HttpGet("RoomType")]
public async Task<APIResponse<List<RoomSearchDTO>>> SearchByRoomType(string roomTypeName)
{
try
{
if(string.IsNullOrEmpty(roomTypeName))
{
_logger.LogInformation("Room Type Name is Empty");
return new APIResponse<List<RoomSearchDTO>>(HttpStatusCode.BadRequest, "Room Type Name is Empty");
}
var rooms = await _hotelSearchRepository.SearchByRoomTypeAsync(roomTypeName);
if (rooms != null && rooms.Count > 0)
{
return new APIResponse<List<RoomSearchDTO>>(rooms, "Fetch rooms by room type Successful");
}
return new APIResponse<List<RoomSearchDTO>>(HttpStatusCode.BadRequest, "No Record Found");
}
catch (Exception ex)
{
_logger.LogError(ex, "Failed to get rooms by room type");
return new APIResponse<List<RoomSearchDTO>>(HttpStatusCode.InternalServerError, "An error occurred while fetching rooms by room type.", ex.Message);
}
}
[HttpGet("ViewType")]
public async Task<APIResponse<List<RoomSearchDTO>>> SearchByViewType(string viewType)
{
try
{
if (string.IsNullOrEmpty(viewType))
{
_logger.LogInformation("View Type is Empty");
return new APIResponse<List<RoomSearchDTO>>(HttpStatusCode.BadRequest, "View Type is Empty");
}
var rooms = await _hotelSearchRepository.SearchByViewTypeAsync(viewType);
if (rooms != null && rooms.Count > 0)
{
return new APIResponse<List<RoomSearchDTO>>(rooms, "Fetch rooms by view type Successful");
}
return new APIResponse<List<RoomSearchDTO>>(HttpStatusCode.BadRequest, "No Record Found");
}
catch (Exception ex)
{
_logger.LogError(ex, "Failed to get rooms by view type");
return new APIResponse<List<RoomSearchDTO>>(HttpStatusCode.InternalServerError, "An error occurred while fetching rooms by view type.", ex.Message);
}
}
[HttpGet("Amenities")]
public async Task<APIResponse<List<RoomSearchDTO>>> SearchByAmenities(string amenityName)
{
try
{
if (string.IsNullOrEmpty(amenityName))
{
_logger.LogInformation("Amenity Name is Empty");
return new APIResponse<List<RoomSearchDTO>>(HttpStatusCode.BadRequest, "Amenity Name is Empty");
}
var rooms = await _hotelSearchRepository.SearchByAmenitiesAsync(amenityName);
if (rooms != null && rooms.Count > 0)
{
return new APIResponse<List<RoomSearchDTO>>(rooms, "Fetch rooms by amenities Successful");
}
return new APIResponse<List<RoomSearchDTO>>(HttpStatusCode.BadRequest, "No Record Found");
}
catch (Exception ex)
{
_logger.LogError(ex, "Failed to get rooms by amenities");
return new APIResponse<List<RoomSearchDTO>>(HttpStatusCode.InternalServerError, "An error occurred while fetching rooms by amenities.", ex.Message);
}
}
[HttpGet("RoomsByType")]
public async Task<APIResponse<List<RoomSearchDTO>>> SearchRoomsByRoomTypeID(int roomTypeID)
{
try
{
if (roomTypeID <= 0)
{
_logger.LogInformation($"Invalid Room Type ID, {roomTypeID}");
return new APIResponse<List<RoomSearchDTO>>(HttpStatusCode.BadRequest, $"Invalid Room Type ID, {roomTypeID}");
}
var rooms = await _hotelSearchRepository.SearchRoomsByRoomTypeIDAsync(roomTypeID);
if (rooms != null && rooms.Count > 0)
{
return new APIResponse<List<RoomSearchDTO>>(rooms, "Fetch rooms by room type ID Successful");
}
return new APIResponse<List<RoomSearchDTO>>(HttpStatusCode.BadRequest, "No Record Found");
}
catch (Exception ex)
{
_logger.LogError(ex, "Failed to get rooms by room type ID");
return new APIResponse<List<RoomSearchDTO>>(HttpStatusCode.InternalServerError, "An error occurred while fetching rooms by room type ID.", ex.Message);
}
}
[HttpGet("RoomDetails")]
public async Task<APIResponse<RoomDetailsWithAmenitiesSearchDTO>> GetRoomDetailsWithAmenitiesByRoomID(int roomID)
{
try
{
if (roomID <= 0)
{
_logger.LogInformation($"Invalid Room ID, {roomID}");
return new APIResponse<RoomDetailsWithAmenitiesSearchDTO>(HttpStatusCode.BadRequest, $"Invalid Room ID, {roomID}");
}
var roomDetails = await _hotelSearchRepository.GetRoomDetailsWithAmenitiesByRoomIDAsync(roomID);
if (roomDetails != null)
return new APIResponse<RoomDetailsWithAmenitiesSearchDTO>(roomDetails, "Fetch room details with amenities for RoomID Successful");
else
return new APIResponse<RoomDetailsWithAmenitiesSearchDTO>(HttpStatusCode.BadRequest, "No Record Found");
}
catch (Exception ex)
{
_logger.LogError(ex, $"Failed to get room details with amenities for RoomID {roomID}");
return new APIResponse<RoomDetailsWithAmenitiesSearchDTO>(HttpStatusCode.InternalServerError, "An error occurred while fetching room details with amenities.", ex.Message);
}
}
[HttpGet("RoomAmenities")]
public async Task<APIResponse<List<AmenitySearchDTO>>> GetRoomAmenitiesByRoomID(int roomID)
{
try
{
if (roomID <= 0)
{
_logger.LogInformation($"Invalid Room ID, {roomID}");
return new APIResponse<List<AmenitySearchDTO>>(HttpStatusCode.BadRequest, $"Invalid Room ID, {roomID}");
}
var amenities = await _hotelSearchRepository.GetRoomAmenitiesByRoomIDAsync(roomID);
if (amenities != null && amenities.Count > 0)
{
return new APIResponse<List<AmenitySearchDTO>>(amenities, "Fetch Amenities for RoomID Successful");
}
return new APIResponse<List<AmenitySearchDTO>>(HttpStatusCode.BadRequest, "No Record Found");
}
catch (Exception ex)
{
_logger.LogError(ex, $"Failed to get amenities for RoomID {roomID}");
return new APIResponse<List<AmenitySearchDTO>>(HttpStatusCode.InternalServerError, "An error occurred while fetching room amenities.", ex.Message);
}
}
[HttpGet("ByRating")]
public async Task<APIResponse<List<RoomSearchDTO>>> SearchByMinRating(float minRating)
{
try
{
if (minRating <= 0 && minRating > 5)
{
_logger.LogInformation($"Invalid Rating: {minRating}");
return new APIResponse<List<RoomSearchDTO>>(HttpStatusCode.BadRequest, $"Invalid Rating: {minRating}");
}
var rooms = await _hotelSearchRepository.SearchByMinRatingAsync(minRating);
if (rooms != null && rooms.Count > 0)
{
return new APIResponse<List<RoomSearchDTO>>(rooms, "Fetch rooms by minimum rating Successful");
}
return new APIResponse<List<RoomSearchDTO>>(HttpStatusCode.BadRequest, "No Record Found");
}
catch (Exception ex)
{
_logger.LogError(ex, "Failed to get rooms by minimum rating");
return new APIResponse<List<RoomSearchDTO>>(HttpStatusCode.InternalServerError, "An error occurred while fetching rooms by minimum rating.", ex.Message);
}
}
[HttpGet("CustomSearch")]
public async Task<APIResponse<List<RoomSearchDTO>>> SearchCustomCombination([FromQuery] CustomHotelSearchCriteriaDTO criteria)
{
try
{
if (!ModelState.IsValid)
{
_logger.LogInformation("Invalid Data in the Request Body");
return new APIResponse<List<RoomSearchDTO>>(HttpStatusCode.BadRequest, "Invalid Data in the Request Body");
}
var rooms = await _hotelSearchRepository.SearchCustomCombinationAsync(criteria);
if (rooms != null && rooms.Count > 0)
{
return new APIResponse<List<RoomSearchDTO>>(rooms, "Fetch Room By Custom Search Successful");
}
return new APIResponse<List<RoomSearchDTO>>(HttpStatusCode.BadRequest, "No Record Found");
}
catch (Exception ex)
{
_logger.LogError(ex,"Failed to perform custom search");
return new APIResponse<List<RoomSearchDTO>>(HttpStatusCode.InternalServerError, "An error occurred during the custom search.", ex.Message);
}
}
}
}
Explanation of the Controller Action Methods:
- SearchByAvailability: This endpoint Handles requests to find rooms available within a specified check-in and check-out date range, providing users with options that match their stay duration without any reservation conflicts.
- SearchByPriceRange: Manages requests to find rooms within a specific price range, helping users find accommodations within their budget constraints.
- SearchByRoomType: This endpoint handles requests to filter rooms based on a specified room type name, allowing users to select rooms that meet specific configuration or feature requirements.
- SearchByViewType: Handles requests for rooms with a particular type of view, like sea or city, catering to preferences for specific types of scenic vistas from the room.
- SearchByAmenities: This endpoint manages requests to find rooms equipped with specified amenities, allowing users to choose rooms with specific desirable features, such as spas or gyms.
- SearchRoomsByRoomTypeID: This endpoint handles requests to retrieve all rooms associated with a specific room type ID. It is useful for users looking for detailed listings based on room categorizations.
- GetRoomDetailsWithAmenitiesByRoomID: Manages detailed information requests for a specific room, including its amenities, thus offering a comprehensive overview for guest decision-making.
- GetRoomAmenitiesByRoomID: This endpoint handles requests to fetch a detailed list of amenities for a specific room, aiding users in understanding what features are included in their potential accommodation.
- SearchByMinRating: Manages requests to filter rooms based on a minimum average guest rating, ensuring users can select rooms that uphold a certain quality standard.
- SearchCustomCombination: Handles flexible search queries based on a combination of criteria such as price, room type, amenities, and view type, offering a dynamic search capability that caters to varied user preferences.
In this article, I explain the Hotel Search Module of the Hotel Booking Application. I hope you enjoy this implementation of the Hotel Search Module of our Hotel Booking Application article. Please take a few minutes to give your valuable feedback about this article. Your feedback means a lot to me and motivates me to give better examples and explanations.
What Next? Implementing the Hotel Booking Module of the Hotel Booking Application.
About the Author: Pranaya Rout
Pranaya Rout has published more than 3,000 articles in his 11-year career. Pranaya Rout has very good experience with Microsoft Technologies, Including C#, VB, ASP.NET MVC, ASP.NET Web API, EF, EF Core, ADO.NET, LINQ, SQL Server, MYSQL, Oracle, ASP.NET Core, Cloud Computing, Microservices, Design Patterns and still learning new technologies.
Hi Pranaya , very nice article on sample real world application , just wanted to check where are the other topics on autorisation,autnentication,JWT etc
Hi
I am working on the same. I will update the remaining modules very soon.