ASP.NET 2.0 Instant Results

Appointment Booking System

CREATE PROCEDURE sprocAppointmentCheckAvailability @bookingObjectId int,

@startDate datetime, @endDate datetime

AS

SELECT COUNT(*)

FROM

BookingObject INNER JOIN

BookingObjectWorkingDay ON BookingObject.Id =

BookingObjectWorkingDay.BookingObjectId INNER JOIN

WorkingDay ON BookingObjectWorkingDay.WorkingDayId = WorkingDay.Id

WHERE

--Check 1 - Select the correct BookingObject Id BookingObject.Id = @bookingObjectId

--Check 2 - Make sure the BookingObject is available on the

--start and the end date

AND

(

DATEPART(dw, @startDate)IN (SELECT BookingObjectWorkingDay.WorkingDayId FROM BookingObjectWorkingDay WHERE BookingObjectId = @bookingObjectId)

AND

DATEPART(dw, @endDate) IN (SELECT BookingObjectWorkingDay.WorkingDayId FROM BookingObjectWorkingDay WHERE BookingObjectId = @bookingObjectId)

)

--Check 3 - Make sure the appointment is between working

--hours for the BookingObject

AND

(

(

--If the Appointment is on the same day, make sure the start and

--end time are between working hours

CONVERT(varchar(8), @startDate, 112) = CONVERT(varchar(8), @endDate, 112) AND

DATEPART(hh, @startDate) >= DATEPART(hh, BookingObject.StartTime) AND

DATEPART(hh, @endDate) <= DATEPART(hh, BookingObject.EndTime)

)

OR

(

--Else, the end date is on the next day. Make sure the

--booking object is available 24 hours a day

CONVERT(varchar(8), @startDate, 112) < CONVERT(varchar(8), @endDate, 112) AND DATEPART(hh, StartTime) = 0

AND DATEPART(hh, EndTime) = 23

)

)

-- Check 4 - Make sure the BookingObject doesn’t have an appointment yet AND (BookingObject.Id NOT IN (

337

Chapter 10

SELECT

BookingObjectId

FROM

Appointment

WHERE

(@startDate >= StartDate AND @startDate < EndDate) OR (@endDate > StartDate AND @endDate <= EndDate)

))

The stored procedure returns a number indicating whether the appointment can be made on the requested date and time. When that number is 0, the appointment cannot be made. When the number is 1, it means the booking object is available on the requested date and time and does not have a conflicting appointment.

The WHERE clause of this procedure is where all the action is. Right after the SELECT statement, it uses four checks in the WHERE clause to determine whether or not the appointment can be made.

First, it filters out all booking objects except for the one with the ID that was passed in through the

@bookingObjectId parameter.

It then ensures that the booking object is available on the date that was requested. Because an appointment could cross midnight into the next day, both the start date and end date are checked. It performs this check by seeing if the day of the week of the requested date is present in the BookingObjectWorkingDay table for the requested booking object.

The next check determines whether the booking object is available during the hours that the appointment should take place. If the appointment takes place on a single day, it means that its start and end time must be between the booking object’s start and end hour.

When the appointment crosses midnight into the next day, the booking object has to be available 24 hours a day (that is, a start time of 0 and an end time of 23). Note that this is a limitation of the current Appointment Booking System. It does not allow for booking objects to be available for night shifts that start on one day and end on the next. This does not apply to appointments though; if a booking object is available 24 hours a day, you can make an appointment that ends on the next day.

The final check ensures that the booking object is not yet tied to another appointment by querying the Appointment table and checking the start and end date of the requested appointment against the existing appointments in the table.

When this complicated WHERE clause is carried out, it returns either 0 (not available) or 1 (available). This value is then converted to a Boolean at the end of the CheckAppointment method in the

AppointmentManagerDB class:

Return Convert.ToInt32(myCommand.ExecuteScalar()) > 0

This value bubbles up all the way to the FinishButtonClick event in the CreateAppointment.aspx page, where it is used to determine whether the appointment can be saved in the database:

338

Appointment Booking System

If AppointmentManager.CheckAppointment(myAppointment) Then

AppointmentManager.CreateAppointment(myAppointment)

MultiView1.ActiveViewIndex = 0

Else

MultiView1.ActiveViewIndex = 1

End If

When CheckAppointment returns True, the appointment is saved in the database by the Create Appointment method with code very similar to other insert code you have seen before. The only exception in this method is that CheckAppointment is called again, to ensure that no other appointments have been made since the check was carried out the last time.

If the appointment was saved correctly, the first view of the MultiView control is made visible. The MultiView is a simple control that allows you to selectively hide or show data based on certain criteria. In this case, View 1 (with an index of zero) is shown when the appointment was made successfully; otherwise View 2 is shown, which displays a message to the user saying that the appointment could not be made.

You may have noticed the user control called HourPicker. This is a custom user control saved in the Controls folder in the root of site. In the user interface, this control displays a drop-down list with a number of hours. To control the hours that appear in the list, the control has two public properties called StartTime and EndTime. Whenever you change the values of one of these properties, the control calls its internal CreateListItems method, which adds the required number of items to the drop-down list with the following code:

Private Sub CreateListItems()

lstHour.Items.Clear()

For i As Integer = StartTime To EndTime

lstHour.Items.Add(New ListItem(i.ToString() & “:00”, i.ToString())) Next

End Sub

First, Items.Clear is called to ensure the drop-down list contains no items. Then a loop is set up that adds the requested number of hours, using the i variable as the value for each ListItem, and then uses that same value followed by a colon and two zeros as the text for the item. To read from or set the selected item in the drop-down list, you can query its SelectedHour property, which returns the underlying value of the selected item:

Public Property SelectedHour() As Integer

Get

If lstHour.SelectedIndex >= 0 Then

Return Convert.ToInt32(lstHour.SelectedValue)

Else

Return -1

End If

End Get

Set(ByVal value As Integer)

If lstHour.Items.FindByValue(value.ToString()) IsNot Nothing Then lstHour.Items.FindByValue(value.ToString()).Selected = True

End If End Set

End Property

339

Chapter 10

Although the HourPicker is a very simple control, it enables you to quickly show drop-down lists with hours on a page. The control is used in the public file CreateAppointment.aspx to allow a user to select a start time. It’s also used in two files in the Management folder: Configuration.aspx and CreateUpdateBookingObject.aspx.

The Sign Up Page

To protect your appointment system from getting bogus appointments from malicious users, it’s a good idea to block access to the Appointment Wizard to unauthenticated users. This means users need a valid account in your system before they can make an appointment. However, the default behavior for Create UserWizard of the new ASP.NET 2.0 security framework allows a user to enter a fake e-mail address. As long as the user enters an address that looks like an e-mail address, the ASP.NET 2.0 framework will happily let the new user in. One way to overcome this problem is through a concept called double opt-in. With this technique, a user’s account is not activated until he has confirmed his e-mail address. To do that, users sign up for an account on a web site. Then they receive an e-mail with instructions about activating their account. As long as the account is not activated, they are not allowed to log in. Once they activate their account, you know they entered a valid e-mail address because they were able to carry out the instructions they received on that very same e-mail address.

In traditional ASP and ASP.NET applications, creating such a double opt-in system meant quite a lot of work. However, with the new security controls in ASP.NET 2.0, this is now simpler than ever. You need to be aware of a few tricks to implement this system with the security controls. First of all, take a look at the CreateUserWizard control in the SignUp.aspx page:

<asp:CreateUserWizard ID=”CreateUserWizard1” runat=”server” DisableCreatedUser=”True” CreateUserButtonText=”Sign Up” LoginCreatedUser=”False” CompleteSuccessText=”Your account has been

successfully created. You’ll receive an e-mail with instructions about activating your account shortly.”>

<WizardSteps>

<asp:CreateUserWizardStep runat=”server” Title=””></asp:CreateUserWizardStep>

<asp:CompleteWizardStep runat=”server” Title=””></asp:CompleteWizardStep> </WizardSteps>

<MailDefinition BodyFileName=”~/StaticFiles/OptInEmail.html”

From=”Appointment Booking <[e-mail]>” IsBodyHtml=”True” Subject=”Please Confirm Your Account With the Appointment Booking System”>

</MailDefinition>

</asp:CreateUserWizard>

This code has a few important attributes. First, you should notice that DisableCreatedUser has been set to True and LoginCreatedUser has been set to False. This means that when the account is created, it is not activated yet, and the user is not logged on automatically.

The other important attribute is BodyFileName of the MailDefinition element. This property allows you to point to a text file that is used as a template for the body of the e-mail message that users receive after they sign up for an account. If you look at that file, you’ll see some text mixed with placeholders marked by double hash marks (##). You’ll also see a link back to the ConfirmAccount.aspx page that has a query string variable called Id. Users who receive the e-mail must click this link to activate their account.

340

Appointment Booking System

This file is read by the CreateUserWizard control right before it fires its SendingMail event. Inside that event, the contents of that file are available through the Message.Body property of the e argument. Using some simple Replace methods, the placeholders are replaced with the user’s data to customize the e-mail message:

e.Message.Body = e.Message.Body.Replace(“##Id##”, _ Membership.GetUser(CreateUserWizard1.UserName).ProviderUserKey.ToString())

e.Message.Body = e.Message.Body.Replace(“##UserName##”, CreateUserWizard1.UserName)

Dim applicationFolder As String = _ Request.ServerVariables.Get(“SCRIPT_NAME”).Substring(0, _ Request.ServerVariables.Get(“SCRIPT_NAME”).LastIndexOf(“/”))

e.Message.Body = e.Message.Body.Replace(“##FullRootUrl##”, _ Helpers.GetCurrentServerRoot & applicationFolder)

This code replaces the placeholders in the message body with actual values. The ID in the query string back to the account confirmation page is filled with the unique ID of the user by calling the following:

Membership.GetUser(CreateUserWizard1.UserName).ProviderUserKey.ToString()

The same technique is deployed to add the user’s UserName to the e-mail.

The second half of the code retrieves the name of the current server and application folder. It uses the GetCurrentServerRoot method defined in the Helpers class to retrieve information about the server’s name or IP address and port number that is used. This is very useful during development because the path to the account confirmation page is not hard-coded in the application. On your development workstation, this path might be something similar to http://localhost:2137/AppointmentBooking, whereas on a production server it could return something like http://www.YourDomain.com. This ensures that the link in the e-mail always points back to the server from which the e-mail was requested.

After the final Replace method has been called, the mail body contains a link to the confirmation page that looks similar to this: http://localhost:2137/AppointmentBooking/ConfirmAccount

.aspx?Id=be2e8119-09ba-485e-8e09-d20218ef3f64.

Once users click that link in the e-mail message, they are taken to the Account Confirmation page that activates the account with the following code in the Page_Load event:

Try

Dim userId As Guid = New Guid(Request.QueryString.Get(“Id”))

Dim myUser As MembershipUser = Membership.GetUser(userId) If myUser IsNot Nothing Then

myUser.IsApproved = True Membership.UpdateUser(myUser)

plcSuccess.Visible = True Else

lblErrorMessage.Visible = True End If

Catch ex As Exception lblErrorMessage.Visible = True

End Try

341

Chapter 10

This code retrieves the unique user ID from the query string and creates a new Guid from it. This Guid is used to retrieve a user from the Membership provider. If the MembershipUser object does not equal Nothing, it means the user was found and the IsApproved field is set to True, the user account is updated, and a confirmation message is shown. In all other cases, an error message is displayed.

With the explanation of the double opt-in technique, you have come to the end of the explanation of the public area of the web site. What remains are a few pages in the Management section of the site.

The Management Section

Many of the concepts and techniques in the Management section shouldn’t be new for you. For example, the page that lists the appointment details (AppointmentDetails.aspx) uses code you also saw in the Web Shop application in Chapter 9. Also, pages like BookingObjects.aspx (that lists the available booking objects) and CreateUpdateBookingObject.aspx contain a lot of familiar code. However, a few pages with a twist need to be discussed in more detail.

Saving Configuration Information

The first page worth looking at is the page that allows you to change the application’s settings that are stored in the Web.config file. This page is called Configuration.aspx. If you look at the markup for the page, you won’t notice many odd things. The page contains an HTML table with two text boxes, a checkbox, and two HourPicker controls that have their Text, Checked, or SelectedHour properties bound to a value in the Web.config file using the expression syntax you saw before:

<asp:TextBox ID=”txtBookingObjectNamePlural” runat=”server”

Text=”<%$ AppSettings:BookingObjectNamePlural %>”></asp:TextBox>

So far, not much is new. However, if you look at the code for the button that saves the settings, things turn out to be very different:

Dim myConfig As System.Configuration.Configuration = _

WebConfigurationManager.OpenWebConfiguration(“~/”)

Dim myElement As KeyValueConfigurationElement = Nothing

myElement = myConfig.AppSettings.Settings(“BookingObjectNameSingular”) If Not myElement Is Nothing Then

myElement.Value = txtBookingObjectNameSingular.Text End If

myElement = myConfig.AppSettings.Settings(“BookingObjectNamePlural”) If Not myElement Is Nothing Then

myElement.Value = txtBookingObjectNamePlural.Text End If

myElement = myConfig.AppSettings.Settings(“RequireCommentsInRequest”) If Not myElement Is Nothing Then

myElement.Value = chkRequireCommentsInRequest.Checked.ToString() End If

myElement = myConfig.AppSettings.Settings(“FirstAvailableWorkingHour”)

342

Appointment Booking System

If Not myElement Is Nothing Then

myElement.Value = hpStartTime.SelectedHour.ToString() End If

myElement = myConfig.AppSettings.Settings(“LastAvailableWorkingHour”) If Not myElement Is Nothing Then

myElement.Value = hpEndTime.SelectedHour.ToString() End If

Try myConfig.Save()

Response.Redirect(“Default.aspx”) Catch ex As Exception

litErrorMessage.Visible = True End Try

Unlike how it was in ASP.NET 1.x, writing to the Web.config file is now very easy. Three steps are involved: first, you need to get a reference to the current configuration using the OpenWebConfiguration method of the WebConfigurationManager class. As the argument for the method, “~/” is passed to indicate a virtual path to the root of the current site. This method has a few overloads allowing you to open other files at different locations as well.

Once myConfig holds a reference to the config file, you then use the Settings property of the AppSettings class to get a reference to a specific <appSettings> key defined in that Web.config file. To get that reference, all you need to do is pass the name of the key to the indexer of the Settings object. With this code example, all that is changed are a few values in the <appSettings> node, but the Configuration class allows you to change other sections as well. For example, to change the connection string for the web site, you can use this code:

myConfig.ConnectionStrings.ConnectionStrings(“AppointmentBooking”) _

.ConnectionString = New Connection String

Notice how the ToString() method is used for the RequireCommentsInRequest, StartTime, and

EndTime properties. Because the Value of the KeyValueConfigurationElement class is a string (the Web.config file is just a text file so it can store only strings), you need to cast these Boolean and integer values to a proper string.

The third and final step in writing to the Web.config file is calling Save() on the Configuration element. Be aware that when you do that, the application will silently restart. This isn’t a problem in the current Appointment Booking System because it doesn’t use sessions. However, if you use this code in a web site that does use sessions, your users might lose valuable information when the application restarts. Just to be sure the administrator knows what he’s doing, the application shows a warning when the page gets submitted. This warning is added in the Page_Load of the page:

If Not Page.IsPostBack Then

btnSave.OnClientClick = “return confirm(‘Saving these settings “ & _

“might result in a loss of data for currently active users. Are you sure “ & _ “you want to continue?’);”

End If

343

Chapter 10

This adds a confirmation dialog to the Save button so you get a warning dialog, and a way to cancel the operation when you try to save the settings.

Although writing to the configuration file is now really easy in ASP.NET 2.0, you could still run into problems when you execute this code. If the Web.config file on disk is read-only or the current user’s account doesn’t have the necessary permissions, an error is raised. When that happens, the Catch block in the code displays a label with some troubleshooting tips.

Managing Booking Objects

Another page that you need to examine a little closer is CreateUpdateBookingObject.aspx. Most of the page contains code you’re familiar with to insert or update a booking object, but there is one control you may not have seen before. At the end of the markup for the page, you’ll see a CheckBoxList control:

<asp:CheckBoxList ID=”chkLstWorkingdays” runat=”server” DataSourceID=”odsWorkingDays” DataTextField=”Description” DataValueField=”Id”>

</asp:CheckBoxList>

This control works similarly to other list controls, such as the <asp:DropDownList>, but it displays checkboxes with a label that allows a user to select one or more items. The DataSourceID of the control is set to the ObjectDataSource control odsWorkingDays, which in turns gets a simple two-column DataSet with the working days and their IDs from the database by calling GetWorkingDays in the

BookingObjectManager class.

When you’re editing a BookingObject, the selected working days for that object need to be preselected. This is done with the code in Page_Load that also sets the other properties of the BookingObject:

chkLstWorkingdays.DataBind()

If Convert.ToBoolean(myBookingObject.AvailableOnWeekdays And Weekdays.Sunday) Then chkLstWorkingdays.Items.FindByValue(“1”).Selected = True

End If

‘ Other days are checked here

To make sure that the CheckBoxList is displaying all working days from the database, DataBind() is called first. Then the AvailableOnWeekdays property of the BookingObject is checked to see if it contains a selection for Sunday. This is done with the following statement:

myBookingObject.AvailableOnWeekdays And Weekdays.Sunday

If this returns True, it means that Sunday is a part of the AvailableOnWeekdays property and the code in the If block runs. That code simply searches the Items collection of the CheckBoxList for an item with a value of 1 (the ID for Sunday in the database) and then changes its Selected property to True. This process is repeated for all the seven days in the Weekdays enumeration.

This process is more or less reversed when the BookingObject is updated again in the Click event of the Update button:

For Each myItem As ListItem In chkLstWorkingdays.Items

If myItem.Selected = True Then

Select Case Convert.ToInt32(myItem.Value)

344

Appointment Booking System

Case 1 ‘ Sunday

myBookingObject.AvailableOnWeekdays = _

myBookingObject.AvailableOnWeekdays Or Weekdays.Sunday

‘ Other days are checked here

End Select

End If

Next

This code loops through all of the checkboxes in the CheckBoxList control. When the item is checked, its value is used in a Select Case statement. In this code, when the Value of the checkbox equals 1, it means Sunday was selected and the value of Sunday is added to the AvailableOnWeekdays property of the BookingObject.

The code for the method GetBookingObject in the BookingObjectManagerDB class, responsible for getting a single BookingObject from the database, uses the same technique to fill the property.

The only section that treats the selected working days differently is the code in the method SaveBooking Object in the same BookingObjectManagerDB. Because SQL Server has no knowledge of the custom Weekdays enumeration, the stored procedure that inserts and updates a BookingObject has seven additional parameters; one for each of the available working days. The values of these parameters are used in the procedure sprocBookingObjectInsertUpdateSingleItem to create a relation between a Booking Object and a WorkingDay by adding a record in the BookingObjectWorkingDay table:

IF (@sunday = 1)

INSERT INTO BookingObjectWorkingDay

(BookingObjectId, WorkingDayId)

VALUES (@id, 1)

When @Sunday equals 1, it means Sunday was selected and a record is inserted in BookingObjectWorkingDay.

Viewing Appointments

The final page that needs to be discussed is the page that allows users to get a report on the appointments that have been made in the system. Right now, this page is accessible only by users in the Manager role, simply because the entire Management folder has been blocked for all users except managers. In a realworld scenario, it’s probably wise to make a second group, such as Planning, that is allowed to view the reports without being able to make changes to the booking objects and the application settings. You can use the Web Site Administration Tool (choose Website ASP.NET Configuration from the main menu in Visual Web Developer) to create the role and then add an additional <location> node to the Web.config file to open the Management folder for that group.

You can access the Appointments page by clicking Appointments in the Management menu. You then need to select a date from the calendar for which you want to see the appointments. You’ll see a screen similar to the one in Figure 10-19.

345

Chapter 10

Figure 10-19

At first glance, you may not notice anything special about this page. All you see is a list with appointments, each one nicely placed under its parent booking object. However, if you look at the source for both the markup of the page and its code-behind, things are not as easy as they look. The markup for the page features a nested Repeater. A nested Repeater is a Repeater control that has been placed inside the <ItemTemplate> of another Repeater, referred to as the outer Repeater. Figure 10-20 shows three rows of an outer Repeater. The <ItemTemplate> for each row displays some text (indicated by the three thin lines) and the nested Repeater (the four stacked rectangles).

With a nested Repeater you can display hierarchical data that you cannot display with a single ASP.NET control. The Appointments page makes use of this technique to display the appointments (inside the nested Repeater) for each booking object (in the outer Repeater). The following code snippet shows a trimmed-down version of both Repeater controls:

<asp:Repeater ID=”repBookingObjects” runat=”server”> <ItemTemplate>

<h1>Appointments for <asp:Literal ID=”litTitle” runat=”Server” Text=’<%# Container.DataItem(“Title”) %>’ /></h1>

<asp:Repeater ID=”repAppointments” runat=”server”> <ItemTemplate>

Appointment with <asp:Literal ID=”litUserName” runat=”server” Text=’<%# Container.DataItem(“UserName”) %>’ />

</ItemTemplate>

</asp:Repeater>

</ItemTemplate>

</asp:Repeater>

346