Room Module of Hotel Booking Application

Back to: ASP.NET Core Web API Tutorials

Implementing Room Module of Hotel Booking Application

In this article, I will discuss implementing the Room Module of a Hotel Booking Application. Please read our previous article discussing How to Implement the Room Type Module of a Hotel Booking Application.

How to Implement Room Module of Hotel Booking Application

Let us implement the Rooms Module. Let us create the Stored Procedures, Model Classes, DTOs, a Repository class, and Room Controller for adding, updating, and deleting Rooms, Fetching the Room Details by ID, Fetching all Room Details with Status, etc.

Create Stored Procedures for Rooms Management

We will start by creating the necessary stored procedures in SQL Server HotelDB Database to Manage the Room Data. Please execute the following Script on the HotelDB database that we are working with so far.

-- Create Room
CREATE OR ALTER PROCEDURE spCreateRoom
    @RoomNumber NVARCHAR(10),
    @RoomTypeID INT,
    @Price DECIMAL(10,2),
    @BedType NVARCHAR(50),
    @ViewType NVARCHAR(50),
    @Status NVARCHAR(50),
    @IsActive BIT,
    @CreatedBy NVARCHAR(100),
    @NewRoomID INT OUTPUT,
    @StatusCode INT OUTPUT,
    @Message NVARCHAR(255) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION
            -- Check if the provided RoomTypeID exists in the RoomTypes table
            IF EXISTS (SELECT 1 FROM RoomTypes WHERE RoomTypeID = @RoomTypeID)
            BEGIN
                -- Ensure the room number is unique
                IF NOT EXISTS (SELECT 1 FROM Rooms WHERE RoomNumber = @RoomNumber)
                BEGIN
                    INSERT INTO Rooms (RoomNumber, RoomTypeID, Price, BedType, ViewType, Status, IsActive, CreatedBy, CreatedDate)
                    VALUES (@RoomNumber, @RoomTypeID, @Price, @BedType, @ViewType, @Status, @IsActive, @CreatedBy, GETDATE())

                    SET @NewRoomID = SCOPE_IDENTITY()
                    SET @StatusCode = 0 -- Success
                    SET @Message = 'Room created successfully.'
                END
                ELSE
                BEGIN
                    SET @StatusCode = 1 -- Failure due to duplicate room number
                    SET @Message = 'Room number already exists.'
                END
            END
            ELSE
            BEGIN
                SET @StatusCode = 3 -- Failure due to invalid RoomTypeID
                SET @Message = 'Invalid Room Type ID provided.'
            END
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION
        SET @StatusCode = ERROR_NUMBER()
        SET @Message = ERROR_MESSAGE()
    END CATCH
END
GO

-- Update Room
CREATE OR ALTER PROCEDURE spUpdateRoom
    @RoomID INT,
    @RoomNumber NVARCHAR(10),
    @RoomTypeID INT,
    @Price DECIMAL(10,2),
    @BedType NVARCHAR(50),
    @ViewType NVARCHAR(50),
    @Status NVARCHAR(50),
    @IsActive BIT,
    @ModifiedBy NVARCHAR(100),
    @StatusCode INT OUTPUT,
    @Message NVARCHAR(255) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION
            -- Check if the RoomTypeID is valid and room number is unique for other rooms
            IF EXISTS (SELECT 1 FROM RoomTypes WHERE RoomTypeID = @RoomTypeID) AND
               NOT EXISTS (SELECT 1 FROM Rooms WHERE RoomNumber = @RoomNumber AND RoomID <> @RoomID)
            BEGIN
                -- Verify the room exists before updating
                IF EXISTS (SELECT 1 FROM Rooms WHERE RoomID = @RoomID)
                BEGIN
                    UPDATE Rooms
                    SET RoomNumber = @RoomNumber,
                        RoomTypeID = @RoomTypeID,
                        Price = @Price,
                        BedType = @BedType,
                        ViewType = @ViewType,
                        Status = @Status,
                        IsActive = @IsActive,
                        ModifiedBy = @ModifiedBy,
                        ModifiedDate = GETDATE()
                    WHERE RoomID = @RoomID

                    SET @StatusCode = 0 -- Success
                    SET @Message = 'Room updated successfully.'
                END
                ELSE
                BEGIN
                    SET @StatusCode = 2 -- Failure due to room not found
                    SET @Message = 'Room not found.'
                END
            END
            ELSE
            BEGIN
                SET @StatusCode = 1 -- Failure due to invalid RoomTypeID or duplicate room number
                SET @Message = 'Invalid Room Type ID or duplicate room number.'
            END
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION
        SET @StatusCode = ERROR_NUMBER()
        SET @Message = ERROR_MESSAGE()
    END CATCH
END
GO

-- Delete Room (Soft Delete)
CREATE OR ALTER PROCEDURE spDeleteRoom
    @RoomID INT,
    @StatusCode INT OUTPUT,
    @Message NVARCHAR(255) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION
            -- Ensure no active reservations exist for the room
            IF NOT EXISTS (SELECT 1 FROM Reservations WHERE RoomID = @RoomID AND Status NOT IN ('Checked-out', 'Cancelled'))
            BEGIN
                -- Verify the room exists and is currently active before deactivating
                IF EXISTS (SELECT 1 FROM Rooms WHERE RoomID = @RoomID AND IsActive = 1)
                BEGIN
                    -- Instead of deleting, we update the IsActive flag to false
                    UPDATE Rooms
                    SET IsActive = 0  -- Set IsActive to false to indicate the room is no longer active
                    WHERE RoomID = @RoomID

                    SET @StatusCode = 0 -- Success
                    SET @Message = 'Room deactivated successfully.'
                END
                ELSE
                BEGIN
                    SET @StatusCode = 2 -- Failure due to room not found or already deactivated
                    SET @Message = 'Room not found or already deactivated.'
                END
            END
            ELSE
            BEGIN
                SET @StatusCode = 1 -- Failure due to active reservations
                SET @Message = 'Room cannot be deactivated, there are active reservations.'
            END
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION
        SET @StatusCode = ERROR_NUMBER()
        SET @Message = ERROR_MESSAGE()
    END CATCH
END
GO

-- Get Room by Id
CREATE OR ALTER PROCEDURE spGetRoomById
    @RoomID INT
AS
BEGIN
    SELECT RoomID, RoomNumber, RoomTypeID, Price, BedType, ViewType, Status, IsActive FROM Rooms WHERE RoomID = @RoomID
END
GO

-- Get All Rooms with Optional Filtering
CREATE OR ALTER PROCEDURE spGetAllRoom
    @RoomTypeID INT = NULL,     -- Optional filter by Room Type
    @Status NVARCHAR(50) = NULL -- Optional filter by Status
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SQL NVARCHAR(MAX)

    -- Start building the dynamic SQL query
    SET @SQL = 'SELECT RoomID, RoomNumber, RoomTypeID, Price, BedType, ViewType, Status, IsActive FROM Rooms WHERE 1=1'

    -- Append conditions based on the presence of optional parameters
    IF @RoomTypeID IS NOT NULL
        SET @SQL = @SQL + ' AND RoomTypeID = @RoomTypeID'
    
    IF @Status IS NOT NULL
        SET @SQL = @SQL + ' AND Status = @Status'

    -- Execute the dynamic SQL statement
    EXEC sp_executesql @SQL, 
                       N'@RoomTypeID INT, @Status NVARCHAR(50)', 
                       @RoomTypeID, 
                       @Status
END
GO
Explanation of Each Stored Procedure:
  • spCreateRoom: This stored procedure is designed to create a new room in the hotel database. It ensures that the room number is unique across all rooms and that the RoomTypeID provided references a valid entry in the RoomTypes table. Successful execution of this procedure results in the addition of a new room with the specified characteristics, and it returns identifiers to confirm the creation status.
  • spUpdateRoom: This procedure facilitates updating existing room details based on the provided RoomID. It checks for the validity of the RoomTypeID and ensures that the new room number is unique among other rooms (excluding the current one). The procedure ensures that modifications are only applied if the specified room exists and meets all validation criteria. It’s designed to manage room data effectively, ensuring consistency and integrity.
  • spDeleteRoom: The purpose of spDeleteRoom is to mark the room as deleted from the database, provided there are no active reservations associated with it. It checks for the room’s existence and verifies that there are no ongoing (non-checked-out or non-canceled) reservations linked to it before proceeding with the deletion. This procedure helps maintain operational integrity by ensuring that a room cannot be marked inactive if needed for upcoming or ongoing guest stays.
  • spGetRoomById: This stored procedure retrieves detailed information about a specific room identified by RoomID. It’s useful for operations that require displaying or further processing individual room details, such as during booking or information updates.
  • spGetAllRoom: Designed to fetch a list of all rooms in the database with the capability to apply optional filters based on RoomTypeID or Status. This dynamic approach allows for flexible data retrieval, which can be used for various user needs, such as generating reports, managing rooms, or providing listings with specific criteria.
Understanding Dynamic SQL in spGetAllRoom Procedure:

We are adding optional filtering options to the spGetAllRoom stored procedure using dynamic SQL, which can significantly enhance its usability and allow users to retrieve rooms based on specific criteria like Status or RoomTypeID. The procedure will construct the SQL query dynamically based on the input parameters provided:

  • Dynamic SQL Construction: The procedure constructs a basic SQL query that retrieves all columns from the Rooms table. It includes a WHERE 1=1 clause to simplify appending additional conditions.
  • Optional Parameters: Two optional parameters, @RoomTypeID and @Status, are included. The corresponding filters are appended to the SQL query if these parameters are provided.
  • Execution of Dynamic SQL: The query is executed using sp_executesql, which allows for parameterized execution of dynamic SQL, enhancing security by preventing SQL injection.

This stored procedure can be called with various combinations of parameters:

  • Without any parameters to get all the rooms.
  • With @RoomTypeID to filter rooms by a specific type.
  • With @Status to filter rooms by their status.
  • With both @RoomTypeID and @Status for combined filtering.

Creating Room DTOs:

Next, we need to create the DTOs required for performing operations on the Room database table. Controllers and Repositories will use these DTOs to manage Operations and Data Transformation effectively. Please create a folder called RoomDTOs inside the DTOs folder, where we will add all the DTOs related to managing Room.

CreateRoomRequestDTO

Create a class file named CreateRoomRequestDTO.cs within the RoomDTOs folder, and then copy and paste the following code. This DTO will only include the properties required to create a new Room.

using System.ComponentModel.DataAnnotations;

namespace HotelBookingAPI.DTOs.RoomDTOs
{
    public class CreateRoomRequestDTO
    {
        [Required]
        [StringLength(10, ErrorMessage = "Room number must be up to 10 characters long.")]
        public string RoomNumber { get; set; }

        [Required]
        [Range(1, int.MaxValue, ErrorMessage = "Invalid Room Type ID.")]
        public int RoomTypeID { get; set; }

        [Required]
        [Range(typeof(decimal), "0.01", "999999.99", ErrorMessage = "Price must be between 0.01 and 999999.99.")]
        public decimal Price { get; set; }

        [Required]
        [StringLength(50, ErrorMessage = "Bed type must be up to 50 characters long.")]
        public string BedType { get; set; }

        [Required]
        [StringLength(50, ErrorMessage = "View type must be up to 50 characters long.")]
        public string ViewType { get; set; }

        [Required]
        [RegularExpression("(Available|Under Maintenance|Occupied)", ErrorMessage = "Status must be 'Available', 'Under Maintenance', or 'Occupied'.")]
        public string Status { get; set; }

        [Required]
        public bool IsActive { get; set; }
    }
}
CreateRoomResponseDTO

Create a class file named CreateRoomResponseDTO.cs within the RoomDTOs folder, and then copy and paste the following code. Once the room is created, this DTO will only include the properties we send.

namespace HotelBookingAPI.DTOs.RoomDTOs
{
    public class CreateRoomResponseDTO
    {
        public int RoomID { get; set; }
        public string Message { get; set; }
        public bool IsCreated { get; set; }
    }
}
UpdateRoomRequestDTO

Create a class file named UpdateRoomRequestDTO.cs within the RoomDTOs folder and copy and paste the following code. This DTO will only include the properties required to update an existing room.

using System.ComponentModel.DataAnnotations;

namespace HotelBookingAPI.DTOs.RoomDTOs
{
    public class UpdateRoomRequestDTO
    {
        [Required]
        public int RoomID { get; set; }

        [Required]
        [StringLength(10, ErrorMessage = "Room number must be up to 10 characters long.")]
        public string RoomNumber { get; set; }

        [Required]
        [Range(1, int.MaxValue, ErrorMessage = "Invalid Room Type ID.")]
        public int RoomTypeID { get; set; }

        [Required]
        [Range(typeof(decimal), "0.01", "999999.99", ErrorMessage = "Price must be between 0.01 and 999999.99.")]
        public decimal Price { get; set; }

        [Required]
        [StringLength(50, ErrorMessage = "Bed type must be up to 50 characters long.")]
        public string BedType { get; set; }

        [Required]
        [StringLength(50, ErrorMessage = "View type must be up to 50 characters long.")]
        public string ViewType { get; set; }

        [Required]
        [RegularExpression("(Available|Under Maintenance|Occupied)", ErrorMessage = "Status must be 'Available', 'Under Maintenance', or 'Occupied'.")]
        public string Status { get; set; }

        [Required]
        public bool IsActive { get; set; }
    }
}
UpdateRoomResponseDTO

Create a class file named UpdateRoomResponseDTO.cs within the RoomDTOs folder, and then copy and paste the following code. Once the room is updated, this DTO will only include the properties we send.

namespace HotelBookingAPI.DTOs.RoomDTOs
{
    public class UpdateRoomResponseDTO
    {
        public int RoomId { get; set; }
        public string Message { get; set; }
        public bool IsUpdated { get; set; }
    }
}
DeleteRoomResponseDTO

Create a class file named DeleteRoomResponseDTO.cs within the RoomDTOs folder, and then copy and paste the following code. This class will only include the properties we send once the Room is deleted.

namespace HotelBookingAPI.DTOs.RoomDTOs
{
    public class DeleteRoomResponseDTO
    {
        public string Message { get; set; }
        public bool IsDeleted { get; set; }
    }
}
RoomDetailsResponseDTO

Create a class file named RoomDetailsResponseDTO.cs within the RoomDTOs folder, and then copy and paste the following code. This DTO will only include the properties required to return the Room information.

namespace HotelBookingAPI.DTOs.RoomDTOs
{
    public class RoomDetailsResponseDTO
    {
        public int RoomID { get; set; }
        public string RoomNumber { get; set; }
        public int RoomTypeID { get; set; }
        public decimal Price { get; set; }
        public string BedType { get; set; }
        public string ViewType { get; set; }
        public string Status { get; set; }
        public bool IsActive { get; set; }
    }
}
GetAllRoomsRequestDTO

Create a class file named GetAllRoomsRequestDTO.cs within the RoomDTOs folder, and then copy and paste the following code. When we fetch the room information, this DTO will only include the optional properties.

using System.ComponentModel.DataAnnotations;

namespace HotelBookingAPI.DTOs.RoomDTOs
{
    public class GetAllRoomsRequestDTO
    {
        // Optional filtering by RoomTypeID; validation ensures positive integers if provided
        [Range(1, int.MaxValue, ErrorMessage = "Room Type ID must be a positive integer.")]
        public int? RoomTypeID { get; set; }

        // Optional filtering by Status; uses regex to ensure the status is one of the predefined values
        [RegularExpression("(Available|Under Maintenance|Occupied|All)", ErrorMessage = "Invalid status. Valid statuses are 'Available', 'Under Maintenance', 'Occupied', or 'All' for no filter.")]
        public string? Status { get; set; }
    }
}

Creating Room Repository:

Next, we need to create the RoomRepository class, where we will implement the business and data access logic. This class will consume the Room Related Stored Procedures and DTOs for the operations that we have created so far. So, create a class file named RoomRepository.cs within the Repository folder and copy and paste the following code.

using HotelBookingAPI.Connection;
using HotelBookingAPI.DTOs.RoomDTOs;
using Microsoft.AspNetCore.SignalR.Protocol;
using Microsoft.Data.SqlClient;
using System.Data;
using System.Reflection.PortableExecutable;

namespace HotelBookingAPI.Repository
{
    public class RoomRepository
    {
        private readonly SqlConnectionFactory _connectionFactory;

        public RoomRepository(SqlConnectionFactory connectionFactory)
        {
            _connectionFactory = connectionFactory;
        }

        public async Task<CreateRoomResponseDTO> CreateRoomAsync(CreateRoomRequestDTO request)
        {
            using var connection = _connectionFactory.CreateConnection();

            using var command = new SqlCommand("spCreateRoom", connection)
            {
                CommandType = CommandType.StoredProcedure
            };
            command.Parameters.AddWithValue("@RoomNumber", request.RoomNumber);
            command.Parameters.AddWithValue("@RoomTypeID", request.RoomTypeID);
            command.Parameters.AddWithValue("@Price", request.Price);
            command.Parameters.AddWithValue("@BedType", request.BedType);
            command.Parameters.AddWithValue("@ViewType", request.ViewType);
            command.Parameters.AddWithValue("@Status", request.Status);
            command.Parameters.AddWithValue("@IsActive", request.IsActive);
            command.Parameters.AddWithValue("@CreatedBy", "System");
            command.Parameters.Add("@NewRoomID", SqlDbType.Int).Direction = ParameterDirection.Output;
            command.Parameters.Add("@StatusCode", SqlDbType.Int).Direction = ParameterDirection.Output;
            command.Parameters.Add("@Message", SqlDbType.NVarChar, 255).Direction = ParameterDirection.Output;

            try
            {
                await connection.OpenAsync();
                await command.ExecuteNonQueryAsync();

                var outputRoomID = command.Parameters["@NewRoomID"].Value;
                var newRoomID = outputRoomID != DBNull.Value ? Convert.ToInt32(outputRoomID) : 0;  // Safely handle potential DBNull values.
                
                return new CreateRoomResponseDTO
                {
                    RoomID = newRoomID,
                    IsCreated = (int)command.Parameters["@StatusCode"].Value == 0,
                    Message = (string)command.Parameters["@Message"].Value
                };
            }
            catch (Exception ex)
            {
                throw new Exception($"Error creating room: {ex.Message}", ex);
            }
        }

        public async Task<UpdateRoomResponseDTO> UpdateRoomAsync(UpdateRoomRequestDTO request)
        {
            using var connection = _connectionFactory.CreateConnection();

            using var command = new SqlCommand("spUpdateRoom", connection)
            {
                CommandType = CommandType.StoredProcedure
            };
            command.Parameters.AddWithValue("@RoomID", request.RoomID);
            command.Parameters.AddWithValue("@RoomNumber", request.RoomNumber);
            command.Parameters.AddWithValue("@RoomTypeID", request.RoomTypeID);
            command.Parameters.AddWithValue("@Price", request.Price);
            command.Parameters.AddWithValue("@BedType", request.BedType);
            command.Parameters.AddWithValue("@ViewType", request.ViewType);
            command.Parameters.AddWithValue("@Status", request.Status);
            command.Parameters.AddWithValue("@IsActive", request.IsActive);
            command.Parameters.AddWithValue("@ModifiedBy", "System");
            command.Parameters.Add("@StatusCode", SqlDbType.Int).Direction = ParameterDirection.Output;
            command.Parameters.Add("@Message", SqlDbType.NVarChar, 255).Direction = ParameterDirection.Output;

            try
            {
                await connection.OpenAsync();
                await command.ExecuteNonQueryAsync();
                return new UpdateRoomResponseDTO
                {
                    RoomId = request.RoomID,
                    IsUpdated = (int)command.Parameters["@StatusCode"].Value == 0,
                    Message = (string)command.Parameters["@Message"].Value
                };
            }
            catch (Exception ex)
            {
                throw new Exception($"Error updating room: {ex.Message}", ex);
            }
        }

        public async Task<DeleteRoomResponseDTO> DeleteRoomAsync(int roomId)
        {
            using var connection = _connectionFactory.CreateConnection();

            using var command = new SqlCommand("spDeleteRoom", connection)
            {
                CommandType = CommandType.StoredProcedure
            };
            command.Parameters.AddWithValue("@RoomID", roomId);
            command.Parameters.Add("@StatusCode", SqlDbType.Int).Direction = ParameterDirection.Output;
            command.Parameters.Add("@Message", SqlDbType.NVarChar, 255).Direction = ParameterDirection.Output;

            try
            {
                await connection.OpenAsync();
                await command.ExecuteNonQueryAsync();
                return new DeleteRoomResponseDTO
                {
                    IsDeleted = (int)command.Parameters["@StatusCode"].Value == 0,
                    Message = (string)command.Parameters["@Message"].Value
                };
            }
            catch (Exception ex)
            {
                throw new Exception($"Error deleting room: {ex.Message}", ex);
            }
        }

        public async Task<RoomDetailsResponseDTO> GetRoomByIdAsync(int roomId)
        {
            using var connection = _connectionFactory.CreateConnection();

            using var command = new SqlCommand("spGetRoomById", connection)
            {
                CommandType = CommandType.StoredProcedure
            };
            command.Parameters.AddWithValue("@RoomID", roomId);

            try
            {
                await connection.OpenAsync();
                using var reader = await command.ExecuteReaderAsync();

                if (await reader.ReadAsync())
                {
                    return new RoomDetailsResponseDTO
                    {
                        RoomID = reader.GetInt32("RoomID"),
                        RoomNumber = reader.GetString("RoomNumber"),
                        RoomTypeID = reader.GetInt32("RoomTypeID"),
                        Price = reader.GetDecimal("Price"),
                        BedType = reader.GetString("BedType"),
                        ViewType = reader.GetString("ViewType"),
                        Status = reader.GetString("Status"),
                        IsActive = reader.GetBoolean("IsActive")
                    };
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                throw new Exception($"Error retrieving room by ID: {ex.Message}", ex);
            }
        }

        public async Task<List<RoomDetailsResponseDTO>> GetAllRoomsAsync(GetAllRoomsRequestDTO request)
        {
            using var connection = _connectionFactory.CreateConnection();
            using var command = new SqlCommand("spGetAllRoom", connection)
            {
                CommandType = CommandType.StoredProcedure
            };

            // Add parameters for RoomTypeID and Status, handling nulls appropriately
            command.Parameters.Add(new SqlParameter("@RoomTypeID", SqlDbType.Int)
            {
                Value = request.RoomTypeID.HasValue ? (object)request.RoomTypeID.Value : DBNull.Value
            });

            command.Parameters.Add(new SqlParameter("@Status", SqlDbType.NVarChar, 50)
            {
                Value = string.IsNullOrEmpty(request.Status) ? DBNull.Value : (object)request.Status
            });

            try
            {
                await connection.OpenAsync();
                var rooms = new List<RoomDetailsResponseDTO>();
                using var reader = await command.ExecuteReaderAsync();
                while (await reader.ReadAsync())
                {
                    rooms.Add(new RoomDetailsResponseDTO
                    {
                        RoomID = reader.GetInt32("RoomID"),
                        RoomNumber = reader.GetString("RoomNumber"),
                        RoomTypeID = reader.GetInt32("RoomTypeID"),
                        Price = reader.GetDecimal("Price"),
                        BedType = reader.GetString("BedType"),
                        ViewType = reader.GetString("ViewType"),
                        Status = reader.GetString("Status"),
                        IsActive = reader.GetBoolean("IsActive")
                    });
                }
                return rooms;
            }
            catch (Exception ex)
            {
                throw new Exception($"Error retrieving all rooms: {ex.Message}", ex);
            }
        }
    }
}

Explanation of Each Method:

CreateRoomAsync

Creates a new room in the database.

  • Input Parameter: CreateRoomRequestDTO containing room details such as room number, type ID, price, bed type, view type, status, and whether the room is active.
  • Output Parameter: CreateRoomResponseDTO which includes the new room ID, a status flag indicating if the room was created successfully, and a message.
  • Stored Procedure: spCreateRoom. This procedure validates whether the room type ID exists and checks for a unique room number before inserting the new room data. It returns the new room ID, a status code, and a message about the operation’s success or failure.
UpdateRoomAsync

Updates the details of an existing room based on the room ID.

  • Input Parameter: UpdateRoomRequestDTO includes the room ID to update, along with new values for the room number, room type ID, price, bed type, view type, status, and whether it is active.
  • Output Parameter: UpdateRoomResponseDTO which contains the room ID, a status flag indicating if the update was successful, and a message.
  • Stored Procedure: spUpdateRoom. This procedure checks if the new room number is unique (excluding the room being updated) and verifies the validity of the room type ID. It then updates the room details in the database and returns a status code and message regarding the operation’s outcome.
DeleteRoomAsync

Deletes a room from the database by setting its active status to false (soft delete).

  • Input Parameter: roomId (int) identifying which room to delete.
  • Output Parameter: DeleteRoomResponseDTO, which includes a flag indicating if the room was successfully deleted and a message.
  • Stored Procedure: spDeleteRoom. This procedure ensures no active reservations are linked to the room before setting its IsActive status to false. It then returns a status code and message about the deletion’s success or failure.
GetRoomByIdAsync

Retrieves details of a specific room by its ID.

  • Input Parameter: roomId (int) specifying the room to retrieve.
  • Output Parameter: RoomDetailsResponseDTO containing details such as room number, room type ID, price, bed type, view type, status, and active status.
  • Stored Procedure: spGetRoomById. Fetches room details from the database based on the provided room ID. Outputs the details directly if the room is found; otherwise, an exception is thrown.
GetAllRoomsAsync

It fetches a list of all rooms, optionally filtered by room type, ID, and status.

  • Input Parameter: GetAllRoomsRequestDTO, which may include optional filters for room type ID and status.
  • Output Parameter: A collection of RoomDetailsResponseDTO that lists room details for each room meeting the filter criteria.
  • Stored Procedure: spGetAllRoom. Dynamically constructs SQL based on the presence of optional parameters to filter rooms by type and status. Returns a list of room details that match the filtering criteria.
Register Room Repository:

Next, we need to register the RoomRepository 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<RoomRepository>();

Creating Room Controller:

Let us create the Room Controller and use the methods defined in the above RoomRepository class. So, create a new Empty API Controller named RoomController within the Controllers folder and copy and paste the following code.

using HotelBookingAPI.DTOs.RoomDTOs;
using HotelBookingAPI.Models;
using HotelBookingAPI.Repository;
using Microsoft.AspNetCore.Mvc;
using System.Net;

namespace HotelBookingAPI.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class RoomController : ControllerBase
    {
        private readonly RoomRepository _roomRepository;
        private readonly ILogger<RoomController> _logger;

        public RoomController(RoomRepository roomRepository, ILogger<RoomController> logger)
        {
            _roomRepository = roomRepository;
            _logger = logger;
        }

        [HttpGet("All")]
        public async Task<APIResponse<List<RoomDetailsResponseDTO>>> GetAllRooms([FromQuery] GetAllRoomsRequestDTO request)
        {
            _logger.LogInformation("Request Received for CreateRoomType: {@GetAllRoomsRequestDTO}", request);
            if (!ModelState.IsValid)
            {
                _logger.LogInformation("Invalid Data in the Request Body");
                return new APIResponse<List<RoomDetailsResponseDTO>>(HttpStatusCode.BadRequest, "Invalid Data in the Query String");
            }
            try
            {
                var rooms = await _roomRepository.GetAllRoomsAsync(request);

                return new APIResponse<List<RoomDetailsResponseDTO>>(rooms, "Retrieved all Room Successfully.");
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error Retriving all Room");
                return new APIResponse<List<RoomDetailsResponseDTO>>(HttpStatusCode.InternalServerError, "Internal server error: " + ex.Message);
            }
        }

        [HttpGet("{id}")]
        public async Task<APIResponse<RoomDetailsResponseDTO>> GetRoomById(int id)
        {
            _logger.LogInformation($"Request Received for GetRoomById, id: {id}");
            try
            {
                var response = await _roomRepository.GetRoomByIdAsync(id);

                if (response == null)
                {
                    return new APIResponse<RoomDetailsResponseDTO>(HttpStatusCode.NotFound, "Room ID not found.");
                }

                return new APIResponse<RoomDetailsResponseDTO>(response, "Room fetched successfully.");
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error getting Room by ID {id}", id);
                return new APIResponse<RoomDetailsResponseDTO>(HttpStatusCode.BadRequest, "Error fetching Room.", ex.Message);
            }
        }

        [HttpPost("Create")]
        public async Task<APIResponse<CreateRoomResponseDTO>> CreateRoom([FromBody] CreateRoomRequestDTO request)
        {
            _logger.LogInformation("Request Received for CreateRoom: {@CreateRoomRequestDTO}", request);
            if (!ModelState.IsValid)
            {
                _logger.LogInformation("Invalid Data in the Request Body");
                return new APIResponse<CreateRoomResponseDTO>(HttpStatusCode.BadRequest, "Invalid Data in the Requrest Body");
            }

            try
            {
                var response = await _roomRepository.CreateRoomAsync(request);

                _logger.LogInformation("CreateRoom Response From Repository: {@CreateRoomResponseDTO}", response);

                if (response.IsCreated)
                {
                    return new APIResponse<CreateRoomResponseDTO>(response, response.Message);
                }
                return new APIResponse<CreateRoomResponseDTO>(HttpStatusCode.BadRequest, response.Message);

            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error adding new Room");
                return new APIResponse<CreateRoomResponseDTO>(HttpStatusCode.InternalServerError, "Room Creation Failed.", ex.Message);
            }
        }

        [HttpPut("Update/{id}")]
        public async Task<APIResponse<UpdateRoomResponseDTO>> UpdateRoom(int id, [FromBody] UpdateRoomRequestDTO request)
        {
            _logger.LogInformation("Request Received for UpdateRoom {@UpdateRoomRequestDTO}", request);
            if (!ModelState.IsValid)
            {
                _logger.LogInformation("UpdateRoom Invalid Request Body");
                return new APIResponse<UpdateRoomResponseDTO>(HttpStatusCode.BadRequest, "Invalid Request Body");
            }
            if (id != request.RoomID)
            {
                _logger.LogInformation("UpdateRoom Mismatched Room ID");
                return new APIResponse<UpdateRoomResponseDTO>(HttpStatusCode.BadRequest, "Mismatched Room ID.");
            }

            try
            {
                var response = await _roomRepository.UpdateRoomAsync(request);
                if (response.IsUpdated)
                {
                    return new APIResponse<UpdateRoomResponseDTO>(response, response.Message);
                }
                return new APIResponse<UpdateRoomResponseDTO>(HttpStatusCode.BadRequest, response.Message);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error Updating Room {id}", id);
                return new APIResponse<UpdateRoomResponseDTO>(HttpStatusCode.InternalServerError, "Update Room Failed.", ex.Message);
            }
        }

        [HttpDelete("Delete/{id}")]
        public async Task<APIResponse<DeleteRoomResponseDTO>> DeleteRoom(int id)
        {
            _logger.LogInformation($"Request Received for DeleteRoom, id: {id}");
            try
            {
                var room = await _roomRepository.GetRoomByIdAsync(id);
                if (room == null)
                {
                    return new APIResponse<DeleteRoomResponseDTO>(HttpStatusCode.NotFound, "Room not found.");
                }

                var response = await _roomRepository.DeleteRoomAsync(id);
                if (response.IsDeleted)
                {
                    return new APIResponse<DeleteRoomResponseDTO>(response, response.Message);
                }
                return new APIResponse<DeleteRoomResponseDTO>(HttpStatusCode.BadRequest, response.Message);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error deleting Room {id}", id);
                return new APIResponse<DeleteRoomResponseDTO>(HttpStatusCode.InternalServerError, "Internal server error: " + ex.Message);
            }
        }
    }
}

Explanation of the Controller Action Methods:

GetAllRooms

Retrieve a list of all rooms with optional filters applied (e.g., by room type or status).

  • Input Parameter: GetAllRoomsRequestDTO from the query string, which may include parameters like RoomTypeID and Status.
  • Output Parameter: An APIResponse<List<RoomDetailsResponseDTO>> which contains a list of room details or an error message.
  • Method Details: This method logs the incoming request, checks the model state, retrieves the list of rooms matching the criteria from the repository, and, depending on the operation’s outcome, returns either the room list or an appropriate error message.
GetRoomById

Fetch detailed information about a specific room identified by its ID.

  • Input Parameter: Room ID as a URL path parameter.
  • Output Parameter: An APIResponse<RoomDetailsResponseDTO> containing details of a specific room or an error message if the room is not found or another error occurs.
  • Method Details: The method logs the request, attempts to retrieve the room by ID from the repository, and returns the room details if found. If the room isn’t found, it returns a not found error. Otherwise, it handles and logs any exceptions that occur.
CreateRoom

Create a new room based on the provided details.

  • Input Parameter: CreateRoomRequestDTO from the request body containing all necessary details to create a room.
  • Output Parameter: An APIResponse<CreateRoomResponseDTO> that indicates whether the room was successfully created, along with a message detailing the outcome.
  • Method Details: After logging the request and validating the model state, this method calls the repository to create a room and logs the response. It returns a response indicating whether the room creation succeeded or failed.
UpdateRoom

Update existing room details based on the provided information.

  • Input Parameter: Room ID as URL path parameter and UpdateRoomRequestDTO from the request body containing updated room details.
  • Output Parameter: An APIResponse<UpdateRoomResponseDTO> indicating the success or failure of the update.
  • Method Details: This method validates the model state and ensures that the room ID in the URL matches the ID in the request body. It then updates the room via the repository and returns a response reflecting the outcome.
DeleteRoom

Delete a specific room identified by its ID.

  • Input Parameter: Room ID as a URL path parameter.
  • Output Parameter: An APIResponse<DeleteRoomResponseDTO> indicating whether the room was successfully deleted.
  • Method Details: This method first confirms the room’s existence. If the room exists, it proceeds with deletion. The repository’s response will determine whether the deletion was successful, and this outcome is then relayed back to the client.
Testing User Functionalities:

Now, run the application and test each functionality; it should work as expected.

In this article, I explain the Room Module of the Hotel Booking Application. I hope you enjoy this implementation of the Room Amenities Module of a 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 Room Amenities Module of the Hotel Booking Application.

Leave a Reply

Your email address will not be published. Required fields are marked *