Back to: ASP.NET Core Web API Tutorials
Implementing the Hotel Booking Module
In this article, I will discuss implementing the Hotel Booking Module, i.e., the Reservation and Payment Module, in our Application using our ASP.NET Core Web API Application. Please read our previous article on how to Implement the Hotel Search Module in our Hotel Booking Application.
Let us implement the Hotel Booking Module, i.e., implementing the Reservation and Payment Module effectively in our application. Let us create the Stored Procedures, DTOs, a Repository class, and the ReservationController for effectively managing the Hotel Booking functionalities such as calculating the Cost, creating a Reservation, adding Guests to the Reservation, processing the Payment, and updating the Payment Status.
Database Schema Changes for Effective Booking:
As I already told you from the beginning, as we progress in this Hotel Application Development, we might come to a situation where we will change the database schema. In order to manage the reservations efficiently, especially when dealing with multiple rooms per reservation, we need to create a separate table. We will create a new table that will store the Reservation ID, with multiple entries for RoomId. Let us proceed and first make the changes.
Reservations Table:
Remove the RoomID and NumberOfGuests fields from the Reservations table, as we need to support multiple rooms per reservation, and the number of guests will be detailed in the linked table. Add fields to manage the overall status and total cost of the reservation, accommodating multiple rooms.
Step 1: Identify and Drop the Foreign Key Constraint:
Before deleting the foreign key column RoomID, first, we need to delete the foreign key constraint. So, execute the following script to identify if there are any constraints created for the RoomId column.
-- This SQL command retrieves the name of the foreign key constraint related to RoomID in the Reservations table
SELECT
CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
TABLE_NAME = 'Reservations'
AND CONSTRAINT_TYPE = 'FOREIGN KEY';
Once you execute the above query, find the Constraint name, then execute the following command to drop the constraint. Please replace the constraint with the name you are getting. Suppose the constraint name is found to be FK__Reservati__RoomI__6383C8BA’, then you drop it using:
ALTER TABLE Reservations DROP CONSTRAINT FK__Reservati__RoomI__6383C8BA;
Step 2: Drop the RoomID and NumberOfGuests Column:
You can now safely drop the columns once the foreign key constraint is removed. So, please execute the following SQL Script on our Hotel DB database to remove the RoomId and NumberOfGuests columns from the Reservation table:
ALTER TABLE Reservations DROP COLUMN RoomID, NumberOfGuests;
Step 3: Adding TotalCost and NumberOfNights Columns to the Reservation table
Now, we want to store the Number Of Nights and the Total Cost of the Reservation in the table. Please execute the following script to add the columns.
ALTER TABLE Reservations
ADD TotalCost DECIMAL(10,2),
NumberOfNights INT;
Step 4: Create the ReservationRooms Table:
Now, create the new ReservationRooms table that will manage the relationship between reservations and rooms. This table will store the ReservationID and the corresponding RoomIds, i.e., managing the One-to-Many Relationships between the table Reservations and Rooms table. So, please execute the following SQL Script in our HotelDB database to create the ReservationRooms table.
-- New Table for Reservation-Rooms linkage
CREATE TABLE ReservationRooms (
ReservationRoomID INT PRIMARY KEY IDENTITY(1,1),
ReservationID INT,
RoomID INT,
CheckInDate DATE,
CheckOutDate DATE,
FOREIGN KEY (ReservationID) REFERENCES Reservations(ReservationID),
FOREIGN KEY (RoomID) REFERENCES Rooms(RoomID),
CONSTRAINT CHK_ResRoomDates CHECK (CheckOutDate > CheckInDate)
);
GO
Step 5: Linking Guests to Specific Rooms in a Reservation
Now, to track not only which reservation a guest is part of but also which specific room they are staying in, we need to link the ReservationGuests table to the ReservationRooms table.
-- First, if the table already exists, delete it:
IF OBJECT_ID('ReservationGuests', 'U') IS NOT NULL
DROP TABLE ReservationGuests;
GO
-- Create the ReservationGuests table
CREATE TABLE ReservationGuests (
ReservationGuestID INT PRIMARY KEY IDENTITY(1,1),
ReservationRoomID INT, -- Linking directly to the ReservationRooms table
GuestID INT,
FOREIGN KEY (ReservationRoomID) REFERENCES ReservationRooms(ReservationRoomID),
FOREIGN KEY (GuestID) REFERENCES Guests(GuestID)
);
Step 6: Modifying the Payments and Removing BatchPayments and Adding Payment Details table
Now, we need to redesign the Payments table.
- Payments Table: This table will hold the Amount, GST, and Total Amount of each payment made linked directly to a reservation. It is useful for managing the overall payment status of a reservation.
- PaymentDetails Table: This table stores detailed payment information for each room within a reservation. Each entry relates to a payment and specifies which room the portion of the payment applies to.
So, please execute the following Script to drop and create the tables. As the tables have relationships with other tables, so, we need to drop and recreate them as follows:
-- Please delete the Existing Refund, PaymentBatches and Payment tables. We are also going to create the tables with different names.
DROP Table Refunds;
DROP TABLE Payments;
DROP TABLE PaymentBatches;
-- Create the Payment table with the following structure.
CREATE TABLE Payments (
PaymentID INT PRIMARY KEY IDENTITY(1,1),
ReservationID INT,
Amount DECIMAL(10,2),
GST DECIMAL(10,2),
TotalAmount DECIMAL(10,2),
PaymentDate DATETIME DEFAULT GETDATE(),
PaymentMethod NVARCHAR(50),
PaymentStatus NVARCHAR(50) DEFAULT 'Pending' CHECK (PaymentStatus IN ('Pending', 'Completed', 'Failed', 'Refunded')),
FailureReason NVARCHAR(MAX),
FOREIGN KEY (ReservationID) REFERENCES Reservations(ReservationID)
);
GO
-- Create the PaymentDetails table with the following structure.
CREATE TABLE PaymentDetails (
PaymentDetailID INT PRIMARY KEY IDENTITY(1,1),
PaymentID INT,
ReservationRoomID INT,
Amount DECIMAL(10,2), -- Base Amount
NumberOfNights INT,
GST DECIMAL(10,2), -- GST Based on the Base Amount
TotalAmount DECIMAL(10,2), -- (Amount * NumberOfNights) + GST
FOREIGN KEY (PaymentID) REFERENCES Payments(PaymentID),
FOREIGN KEY (ReservationRoomID) REFERENCES ReservationRooms(ReservationRoomID)
);
GO
-- Table for tracking Refunds. We are not changing this table
CREATE TABLE Refunds (
RefundID INT PRIMARY KEY IDENTITY(1,1),
PaymentID INT,
RefundAmount DECIMAL(10,2),
RefundDate DATETIME DEFAULT GETDATE(),
RefundReason NVARCHAR(255),
RefundMethodID INT,
ProcessedByUserID INT,
RefundStatus NVARCHAR(50),
FOREIGN KEY (PaymentID) REFERENCES Payments(PaymentID),
FOREIGN KEY (RefundMethodID) REFERENCES RefundMethods(MethodID),
FOREIGN KEY (ProcessedByUserID) REFERENCES Users(UserID)
);
GO
Using the Payment Status:
With the PaymentStatus column, we can easily manage and update the status of each payment. For example:
- When a payment is initially recorded, it’s marked as ‘Pending’.
- Once the payment is confirmed (e.g., the funds have cleared or the credit card transaction is approved), we need to update the status to ‘Completed’.
- If there are issues with the payment (e.g., a declined credit card or a failed transfer), the status might be set to ‘Failed’.
- In cases where a payment is refunded, updating the status to ‘Refunded’ helps keep the financial records consistent.
Note: With the above Schema Changes in place, please make sure your existing functionalities are not affected by testing all the endpoints that we have created so far. For example, the Hotel Search by Availability is affected. So, modify the following Stored Procedure as follows.
Modifying spSearchByAvailability Stored Procedure:
-- 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 rooms that are not currently booked for the given date range and not under maintenance
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 ReservationRooms rr ON rr.RoomID = r.RoomID
LEFT JOIN Reservations res ON rr.ReservationID = res.ReservationID
AND res.Status NOT IN ('Cancelled')
AND (
(res.CheckInDate <= @CheckOutDate AND res.CheckOutDate >= @CheckInDate)
)
WHERE res.ReservationID IS NULL AND r.Status = 'Available' AND r.IsActive = 1
END;
GO
Creating the Stored Procedures:
We will start by creating the necessary stored procedures in SQL Server to Manage the Hotel Booking Functionalities in the database. Please execute the following Script on the HotelDB database we are working with so far to create the required Table type and Stored Procedures to manage the Payment effectively.
Create a User-Defined Table Type
-- First, we need to create a user-defined table type that can be used as a parameter for our stored procedure. This will take multiple Room IDs CREATE TYPE RoomIDTableType AS TABLE (RoomID INT); GO
SQL Procedure to Calculate Total Room Cost and Provide Cost Breakup:
-- This stored procedure will calculate and return the Total Cost and Room wise Cost Breakup
CREATE OR ALTER PROCEDURE spCalculateRoomCosts
@RoomIDs RoomIDTableType READONLY,
@CheckInDate DATE,
@CheckOutDate DATE,
@Amount DECIMAL(10, 2) OUTPUT, -- Base total cost before tax
@GST DECIMAL(10, 2) OUTPUT, -- GST amount based on 18%
@TotalAmount DECIMAL(10, 2) OUTPUT -- Total cost including GST
AS
BEGIN
SET NOCOUNT ON;
-- Calculate the number of nights based on CheckInDate and CheckOutDate
DECLARE @NumberOfNights INT = DATEDIFF(DAY, @CheckInDate, @CheckOutDate);
IF @NumberOfNights <= 0
BEGIN
SET @Amount = 0;
SET @GST = 0;
SET @TotalAmount = 0;
RETURN; -- Exit if the number of nights is zero or negative, which shouldn't happen
END
-- Select Individual Rooms Price details
SELECT
r.RoomID,
r.RoomNumber,
r.Price AS RoomPrice,
@NumberOfNights AS NumberOfNights,
r.Price * @NumberOfNights AS TotalPrice
FROM
Rooms r
INNER JOIN
@RoomIDs ri ON r.RoomID = ri.RoomID;
-- Calculate total cost (base amount) from the rooms identified by RoomIDs multiplied by NumberOfNights
SELECT @Amount = SUM(Price * @NumberOfNights) FROM Rooms
WHERE RoomID IN (SELECT RoomID FROM @RoomIDs);
-- Calculate GST as 18% of the Amount
SET @GST = @Amount * 0.18;
-- Calculate Total Amount as Amount plus GST
SET @TotalAmount = @Amount + @GST;
END;
GO
Explanation of the Stored Procedure
The procedure accepts the room IDs and calculates the total booking cost and a detailed room-wise breakdown.
- Returning Cost Breakdown: The stored procedure selects and returns the RoomID, RoomNumber, and Cost directly as a result set.
- @Amount Output Parameter: This now represents the total base cost calculated by summing the prices of the selected rooms based on the RoomIDs provided.
- @GST Output Parameter: This is calculated as 18% of the @Amount. The calculation is straightforward, using the constant rate of 18%, which is a common GST rate for many services and goods in India.
- @TotalAmount Output Parameter: This combines the base amount (@Amount) with the calculated GST (@GST) to reflect the total amount that would be charged, including taxes.
Stored Procedure for Creating a New Reservation:
-- Stored Procedure for Creating a New Reservation
-- This stored procedure will ensure that both the user exists and the selected rooms are available before creating a reservation
CREATE OR ALTER PROCEDURE spCreateReservation
@UserID INT,
@RoomIDs RoomIDTableType READONLY, -- Using the table-valued parameter
@CheckInDate DATE,
@CheckOutDate DATE,
@CreatedBy NVARCHAR(100),
@Message NVARCHAR(255) OUTPUT,
@Status BIT OUTPUT,
@ReservationID INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON; -- Automatically roll-back the transaction on error.
BEGIN TRY
BEGIN TRANSACTION
-- Check if the user exists
IF NOT EXISTS (SELECT 1 FROM Users WHERE UserID = @UserID AND IsActive = 1)
BEGIN
SET @Message = 'User does not exist or inactive.';
SET @Status = 0; -- 0 means Failed
RETURN;
END
-- Check if all rooms are available
IF EXISTS (SELECT 1 FROM Rooms WHERE RoomID IN (SELECT RoomID FROM @RoomIDs) AND Status <> 'Available')
BEGIN
SET @Message = 'One or more rooms are not available.';
SET @Status = 0; -- 0 means Failed
RETURN;
END
-- Calculate the number of nights between CheckInDate and CheckOutDate
DECLARE @NumberOfNights INT = DATEDIFF(DAY, @CheckInDate, @CheckOutDate);
IF @NumberOfNights <= 0
BEGIN
SET @Message = 'Check-out date must be later than check-in date.';
SET @Status = 0; -- 0 means Failed
RETURN;
END
-- Calculate the base cost of the rooms for the number of nights and add GST
DECLARE @BaseCost DECIMAL(10, 2);
SELECT @BaseCost = SUM(Price * @NumberOfNights) FROM Rooms
WHERE RoomID IN (SELECT RoomID FROM @RoomIDs);
-- Calculate Total Amount including 18% GST
DECLARE @TotalAmount DECIMAL(10, 2) = @BaseCost * 1.18;
-- Create the Reservation
INSERT INTO Reservations (UserID, BookingDate, CheckInDate, CheckOutDate, NumberOfNights, TotalCost, Status, CreatedBy, CreatedDate)
VALUES (@UserID, GETDATE(), @CheckInDate, @CheckOutDate, @NumberOfNights, @TotalAmount, 'Reserved', @CreatedBy, GETDATE());
SET @ReservationID = SCOPE_IDENTITY();
-- Assign rooms to the reservation and update room status
INSERT INTO ReservationRooms (ReservationID, RoomID, CheckInDate, CheckOutDate)
SELECT @ReservationID, RoomID, @CheckInDate, @CheckOutDate FROM @RoomIDs;
-- Update the status of the rooms to 'Occupied'
UPDATE Rooms
SET Status = 'Occupied'
WHERE RoomID IN (SELECT RoomID FROM @RoomIDs);
SET @Message = 'Reservation created successfully.';
SET @Status = 1; -- 1 means Success
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
SET @Message = ERROR_MESSAGE();
SET @Status = 0; -- 0 means Failed
END CATCH
END;
GO
Explanation of the Above Stored Procedure:
The spCreateReservation stored procedure is used to create a new reservation in a hotel management system, ensuring that several preconditions are met before proceeding.
- User and Room Validation: Before proceeding with the reservation, the procedure checks if the user exists and if all selected rooms are available. If any of these conditions fail, it sets an appropriate message and status and then exits.
- GST Calculation: The total cost is now calculated dynamically within the procedure based on the room rates fetched from the Rooms table. An 18% GST is applied to the base cost to derive the TotalAmount.
- Creating the Reservation: It inserts a new record into the Reservations table if validations pass.
- Room Assignment and Status Update: It assigns each room to the Reservation Rooms and updates the room status to ‘Occupied’ in the Rooms table.
- Output Parameters: It uses output parameters to return the operation’s status, a message describing the outcome, and the ID of the newly created reservation.
Stored Procedure for Handling Guests:
First, define a table type to pass multiple guests’ details to the stored procedure. This will allow us to handle multiple guest entries in a single call.
-- Designing the GuestDetailsTableType
CREATE TYPE GuestDetailsTableType AS TABLE (
FirstName NVARCHAR(50),
LastName NVARCHAR(50),
Email NVARCHAR(100),
Phone NVARCHAR(15),
AgeGroup NVARCHAR(50),
Address NVARCHAR(500),
CountryId INT,
StateId INT,
RoomID INT -- This will link the guest to a specific room in a reservation
);
GO
Create the Stored Procedure for Adding Guests
This stored procedure’s primary role is to add multiple guests to a reservation, linking each guest to a specific room in that reservation. This is done in a structured and efficient manner using a table-valued parameter (@GuestDetails) to pass guest information in bulk.
CREATE OR ALTER PROCEDURE spAddGuestsToReservation
@UserID INT,
@ReservationID INT,
@GuestDetails GuestDetailsTableType READONLY,
@Status BIT OUTPUT,
@Message NVARCHAR(255) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON; -- Automatically roll-back the transaction on error.
BEGIN TRY
BEGIN TRANSACTION
-- Validate the existence of the user
IF NOT EXISTS (SELECT 1 FROM Users WHERE UserID = @UserID AND IsActive = 1)
BEGIN
SET @Status = 0; -- Failure
SET @Message = 'User does not exist or inactive.';
RETURN;
END
-- Validate that all RoomIDs are part of the reservation
IF EXISTS (
SELECT 1 FROM @GuestDetails gd
WHERE NOT EXISTS (
SELECT 1 FROM ReservationRooms rr
WHERE rr.ReservationID = @ReservationID AND rr.RoomID = gd.RoomID
)
)
BEGIN
SET @Status = 0; -- Failure
SET @Message = 'One or more RoomIDs are not valid for this reservation.';
RETURN;
END
-- Create a temporary table to store Guest IDs with ReservationRoomID
CREATE TABLE #TempGuests
(
TempID INT IDENTITY(1,1),
GuestID INT,
ReservationRoomID INT
);
-- Insert guests into Guests table and retrieve IDs
INSERT INTO Guests (UserID, FirstName, LastName, Email, Phone, AgeGroup, Address, CountryID, StateID, CreatedBy, CreatedDate)
SELECT @UserID, gd.FirstName, gd.LastName, gd.Email, gd.Phone, gd.AgeGroup, gd.Address, gd.CountryId, gd.StateId, @UserID, GETDATE()
FROM @GuestDetails gd;
-- Capture the Guest IDs and the corresponding ReservationRoomID
INSERT INTO #TempGuests (GuestID, ReservationRoomID)
SELECT SCOPE_IDENTITY(), rr.ReservationRoomID
FROM @GuestDetails gd
JOIN ReservationRooms rr ON gd.RoomID = rr.RoomID AND rr.ReservationID = @ReservationID;
-- Link each new guest to a room in the reservation
INSERT INTO ReservationGuests (ReservationRoomID, GuestID)
SELECT ReservationRoomID, GuestID
FROM #TempGuests;
SET @Status = 1; -- Success
SET @Message = 'All guests added successfully.';
COMMIT TRANSACTION;
-- Cleanup the temporary table
DROP TABLE #TempGuests;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
SET @Status = 0; -- Failure
SET @Message = ERROR_MESSAGE();
-- Cleanup the temporary table in case of failure
IF OBJECT_ID('tempdb..#TempGuests') IS NOT NULL
DROP TABLE #TempGuests;
END CATCH
END;
GO
Explanation of the Stored Procedure:
The stored procedure spAddGuestsToReservation handles adding multiple guests to specific rooms under an existing reservation in a hotel management system.
- User Validation: This step ensures that the user adding guests (identified by @UserID) exists and is active within the system. It also ensures that only valid and authorized users can add guests.
- Room Validation: Verifies that each room ID provided within the guest details actually belongs to the specified reservation (@ReservationID). This prevents errors such as assigning guests to rooms that are not part of the reservation or do not exist within the hotel’s current booking context.
- Guest Insertion: Guests are inserted into the Guest table with all necessary details such as name, contact information, demographic data, and the creator’s user ID.
- ID Retrieval and Linking: Immediately after inserting each guest, their unique identifier (GuestID) is captured using SCOPE_IDENTITY() and associated with the respective room ID provided in the guest details. This association is stored temporarily in a local table #TempGuests for further processing.
- Temporary Data Management: A temporary table (#TempGuests) efficiently manages the mapping between new guest IDs and their assigned room IDs. This table is created and dropped within the procedure to ensure no data that might affect subsequent operations remains.
Process Payment Stored Procedure:
Let us create a stored procedure to process the payment. The following stored procedure is used to efficiently handle the payment processing for a hotel reservation system.
-- Stored Procedure for Processing the Payment
CREATE OR ALTER PROCEDURE spProcessPayment
@ReservationID INT,
@TotalAmount DECIMAL(10,2),
@PaymentMethod NVARCHAR(50),
@PaymentID INT OUTPUT,
@Status BIT OUTPUT,
@Message NVARCHAR(255) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON; -- Ensures that if an error occurs, all changes are rolled back
BEGIN TRY
BEGIN TRANSACTION
-- Validate that the reservation exists and the total cost matches
DECLARE @TotalCost DECIMAL(10,2);
DECLARE @NumberOfNights INT;
SELECT @TotalCost = TotalCost, @NumberOfNights = NumberOfNights
FROM Reservations
WHERE ReservationID = @ReservationID;
IF @TotalCost IS NULL
BEGIN
SET @Status = 0; -- Failure
SET @Message = 'Reservation does not exist.';
RETURN;
END
IF @TotalAmount <> @TotalCost
BEGIN
SET @Status = 0; -- Failure
SET @Message = 'Input total amount does not match the reservation total cost.';
RETURN;
END
-- Calculate Base Amount and GST, assuming GST as 18% for the Payments table
DECLARE @BaseAmount DECIMAL(10,2) = @TotalCost / 1.18;
DECLARE @GST DECIMAL(10,2) = @TotalCost - @BaseAmount;
-- Insert into Payments Table
INSERT INTO Payments (ReservationID, Amount, GST, TotalAmount, PaymentDate, PaymentMethod, PaymentStatus)
VALUES (@ReservationID, @BaseAmount, @GST, @TotalCost, GETDATE(), @PaymentMethod, 'Pending');
SET @PaymentID = SCOPE_IDENTITY(); -- Capture the new Payment ID
-- Insert into PaymentDetails table for each room with number of nights and calculated amounts
INSERT INTO PaymentDetails (PaymentID, ReservationRoomID, Amount, NumberOfNights, GST, TotalAmount)
SELECT @PaymentID, rr.ReservationRoomID, r.Price, @NumberOfNights, (r.Price * @NumberOfNights) * 0.18, r.Price * @NumberOfNights + (r.Price * @NumberOfNights) * 0.18
FROM ReservationRooms rr
JOIN Rooms r ON rr.RoomID = r.RoomID
WHERE rr.ReservationID = @ReservationID;
SET @Status = 1; -- Success
SET @Message = 'Payment Processed Successfully.';
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
SET @Status = 0; -- Failure
SET @Message = ERROR_MESSAGE();
END CATCH
END;
GO
Explanation of the Stored Procedure:
The Stored Procedure starts by verifying that the specified reservation exists and that the provided payment amount matches the total cost recorded for the reservation.
- Payments Table: The procedure records the payment information in the Payments table. This includes the reservation ID, the total amount being paid, and the payment method. It also sets the initial payment status to ‘Pending’. Based on the outcome of the actual payment process (which might involve external payment gateways in a real-world scenario), this status can later be updated to Completed or Failed.
- PaymentDetails Table: The PaymentDetails table also records detailed payment information. This includes room-specific costs at the time of the payment to ensure historical accuracy even if room prices change in the future.
- Output Parameter: The new payment ID generated by the Payments table insert is returned via the @PaymentID output parameter, allowing the calling application to track or reference the specific payment attempt.
Payment Status Update Stored Procedure:
The following stored procedure is used to update the payment status effectively. This Stored Procedure plays an important role in maintaining the integrity and accuracy of records in the database. The primary function of this procedure is to update the status of a payment record from ‘Pending’ to either Completed or Failed.
-- Stored Procedure for Updating the Payment Status
CREATE OR ALTER PROCEDURE spUpdatePaymentStatus
@PaymentID INT,
@NewStatus NVARCHAR(50), -- 'Completed' or 'Failed'
@FailureReason NVARCHAR(255) = NULL, -- Optional reason for failure
@Status BIT OUTPUT, -- Output to indicate success/failure of the procedure
@Message NVARCHAR(255) OUTPUT -- Output message detailing the result
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON; -- Ensure that if an error occurs, all changes are rolled back
BEGIN TRY
BEGIN TRANSACTION
-- Check if the payment exists and is in a 'Pending' status
DECLARE @CurrentStatus NVARCHAR(50);
SELECT @CurrentStatus = PaymentStatus FROM Payments WHERE PaymentID = @PaymentID;
IF @CurrentStatus IS NULL
BEGIN
SET @Status = 0; -- Failure
SET @Message = 'Payment record does not exist.';
RETURN;
END
IF @CurrentStatus <> 'Pending'
BEGIN
SET @Status = 0; -- Failure
SET @Message = 'Payment status is not Pending. Cannot update.';
RETURN;
END
-- Validate the new status
IF @NewStatus NOT IN ('Completed', 'Failed')
BEGIN
SET @Status = 0; -- Failure
SET @Message = 'Invalid status value. Only "Completed" or "Failed" are acceptable.';
RETURN;
END
-- Update the Payment Status
UPDATE Payments
SET PaymentStatus = @NewStatus,
FailureReason = CASE WHEN @NewStatus = 'Failed' THEN @FailureReason ELSE NULL END
WHERE PaymentID = @PaymentID;
-- If Payment Fails, update corresponding reservation and room statuses
IF @NewStatus = 'Failed'
BEGIN
DECLARE @ReservationID INT;
SELECT @ReservationID = ReservationID FROM Payments WHERE PaymentID = @PaymentID;
-- Update Reservation Status
UPDATE Reservations
SET Status = 'Cancelled'
WHERE ReservationID = @ReservationID;
-- Update Room Status
UPDATE Rooms
SET Status = 'Available'
FROM Rooms
JOIN ReservationRooms ON Rooms.RoomID = ReservationRooms.RoomID
WHERE ReservationRooms.ReservationID = @ReservationID;
END
SET @Status = 1; -- Success
SET @Message = 'Payment Status Updated Successfully.';
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
SET @Status = 0; -- Failure
SET @Message = ERROR_MESSAGE();
END CATCH
END;
GO
Explanation of the above Stored Procedure:
- Validation of Current Status: This ensures that the payment status can only be updated if it is currently in the Pending state. This prevents unintended or unauthorized changes to payments that have already been processed or marked as failed.
- Restricted Status Options: This option validates that the new status must be either ‘Completed’ or ‘Failed’, ensuring that the payment status transitions adhere to our business rules.
- Contextual Updates: If a payment status update is ‘Failed’, the procedure also updates the related reservation status to ‘Cancelled’ and changes the status of the associated rooms to Available. This ensures that resources (like hotel rooms) are managed efficiently and made available for rebooking when a payment fails.
- Failure Reason Logging: This option allows for recording an optional failure reason when the status is updated to ‘Failed’. This will provide valuable information about why payments are not successful, which is valuable for troubleshooting and improving the payment process.
Common for All Stored Procedures:
- Transaction Management: The entire operation of all stored procedures is wrapped in a SQL transaction. This ensures that all database operations related to the payment are completed successfully together or rolled back if any part fails, thus maintaining data integrity. The use of SET XACT_ABORT ON; guarantees that SQL Server will automatically roll back the transaction if an error occurs anywhere in the batch, preventing partial data writes.
- Error Handling: All the Stored procedures use TRY…CATCH block to handle exceptions. If an error occurs during the transaction, the transaction is rolled back, and the procedure outputs a failure status and an error message detailing what went wrong.
- Output Parameters: The Output Parameters communicate the operation’s status (@Status) and provide a user-friendly message (@Message) back to the calling application.
Creating Hotel Reservation and Payment DTOs:
Next, we need to create the DTOs required for performing Hotel Reservation and Payment operations. Please create a folder called BookingDTOs and PaymentDTOs inside the DTOs folder, where we will add all the DTO-related hotel Booking Functions.
RoomCostsDTO
Create a class file named RoomCostsDTO.cs within the BookingDTOs folder, and then copy and paste the following code. This DTO will hold the properties required to fetch the price details.
using HotelBookingAPI.CustomValidator;
using System.ComponentModel.DataAnnotations;
namespace HotelBookingAPI.DTOs.BookingDTOs
{
public class RoomCostsDTO
{
[Required]
public List<int> RoomIDs { get; set; }
[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; }
}
}
RoomCostsResponseDTO
Create a class file named RoomCostsResponseDTO.cs within the BookingDTOs folder, and then copy and paste the following code. This DTO will hold the properties required while returning the Price details.
namespace HotelBookingAPI.DTOs.BookingDTOs
{
public class RoomCostsResponseDTO
{
public List<RoomCostDetailDTO> RoomDetails { get; set; } = new List<RoomCostDetailDTO>();
public decimal Amount { get; set; } // Base total cost before tax
public decimal GST { get; set; } // GST amount based on 18%
public decimal TotalAmount { get; set; } // Total cost including GST
public bool Status { get; set; }
public string Message { get; set; }
}
public class RoomCostDetailDTO
{
public int RoomID { get; set; }
public string RoomNumber { get; set; }
public decimal RoomPrice { get; set; } // Cost for individual room
public int NumberOfNights { get; set; }
public decimal TotalPrice { get; set; } // Cost for individual room multiplied by Number of Nights
}
}
CreateReservationDTO
Create a class file named CreateReservationDTO.cs within the BookingDTOs folder, and then copy and paste the following code. This DTO will hold the properties required for Creating a New Reservation.
using HotelBookingAPI.CustomValidator;
using System.ComponentModel.DataAnnotations;
namespace HotelBookingAPI.DTOs.BookingDTOs
{
public class CreateReservationDTO
{
[Required]
public int UserID { get; set; }
[Required]
[MinLength(1, ErrorMessage = "At least one room ID must be provided.")]
public List<int> RoomIDs { get; set; } // Room IDs for the reservation
[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; }
}
}
CreateReservationResponseDTO
Create a class file named CreateReservationResponseDTO.cs within the BookingDTOs folder, and then copy and paste the following code. This DTO will hold the properties that we return once the Reservation is created.
namespace HotelBookingAPI.DTOs.BookingDTOs
{
public class CreateReservationResponseDTO
{
public int ReservationID { get; set; }
public bool Status { get; set; }
public string Message { get; set; }
}
}
AddGuestsToReservationDTO
Create a class file named AddGuestsToReservationDTO.cs within the BookingDTOs folder, and then copy and paste the following code. This DTO will hold the properties required to add multiple guests to the database.
using System.ComponentModel.DataAnnotations;
namespace HotelBookingAPI.DTOs.BookingDTOs
{
public class AddGuestsToReservationDTO
{
[Required]
public int UserID { get; set; }
[Required]
public int ReservationID { get; set; }
[Required]
[MinLength(1, ErrorMessage = "At least one guest detail must be provided.")]
public List<GuestDetail> GuestDetails { get; set; } // Guest details including room association
}
public class GuestDetail
{
[Required]
[StringLength(50)]
public string FirstName { get; set; }
[Required]
[StringLength(50)]
public string LastName { get; set; }
[EmailAddress]
public string Email { get; set; }
[Phone]
public string Phone { get; set; }
[Required]
public string AgeGroup { get; set; }
public string Address { get; set; }
[Required]
public int CountryId { get; set; }
[Required]
public int StateId { get; set; }
[Required]
public int RoomID { get; set; } // RoomID associated with each guest
}
}
AddGuestsToReservationResponseDTO
Create a class file named AddGuestsToReservationResponseDTO.cs within the BookingDTOs folder, and then copy and paste the following code. This DTO will hold the properties that we return once we add the guests.
namespace HotelBookingAPI.DTOs.BookingDTOs
{
public class AddGuestsToReservationResponseDTO
{
public bool Status { get; set; }
public string Message { get; set; }
}
}
ProcessPaymentDTO
Create a class file named ProcessPaymentDTO.cs within the PaymentDTOs folder, and then copy and paste the following code. This DTO will hold the properties required to process the payment.
using System.ComponentModel.DataAnnotations;
namespace HotelBookingAPI.DTOs.PaymentDTOs
{
public class ProcessPaymentDTO
{
[Required]
public int ReservationID { get; set; }
[Required]
[Range(0.01, double.MaxValue, ErrorMessage = "Total amount must be greater than zero.")]
public decimal TotalAmount { get; set; }
[Required]
[StringLength(50)]
public string PaymentMethod { get; set; }
}
}
ProcessPaymentResponseDTO
Create a class file named ProcessPaymentResponseDTO.cs within the PaymentDTOs folder and then copy and paste the following code. This DTO will hold the properties that we return once the Payment is processed.
namespace HotelBookingAPI.DTOs.PaymentDTOs
{
public class ProcessPaymentResponseDTO
{
public int PaymentID { get; set; }
public bool Status { get; set; }
public string Message { get; set; }
}
}
UpdatePaymentStatusDTO
Create a class file named UpdatePaymentStatusDTO.cs within the PaymentDTOs folder and then copy and paste the following code. This DTO will hold the properties required to update the payment.
using System.ComponentModel.DataAnnotations;
namespace HotelBookingAPI.DTOs.PaymentDTOs
{
public class UpdatePaymentStatusDTO
{
[Required]
public int PaymentID { get; set; }
[Required]
[RegularExpression("(Completed|Failed)", ErrorMessage = "Status must be either 'Completed' or 'Failed'.")]
public string NewStatus { get; set; } // 'Completed' or 'Failed'
public string FailureReason { get; set; }
}
}
UpdatePaymentStatusResponseDTO
Create a class file named UpdatePaymentStatusResponseDTO.cs within the PaymentDTOs folder and then copy and paste the following code. This DTO will hold the properties that we return once the Payment Status is updated.
namespace HotelBookingAPI.DTOs.PaymentDTOs
{
public class UpdatePaymentStatusResponseDTO
{
public bool Status { get; set; }
public string Message { get; set; }
}
}
Creating Hotel Reservation Repository:
Next, we need to create the Hotel Reservation Repository class to implement the business and data access logic required for Hotel Reservation and Payment Processing. This Repository class will consume the Hotel Booking and Payment-Related Stored Procedures and DTOs for the operations we have created so far. So, create a class file named ReservationRepository.cs within the Repository folder and copy and paste the following code.
using HotelBookingAPI.Connection;
using HotelBookingAPI.DTOs.BookingDTOs;
using HotelBookingAPI.DTOs.PaymentDTOs;
using Microsoft.Data.SqlClient;
using System.Data;
namespace HotelBookingAPI.Repository
{
public class ReservationRepository
{
private readonly SqlConnectionFactory _connectionFactory;
public ReservationRepository(SqlConnectionFactory connectionFactory)
{
_connectionFactory = connectionFactory;
}
public async Task<RoomCostsResponseDTO> CalculateRoomCostsAsync(RoomCostsDTO model)
{
RoomCostsResponseDTO roomCostsResponseDTO = new RoomCostsResponseDTO();
try
{
using var connection = _connectionFactory.CreateConnection();
using var command = new SqlCommand("spCalculateRoomCosts", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@CheckInDate", model.CheckInDate);
command.Parameters.AddWithValue("@CheckOutDate", model.CheckOutDate);
// Setting up the RoomIDs parameter
var table = new DataTable();
table.Columns.Add("RoomID", typeof(int));
model.RoomIDs.ForEach(id => table.Rows.Add(id));
command.Parameters.AddWithValue("@RoomIDs", table).SqlDbType = SqlDbType.Structured;
// Adding output parameters
command.Parameters.Add("@Amount", SqlDbType.Decimal).Direction = ParameterDirection.Output;
command.Parameters.Add("@GST", SqlDbType.Decimal).Direction = ParameterDirection.Output;
command.Parameters.Add("@TotalAmount", SqlDbType.Decimal).Direction = ParameterDirection.Output;
await connection.OpenAsync();
using var reader = await command.ExecuteReaderAsync();
// Populate room details
while (reader.Read())
{
roomCostsResponseDTO.RoomDetails.Add(new RoomCostDetailDTO
{
RoomID = reader.GetInt32(reader.GetOrdinal("RoomID")),
RoomNumber = reader.GetString(reader.GetOrdinal("RoomNumber")),
RoomPrice = reader.GetDecimal(reader.GetOrdinal("RoomPrice")),
TotalPrice = reader.GetDecimal(reader.GetOrdinal("TotalPrice")),
NumberOfNights = reader.GetInt32(reader.GetOrdinal("NumberOfNights"))
});
}
// Ensuring the reader is closed before accessing output parameters
await reader.CloseAsync();
// Access output parameters
roomCostsResponseDTO.Amount = (decimal)command.Parameters["@Amount"].Value;
roomCostsResponseDTO.GST = (decimal)command.Parameters["@GST"].Value;
roomCostsResponseDTO.TotalAmount = (decimal)command.Parameters["@TotalAmount"].Value;
roomCostsResponseDTO.Status = true;
roomCostsResponseDTO.Message = "Sucess";
}
catch (Exception ex)
{
// Log exception here
roomCostsResponseDTO.Status = false;
roomCostsResponseDTO.Message = ex.Message;
}
return roomCostsResponseDTO;
}
public async Task<CreateReservationResponseDTO> CreateReservationAsync(CreateReservationDTO reservation)
{
CreateReservationResponseDTO createReservationResponseDTO = new CreateReservationResponseDTO();
try
{
using var connection = _connectionFactory.CreateConnection();
using var command = new SqlCommand("spCreateReservation", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@UserID", reservation.UserID);
command.Parameters.AddWithValue("@CheckInDate", reservation.CheckInDate);
command.Parameters.AddWithValue("@CheckOutDate", reservation.CheckOutDate);
command.Parameters.AddWithValue("@CreatedBy", reservation.UserID);
var table = new DataTable();
table.Columns.Add("RoomID", typeof(int));
reservation.RoomIDs.ForEach(id => table.Rows.Add(id));
command.Parameters.AddWithValue("@RoomIDs", table).SqlDbType = SqlDbType.Structured;
command.Parameters.Add("@Message", SqlDbType.NVarChar, 255).Direction = ParameterDirection.Output;
command.Parameters.Add("@Status", SqlDbType.Bit).Direction = ParameterDirection.Output;
command.Parameters.Add("@ReservationID", SqlDbType.Int).Direction = ParameterDirection.Output;
await connection.OpenAsync();
await command.ExecuteNonQueryAsync();
createReservationResponseDTO.Message = command.Parameters["@Message"].Value.ToString();
createReservationResponseDTO.Status = (bool)command.Parameters["@Status"].Value;
createReservationResponseDTO.ReservationID = (int)command.Parameters["@ReservationID"].Value;
}
catch (Exception ex)
{
// Log exception here
createReservationResponseDTO.Message = ex.Message;
createReservationResponseDTO.Status = false;
}
return createReservationResponseDTO;
}
public async Task<AddGuestsToReservationResponseDTO> AddGuestsToReservationAsync(AddGuestsToReservationDTO details)
{
AddGuestsToReservationResponseDTO addGuestsToReservationResponseDTO = new AddGuestsToReservationResponseDTO();
try
{
using var connection = _connectionFactory.CreateConnection();
using var command = new SqlCommand("spAddGuestsToReservation", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@UserID", details.UserID);
command.Parameters.AddWithValue("@ReservationID", details.ReservationID);
var table = new DataTable();
table.Columns.Add("FirstName", typeof(string));
table.Columns.Add("LastName", typeof(string));
table.Columns.Add("Email", typeof(string));
table.Columns.Add("Phone", typeof(string));
table.Columns.Add("AgeGroup ", typeof(string));
table.Columns.Add("Address", typeof(string));
table.Columns.Add("CountryId", typeof(int));
table.Columns.Add("StateId", typeof(int));
table.Columns.Add("RoomID", typeof(int));
details.GuestDetails.ForEach(guest =>
{
table.Rows.Add(guest.FirstName, guest.LastName, guest.Email, guest.Phone,
guest.AgeGroup, guest.Address, guest.CountryId, guest.StateId, guest.RoomID);
});
command.Parameters.AddWithValue("@GuestDetails", table).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();
addGuestsToReservationResponseDTO.Status = (bool)command.Parameters["@Status"].Value;
addGuestsToReservationResponseDTO.Message = command.Parameters["@Message"].Value.ToString();
}
catch (Exception ex)
{
// Log exception here
addGuestsToReservationResponseDTO.Message = ex.Message;
addGuestsToReservationResponseDTO.Status = false;
}
return addGuestsToReservationResponseDTO;
}
public async Task<ProcessPaymentResponseDTO> ProcessPaymentAsync(ProcessPaymentDTO payment)
{
ProcessPaymentResponseDTO processPaymentResponseDTO = new ProcessPaymentResponseDTO();
try
{
using var connection = _connectionFactory.CreateConnection();
using var command = new SqlCommand("spProcessPayment", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@ReservationID", payment.ReservationID);
command.Parameters.AddWithValue("@TotalAmount", payment.TotalAmount);
command.Parameters.AddWithValue("@PaymentMethod", payment.PaymentMethod);
command.Parameters.Add("@PaymentID", SqlDbType.Int).Direction = ParameterDirection.Output;
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();
processPaymentResponseDTO.PaymentID = (int)command.Parameters["@PaymentID"].Value;
processPaymentResponseDTO.Status = (bool)command.Parameters["@Status"].Value;
processPaymentResponseDTO.Message = command.Parameters["@Message"].Value.ToString();
}
catch (Exception ex)
{
// Log exception here
processPaymentResponseDTO.Message = ex.Message;
processPaymentResponseDTO.Status = false;
}
return processPaymentResponseDTO;
}
public async Task<UpdatePaymentStatusResponseDTO> UpdatePaymentStatusAsync(UpdatePaymentStatusDTO statusUpdate)
{
UpdatePaymentStatusResponseDTO updatePaymentStatusResponseDTO = new UpdatePaymentStatusResponseDTO();
try
{
using var connection = _connectionFactory.CreateConnection();
using var command = new SqlCommand("spUpdatePaymentStatus", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@PaymentID", statusUpdate.PaymentID);
command.Parameters.AddWithValue("@NewStatus", statusUpdate.NewStatus);
command.Parameters.AddWithValue("@FailureReason", string.IsNullOrEmpty(statusUpdate.FailureReason) ? DBNull.Value : (object)statusUpdate.FailureReason);
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();
updatePaymentStatusResponseDTO.Status = (bool)command.Parameters["@Status"].Value;
updatePaymentStatusResponseDTO.Message = command.Parameters["@Message"].Value.ToString();
}
catch (Exception ex)
{
// Log exception here
updatePaymentStatusResponseDTO.Message = ex.Message;
updatePaymentStatusResponseDTO.Status = false;
}
return updatePaymentStatusResponseDTO;
}
}
}
Explanation of the Repository Methods:
Let us understand the objective of each Repository method:
- CalculateRoomCostsAsync: This method calculates the total costs associated with a room booking. It uses the stored procedure spCalculateRoomCosts to get detailed pricing information, including individual room details and aggregated costs such as the amount, GST, and total amount. It handles database interactions, executes the procedure, and maps the results back to a RoomCostsResponseDTO.
- CreateReservationAsync: This method creates a new reservation. It sends user and room details to the spCreateReservation stored procedure, which inserts data into reservation-related tables. The procedure returns output parameters, including the reservation status, a message, and the reservation ID, which are then encapsulated into the CreateReservationResponseDTO instance.
- AddGuestsToReservationAsync: This method adds guest details to an existing reservation. It populates the data table with guest information and sends it to the spAddGuestsToReservation stored procedure. The procedure adds the Guest’s details to the specified reservation and returns a status and a message indicating the operation’s success or failure.
- ProcessPaymentAsync: This method processes the payment associated with a reservation. It calls the spProcessPayment stored procedure, passing the reservation ID, total amount, and payment method. It retrieves the newly created payment ID, status, and message about the transaction’s success or failure, which it returns wrapped into the ProcessPaymentResponseDTO instance.
- UpdatePaymentStatusAsync: This method updates the status of a payment (e.g., from pending to completed or failed). It calls the spUpdatePaymentStatus stored procedure, which updates the payment record in the database based on the new status and optional failure reason provided. The method returns an updated status and message through the UpdatePaymentStatusResponseDTO instance.
Register Reservation Repository:
Next, we need to register the ReservationRepository 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<ReservationRepository>();
Creating Reservation Controller:
Let us create the Hotel Reservation Controller and use the methods defined in the above ReservationRepository class. So, create a new Empty API Controller named ReservationController within the Controllers folder and copy and paste the following code.
using HotelBookingAPI.DTOs.BookingDTOs;
using HotelBookingAPI.DTOs.PaymentDTOs;
using HotelBookingAPI.Models;
using HotelBookingAPI.Repository;
using Microsoft.AspNetCore.Mvc;
using System.Net;
namespace HotelBookingAPI.Controllers
{
[ApiController]
[Route("api/[controller]")]
public class ReservationController : ControllerBase
{
private readonly ReservationRepository _reservationRepository;
private readonly ILogger<ReservationController> _logger;
public ReservationController(ReservationRepository reservationRepository, ILogger<ReservationController> logger)
{
_reservationRepository = reservationRepository;
_logger = logger;
}
[HttpPost("CalculateRoomCosts")]
public async Task<APIResponse<RoomCostsResponseDTO>> CalculateRoomCosts([FromBody] RoomCostsDTO model)
{
_logger.LogInformation("Request Received for CalculateRoomCosts: {@RoomCostsDTO}", model);
if (!ModelState.IsValid)
{
_logger.LogInformation("Invalid Data in the Request Body");
return new APIResponse<RoomCostsResponseDTO>(HttpStatusCode.BadRequest, "Invalid Data in the Request Body");
}
try
{
var result = await _reservationRepository.CalculateRoomCostsAsync(model);
if(result.Status)
{
return new APIResponse<RoomCostsResponseDTO>(result, "Success");
}
return new APIResponse<RoomCostsResponseDTO>(HttpStatusCode.BadRequest, "Failed");
}
catch (Exception ex)
{
_logger.LogError(ex, "Failed to calculate room costs");
return new APIResponse<RoomCostsResponseDTO>(HttpStatusCode.InternalServerError, "Failed to calculate room costs", ex.Message);
}
}
[HttpPost("CreateReservation")]
public async Task<APIResponse<CreateReservationResponseDTO>> CreateReservation([FromBody] CreateReservationDTO reservation)
{
_logger.LogInformation("Request Received for CreateReservation: {@CreateReservationDTO}", reservation);
if (!ModelState.IsValid)
{
_logger.LogInformation("Invalid Data in the Request Body");
return new APIResponse<CreateReservationResponseDTO>(HttpStatusCode.BadRequest, "Invalid Data in the Request Body");
}
try
{
var result = await _reservationRepository.CreateReservationAsync(reservation);
if (result.Status)
{
return new APIResponse<CreateReservationResponseDTO>(result, result.Message);
}
return new APIResponse<CreateReservationResponseDTO>(HttpStatusCode.BadRequest, result.Message);
}
catch (Exception ex)
{
_logger.LogError(ex, "Failed to create reservation");
return new APIResponse<CreateReservationResponseDTO>(HttpStatusCode.InternalServerError, "Failed to create reservation", ex.Message);
}
}
[HttpPost("AddGuestsToReservation")]
public async Task<APIResponse<AddGuestsToReservationResponseDTO>> AddGuestsToReservation([FromBody] AddGuestsToReservationDTO details)
{
_logger.LogInformation("Request Received for AddGuestsToReservation: {@AddGuestsToReservationDTO}", details);
if (!ModelState.IsValid)
{
_logger.LogInformation("Invalid Data in the Request Body");
return new APIResponse<AddGuestsToReservationResponseDTO>(HttpStatusCode.BadRequest, "Invalid Data in the Request Body");
}
try
{
var result = await _reservationRepository.AddGuestsToReservationAsync(details);
if (result.Status)
{
return new APIResponse<AddGuestsToReservationResponseDTO>(result, result.Message);
}
return new APIResponse<AddGuestsToReservationResponseDTO>(HttpStatusCode.BadRequest, result.Message);
}
catch (Exception ex)
{
_logger.LogError(ex, "Failed to add guests to reservation");
return new APIResponse<AddGuestsToReservationResponseDTO>(HttpStatusCode.InternalServerError, "Failed to add guests to reservation", ex.Message);
}
}
[HttpPost("ProcessPayment")]
public async Task<APIResponse<ProcessPaymentResponseDTO>> ProcessPayment([FromBody] ProcessPaymentDTO payment)
{
_logger.LogInformation("Request Received for ProcessPayment: {@ProcessPaymentDTO}", payment);
if (!ModelState.IsValid)
{
_logger.LogInformation("Invalid Data in the Request Body");
return new APIResponse<ProcessPaymentResponseDTO>(HttpStatusCode.BadRequest, "Invalid Data in the Request Body");
}
try
{
var result = await _reservationRepository.ProcessPaymentAsync(payment);
if (result.Status)
{
return new APIResponse<ProcessPaymentResponseDTO>(result, result.Message);
}
return new APIResponse<ProcessPaymentResponseDTO>(HttpStatusCode.BadRequest, result.Message);
}
catch (Exception ex)
{
_logger.LogError(ex, "Failed to Process Payment");
return new APIResponse<ProcessPaymentResponseDTO>(HttpStatusCode.InternalServerError, "Failed to Process Payment", ex.Message);
}
}
[HttpPost("UpdatePaymentStatus")]
public async Task<APIResponse<UpdatePaymentStatusResponseDTO>> UpdatePaymentStatus([FromBody] UpdatePaymentStatusDTO statusUpdate)
{
_logger.LogInformation("Request Received for UpdatePaymentStatus: {@UpdatePaymentStatusDTO}", statusUpdate);
if (!ModelState.IsValid)
{
_logger.LogInformation("Invalid Data in the Request Body");
return new APIResponse<UpdatePaymentStatusResponseDTO>(HttpStatusCode.BadRequest, "Invalid Data in the Request Body");
}
try
{
var result = await _reservationRepository.UpdatePaymentStatusAsync(statusUpdate);
if (result.Status)
{
return new APIResponse<UpdatePaymentStatusResponseDTO>(result, result.Message);
}
return new APIResponse<UpdatePaymentStatusResponseDTO>(HttpStatusCode.BadRequest, result.Message);
}
catch (Exception ex)
{
_logger.LogError(ex, "Failed to update payment status");
return new APIResponse<UpdatePaymentStatusResponseDTO>(HttpStatusCode.InternalServerError, "Failed to update payment status", ex.Message);
}
}
}
}
Explanation of Controller Action Methods:
Let us understand the objective of each action method.
- CalculateRoomCosts: This API Endpoint calculates the total cost of a room booking based on input parameters such as room IDs, dates, and number of nights. This method logs the received request body, validates the input data, and then calls the Repository method to compute the costs. It handles success by returning the calculated costs, and in case of errors, it returns appropriate error messages.
- CreateReservation: This API Endpoint creates a new reservation based on the details provided in the request, including booking dates, room IDs, etc. The method logs the request, checks for data validity, and calls the repository method to create the reservation. It responds with the ReservationId if successful or appropriate error messages upon failure.
- AddGuestsToReservation: This API Endpoint is used to add guest details (who are actually going to stay in the hotel) to an existing reservation. This is done by providing details such as reservation ID and guest information. It logs the request, validates the input, and updates the reservation details using the repository if valid. It returns success or error messages based on the operation’s outcome.
- ProcessPayment: This API Endpoint is used to handle payment processing for a reservation. This includes validating payment details like amount, payment method, and reservation ID. The method logs the transaction details, checks for data accuracy, and processes the payment through the repository. Successful transactions or errors are communicated back to the client appropriately.
- UpdatePaymentStatus: This API Endpoint is used to update the status of a payment associated with a reservation. Depending on the transaction outcome, it could change the payment status to completed or failed. This method logs the update request, ensures data accuracy, and updates the payment status through the repository method, providing a response based on whether the update was successful or not.
Testing the Booking Functionalities:
Request Body for Price Calculation:
{
"RoomIDs": [
1,4
],
"CheckInDate": "2024-06-15",
"CheckOutDate": "2024-06-18"
}
Request Body for Hotel Reservation:
{
"UserID": 1,
"RoomIDs": [
1,4
],
"CheckInDate": "2024-06-15",
"CheckOutDate": "2024-06-18"
}
Note: This will return the Reservation ID, which we will use for further processing.
Request Body for Adding Guest Details:
{
"UserID": 1,
"ReservationID": 2,
"GuestDetails": [
{
"FirstName": "Pranaya",
"LastName": "Rout",
"Email": "Pranaya@example.com",
"Phone": "12345",
"AgeGroup": "Adult",
"Address": "Test",
"CountryId": 1,
"StateId": 2,
"RoomID": 1
},
{
"FirstName": "Ramesh",
"LastName": "Sethy",
"Email": "Ramesh@example.com",
"Phone": "12345",
"AgeGroup": "Infant",
"Address": "Test",
"CountryId": 1,
"StateId": 2,
"RoomID": 1
},
{
"FirstName": "Hina",
"LastName": "Sharma",
"Email": "Hina@example.com",
"Phone": "12345",
"AgeGroup": "Child",
"Address": "Test",
"CountryId": 1,
"StateId": 2,
"RoomID": 4
}
]
}
Request Body for Payment Processing:
{
"ReservationID": 1,
"TotalAmount": 1062,
"PaymentMethod": "CC"
}
Note: This will return the Payment ID, which we will use for further processing.
Request Body for Updating the Payment Status:
{
"PaymentID": 1,
"NewStatus": "Failed",
"FailureReason": "Bank Declined the Transaction"
}
In this article, I explain the Hotel Booking Module of our Application. I hope you enjoy this implementation of the Hotel Reservation and Payment 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 Cancellation 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.
I got error “Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.”
i got the same error Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
oh it’s ok, it’s still good