Amenities Module of Hotel Booking Application

Back to: ASP.NET Core Web API Tutorials

Implementing Amenities Module of Hotel Booking Application

In this article, I will discuss implementing the Hotel Booking Application’s Amenities Module in our ASP.NET Core Web API Application. Please read our previous article discussing How to Implement the Room Module in our Hotel Booking Application.

How to Implement Amenities Module of Hotel Booking Application

Let us implement the Amenities Module. Let us create the Stored Procedures, DTOs, a Repository class, and an Amenity Controller for adding, updating, and deleting Amenities, Fetching the Amenities Details by ID, Fetching all Amenities Details with Status, etc. Also, let us implement the Bulk Insert, Update, and Delete functionalities.

Create Stored Procedures for Amenities Management

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

-- Description: Fetches amenities based on their active status.
-- If @IsActive is provided, it returns amenities filtered by the active status.
CREATE OR ALTER PROCEDURE spFetchAmenities
    @IsActive BIT = NULL,
    @Status BIT OUTPUT,
    @Message NVARCHAR(255) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY

        -- Retrieve all amenities or filter by active status based on the input parameter.
        IF @IsActive IS NULL
            SELECT * FROM Amenities;
        ELSE
            SELECT * FROM Amenities WHERE IsActive = @IsActive;

        -- Return success status and message.
        SET @Status = 1; -- Success
        SET @Message = 'Data retrieved successfully.';

    END TRY
    BEGIN CATCH
        -- Handle errors and return failure status.
        SET @Status = 0; -- Failure
        SET @Message = ERROR_MESSAGE();
    END CATCH;
END;
GO

-- Description: Fetches a specific amenity based on its ID.
-- Returns the details of the amenity if it exists.
CREATE OR ALTER PROCEDURE spFetchAmenityByID
    @AmenityID INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT AmenityID, Name, Description, IsActive FROM Amenities 
    WHERE AmenityID = @AmenityID;
END;
GO

-- Description: Inserts a new amenity into the Amenities table.
-- Prevents duplicates based on the amenity name.
CREATE OR ALTER PROCEDURE spAddAmenity
    @Name NVARCHAR(100),
    @Description NVARCHAR(255),
    @CreatedBy NVARCHAR(100),
    @AmenityID INT OUTPUT,
    @Status BIT OUTPUT,
    @Message NVARCHAR(255) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION
            -- Check if an amenity with the same name already exists to avoid duplication.
            IF EXISTS (SELECT 1 FROM Amenities WHERE Name = @Name)
            BEGIN
                SET @Status = 0;
                SET @Message = 'Amenity already exists.';
            END
            ELSE
            BEGIN
                -- Insert the new amenity record.
                INSERT INTO Amenities (Name, Description, CreatedBy, CreatedDate, IsActive)
                VALUES (@Name, @Description, @CreatedBy, GETDATE(), 1);

                -- Retrieve the ID of the newly inserted amenity.
                SET @AmenityID = SCOPE_IDENTITY();
                SET @Status = 1;
                SET @Message = 'Amenity added successfully.';
            END
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        SET @Status = 0;
        SET @Message = ERROR_MESSAGE();
    END CATCH;
END;
GO

-- Description: Updates an existing amenity's details in the Amenities table.
-- Checks if the amenity exists before attempting an update.
CREATE OR ALTER PROCEDURE spUpdateAmenity
    @AmenityID INT,
    @Name NVARCHAR(100),
    @Description NVARCHAR(255),
    @IsActive BIT,
    @ModifiedBy NVARCHAR(100),
    @Status BIT OUTPUT,
    @Message NVARCHAR(255) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION
            -- Check if the amenity exists before updating.
            IF NOT EXISTS (SELECT 1 FROM Amenities WHERE AmenityID = @AmenityID)
            BEGIN
                SET @Status = 0;
                SET @Message = 'Amenity does not exist.';
                ROLLBACK TRANSACTION;
                RETURN;
            END

            -- Check for name uniqueness excluding the current amenity.
            IF EXISTS (SELECT 1 FROM Amenities WHERE Name = @Name AND AmenityID <> @AmenityID)
            BEGIN
                SET @Status = 0;
                SET @Message = 'The name already exists for another amenity.';
                ROLLBACK TRANSACTION;
                RETURN;
            END

            -- Update the amenity details.
            UPDATE Amenities
            SET Name = @Name, Description = @Description, IsActive = @IsActive, ModifiedBy = @ModifiedBy, ModifiedDate = GETDATE()
            WHERE AmenityID = @AmenityID;

            -- Check if the update was successful
            IF @@ROWCOUNT = 0
            BEGIN
                SET @Status = 0;
                SET @Message = 'No records updated.';
                ROLLBACK TRANSACTION;
            END
            ELSE
            BEGIN
                SET @Status = 1;
                SET @Message = 'Amenity updated successfully.';
                COMMIT TRANSACTION;
            END
    END TRY
    BEGIN CATCH
        -- Handle exceptions and roll back the transaction if an error occurs.
        ROLLBACK TRANSACTION;
        SET @Status = 0;
        SET @Message = ERROR_MESSAGE();
    END CATCH;
END;
GO

-- Description: Soft deletes an amenity by setting its IsActive flag to 0.
-- Checks if the amenity exists before marking it as inactive.
CREATE OR ALTER PROCEDURE spDeleteAmenity
    @AmenityID INT,
    @Status BIT OUTPUT,
    @Message NVARCHAR(255) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION
            -- Check if the amenity exists before attempting to delete.
            IF NOT EXISTS (SELECT 1 FROM Amenities WHERE AmenityID = @AmenityID)
            BEGIN
                SET @Status = 0;
                SET @Message = 'Amenity does not exist.';
            END
            ELSE
            BEGIN
                -- Update the IsActive flag to 0 to soft delete the amenity.
                UPDATE Amenities
                SET IsActive = 0
                WHERE AmenityID = @AmenityID;

                SET @Status = 1;
                SET @Message = 'Amenity deleted successfully.';
            END
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        -- Roll back the transaction if an error occurs.
        ROLLBACK TRANSACTION;
        SET @Status = 0;
        SET @Message = ERROR_MESSAGE();
    END CATCH;
END;
GO

-- Creating a User-Defined Table Type for Bulk Insert
CREATE TYPE AmenityInsertType AS TABLE (
    Name NVARCHAR(100),
    Description NVARCHAR(255),
    CreatedBy NVARCHAR(100)
);
GO

-- Description: Performs a bulk insert of amenities into the Amenities table.
-- Ensures that no duplicate names are inserted.
CREATE OR ALTER PROCEDURE spBulkInsertAmenities
    @Amenities AmenityInsertType READONLY,
    @Status BIT OUTPUT,
    @Message NVARCHAR(255) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION
            -- Check for duplicate names within the insert dataset.
            IF EXISTS (
                SELECT 1
                FROM @Amenities
                GROUP BY Name
                HAVING COUNT(*) > 1
            )
            BEGIN
                SET @Status = 0;
                SET @Message = 'Duplicate names found within the new data.';
                ROLLBACK TRANSACTION;
                RETURN;
            END

            -- Check for existing names in the Amenities table that might conflict with the new data.
            IF EXISTS (
                SELECT 1
                FROM @Amenities a
                WHERE EXISTS (
                    SELECT 1 FROM Amenities WHERE Name = a.Name
                )
            )
            BEGIN
                SET @Status = 0;
                SET @Message = 'One or more names conflict with existing records.';
                ROLLBACK TRANSACTION;
                RETURN;
            END

            -- Insert new amenities ensuring there are no duplicates by name.
            INSERT INTO Amenities (Name, Description, CreatedBy, CreatedDate, IsActive)
            SELECT Name, Description, CreatedBy, GETDATE(), 1
            FROM @Amenities;

            -- Check if any records were actually inserted.
            IF @@ROWCOUNT = 0
            BEGIN
                SET @Status = 0;
                SET @Message = 'No records inserted. Please check the input data.';
                ROLLBACK TRANSACTION;
            END
            ELSE
            BEGIN
                SET @Status = 1;
                SET @Message = 'Bulk insert completed successfully.';
                COMMIT TRANSACTION;
            END
    END TRY
    BEGIN CATCH
        -- Handle any errors that occur during the transaction.
        ROLLBACK TRANSACTION;
        SET @Status = 0;
        SET @Message = ERROR_MESSAGE();
    END CATCH;
END;
GO

-- Creating User-Defined Table Type for Bulk Update
CREATE TYPE AmenityUpdateType AS TABLE (
    AmenityID INT,
    Name NVARCHAR(100),
    Description NVARCHAR(255),
    IsActive BIT
);
GO

-- Description: Updates multiple amenities in the Amenities table using a provided list.
-- Applies updates to the Name, Description, and IsActive status.
CREATE OR ALTER PROCEDURE spBulkUpdateAmenities
    @AmenityUpdates AmenityUpdateType READONLY,
    @Status BIT OUTPUT,
    @Message NVARCHAR(255) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION
            -- Check for duplicate names within the update dataset.
            IF EXISTS (
                SELECT 1
                FROM @AmenityUpdates u
                GROUP BY u.Name
                HAVING COUNT(*) > 1
            )
            BEGIN
                SET @Status = 0;
                SET @Message = 'Duplicate names found within the update data.';
                ROLLBACK TRANSACTION;
                RETURN;
            END

            -- Check for duplicate names in existing data.
            IF EXISTS (
                SELECT 1
                FROM @AmenityUpdates u
                JOIN Amenities a ON u.Name = a.Name AND u.AmenityID != a.AmenityID
            )
            BEGIN
                SET @Status = 0;
                SET @Message = 'One or more names conflict with existing records.';
                ROLLBACK TRANSACTION;
                RETURN;
            END

            -- Update amenities based on the provided data.
            UPDATE a
            SET a.Name = u.Name,
                a.Description = u.Description,
                a.IsActive = u.IsActive
            FROM Amenities a
            INNER JOIN @AmenityUpdates u ON a.AmenityID = u.AmenityID;

            -- Check if any records were actually updated.
            IF @@ROWCOUNT = 0
            BEGIN
                SET @Status = 0;
                SET @Message = 'No records updated. Please check the input data.';
            END
            ELSE
            BEGIN
                SET @Status = 1;
                SET @Message = 'Bulk update completed successfully.';
            END
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        -- Roll back the transaction and handle the error.
        ROLLBACK TRANSACTION;
        SET @Status = 0;
        SET @Message = ERROR_MESSAGE();
    END CATCH;
END;
GO

-- Creating a User-Defined Table Type for Bulk Active and InActive
CREATE TYPE AmenityStatusType AS TABLE (
    AmenityID INT,
    IsActive BIT
);
GO

-- Description: Updates the active status of multiple amenities in the Amenities table.
-- Takes a list of amenity IDs and their new IsActive status.
CREATE OR ALTER PROCEDURE spBulkUpdateAmenityStatus
    @AmenityStatuses AmenityStatusType READONLY,
    @Status BIT OUTPUT,
    @Message NVARCHAR(255) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION
            -- Update the IsActive status for amenities based on the provided AmenityID.
            UPDATE a
            SET a.IsActive = s.IsActive
            FROM Amenities a
            INNER JOIN @AmenityStatuses s ON a.AmenityID = s.AmenityID;

            -- Check if any records were actually updated.
            SET @Status = 1; -- Success
            SET @Message = 'Bulk status update completed successfully.';
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        -- Roll back the transaction if an error occurs.
        ROLLBACK TRANSACTION;
        SET @Status = 0; -- Failure
        SET @Message = ERROR_MESSAGE();
    END CATCH;
END;
GO

Here are the objectives and detailed explanations for each of the stored procedures and user-defined table types we created above, which are designed to manage the Amenities table effectively.

User-Defined Table Types

AmenityInsertType: Facilitates bulk insert operations by allowing multiple amenity records to be passed into stored procedures as a single parameter. The following are the Fields:

  • Name: Stores the name of the amenity.
  • Description: Stores a description of the amenity.
  • CreatedBy: Stores the identifier of the user who created the amenity record.

AmenityUpdateType: Enables bulk update operations by permitting multiple amenity updates to be passed at once. This type supports operations that modify several records simultaneously based on their ID. The following are the Fields:

  • AmenityID: The unique identifier for each amenity that needs to be updated.
  • Name: The new name to update.
  • Description: The new description is to be updated.
  • IsActive: The new active status needs to be updated.

AmenityStatusType: Designed to update the active status (IsActive) of multiple amenities in a single operation. The following are the Fields:

  • AmenityID: The unique identifier for each amenity.
  • IsActive: The boolean status indicates whether the amenity is active or inactive.
Stored Procedures
  • spFetchAmenities: Retrieves all amenities or filters them based on their active status. Returns either all amenities or a subset of amenities based on the specified IsActive status.
  • spFetchAmenityByID: Retrieves the details of a specific amenity by its ID to ensure that accurate, targeted information is provided upon request. Check for the existence of the amenity using its ID and, if found, return its details.
  • spAddAmenity: Inserts a new amenity into the database while checking for duplicate names to ensure uniqueness. The procedure checks if an amenity with the same name exists. If it does, it aborts the insertion; otherwise, it adds the new amenity.
  • spUpdateAmenity: Updates the details of an existing amenity, such as its name, description, and active status, after verifying that it exists. Before updating, it checks if the amenity exists using its ID. If the amenity is found, it updates the specified fields.
  • spDeleteAmenity: Soft-deletes an amenity by setting its IsActive flag to 0 rather than deleting it from the database after ensuring it exists. Check for the existence of the amenity by its ID and, if found, update its IsActive status to 0.
  • spBulkInsertAmenities: Performs a bulk insertion of amenities while preventing the insertion of duplicates based on the amenity name. The procedure iterates through the provided list of amenities and inserts each one that does not already exist in the database based on its name.
  • spBulkUpdateAmenities: Updates multiple amenities at once using a list of amenity details provided through a user-defined table type. The procedure updates the name, description, and active status of each amenity in the list based on its ID.
  • spBulkUpdateAmenityStatus: Updates the active status of multiple amenities based on a list that includes each amenity’s ID and the desired active status. The procedure updates the IsActive field for each amenity in the provided list.

Creating Amenity DTOs:

Next, we need to create the DTOs required for performing operations on the Amenity database table. Please create a folder called AmenityDTOs inside the DTOs folder, where we will add all the DTOs related to managing Amenity.

AmenityInsertDTO

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

using System.ComponentModel.DataAnnotations;

namespace HotelBookingAPI.DTOs.AmenityDTOs
{
    public class AmenityInsertDTO
    {
        [Required]
        [StringLength(100, ErrorMessage = "Name length can't be more than 100 characters.")]
        public string Name { get; set; }

        [StringLength(255, ErrorMessage = "Description length can't be more than 255 characters.")]
        public string Description { get; set; }
    }
}
AmenityInsertResponseDTO

Create a class file named AmenityInsertResponseDTO.cs within the AmenityDTOs folder, and then copy and paste the following code. Once the Amenity is created, this DTO will only include the properties we send to the client.

namespace HotelBookingAPI.DTOs.AmenityDTOs
{
    public class AmenityInsertResponseDTO
    {
        public int AmenityID { get; set; }
        public string Message { get; set; }
        public bool IsCreated { get; set; }
    }
}
AmenityUpdateDTO

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

using System.ComponentModel.DataAnnotations;

namespace HotelBookingAPI.DTOs.AmenityDTOs
{
    public class AmenityUpdateDTO
    {
        [Required]
        public int AmenityID { get; set; }

        [Required]
        [StringLength(100, ErrorMessage = "Name length can't be more than 100 characters.")]
        public string Name { get; set; }

        [StringLength(255, ErrorMessage = "Description length can't be more than 255 characters.")]
        public string Description { get; set; }

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

Create a class file named AmenityUpdateResponseDTO.cs within the AmenityDTOs folder, and then copy and paste the following code. Once the Amenity is updated, this DTO will only include the properties we send to the client.

namespace HotelBookingAPI.DTOs.AmenityDTOs
{
    public class AmenityUpdateResponseDTO
    {
        public int AmenityID { get; set; }
        public string Message { get; set; }
        public bool IsUpdated { get; set; }
    }
}
AmenityDeleteResponseDTO

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

namespace HotelBookingAPI.DTOs.AmenityDTOs
{
    public class AmenityDeleteResponseDTO
    {
        public string Message { get; set; }
        public bool IsDeleted { get; set; }
    }
}
AmenityDetailsDTO

Create a class file named AmenityDetailsDTO.cs within the AmenityDTOs folder, and then copy and paste the following code. This DTO will only include the properties required to return a single Amenity.

namespace HotelBookingAPI.DTOs.AmenityDTOs
{
    public class AmenityDetailsDTO
    {
        public int AmenityID { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
        public bool IsActive { get; set; }
    }
}
AmenityFetchResultDTO

Create a class file named AmenityFetchResultDTO.cs within the AmenityDTOs folder, and then copy and paste the following code. This DTO will only include the properties required to return multiple Amenities.

namespace HotelBookingAPI.DTOs.AmenityDTOs
{
    public class AmenityFetchResultDTO
    {
        public IEnumerable<AmenityDetailsDTO> Amenities { get; set; }
        public string Message { get; set; }
        public bool IsSuccess { get; set; }
    }
}
AmenityBulkOperationResultDTO

Create a class file named AmenityBulkOperationResultDTO.cs within the AmenityDTOs folder, and then copy and paste the following code. This DTO will only include the properties required to be returned when performing bulk operations.

namespace HotelBookingAPI.DTOs.AmenityDTOs
{
    public class AmenityBulkOperationResultDTO
    {
        public string Message { get; set; }
        public bool IsSuccess { get; set; }
    }
}
AmenityStatusDTO

Create a class file named AmenityStatusDTO.cs within the AmenityDTOs folder, and then copy and paste the following code. This DTO will only include the properties required to perform the Bulk Status Update Operation.

namespace HotelBookingAPI.DTOs.AmenityDTOs
{
    public class AmenityStatusDTO
    {
        public int AmenityID { get; set; }  
        public bool IsActive { get; set; }
    }
}
Creating Amenity Repository:

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

using HotelBookingAPI.Connection;
using HotelBookingAPI.DTOs.AmenityDTOs;
using Microsoft.Data.SqlClient;
using System.Data;

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

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

        public async Task<AmenityFetchResultDTO> FetchAmenitiesAsync(bool? isActive)
        {
            using var connection = _connectionFactory.CreateConnection();
            using var command = new SqlCommand("spFetchAmenities", connection);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@IsActive", (object)isActive ?? DBNull.Value);
            var statusCode = new SqlParameter("@Status", SqlDbType.Int) { Direction = ParameterDirection.Output };
            var message = new SqlParameter("@Message", SqlDbType.NVarChar, 255) { Direction = ParameterDirection.Output };
            command.Parameters.Add(statusCode);
            command.Parameters.Add(message);

            await connection.OpenAsync();

            var amenities = new List<AmenityDetailsDTO>();

            using (SqlDataReader reader = await command.ExecuteReaderAsync())
            {
                while (await reader.ReadAsync())
                {
                    amenities.Add(new AmenityDetailsDTO
                    {
                        AmenityID = reader.GetInt32(reader.GetOrdinal("AmenityID")),
                        Name = reader.GetString(reader.GetOrdinal("Name")),
                        Description = reader.GetString(reader.GetOrdinal("Description")),
                        IsActive = reader.GetBoolean(reader.GetOrdinal("IsActive"))
                    });
                }
            }

            // Important: Access output parameters after closing the reader, else you will get NULL

            return new AmenityFetchResultDTO
            {
                Amenities = amenities,
                IsSuccess = Convert.ToBoolean(statusCode.Value),
                Message = message.Value.ToString()
            };
        }

        public async Task<AmenityDetailsDTO> FetchAmenityByIdAsync(int amenityId)
        {
            using var connection = _connectionFactory.CreateConnection();
            using var command = new SqlCommand("spFetchAmenityByID", connection);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@AmenityID", amenityId);

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

            if (await reader.ReadAsync())
            {
                return new AmenityDetailsDTO
                {
                    AmenityID = reader.GetInt32(reader.GetOrdinal("AmenityID")),
                    Name = reader.GetString(reader.GetOrdinal("Name")),
                    Description = reader.GetString(reader.GetOrdinal("Description")),
                    IsActive = reader.GetBoolean(reader.GetOrdinal("IsActive"))
                };
            }
            else
            {
                return null;
            }
        }

        public async Task<AmenityInsertResponseDTO> AddAmenityAsync(AmenityInsertDTO amenity)
        {
            AmenityInsertResponseDTO amenityInsertResponseDTO = new AmenityInsertResponseDTO();
            using var connection = _connectionFactory.CreateConnection();
            using var command = new SqlCommand("spAddAmenity", connection);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@Name", amenity.Name);
            command.Parameters.AddWithValue("@Description", amenity.Description);
            command.Parameters.AddWithValue("@CreatedBy", "System");
            command.Parameters.Add("@AmenityID", SqlDbType.Int).Direction = ParameterDirection.Output;
            command.Parameters.Add("@Status", SqlDbType.Bit).Direction = ParameterDirection.Output;
            command.Parameters.Add("@Message", SqlDbType.NVarChar, 255).Direction = ParameterDirection.Output;

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

                if (Convert.ToBoolean(command.Parameters["@Status"].Value))
                {
                    amenityInsertResponseDTO.Message = Convert.ToString(command.Parameters["@Message"].Value);
                    amenityInsertResponseDTO.IsCreated = true;
                    amenityInsertResponseDTO.AmenityID = Convert.ToInt32(command.Parameters["@AmenityID"].Value);
                    return amenityInsertResponseDTO;
                }

                amenityInsertResponseDTO.Message = Convert.ToString(command.Parameters["@Message"].Value);
                amenityInsertResponseDTO.IsCreated = false;

                return amenityInsertResponseDTO;
            }
            catch (SqlException ex)
            {
                amenityInsertResponseDTO.Message = ex.Message;
                amenityInsertResponseDTO.IsCreated = false;
                return amenityInsertResponseDTO;
            }
        }

        public async Task<AmenityUpdateResponseDTO> UpdateAmenityAsync(AmenityUpdateDTO amenity)
        {
            AmenityUpdateResponseDTO amenityUpdateResponseDTO = new AmenityUpdateResponseDTO()
            {
                AmenityID = amenity.AmenityID
            };
            using var connection = _connectionFactory.CreateConnection();
            using var command = new SqlCommand("spUpdateAmenity", connection);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@AmenityID", amenity.AmenityID);
            command.Parameters.AddWithValue("@Name", amenity.Name);
            command.Parameters.AddWithValue("@Description", amenity.Description);
            command.Parameters.AddWithValue("@IsActive", amenity.IsActive);
            command.Parameters.AddWithValue("@ModifiedBy", "System"); // Assume modified by system or pass a real user
            command.Parameters.Add("@Status", SqlDbType.Bit).Direction = ParameterDirection.Output;
            command.Parameters.Add("@Message", SqlDbType.NVarChar, 255).Direction = ParameterDirection.Output;

            try
            {
                await connection.OpenAsync();
                await command.ExecuteNonQueryAsync();
                amenityUpdateResponseDTO.Message = command.Parameters["@Message"].Value.ToString();
                amenityUpdateResponseDTO.IsUpdated = Convert.ToBoolean(command.Parameters["@Status"].Value);

                return amenityUpdateResponseDTO;
            }
            catch (SqlException ex)
            {
                amenityUpdateResponseDTO.Message = ex.Message;
                amenityUpdateResponseDTO.IsUpdated = false;

                return amenityUpdateResponseDTO;
            }
        }
        public async Task<AmenityDeleteResponseDTO> DeleteAmenityAsync(int amenityId)
        {
            using var connection = _connectionFactory.CreateConnection();
            using var command = new SqlCommand("spDeleteAmenity", connection);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@AmenityID", amenityId);
            command.Parameters.Add("@Status", SqlDbType.Bit).Direction = ParameterDirection.Output;
            command.Parameters.Add("@Message", SqlDbType.NVarChar, 255).Direction = ParameterDirection.Output;

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

            return new AmenityDeleteResponseDTO
            {
                IsDeleted = Convert.ToBoolean(command.Parameters["@Status"].Value),
                Message = command.Parameters["@Message"].Value.ToString()
            };
        }
        
        public async Task<AmenityBulkOperationResultDTO> BulkInsertAmenitiesAsync(List<AmenityInsertDTO> amenities)
        {
            using var connection = _connectionFactory.CreateConnection();
            using var command = new SqlCommand("spBulkInsertAmenities", connection);
            command.CommandType = CommandType.StoredProcedure;
            var amenitiesTable = new DataTable();
            amenitiesTable.Columns.Add("Name", typeof(string));
            amenitiesTable.Columns.Add("Description", typeof(string));
            amenitiesTable.Columns.Add("CreatedBy", typeof(string));

            foreach (var amenity in amenities)
            {
                amenitiesTable.Rows.Add(amenity.Name, amenity.Description, "System");
            }

            var param = command.Parameters.AddWithValue("@Amenities", amenitiesTable);
            param.SqlDbType = SqlDbType.Structured;
            command.Parameters.Add("@Status", SqlDbType.Bit).Direction = ParameterDirection.Output;
            command.Parameters.Add("@Message", SqlDbType.NVarChar, 255).Direction = ParameterDirection.Output;

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

            return new AmenityBulkOperationResultDTO
            {
                IsSuccess = Convert.ToBoolean(command.Parameters["@Status"].Value),
                Message = command.Parameters["@Message"].Value.ToString()
            };
        }
        
        public async Task<AmenityBulkOperationResultDTO> BulkUpdateAmenitiesAsync(List<AmenityUpdateDTO> amenities)
        {
            using var connection = _connectionFactory.CreateConnection();
            using var command = new SqlCommand("spBulkUpdateAmenities", connection);
            command.CommandType = CommandType.StoredProcedure;
            var amenitiesTable = new DataTable();
            amenitiesTable.Columns.Add("AmenityID", typeof(int));
            amenitiesTable.Columns.Add("Name", typeof(string));
            amenitiesTable.Columns.Add("Description", typeof(string));
            amenitiesTable.Columns.Add("IsActive", typeof(bool));

            foreach (var amenity in amenities)
            {
                amenitiesTable.Rows.Add(amenity.AmenityID, amenity.Name, amenity.Description, amenity.IsActive);
            }

            var param = command.Parameters.AddWithValue("@AmenityUpdates", amenitiesTable);
            param.SqlDbType = SqlDbType.Structured;
            command.Parameters.Add("@Status", SqlDbType.Bit).Direction = ParameterDirection.Output;
            command.Parameters.Add("@Message", SqlDbType.NVarChar, 255).Direction = ParameterDirection.Output;

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

            return new AmenityBulkOperationResultDTO
            {
                IsSuccess = Convert.ToBoolean(command.Parameters["@Status"].Value),
                Message = command.Parameters["@Message"].Value.ToString()
            };
        }

        public async Task<AmenityBulkOperationResultDTO> BulkUpdateAmenityStatusAsync(List<AmenityStatusDTO> amenityStatuses)
        {
            using var connection = _connectionFactory.CreateConnection();
            using var command = new SqlCommand("spBulkUpdateAmenityStatus", connection);
            command.CommandType = CommandType.StoredProcedure;
            var amenityStatusTable = new DataTable();
            amenityStatusTable.Columns.Add("AmenityID", typeof(int));
            amenityStatusTable.Columns.Add("IsActive", typeof(bool));

            foreach (var status in amenityStatuses)
            {
                amenityStatusTable.Rows.Add(status.AmenityID, status.IsActive);
            }

            var param = command.Parameters.AddWithValue("@AmenityStatuses", amenityStatusTable);
            param.SqlDbType = SqlDbType.Structured;
            command.Parameters.Add("@Status", SqlDbType.Bit).Direction = ParameterDirection.Output;
            command.Parameters.Add("@Message", SqlDbType.NVarChar, 255).Direction = ParameterDirection.Output;

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

            return new AmenityBulkOperationResultDTO
            {
                IsSuccess = Convert.ToBoolean(command.Parameters["@Status"].Value),
                Message = command.Parameters["@Message"].Value.ToString()
            };
        }
    }
}
Explanation of Each Method:
  • FetchAmenitiesAsync(bool? isActive): This method retrieves a list of amenities based on their active status (either all, active, or inactive) from the database. It uses the spFetchAmenities stored procedure, which filters the amenities by the IsActive parameter. It returns detailed information for each amenity, including name, description, and active status.
  • FetchAmenityByIdAsync(int amenityId): Fetch details of a specific amenity identified by its ID. This method uses the spFetchAmenityByID stored procedure to retrieve details of a single amenity, making it useful for viewing or editing specific amenity details in a detailed view or form in the application.
  • AddAmenityAsync(AmenityInsertDTO amenity): This method adds a new amenity to the database. It calls the spAddAmenity stored procedure and inserts an amenity with details provided by the user. Then, it returns whether the addition was successful, along with any relevant messages.
  • UpdateAmenityAsync(AmenityUpdateDTO amenity): This method updates the details of an existing amenity. It invokes the spUpdateAmenity stored procedure, updating fields such as the name, description, active status, and last modified details. It ensures data integrity by only updating if a specific amenity exists and returns the outcome of the update operation.
  • DeleteAmenityAsync(int amenityId): This method removes an amenity from the database based on its ID. It uses the spDeleteAmenity stored procedure, which is typically used to manage amenities that are no longer needed or valid. This procedure provides status and messages about the deletion operation.
  • BulkInsertAmenitiesAsync(List<AmenityInsertDTO> amenities): Perform a bulk insert of multiple amenities into the database. This method efficiently handles inserting multiple records at once, which is useful for initializing the database with a batch of data or importing amenities from external sources.
  • BulkUpdateAmenitiesAsync(List<AmenityUpdateDTO> amenities): Execute a bulk update on a list of amenities. This method updates multiple amenities based on their IDs and provides new details. It’s useful for batch processing changes, such as batch updates from an admin panel or syncing with external systems.
  • BulkUpdateAmenityStatusAsync(List<AmenityStatusDTO> amenityStatuses): Specifically updates the active status for a batch of amenities. This focuses on changing the IsActive status in bulk, which can be used to activate or deactivate multiple amenities based on seasonal requirements or operational changes.
Register Amenity Repository:

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

Creating Amenity Controller:

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

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

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

        public AmenityController(AmenityRepository amenityRepository, ILogger<AmenityController> logger)
        {
            _amenityRepository = amenityRepository;
            _logger = logger;
        }

        [HttpGet("Fetch")]
        public async Task<APIResponse<AmenityFetchResultDTO>> FetchAmenities(bool? isActive = null)
        {
            try
            {
                var response = await _amenityRepository.FetchAmenitiesAsync(isActive);
                if (response.IsSuccess)
                {
                    return new APIResponse<AmenityFetchResultDTO>(response, "Retrieved all Room Amenity Successfully.");
                }

                return new APIResponse<AmenityFetchResultDTO>(HttpStatusCode.BadRequest, response.Message);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error occurred while fetching amenities.");
                return new APIResponse<AmenityFetchResultDTO>(HttpStatusCode.InternalServerError, "An error occurred while processing your request.", ex.Message);
            }
        }

        [HttpGet("Fetch/{id}")]
        public async Task<APIResponse<AmenityDetailsDTO>> FetchAmenityById(int id)
        {
            try
            {
                var response = await _amenityRepository.FetchAmenityByIdAsync(id);
                if (response != null)
                {
                    return new APIResponse<AmenityDetailsDTO>(response, "Retrieved Room Amenity Successfully.");
                }

                return new APIResponse<AmenityDetailsDTO>(HttpStatusCode.NotFound, "Amenity ID not found.");
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error occurred while fetching amenity by ID.");
                return new APIResponse<AmenityDetailsDTO>(HttpStatusCode.InternalServerError, "An error occurred while processing your request.", ex.Message);
            }
        }

        [HttpPost("Add")]
        public async Task<APIResponse<AmenityInsertResponseDTO>> AddAmenity([FromBody] AmenityInsertDTO amenity)
        {
            try
            {
                if (!ModelState.IsValid)
                {
                    return new APIResponse<AmenityInsertResponseDTO>(HttpStatusCode.BadRequest, "Invalid Data in the Requrest Body");
                }

                var response = await _amenityRepository.AddAmenityAsync(amenity);
                if (response.IsCreated)
                {
                    return new APIResponse<AmenityInsertResponseDTO>(response, response.Message);
                }
                return new APIResponse<AmenityInsertResponseDTO>(HttpStatusCode.BadRequest, response.Message);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error occurred while adding amenity.");
                return new APIResponse<AmenityInsertResponseDTO>(HttpStatusCode.InternalServerError, "Amenity Creation Failed.", ex.Message);
            }
        }

        [HttpPut("Update/{id}")]
        public async Task<APIResponse<AmenityUpdateResponseDTO>> UpdateAmenity(int id, [FromBody] AmenityUpdateDTO amenity)
        {
            try
            {
                if (id != amenity.AmenityID)
                {
                    _logger.LogInformation("UpdateRoom Mismatched Amenity ID");
                    return new APIResponse<AmenityUpdateResponseDTO>(HttpStatusCode.BadRequest, "Mismatched Amenity ID.");
                }

                if (!ModelState.IsValid)
                {
                    return new APIResponse<AmenityUpdateResponseDTO>(HttpStatusCode.BadRequest, "Invalid Request Body");
                }

                var response = await _amenityRepository.UpdateAmenityAsync(amenity);
                if (response.IsUpdated)
                {
                    return new APIResponse<AmenityUpdateResponseDTO>(response, response.Message);
                }
                return new APIResponse<AmenityUpdateResponseDTO>(HttpStatusCode.BadRequest, response.Message);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error occurred while updating amenity.");
                return new APIResponse<AmenityUpdateResponseDTO>(HttpStatusCode.InternalServerError, "An error occurred while processing your request.", ex.Message);
            }
        }

        [HttpDelete("Delete/{id}")]
        public async Task<APIResponse<AmenityDeleteResponseDTO>> DeleteAmenity(int id)
        {
            try
            {
                var amenity = await _amenityRepository.FetchAmenityByIdAsync(id);
                if (amenity == null)
                {
                    return new APIResponse<AmenityDeleteResponseDTO>(HttpStatusCode.NotFound, "Amenity not found.");
                }

                var response = await _amenityRepository.DeleteAmenityAsync(id);
                if (response.IsDeleted)
                {
                    return new APIResponse<AmenityDeleteResponseDTO>(response, response.Message);
                }
                return new APIResponse<AmenityDeleteResponseDTO>(HttpStatusCode.BadRequest, response.Message);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error occurred while deleting amenity.");
                return new APIResponse<AmenityDeleteResponseDTO>(HttpStatusCode.InternalServerError, "An error occurred while processing your request.", ex.Message);
            }
        }

        [HttpPost("BulkInsert")]
        public async Task<APIResponse<AmenityBulkOperationResultDTO>> BulkInsertAmenities(List<AmenityInsertDTO> amenities)
        {
            try
            {
                var response = await _amenityRepository.BulkInsertAmenitiesAsync(amenities);
                if (response.IsSuccess)
                {
                    return new APIResponse<AmenityBulkOperationResultDTO>(response, response.Message);
                }

                return new APIResponse<AmenityBulkOperationResultDTO>(HttpStatusCode.BadRequest, response.Message);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error occurred while bulk inserting amenities.");
                return new APIResponse<AmenityBulkOperationResultDTO>(HttpStatusCode.InternalServerError, "An error occurred while processing your request.", ex.Message);
            }
        }

        [HttpPost("BulkUpdate")]
        public async Task<APIResponse<AmenityBulkOperationResultDTO>> BulkUpdateAmenities(List<AmenityUpdateDTO> amenities)
        {
            try
            {
                if (!ModelState.IsValid)
                {
                    return new APIResponse<AmenityBulkOperationResultDTO>(HttpStatusCode.BadRequest, "Invalid Data in the Request Body");
                }

                var response = await _amenityRepository.BulkUpdateAmenitiesAsync(amenities);
                if (response.IsSuccess)
                {
                    return new APIResponse<AmenityBulkOperationResultDTO>(response, response.Message);
                }

                return new APIResponse<AmenityBulkOperationResultDTO>(HttpStatusCode.BadRequest, response.Message);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error occurred while bulk updating amenities.");
                return new APIResponse<AmenityBulkOperationResultDTO>(HttpStatusCode.InternalServerError, "An error occurred while processing your request.", ex.Message);
            }
        }

        [HttpPut("BulkUpdateStatus")]
        public async Task<APIResponse<AmenityBulkOperationResultDTO>> BulkUpdateAmenityStatus(List<AmenityStatusDTO> amenityStatuses)
        {
            try
            {
                if (!ModelState.IsValid)
                {
                    return new APIResponse<AmenityBulkOperationResultDTO>(HttpStatusCode.BadRequest, "Invalid Data in the Request Body");
                }

                var response = await _amenityRepository.BulkUpdateAmenityStatusAsync(amenityStatuses);
                if (response.IsSuccess)
                {
                    return new APIResponse<AmenityBulkOperationResultDTO>(response, response.Message);
                }

                return new APIResponse<AmenityBulkOperationResultDTO>(HttpStatusCode.BadRequest, response.Message);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error occurred while bulk updating amenity status.");
                return new APIResponse<AmenityBulkOperationResultDTO>(HttpStatusCode.InternalServerError, "An error occurred while processing your request.", ex.Message);
            }
        }
    }
}
Explanation of the Controller Action Methods:
  • FetchAmenities(bool? isActive = null): Retrieves a list of all amenities, optionally filtered by their active status. This method is useful for displaying amenities in the UI, where the user might want to filter amenities based on whether they are active or not.
  • FetchAmenityById(int id): This endpoint fetches details of a single amenity by its ID. It is essential for detail views where specific information about an amenity is needed, such as in detail pages or edit forms.
  • AddAmenity(AmenityInsertDTO amenity): This method adds a new amenity to the system. It handles POST requests, where the necessary details for creating a new amenity are encapsulated in the AmenityInsertDTO. This method is crucial for functionality, as it allows users to add new amenities through the application interface.
  • UpdateAmenity(int id, AmenityUpdateDTO amenity): This endpoint updates an existing amenity and allows modifications to it, including details like name, description, and active status. It’s typically accessed through an edit form in the UI.
  • DeleteAmenity(int id): This function deletes an amenity from the system based on its ID. It is important to remove amenities that are no longer needed or relevant to ensure that the system remains clean and efficient.
  • BulkInsertAmenities(List<AmenityInsertDTO> amenities): Handles the bulk insertion of amenities. This method is useful for scenarios where multiple amenities need to be added at once, such as initializing the system with a batch of data or importing amenities from another source.
  • BulkUpdateAmenities(List<AmenityUpdateDTO> amenities): Performs bulk updates on a list of amenities. This endpoint is beneficial for making widespread changes to amenities, such as updating their descriptions or active statuses in a single operation.
  • BulkUpdateAmenityStatus(List<AmenityStatusDTO> amenityStatuses): Specifically updates the active status of multiple amenities. This function is especially useful for batch operations, such as activating or deactivating amenities based on seasonal requirements or operational needs.

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