From your other questions on this, you've using a few business objects \ layers to create the booking.
So, you could probably handle this in your business layer, using the following workflow
User submits request -> business layer processes -> sql query -> meeting room already booking (sql returns results for specified range -> throw exception from business layer -> catch exception in UI
In your Business Layer Code
public void SaveConferenceDetails(ConferenceBM conference)
{
DataTable bookings = YourDatabaseCode.ExecuteDataTable("SomeStoredProceudre" conference.StartDate, conference.EndDate);
if (bookings.Rows.Count > 0)
{
throw new ConferenceRoomAlreadyBookedException("The meeting room is already booked");
}
}
In your UI code
ConferenceBM confBM = new ConferenceBM();
ConferenceBL confBL = new ConferenceBL();
confBM.EmployeeName = ddlEmployeeName.SelectedItem.Text;
confBM.PurposeOfBooking = txtPurpose.Text;
confBM.StartDate = Convert.ToDateTime(txtStartDate.Text);
confBM.EndDate = Convert.ToDateTime(txtEndDate.Text);
confBM.StartTime = Convert.ToDateTime(txtStartTime.Text);
confBM.EndTime = Convert.ToDateTime(txtEndTime.Text);
confBM.ConferenceRoom = dd1conference.SelectedItem.Text;
try
{
confBL.SaveConferenceDetails(confBM);
}
catch (ConferenceRoomAlreadyBookedException ex)
{
}
Your spAddConferenceRoom should check for existing records and throw an error if they exist....e.g.
ALTER PROCEDURE [dbo].[spAddConferenceRoom]
(
@StartDate DATETIME,
@EndDate DATETIME
)
AS
SET NOCOUNT ON
IF EXISTS(SELECT * FROM Conferences
WHERE (@StartDate BETWEEN StartDate AND EndDate) OR
(@EndDate BETWEEN StartDate AND EndDate) OR
(StartDate BETWEEN @StartDate AND @EndDate) OR
(EndDate BETWEEN @StartDate AND @EndDate))
BEGIN
RAISERROR ('Cannot save details, the specified conference date is already booked',16,1)
END
--Otherwise, normal save code here...
RETURN @@ERROR
SET NOCOUNT OFF
I'd go a bit further with this. Create a procedure to check the availability
before trying to save. You could then call this from AJAX so that when a user selects start \ end dates, you can show some feedback to say 'Room Available' or 'Room Not Available'
CREATE PROCEDURE spCheckConferenceAvailability
(
@StartDate DATETIME,
@EndDate DATETIME
)
AS
SET NOCOUNT ON
SELECT * FROM Conferences
WHERE (@StartDate BETWEEN StartDate AND EndDate) OR
(@EndDate BETWEEN StartDate AND EndDate) OR
(StartDate BETWEEN @StartDate AND @EndDate) OR
(EndDate BETWEEN @StartDate AND @EndDate))
RETURN @@ERROR
SET NOCOUNT OFF
GO
You could probably optimise the date overlap checking, but should work!!