Stored Procedures in Razor Pages

Question

Hi All,

I have a booking system developed in Razor Pages.

My requirement is , when i make a booking to an activity on a day it should show me the available timeslots . And the timeslots can be one hour or half an hour slots. To maintain this i have two tables with 1 hour timeslots and half an hour timeslots.

If the person is selecting 1 hour duration, then slots from 1 hour timeslot table should be shown. If it is half an hour then slots from half an hour table be shown.

i want to handle this with stored procedures. So i have created two stored procedures, one is for one hour timeslot filteration and the other one is for half an hour time slot filteration.

This is my ManageBooking.cshtml.cs

div class="row ml-2">
    <div class="col-md-4">
        <form method="post">
            <div asp-validation-summary="ModelOnly" class="text-danger"></div>
            <div class="form-group">
                <label asp-for="ManageBooking.ActivityName" class="control-label"></label>
                <select id="DrpDwnActivityList" asp-for="ManageBooking.ActivityName" asp-items="@Model.DrpdwnActivityList" class="form-control">
                    <option value="">Please Select</option>
                </select>
                <span asp-validation-for="ManageBooking.ActivityName" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="ManageBooking.BookingDate" class="control-label"></label>
                <input id="DateBookedDate" asp-for="ManageBooking.BookingDate" class="form-control" min="@DateTime.Now.ToString("yyyy-MM-dd")" max="@DateTime.Now.AddDays(14).ToString("yyyy-MM-dd")" />
                <span asp-validation-for="ManageBooking.BookingDate" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="ManageBooking.Duration" class="control-label"></label>
                <select id="DrpDwnDuration" asp-for="ManageBooking.Duration" asp-items="@Model.DurationList" class="form-control">
                    <option value="">Please Select</option>
                </select>
                <span asp-validation-for="ManageBooking.Duration" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="ManageBooking.PreferredTimeslot" class="control-label"></label><br />
                <select id="DrpDwnHourBasedList" asp-for="ManageBooking.PreferredTimeslot" asp-items="@Model.HourBasedList" class="form-control">
                    <option value="">Please Select</option>
                </select>
                <select id="DrpDwnHalfAndHourList" asp-for="ManageBooking.PreferredHalfAnHourTimeslot" asp-items="@Model.HalfAnHourList" class="form-control" style="display:none;">
                    <option value="">Please Select</option>
                </select>
                <span id="ErrHourList" asp-validation-for="ManageBooking.PreferredHalfAnHourTimeslot" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="ManageBooking.EmployeeNo" class="control-label"></label>
                <input asp-for="ManageBooking.EmployeeNo" class="form-control" />
                <span asp-validation-for="ManageBooking.EmployeeNo" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="ManageBooking.EmployeeName" class="control-label"></label>
                <input asp-for="ManageBooking.EmployeeName" class="form-control" />
                <span asp-validation-for="ManageBooking.EmployeeName" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="ManageBooking.EmployeeEmailAddress" class="control-label"></label>
                <input asp-for="ManageBooking.EmployeeEmailAddress" class="form-control" />
                <span asp-validation-for="ManageBooking.EmployeeEmailAddress" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="ManageBooking.EmployeePhoneNumber" class="control-label"></label>
                <input asp-for="ManageBooking.EmployeePhoneNumber" class="form-control" />
                <span asp-validation-for="ManageBooking.EmployeePhoneNumber" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="ManageBooking.SupervisorName" class="control-label"></label>
                <input asp-for="ManageBooking.SupervisorName" class="form-control" />
                <span asp-validation-for="ManageBooking.SupervisorName" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="ManageBooking.SupervisorEmailAddress" class="control-label"></label>
                <input asp-for="ManageBooking.SupervisorEmailAddress" class="form-control" />
                <span asp-validation-for="ManageBooking.SupervisorEmailAddress" class="text-danger"></span>
            </div>
            <div class="form-group" style="display:none;">
                <label asp-for="ManageBooking.ActivityType" class="control-label"></label>
                <input asp-for="ManageBooking.ActivityType" class="form-control" />
                <span asp-validation-for="ManageBooking.ActivityType" class="text-danger"></span>
            </div>
            <div class="form-group" style="display:none;">
                <label asp-for="ManageBooking.LastModifiedTimeStamp" class="control-label"></label>
                <input asp-for="ManageBooking.LastModifiedTimeStamp" class="form-control" />
                <span asp-validation-for="ManageBooking.LastModifiedTimeStamp" class="text-danger"></span>
            </div>
            <div class="form-group">
                <input type="submit" value="Create" class="btn btn-primary" />
            </div>
        </form>
    </div>

How can i call the stored procedure with AJAX ? and i want to display the output in radio button.

Please find my STORED PROCEDURES below

USE [ActivityBookingSystem_UAT]
GO

/****** Object:  StoredProcedure [dbo].[AvailableHalfAnHourSlots]    Script Date: 1/18/2023 12:07:25 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[AvailableHalfAnHourSlots] 
   @ActivityName nvarchar(max) ,
   @BookedDate datetime2(7)
AS  

    SELECT distinct hh.halfanhourtime
FROM ManageBooking as mb, Halfanhour hh
WHERE  bookingdate = @BookedDate
and ActivityName = @ActivityName 
and (substring(hh.halfanhourtime,1,5) <> substring(mb.PreferredTimeslot,1,5))
and (right(hh.halfanhourtime,5) <> right(mb.PreferredTimeslot,5))

GO

USE [ActivityBookingSystem_UAT]
GO

/****** Object:  StoredProcedure [dbo].[AvailableHourSlots]    Script Date: 1/18/2023 12:08:15 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[AvailableHourSlots] 
   @ActivityName nvarchar(max) ,
   @BookedDate datetime2(7)
AS  

    SELECT distinct HourlyBasedTime 
    FROM   ManageBooking as mb, HourlyBased as hb  
    WHERE bookingdate = @BookedDate and ActivityName = @ActivityName
	and (substring(HourlyBasedTime,1,2) <> substring(mb.PreferredTimeslot,1,2))

GO

Appreciate your help!