User Module of Hotel Booking Application

Back to: ASP.NET Core Web API Tutorials

Implementing User Module of Hotel Booking Application

In this article, I will discuss implementing the User Module of a Hotel Booking Application. Please read our previous article discussing the ASP.NET Core Web API Project Setup for Hotel Booking.

How to Implement User Module of Hotel Booking Application

Let us start with the Users Module. Let us create the Model classes, DTOS, a Repository Class, Stored Procedures, and a User Controller for Registering a User, Logging a user to validate and update login details, Fetching the User by ID, Fetching all users with Status, Updating and Deleting a User with Soft delete, Assigning a Role to a User, etc.

Create Stored Procedures for Users

We will start by creating the necessary stored procedures in SQL Server for Managing the User Data in the database. So, please execute the following SQL Script on the HotelDB database that we already created.

-- Add a New User
CREATE PROCEDURE spAddUser
    @Email NVARCHAR(100),
    @PasswordHash NVARCHAR(255),
    @CreatedBy NVARCHAR(100),
    @UserID INT OUTPUT,
    @ErrorMessage NVARCHAR(255) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        -- Check if email or password is null
        IF @Email IS NULL OR @PasswordHash IS NULL
        BEGIN
            SET @ErrorMessage = 'Email and Password cannot be null.';
            SET @UserID = -1;
            RETURN;
        END

        -- Check if email already exists in the system
        IF EXISTS (SELECT 1 FROM Users WHERE Email = @Email)
        BEGIN
            SET @ErrorMessage = 'A user with the given email already exists.';
            SET @UserID = -1;
            RETURN;
        END

        -- Default role ID for new users
        DECLARE @DefaultRoleID INT = 2; -- Assuming 'Guest' role ID is 2

        BEGIN TRANSACTION
            INSERT INTO Users (RoleID, Email, PasswordHash, CreatedBy, CreatedDate)
            VALUES (@DefaultRoleID, @Email, @PasswordHash, @CreatedBy, GETDATE());

            SET @UserID = SCOPE_IDENTITY(); -- Retrieve the newly created UserID
            SET @ErrorMessage = NULL;
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        -- Handle exceptions
        ROLLBACK TRANSACTION
        SET @ErrorMessage = ERROR_MESSAGE();
        SET @UserID = -1;
    END CATCH
END;
GO

-- Assign a Role to User
CREATE PROCEDURE spAssignUserRole
    @UserID INT,
    @RoleID INT,
    @ErrorMessage NVARCHAR(255) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        -- Check if the user exists
        IF NOT EXISTS (SELECT 1 FROM Users WHERE UserID = @UserID)
        BEGIN
            SET @ErrorMessage = 'User not found.';
            RETURN;
        END

        -- Check if the role exists
        IF NOT EXISTS (SELECT 1 FROM UserRoles WHERE RoleID = @RoleID)
        BEGIN
            SET @ErrorMessage = 'Role not found.';
            RETURN;
        END

        -- Update user role
        BEGIN TRANSACTION
            UPDATE Users SET RoleID = @RoleID WHERE UserID = @UserID;
        COMMIT TRANSACTION

        SET @ErrorMessage = NULL;
    END TRY
    BEGIN CATCH
        -- Handle exceptions
        ROLLBACK TRANSACTION
        SET @ErrorMessage = ERROR_MESSAGE();
    END CATCH
END;
GO

-- List All Users
CREATE PROCEDURE spListAllUsers
    @IsActive BIT = NULL  -- Optional parameter to filter by IsActive status
AS
BEGIN
    SET NOCOUNT ON;

    -- Select users based on active status
    IF @IsActive IS NULL
    BEGIN
        SELECT UserID, Email, RoleID, IsActive, LastLogin, CreatedBy, CreatedDate FROM Users;
    END
    ELSE
    BEGIN
        SELECT UserID, Email, RoleID, IsActive, LastLogin, CreatedBy, CreatedDate FROM Users 
  WHERE IsActive = @IsActive;
    END
END;
GO

-- Get User by ID
CREATE PROCEDURE spGetUserByID
    @UserID INT,
    @ErrorMessage NVARCHAR(255) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    -- Check if the user exists
    IF NOT EXISTS (SELECT 1 FROM Users WHERE UserID = @UserID)
    BEGIN
        SET @ErrorMessage = 'User not found.';
        RETURN;
    END

    -- Retrieve user details
    SELECT UserID, Email, RoleID, IsActive, LastLogin, CreatedBy, CreatedDate FROM Users WHERE UserID = @UserID;
    SET @ErrorMessage = NULL;
END;
GO

-- Update User Information
CREATE PROCEDURE spUpdateUserInformation
    @UserID INT,
    @Email NVARCHAR(100),
    @Password NVARCHAR(100),
    @ModifiedBy NVARCHAR(100),
    @ErrorMessage NVARCHAR(255) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        -- Check user existence
        IF NOT EXISTS (SELECT 1 FROM Users WHERE UserID = @UserID)
        BEGIN
            SET @ErrorMessage = 'User not found.';
            RETURN;
        END

        -- Check email uniqueness except for the current user
        IF EXISTS (SELECT 1 FROM Users WHERE Email = @Email AND UserID <> @UserID)
        BEGIN
            SET @ErrorMessage = 'Email already used by another user.';
            RETURN;
        END

        -- Update user details
        BEGIN TRANSACTION
            UPDATE Users
            SET Email = @Email, PasswordHash =@Password, ModifiedBy = @ModifiedBy, ModifiedDate = GETDATE()
            WHERE UserID = @UserID;
        COMMIT TRANSACTION

        SET @ErrorMessage = NULL;
    END TRY
    -- Handle exceptions
    BEGIN CATCH
        ROLLBACK TRANSACTION
        SET @ErrorMessage = ERROR_MESSAGE();
    END CATCH
END;
GO

-- Activate/Deactivate User
-- This can also be used for deleting a User
CREATE PROCEDURE spToggleUserActive
    @UserID INT,
    @IsActive BIT,
    @ErrorMessage NVARCHAR(255) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        -- Check user existence
        IF NOT EXISTS (SELECT 1 FROM Users WHERE UserID = @UserID)
        BEGIN
            SET @ErrorMessage = 'User not found.';
            RETURN;
        END

        -- Update IsActive status
        BEGIN TRANSACTION
            UPDATE Users SET IsActive = @IsActive WHERE UserID = @UserID;
        COMMIT TRANSACTION

        SET @ErrorMessage = NULL;
    END TRY
    -- Handle exceptions
    BEGIN CATCH
        ROLLBACK TRANSACTION
        SET @ErrorMessage = ERROR_MESSAGE();
    END CATCH
END;
GO

-- Login a User
CREATE PROCEDURE spLoginUser
    @Email NVARCHAR(100),
    @PasswordHash NVARCHAR(255),
    @UserID INT OUTPUT,
    @ErrorMessage NVARCHAR(255) OUTPUT
AS
BEGIN
    -- Attempt to retrieve the user based on email and password hash
    SELECT @UserID = UserID FROM Users WHERE Email = @Email AND PasswordHash = @PasswordHash;

    -- Check if user ID was set (means credentials are correct)
    IF @UserID IS NOT NULL
    BEGIN
        -- Check if the user is active
        IF EXISTS (SELECT 1 FROM Users WHERE UserID = @UserID AND IsActive = 1)
        BEGIN
            -- Update the last login time
            UPDATE Users SET LastLogin = GETDATE() WHERE UserID = @UserID;
            SET @ErrorMessage = NULL; -- Clear any previous error messages
        END
        ELSE
        BEGIN
            SET @ErrorMessage = 'User account is not active.';
            SET @UserID = NULL; -- Reset the UserID as login should not be considered successful
        END
    END
    ELSE
    BEGIN
        SET @ErrorMessage = 'Invalid Credentials.';
    END
END;
GO

Explanation of the above Stored Procedures:

The above-stored procedures manage user registration, role assignment, data retrieval, updates, and authentication within a database system.

• spAddUser: Registers a new user by inserting their details into the Users table. It validates that the email and password are not null, ensures that the email does not already exist in the system, and assigns a default role to the new user. It outputs the newly created user ID or an error message if the registration fails.
• spAssignUserRole: This function updates a user’s role by changing their RoleID in the Users table. Before updating, it checks whether both the user and the role exist. If successful, it commits the transaction; otherwise, it returns an error message.
• spListAllUsers: Retrieves a list of all users from the Users table. It can optionally filter users based on their active status. This procedure helps in viewing all users or only those who are currently active or inactive.
• spGetUserByID: Fetches detailed information for a specific user based on their user ID. It checks if the user exists and then selects their details. If the user does not exist, it returns an error message.
• spUpdateUserInformation: Updates the information of an existing user, including their email, password, and the name of the user who modified the record. Before updating, it ensures the user exists, and the new email is unique. Errors or success feedback is communicated through an output parameter.
• spToggleUserActive: This procedure toggles a user’s active status in the Users table. By setting the user’s IsActive flag, it effectively activates, deactivates, or soft-deletes them. It confirms the user’s existence before changing the status.
• spLoginUser: Handles user login by verifying their email and password hash. It checks if the user is active and updates their last login timestamp if successful. It sets an error message if the credentials are invalid or if the account is inactive.

Key Points to Remember:

• I added SET NOCOUNT ON to prevent sending row count messages.
• Used TRY…CATCH blocks to handle exceptions and ensure proper rollback in case of failures.
• Wrapped data modifications within Transactions to ensure atomicity.
• Optimized SELECT statements by Explicitly Specifying Column Names instead of SELECT *.

Creating User DTOs:

Next, we need to create the DTOs required for User management operations. Controllers and Repositories will use these DTOs to manage Operations and Data Transformation effectively. Using DTOs, we can only include the Properties required for Data Transformation.

Please make sure to have a folder called DTOs. Inside this DTOs folder, we should have another folder called UserDTOs, where we will add all the DTOs related to managing the user.

CreateUserDTO

Create a class file named CreateUserDTO.cs within the UserDTOs folder, and then copy and paste the following code. This class will only include the properties which are required for creating a new User.

using System.ComponentModel.DataAnnotations;

namespace HotelBookingAPI.DTOs.UserDTOs
{
    public class CreateUserDTO
    {
        [Required(ErrorMessage = "Email is Required")]
        [EmailAddress(ErrorMessage = "Invalid Email Address")]
        public string Email { get; set; }

        [Required(ErrorMessage = "Password is Required")]
        public string Password { get; set; } // This should be converted to a hash before being sent to the database.
    }
}

UpdateUserDTO

Create a class file named UpdateUserDTO.cs within the UserDTOs folder and copy and paste the following code. This class will only include the properties required to update an existing User.

using System.ComponentModel.DataAnnotations;

namespace HotelBookingAPI.DTOs.UserDTOs
{
    public class UpdateUserDTO
    {
        [Required(ErrorMessage = "UserID is Required")]
        public int UserID { get; set; }
        [Required(ErrorMessage = "Email is Required")]
        [EmailAddress(ErrorMessage = "Invalid Email Address")]
        public string Email { get; set; }
        [Required(ErrorMessage = "Password is Required")]
        public string Password { get; set; }
    }
}

LoginUserDTO

Create a class file named LoginUserDTO.cs within the UserDTOs folder and copy and paste the following code. This class will only include the properties required for the login operation.

using System.ComponentModel.DataAnnotations;

namespace HotelBookingAPI.DTOs.UserDTOs
{
    public class LoginUserDTO
    {
        [Required(ErrorMessage = "Email is Required")]
        [EmailAddress(ErrorMessage = "Invalid Email Address")]
        public string Email { get; set; }
        [Required(ErrorMessage = "Password is Required")]
        public string Password { get; set; }
    }
}

UserRoleDTO

Create a class file named UserRoleDTO.cs within the UserDTOs folder and copy and paste the following code. This class will only include the properties required for assigning a user to a role.

using System.ComponentModel.DataAnnotations;

namespace HotelBookingAPI.DTOs.UserDTOs
{
    public class UserRoleDTO
    {
        [Required(ErrorMessage ="User Id is Required")]
        public int UserID { get; set; }
        [Required(ErrorMessage = "Role Id is Required")]
        public int RoleID { get; set; }
    }
}

UserRoleResponseDTO

Create a class file named UserRoleResponseDTO.cs within the UserDTOs folder and copy and paste the following code. This class will only include the properties we send once the user is assigned a role.

namespace HotelBookingAPI.DTOs.UserDTOs
{
    public class UserRoleResponseDTO
    {
        public string Message { get; set; }
        public bool IsAssigned { get; set; }
    }
}

CreateUserResponseDTO

Create a class file named CreateUserResponseDTO.cs within the UserDTOs folder and copy and paste the following code. This class will only include the properties we send once the user is created.

namespace HotelBookingAPI.DTOs.UserDTOs
{
    public class CreateUserResponseDTO
    {
        public int UserId { get; set; }
        public string Message { get; set; }
        public bool IsCreated { get; set; }
    }
}

UpdateUserResponseDTO

Create a class file named UpdateUserResponseDTO.cs within the UserDTOs folder and copy and paste the following code. Once the user is updated, this class only includes the properties we send to the client.

namespace HotelBookingAPI.DTOs.UserDTOs
{
    public class UpdateUserResponseDTO
    {
        public int UserId { get; set; }
        public string Message { get; set; }
        public bool IsUpdated { get; set; }
    }
}

DeleteUserResponseDTO

Create a class file named DeleteUserResponseDTO.cs within the UserDTOs folder and copy and paste the following code. This class will only include the properties we send once the user is deleted.

namespace HotelBookingAPI.DTOs.UserDTOs
{
    public class DeleteUserResponseDTO
    {
        public string Message { get; set; }
        public bool IsDeleted { get; set; }
    }
}

LoginUserResponseDTO

Create a class file named LoginUserResponseDTO.cs within the UserDTOs folder and copy and paste the following code. This class will only include the properties we are sending once the user is logged in.

namespace HotelBookingAPI.DTOs.UserDTOs
{
    public class LoginUserResponseDTO
    {
        public int UserId { get; set; }
        public string Message { get; set; }
        public bool IsLogin { get; set; }
    }
}

UserResponseDTO

Create a class file named UserResponseDTO.cs within the UserDTOs folder and copy and paste the following code. This class will only include the properties required to return the user information.

namespace HotelBookingAPI.DTOs.UserDTOs
{
    public class UserResponseDTO
    {
        public int UserID { get; set; }
        public string Email { get; set; }
        public bool IsActive { get; set; }
        public DateTime? LastLogin { get; set; }
        public int RoleID { get; set; }
    }
}

How to Handle the DB NULL Values while Reading the Data from SqlReader?

Handling NULL values while retrieving data from a DataReader object in C# is a common task, especially when working with databases where some columns may not have values in every row. There are a few approaches to handling NULL values safely:

Approach 1: Check for NULLs Before Assignment

Use the IsDBNull method of the DataReader to check if the current value is NULL before attempting to retrieve it. This prevents exceptions and errors in your code. Here’s how you can do it:

while (reader.Read())
{
    int? columnValue = reader.IsDBNull(reader.GetOrdinal("ColumnName")) ? (int?)null : reader.GetInt32(reader.GetOrdinal("ColumnName"));
    // You can now safely use columnValue which will be null or an integer.
}

Instead of checking the DB NULL and fetching the data again and again in all places, we can create a generic extension method and delegate this task to that generic extension method, which is the preferred approach nowadays in the industry.

Approach 2: Generic Extension Method for Any Type

You can create a generic extension method to handle any data type. In this project, we are going to use the generic extension method to handle DB NULL Values. So, create a folder called Extensions within the Project root Directory.

Within the Extensions folder, create a class file named DataReaderExtensions.cs and then copy and paste the following code. The GetValueByColumn is a generic method defined with the type parameter T. It’s an extension method on the SqlDataReader type, as indicated by the “this” keyword before the SqlDataReader parameter. This method enables us to call GetValueByColumn as if it were a method of SqlDataReader itself. The following code is self-explained, so please go through the comment lines for a better understanding.

using Microsoft.Data.SqlClient;

namespace HotelBookingAPI.Extensions
{
    public static class DataReaderExtensions
    {
        public static T GetValueByColumn<T>(this SqlDataReader reader, string columnName)
        {
            //Getting Column Index
            //This line retrieves the zero-based column ordinal (index) based on the column name provided.
            //GetOrdinal throws an exception if the column name specified does not exist in the reader.
            int index = reader.GetOrdinal(columnName);

            //Checking for Null Values
            //Before trying to retrieve the value, the method checks whether the data at the specified column index is DB null using the IsDBNull method.
            //This prevents exceptions that occur when reading null values.
            if (!reader.IsDBNull(index))
            {
                //Returning the Value
                //If the value is not null, it retrieves the value from the reader at the given index and casts it to the type T.
                //This allows the method to be type-safe and handling various data types.
                return (T)reader[index];
            }

            //Handling Null Values
            //If the value in the database is null, the method returns the default value for the type T.
            //The default value depends on what T is; for reference types, it is null,
            //and for value types, it is typically zero or a struct with all zero values.
            return default(T);
        }
    }
}

This method then allows us to retrieve any type of data in a nullable form by specifying the type when calling the method. This technique will help us to handle the database column NULL values effectively when working with a DataReader object in C#.

Creating UserRepository:

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

using HotelBookingAPI.Connection;
using HotelBookingAPI.DTOs.UserDTOs;
using HotelBookingAPI.Extensions;
using Microsoft.Data.SqlClient;
using System.Data;

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

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

        public async Task<CreateUserResponseDTO> AddUserAsync(CreateUserDTO user)
        {
            CreateUserResponseDTO createUserResponseDTO = new CreateUserResponseDTO();

            using var connection = _connectionFactory.CreateConnection();
            using var command = new SqlCommand("spAddUser", connection)
            {
                CommandType = CommandType.StoredProcedure
            };

            command.Parameters.AddWithValue("@Email", user.Email);
            // Convert password to hash here 
            command.Parameters.AddWithValue("@PasswordHash", user.Password);
            command.Parameters.AddWithValue("@CreatedBy", "System");

            var userIdParam = new SqlParameter("@UserID", SqlDbType.Int)
            {
                Direction = ParameterDirection.Output
            };
            var errorMessageParam = new SqlParameter("@ErrorMessage", SqlDbType.NVarChar, 255)
            {
                Direction = ParameterDirection.Output
            };

            command.Parameters.Add(userIdParam);
            command.Parameters.Add(errorMessageParam);

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

            var UserId = (int)userIdParam.Value;

            if (UserId != -1)
            {
                createUserResponseDTO.UserId = UserId;
                createUserResponseDTO.IsCreated = true;
                createUserResponseDTO.Message = "User Created Successfully";
                return createUserResponseDTO;
            }

            var message = errorMessageParam.Value?.ToString();
            createUserResponseDTO.IsCreated = false;
            createUserResponseDTO.Message = message ?? "An unknown error occurred while creating the user.";
            return createUserResponseDTO;
        }

        public async Task<UserRoleResponseDTO> AssignRoleToUserAsync(UserRoleDTO userRole)
        {
            UserRoleResponseDTO userRoleResponseDTO = new UserRoleResponseDTO();

            using var connection = _connectionFactory.CreateConnection();
            using var command = new SqlCommand("spAssignUserRole", connection)
            {
                CommandType = CommandType.StoredProcedure
            };
            command.Parameters.AddWithValue("@UserID", userRole.UserID);
            command.Parameters.AddWithValue("@RoleID", userRole.RoleID);

            var errorMessageParam = new SqlParameter("@ErrorMessage", SqlDbType.NVarChar, 255)
            {
                Direction = ParameterDirection.Output
            };
            command.Parameters.Add(errorMessageParam);

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

            var message = errorMessageParam.Value?.ToString();

            if (!string.IsNullOrEmpty(message))
            {
                userRoleResponseDTO.Message = message;
                userRoleResponseDTO.IsAssigned = false;
            }
            else
            {
                userRoleResponseDTO.Message = "User Role Assigned";
                userRoleResponseDTO.IsAssigned = true;
            }
            
            return userRoleResponseDTO;
        }

        public async Task<List<UserResponseDTO>> ListAllUsersAsync(bool? isActive)
        {
            using var connection = _connectionFactory.CreateConnection();
            using var command = new SqlCommand("spListAllUsers", connection)
            {
                CommandType = CommandType.StoredProcedure
            };
            command.Parameters.AddWithValue("@IsActive", (object)isActive ?? DBNull.Value);

            await connection.OpenAsync();
            using var reader = await command.ExecuteReaderAsync();
            var users = new List<UserResponseDTO>();

            while (reader.Read())
            {
                users.Add(new UserResponseDTO
                {
                    UserID = reader.GetInt32("UserID"),
                    Email = reader.GetString("Email"),
                    IsActive = reader.GetBoolean("IsActive"),
                    RoleID = reader.GetInt32("RoleID"),
                    LastLogin = reader.GetValueByColumn<DateTime?>("LastLogin"),
                });
            }

            return users;
        }

        public async Task<UserResponseDTO> GetUserByIdAsync(int userId)
        {
            using var connection = _connectionFactory.CreateConnection();
            using var command = new SqlCommand("spGetUserByID", connection)
            {
                CommandType = CommandType.StoredProcedure
            };
            command.Parameters.AddWithValue("@UserID", userId);

            var errorMessageParam = new SqlParameter("@ErrorMessage", SqlDbType.NVarChar, 255) { Direction = ParameterDirection.Output };
            command.Parameters.Add(errorMessageParam);

            await connection.OpenAsync();
            using var reader = await command.ExecuteReaderAsync();
            if (!reader.Read())
            {
                return null;
            }

            var user = new UserResponseDTO
            {
                UserID = reader.GetInt32("UserID"),
                Email = reader.GetString("Email"),
                IsActive = reader.GetBoolean("IsActive"),
                RoleID = reader.GetInt32("RoleID"),
                LastLogin = reader.GetValueByColumn<DateTime?>("LastLogin"),
            };

            return user;
        }

        public async Task<UpdateUserResponseDTO> UpdateUserAsync(UpdateUserDTO user)
        {
            UpdateUserResponseDTO updateUserResponseDTO = new UpdateUserResponseDTO()
            {
                UserId = user.UserID
            };

            using var connection = _connectionFactory.CreateConnection();
            using var command = new SqlCommand("spUpdateUserInformation", connection)
            {
                CommandType = CommandType.StoredProcedure
            };
            command.Parameters.AddWithValue("@UserID", user.UserID);
            command.Parameters.AddWithValue("@Email", user.Email);
            command.Parameters.AddWithValue("@Password", user.Password);
            command.Parameters.AddWithValue("@ModifiedBy", "System");

            var errorMessageParam = new SqlParameter("@ErrorMessage", SqlDbType.NVarChar, 255)
            {
                Direction = ParameterDirection.Output
            };
            command.Parameters.Add(errorMessageParam);

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

            var message = errorMessageParam.Value?.ToString();

            if (string.IsNullOrEmpty(message))
            {
                updateUserResponseDTO.Message = "User Information Updated.";
                updateUserResponseDTO.IsUpdated = true;
            }
            else
            {
                updateUserResponseDTO.Message = message;
                updateUserResponseDTO.IsUpdated = false;
            }

            return updateUserResponseDTO;
        }

        public async Task<DeleteUserResponseDTO> DeleteUserAsync(int userId)
        {
            DeleteUserResponseDTO deleteUserResponseDTO = new DeleteUserResponseDTO();
            using var connection = _connectionFactory.CreateConnection();
            using var command = new SqlCommand("spToggleUserActive", connection)
            {
                CommandType = CommandType.StoredProcedure
            };
            command.Parameters.AddWithValue("@UserID", userId);
            command.Parameters.AddWithValue("@IsActive", false);

            var errorMessageParam = new SqlParameter("@ErrorMessage", SqlDbType.NVarChar, 255) 
            { 
                Direction = ParameterDirection.Output 
            };
            command.Parameters.Add(errorMessageParam);

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

            var message = errorMessageParam.Value?.ToString();

            if (!string.IsNullOrEmpty(message))
            {
                deleteUserResponseDTO.Message = message;
                deleteUserResponseDTO.IsDeleted = false;
            }
            else
            {
                deleteUserResponseDTO.Message = "User Deleted.";
                deleteUserResponseDTO.IsDeleted = true;
            }

            return deleteUserResponseDTO;
        }

        public async Task<LoginUserResponseDTO> LoginUserAsync(LoginUserDTO login)
        {
            LoginUserResponseDTO userLoginResponseDTO = new LoginUserResponseDTO();

            using var connection = _connectionFactory.CreateConnection();
            using var command = new SqlCommand("spLoginUser", connection)
            {
                CommandType= CommandType.StoredProcedure
            };
            
            command.Parameters.AddWithValue("@Email", login.Email);
            command.Parameters.AddWithValue("@PasswordHash", login.Password); // Ensure password is hashed

            var userIdParam = new SqlParameter("@UserID", SqlDbType.Int) 
            { 
                Direction = ParameterDirection.Output 
            };
            var errorMessageParam = new SqlParameter("@ErrorMessage", SqlDbType.NVarChar, 255) 
            { 
                Direction = ParameterDirection.Output 
            };

            command.Parameters.Add(userIdParam);
            command.Parameters.Add(errorMessageParam);

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

            var success = userIdParam.Value != DBNull.Value && (int)userIdParam.Value > 0;
            
            if (success)
            {
                var userId = Convert.ToInt32(userIdParam.Value);
                userLoginResponseDTO.UserId = userId;
                userLoginResponseDTO.IsLogin = true;
                userLoginResponseDTO.Message = "Login Successful";
                return userLoginResponseDTO;
            }

            var message = errorMessageParam.Value?.ToString();
            userLoginResponseDTO.IsLogin = false;
            userLoginResponseDTO.Message = message;
            return userLoginResponseDTO;
        }

        public async Task<(bool Success, string Message)> ToggleUserActiveAsync(int userId, bool isActive)
        {
            using var connection = _connectionFactory.CreateConnection();
            using var command = new SqlCommand("spToggleUserActive", connection)
            {
                CommandType = CommandType.StoredProcedure
            };
            command.Parameters.AddWithValue("@UserID", userId);
            command.Parameters.AddWithValue("@IsActive", isActive);

            var errorMessageParam = new SqlParameter("@ErrorMessage", SqlDbType.NVarChar, 255) { Direction = ParameterDirection.Output };
            command.Parameters.Add(errorMessageParam);

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

            var message = errorMessageParam.Value?.ToString();
            var success = string.IsNullOrEmpty(message);

            return (success, message);
        }
    }
}

Explanation of Each Method:

Let us understand the objective of each method in detail.

AddUserAsync

Adds a new user to the database using the spAddUser stored procedure.

• Parameters: Accepts a CreateUserDTO containing the user’s email and password.
• Process: Calls the spAddUser stored procedure with parameters for the email and a hashed version of the password. It also handles output parameters for the user ID and error messages.
• Outcome: Returns a CreateUserResponseDTO indicating whether the user was successfully created and any relevant messages.

AssignRoleToUserAsync

Assigns a specific role to a user, facilitated by the spAssignUserRole stored procedure.

• Parameters: Takes a UserRoleDTO, which includes the user ID and role ID.
• Process: Executes the stored procedure that updates the user’s role if both the user and role exist.
• Outcome: Returns a UserRoleResponseDTO that states whether the role was successfully assigned and includes any error messages.

ListAllUsersAsync

Retrieves a list of all users or those filtered by active status using the spListAllUsers stored procedure.

• Parameters: An optional isActive boolean to filter users based on their active status.
• Process: Fetches users based on the active status parameter and constructs a list of UserResponseDTO.
• Outcome: This function returns a list of users encapsulated in UserResponseDTOs, which includes user IDs, emails, and active status.

GetUserByIdAsync

Fetches a specific user by their user ID through the spGetUserByID stored procedure.

• Parameters: The user ID of the desired user.
• Process: Calls the stored procedure to retrieve details for a specific user.
• Outcome: Returns a UserResponseDTO containing the user’s details if found or null if not found, with appropriate error handling.

UpdateUserAsync

Updates user information such as email and password using the spUpdateUserInformation stored procedure.

• Parameters: Accepts an UpdateUserDTO, which includes the user ID, new email, and new password.
• Process: Executes the stored procedure that updates user details in the database.
• Outcome: Returns an UpdateUserResponseDTO indicating whether the update was successful and includes any relevant messages.

DeleteUserAsync

Soft deletes a user by setting their IsActive status to false using the spToggleUserActive stored procedure.

• Parameters: The user ID of the user to deactivate.
• Process: Calls the stored procedure to update the IsActive field in the database.
• Outcome: Produces a DeleteUserResponseDTO indicating whether the user was successfully deactivated, along with any error messages.

LoginUserAsync

Authenticates a user by verifying their email and password hash and updates their last login timestamp using the spLoginUser stored procedure.

• Parameters: A LoginUserDTO with the user’s email and password.
• Process: Validates the user’s credentials and checks if the user is active. If successful, update the last login date/time.
• Outcome: Returns a LoginUserResponseDTO that indicates whether the login was successful and includes the user ID if authentication passes.

ToggleUserActiveAsync

Activates or deactivates a user account by updating the IsActive status, handled by the spToggleUserActive stored procedure.

• Parameters: User ID and a boolean indicating the desired active status.
• Process: Updates the IsActive status of the specified user.
• Outcome: Returns a tuple (bool Success, string Message) indicating the success of the operation and providing any relevant messages.

Register the Repository:

Next, we need to register the User Repository into the dependency injection container. So, please add the following code to the Program.cs class file:

builder.Services.AddScoped<UserRepository>();

Creating User Controller:

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

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

namespace HotelBookingAPI.Controllers
{
    [ApiController]
    [Route("[controller]")]
    public class UserController : ControllerBase
    {
        private readonly UserRepository _userRepository;
        private readonly ILogger<UserController> _logger;

        public UserController(UserRepository userRepository, ILogger<UserController> logger)
        {
            _userRepository = userRepository;
            _logger = logger;
        }

        [HttpPost("AddUser")]
        public async Task<APIResponse<CreateUserResponseDTO>> AddUser(CreateUserDTO createUserDTO)
        {
            _logger.LogInformation("Request Received for AddUser: {@CreateUserDTO}", createUserDTO);

            if (!ModelState.IsValid)
            {
                _logger.LogInformation("Invalid Data in the Requrest Body");
                return new APIResponse<CreateUserResponseDTO>(HttpStatusCode.BadRequest, "Invalid Data in the Requrest Body");
            }

            try
            {
                var response = await _userRepository.AddUserAsync(createUserDTO);
                _logger.LogInformation("AddUser Response From Repository: {@CreateUserResponseDTO}", response);

                if (response.IsCreated)
                {
                    return new APIResponse<CreateUserResponseDTO>(response, response.Message);
                }
                return new APIResponse<CreateUserResponseDTO>(HttpStatusCode.BadRequest, response.Message);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error adding new user with email {Email}", createUserDTO.Email);
                return new APIResponse<CreateUserResponseDTO>(HttpStatusCode.InternalServerError, "Registration Failed.", ex.Message);
            }
        }

        [HttpPost("AssignRole")]
        public async Task<APIResponse<UserRoleResponseDTO>> AssignRole(UserRoleDTO userRoleDTO)
        {
            _logger.LogInformation("Request Received for AssignRole: {@UserRoleDTO}", userRoleDTO);

            if (!ModelState.IsValid)
            {
                _logger.LogInformation("Invalid Data in the Request Body");
                return new APIResponse<UserRoleResponseDTO>(HttpStatusCode.BadRequest, "Invalid Data in the Requrest Body");
            }

            try
            {
                var response = await _userRepository.AssignRoleToUserAsync(userRoleDTO);
                _logger.LogInformation("AssignRole Response From Repository: {@UserRoleResponseDTO}", response);

                if (response.IsAssigned)
                {
                    return new APIResponse<UserRoleResponseDTO>(response, response.Message);
                }
                return new APIResponse<UserRoleResponseDTO>(HttpStatusCode.BadRequest, response.Message);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error assigning role {RoleID} to user {UserID}", userRoleDTO.RoleID, userRoleDTO.UserID);
                return new APIResponse<UserRoleResponseDTO>(HttpStatusCode.InternalServerError, "Role Assigned Failed.", ex.Message);
            }
        }

        [HttpGet("AllUsers")]
        public async Task<APIResponse<List<UserResponseDTO>>> GetAllUsers(bool? isActive = null)
        {
            _logger.LogInformation($"Request Received for GetAllUsers, IsActive: {isActive}");
            try
            {
                var users = await _userRepository.ListAllUsersAsync(isActive);
                return new APIResponse<List<UserResponseDTO>>(users, "Retrieved all Users Successfully.");
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error listing users");
                return new APIResponse<List<UserResponseDTO>>(HttpStatusCode.InternalServerError, "Internal server error: " + ex.Message);
            }
        }

        [HttpGet("GetUser/{userId}")]
        public async Task<APIResponse<UserResponseDTO>> GetUserById(int userId)
        {
            _logger.LogInformation($"Request Received for GetUserById, ID: {userId}");
            try
            {
                var user = await _userRepository.GetUserByIdAsync(userId);
                if (user == null)
                {
                    return new APIResponse<UserResponseDTO>(HttpStatusCode.NotFound, "User not found.");
                }

                return new APIResponse<UserResponseDTO>(user, "User fetched successfully.");
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error getting user by ID {UserID}", userId);
                return new APIResponse<UserResponseDTO>(HttpStatusCode.InternalServerError, "Error fetching user.", ex.Message);
            }
        }

        [HttpPut("Update/{id}")]
        public async Task<APIResponse<UpdateUserResponseDTO>> UpdateUser(int id, [FromBody] UpdateUserDTO updateUserDTO)
        {
            _logger.LogInformation("Request Received for UpdateUser {@UpdateUserDTO}", updateUserDTO);
            if (!ModelState.IsValid)
            {
                _logger.LogInformation("UpdateUser Invalid Request Body");
                return new APIResponse<UpdateUserResponseDTO>(HttpStatusCode.BadRequest, "Invalid Request Body");
            }
            if (id != updateUserDTO.UserID)
            {
                _logger.LogInformation("UpdateUser Mismatched User ID.");
                return new APIResponse<UpdateUserResponseDTO>(HttpStatusCode.BadRequest, "Mismatched User ID.");
            }
            try
            {
                var response = await _userRepository.UpdateUserAsync(updateUserDTO);

                if (response.IsUpdated)
                {
                    return new APIResponse<UpdateUserResponseDTO>(response, response.Message);
                }
                return new APIResponse<UpdateUserResponseDTO>(HttpStatusCode.BadRequest, response.Message);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error updating user {UserID}", updateUserDTO.UserID);
                return new APIResponse<UpdateUserResponseDTO>(HttpStatusCode.InternalServerError, "Update Failed.", ex.Message);
            }
        }

        [HttpDelete("Delete/{id}")]
        public async Task<APIResponse<DeleteUserResponseDTO>> DeleteUser(int id)
        {
            _logger.LogInformation($"Request Received for DeleteUser, Id: {id}");
            try
            {
                var user = await _userRepository.GetUserByIdAsync(id);
                if (user == null)
                {
                    return new APIResponse<DeleteUserResponseDTO>(HttpStatusCode.NotFound, "User not found.");
                }

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

        [HttpPost("Login")]
        public async Task<APIResponse<LoginUserResponseDTO>> LoginUser([FromBody] LoginUserDTO loginUserDTO)
        {
            _logger.LogInformation("Request Received for LoginUser {@LoginUserDTO}", loginUserDTO);

            if (!ModelState.IsValid)
            {
                return new APIResponse<LoginUserResponseDTO>(HttpStatusCode.BadRequest, "Invalid Data in the Requrest Body");
            }

            try
            {
                var response = await _userRepository.LoginUserAsync(loginUserDTO);

                if (response.IsLogin)
                {
                    return new APIResponse<LoginUserResponseDTO>(response, response.Message);
                }
                return new APIResponse<LoginUserResponseDTO>(HttpStatusCode.BadRequest, response.Message);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error logging in user with email {Email}", loginUserDTO.Email);
                return new APIResponse<LoginUserResponseDTO>(HttpStatusCode.InternalServerError, "Login failed.", ex.Message);
            }
        }

        [HttpPost("ToggleActive")]
        public async Task<IActionResult> ToggleActive(int userId, bool isActive)
        {
            try
            {
                var result = await _userRepository.ToggleUserActiveAsync(userId, isActive);
                if (result.Success)
                    return Ok(new { Message = "User activation status updated successfully." });
                else
                    return BadRequest(new { Message = result.Message });
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error toggling active status for user {UserID}", userId);
                return StatusCode(500, "An error occurred while processing your request.");
            }
        }
    }
}

Explanation of the Controller’s Functions:

• AddUser: This method adds a new user to the system. It takes a CreateUserDTO object containing user information as input and returns an APIResponse containing information about the operation’s success or failure.
• AssignRole: This method assigns a user a role. It takes a UserRoleDTO object containing user and role information as input and returns an APIResponse containing information about the operation’s success or failure.
• GetAllUsers: This method retrieves all users from the system. It takes an optional parameter, IsActive, to filter active or InActive users and returns an APIResponse containing a list of UserResponseDTO objects representing the users.
• GetUserById: This method retrieves a user by their ID. It takes the user ID as input and returns an APIResponse containing a UserResponseDTO object representing the user if found, otherwise, it returns a not found error.
• UpdateUser: This method updates user information. It takes the user ID and a UpdateUserDTO object containing updated user information as input and returns an APIResponse containing information about the success or failure of the operation.
• DeleteUser: This method deletes a user from the system. It takes the user ID as input and returns an APIResponse containing information about the operation’s success or failure.
• LoginUser: This method authenticates a user login. It takes a LoginUserDTO object containing user credentials as input and returns an APIResponse containing information about the success or failure of the login attempt.
• ToggleActive: This method toggles a user’s active status. It takes the user ID and a boolean indicating the new active status as input and returns an IActionResult indicating the operation’s success or failure.

Testing User Functionalities:

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

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