ASP.NET 2.0 Instant Results

Appointment Booking System

The Availability Checker is discussed first, followed by the Appointment Wizard. You then see how the Sign Up page with its double opt-in feature works. Near the end of the chapter, you see some of the more complicated pages in the Management section.

The Availability Checker

As you saw in the section “Using the Appointment Booking System” at the beginning of this chapter, the Availability Checker displays a time sheet for all available booking objects for a specific date. The process for displaying the time sheet consists of the following steps:

1.Get the requested date from an <asp:Calendar> control on the page.

2.Get a list with available booking objects and appointments for the selected date from the database in a DataSet.

3.Build up the time sheet by adding a table row for each available booking object:

For each booking object in the DataSet, add an HTML row to the HTML table.

For each booking object being added to the table, get the appointments for the selected date from the database.

For each hour on the time sheet, see if the booking object is available on that hour. If the object is available, see if the hour conflicts with an existing appointment. If the hour doesn’t conflict, add a link to allow a user to make an appointment.

4.Add a legend below the table, to visually differentiate the available and the unavailable hours.

The user interface for this functionality consists of two parts: the page CheckAvailability.aspx located in the root of the site and a user control called TimeSheet.ascx that you find in the Controls folder. Technically, the Time Sheet doesn’t have to be a user control and could have been placed in the CheckAvailability.aspx directly. However, now that it is implemented as a user control, it’s easy to reuse its functionality. For example, you could add another TimeSheet control on the homepage that shows the availability for today’s date.

A huge improvement in working with user controls in Visual Web Developer is design-time support. When your user control has public properties, they show up automatically in the control’s Property grid. Changes to public properties are now stored in the markup for the control automatically. Take a look at the TimeSheet control in the page to see how this works:

<Wrox:TimeSheet ID=”TimeSheet1” runat=”server” StartTime=”<%$ AppSettings:FirstAvailableWorkingHour %>” EndTime=”<%$ AppSettings:LastAvailableWorkingHour %>” />

In this case, both the StartTime and EndTime properties get their values from the Web.config file (you see later what these properties are used for). Now if you look at the Property grid for the control you’ll see Figure 10-16.

327

Chapter 10

Figure 10-16

When you make a change to one of the properties, say you change EndTime to 23, the changes are automatically persisted in the control’s markup:

<Wrox:TimeSheet ID=”TimeSheet1” runat=”server” StartTime=”<%$ AppSettings:FirstAvailableWorkingHour %>” EndTime=”23” />

Also, design-time rendering of user controls is now supported. Previous versions of Visual Studio just displayed a gray box instead of the actual control. Because the TimeSheet control is built up almost completely in the code-behind for the file at run time, you cannot benefit from this enhancement in the CheckAvailability.aspx.

In addition to the markup for the TimeSheet control, CheckAvailability.aspx contains a number of other controls, including a Calendar and a Label to allow a user to select a date for which they want to see the availability. The introduction text of the page also contains a number of <asp:Literal> controls that look like this:

<asp:Literal ID=”Literal1” runat=”server”

Text=”<%$ AppSettings:BookingObjectNameSingular %>”></asp:Literal>

The Text property of the Literal control is set using the new declarative expression syntax that allows you to bind properties to application settings, connection strings, and localization resources (used to create multi-lingual web sites). In this case, the Text property is directly bound to the appSetting key called BookingObjectNameSingular. At run time, the value for this key is retrieved from the Web.config file and added to the page. You see this expression syntax used in other pages where the friendly name of the booking object must be displayed.

Another important part of the Availability Checker page is the Calendar control. Whenever the user selects a new date on the calendar, the control fires its SelectionChanged event, which is handled in the code-behind for the page:

Protected Sub calAppointmentDate_SelectionChanged(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles calAppointmentDate.SelectionChanged

If calAppointmentDate.SelectedDate.CompareTo(DateTime.Now.Date) < 0 Then valSelectedDate.IsValid = False

divCalendar.Style.Item(“display”) = “block”

328

Appointment Booking System

Else

divCalendar.Style.Item(“display”) = “none” lblSelectedDate.Visible = True lblSelectedDate.Text = “You selected: <strong>” & _

calAppointmentDate.SelectedDate.ToShortDateString() & “</strong>”

lblInstructions.Text = _

“   Click the calendar again to select a different date:” LoadData()

End If End Sub

This code first validates the selected date. If the new date is in the past, the IsValid property of the custom validator valSelectedDate is set to False. Otherwise, the label that displays the selected date is updated and the LoadData method is called.

The LoadData method retrieves time sheet information from the database by calling AppointmentManager

.GetTimeSheet and passing it the selected date, as you can see in the following code block:

Private Sub LoadData()

If Not calAppointmentDate.SelectedDate = DateTime.MinValue Then

TimeSheet1.DataSource = _

AppointmentManager.GetTimeSheet(calAppointmentDate.SelectedDate)

TimeSheet1.SelectedDate = calAppointmentDate.SelectedDate

TimeSheet1.DataBind()

End If

End Sub

GetTimeSheet of the AppointmentManager class then forwards its call to the AppointmentManagerDB class, which retrieves the time sheet information from the database. Take a look at the code for this method to see how it works:

Public Shared Function GetTimeSheet(ByVal selectedDate As DateTime) As DataSet Dim myDataSet As DataSet = New DataSet()

Using myConnection As New SqlConnection(AppConfiguration.ConnectionString) Try

Dim myCommand As SqlCommand = _

New SqlCommand(“sprocTimesheetSelectList”, myConnection) myCommand.CommandType = CommandType.StoredProcedure

myCommand.Parameters.AddWithValue(“@selectedDate”, selectedDate)

Dim myDataAdapter As SqlDataAdapter = New SqlDataAdapter() myDataAdapter.SelectCommand = myCommand myDataAdapter.Fill(myDataSet)

myDataSet.Tables(0).TableName = “BookingObject” myDataSet.Tables(1).TableName = “Appointment”

myDataSet.Relations.Add(“BookingObjectAppointment”, _ myDataSet.Tables(“BookingObject”).Columns(“Id”), _ myDataSet.Tables(“Appointment”).Columns(“BookingObjectId”))

Return myDataSet

329

Chapter 10

Catch ex As Exception Throw

Finally myConnection.Close()

End Try

End Using

End Function

Similar to other data access code you have seen in this book, this method creates a SqlConnection and a SqlCommand object to retrieve information from the database. What’s different in this method is that the stored procedure sprocTimesheetSelectList does not return a single result set, but that it returns a result set for both the booking objects and the appointments:

CREATE PROCEDURE sprocTimesheetSelectList

@selectedDate datetime

AS

SELECT DISTINCT b.Id, b.Title, b.StartTime, b.EndTime,

(

SELECT COUNT(*) FROM BookingObjectWorkingDay WHERE BookingObjectId = b.Id AND BookingObjectWorkingDay.WorkingDayId = DATEPART(dw, @selectedDate)

) AS AvailableOnSelectedDay

FROM

BookingObject b

SELECT BookingObjectId, StartDate, EndDate

FROM Appointment

WHERE

CONVERT(varchar(8), StartDate, 112) = CONVERT(varchar(8), @selectedDate, 112) OR CONVERT(varchar(8), EndDate, 112) = CONVERT(varchar(8), @selectedDate, 112)

ORDER BY StartDate

As you can see, this procedure has two SELECT statements; the first returns a list with all the available booking objects and includes their ID, title, and the hours they are available. The inner SELECT COUNT(*) statement is used to determine whether the booking object is available on the requested weekday by looking at the junction table BookingObjectWorkingDay. It compares the weekday number (1 for Sunday, 2 for Monday, and so on) against a record in the junction table for each BookingObject. When the count returns 1, the booking object is available on the requested date; 0 means the object is not available.

The second SELECT statement returns a list with all the current appointments for all booking objects on the requested date.

330

Appointment Booking System

In the GetTimeSheet method, the two result sets that are returned from this stored procedure are added to a DataSet by calling Fill. This demonstrates that a DataSet can really be seen as an in-memory database. In many circumstances, a DataSet is used to hold just a single DataTable. However, in this code two DataTables are stored in the DataSet. By default, multiple tables in a DataSet get a sequential name, like Table2, Table3, and so forth. To be able to refer to the DataTables by name, they are renamed as soon as they have been added to the DataSet:

myDataSet.Tables(0).TableName = “BookingObject”

myDataSet.Tables(1).TableName = “Appointment”

The first DataTable (with an index of 0) holds the booking objects returned from the database, so it gets renamed to BookingObject. The second table is renamed to Appointment.

The final step in the GetTimeSheet method adds a DataRelation between these two tables. The appointments in the second DataTable have a BookingObjectId that points back to a BookingObject in the first DataTable. To relate these two result sets inside the DataSet, the following code is used:

myDataSet.Relations.Add(“BookingObjectAppointment”, _ myDataSet.Tables(“BookingObject”).Columns(“Id”), _ myDataSet.Tables(“Appointment”).Columns(“BookingObjectId”))

This DataRelation, called BookingObjectAppointment, allows you to retrieve all the child appointments for a certain booking object. The relation works very similarly to a traditional relation in a database in that it allows you to retrieve related records in a child table for a record in the parent table. You can get the rows in the child table by calling GetChildRows, which you see at work a little later.

At the end of this method, the DataSet is returned to the calling code in CheckAvailability.aspx, and then assigned to the DataSource property of the TimeSheet.ascx control:

TimeSheet1.DataSource = _

AppointmentManager.GetTimeSheet(calAppointmentDate.SelectedDate)

TimeSheet1.SelectedDate = calAppointmentDate.SelectedDate

TimeSheet1.DataBind()

The DataBind method of the user control contains a lot of code, so not all of it is covered, but instead you’ll see a few important sections. The method starts off with checking if the DataSource and the SelectedDate have been set. Both are critical properties for the TimeSheet control to operate correctly, so when one of the two is missing, an error is raised.

The code then declares two variables that can hold a DataRow (a row from a DataTable inside the DataSet): one to hold a BookingObject and one for an appointment. Two other variables are declared that can hold a TableRow and a TableCell (that represent the rows and cells of an HTML <table> in the browser).

Next, a new TableRow and a TableCell are created. The cell’s Text property is set to the friendly name of the booking object with AppConfiguration.BookingObjectNameSingular. The cell is then added to the TableRow.

The number of hours that the TimeSheet control can display is configurable through two public properties on the control: StartTime and EndTime. For all the hours between these two values, a column is added to the HTML table with the following code:

331

Chapter 10

For i As Integer = _StartTime To _EndTime myTableCell = New TableCell myTableCell.Text = i.ToString()

myTableRow.Cells.Add(myTableCell) Next TimeSheetTable.Rows.Add(myTableRow)

So if the Appointment Booking System is set up to make appointments for conference rooms, and the TimeSheet user control must display the hours from 7 a.m. until 7 p.m., the first row in the table looks like Figure 10-17.

Figure 10-17

Both the friendly name of the booking object and the numbers serve as the column header for the rows that are about to be added.

The code continues with another loop, this time for each row in the DataTable called BookingObject:

For Each myBookingObjectRow In _DataSource.Tables(“BookingObject”).Rows

Inside this loop, a new TableRow is created that gets a cell with the name of the booking object:

myTableRow = New TableRow() myTableCell = New TableCell()

myTableCell.Text = Convert.ToString(myBookingObjectRow(“Title”)) myTableCell.Wrap = False

myTableRow.Cells.Add(myTableCell)

The next step is to create a table cell for each of the available hours on the TimeSheet. As with the column headers you just saw, this is done with a loop that runs from _StartTime till _EndTime. On each iteration of this loop, a new TableCell is created and added to the TableRow. A new HyperLink control is created and added to the TableCell:

Dim myHyperLink As New HyperLink() myHyperLink.NavigateUrl = String.Format( _

“~/CreateAppointment.aspx?” & _ “BookingObjectId={0}&SelectedDate={1}&StartTime={2}”, _ Convert.ToString(myBookingObjectRow(“Id”)), _ Server.UrlEncode(_SelectedDate.ToString()), i.ToString())

myHyperLink.Text = “Book” myTableCell.Controls.Add(myHyperLink) myTableCell.CssClass = “TimesheetCellFree”

This new HyperLink points to the CreateAppointment.aspx page and passes the selected date, the ID of the booking object, and the current hour to that page in the query string. The Appointment Wizard uses these query string variables to preselect the controls in the wizard, making it easier for the user to make an appointment for the requested booking object, date, and time.

332

Appointment Booking System

Once the TableCell contains the hyperlink, the code checks whether the current hour (the hour being added to the TimeSheet) is actually available for new appointments. Four reasons exist for why the current hour could not be available for booking:

1.The booking object is not available on the day of the week that the time sheet is currently displaying.

2.The current hour is less than the starting hour of the booking object.

3.The current hour is greater than the end hour of the booking object.

4.There is already an appointment for the booking object that overlaps with the current hour.

The first three reasons are checked by a single If statement:

If i >= Convert.ToDateTime(myBookingObjectRow(“StartTime”)).Hour _ And i <= Convert.ToDateTime(myBookingObjectRow(“EndTime”)).Hour _

And Convert.ToInt32(myBookingObjectRow(“AvailableOnSelectedDay”)) > 0 Then

If all three of these conditions are not met, the code in the Else clause of this If statement removes the hyperlink from the TableCell and sets the cell’s CssClass to TimesheetCellBusy:

Else

myTableCell.CssClass = “TimesheetCellBusy” myTableCell.Controls.Clear() myTableCell.Text = “ ”

End If

If all three conditions are met, the code continues to query the appointments for the current booking object. It does this with the GetChildRows method of the DataRow with the booking object:

For Each myAppointmentRow In _

myBookingObjectRow.GetChildRows(“BookingObjectAppointment”)

As the relationName argument for this method, the string BookingObjectAppointment is passed, which is the name of the relation that was set up in the GetTimeSheet method you saw earlier in this chapter. Through this relation, the GetChildRows method is able to correctly identify the appointment rows in the appointment DataTable that are related to the BookingObject row currently held in myBookingObjectRow. The method GetChildRows returns those rows as an array of DataRows so you can use For Each to loop through them:

For Each myAppointmentRow In _ myBookingObjectRow.GetChildRows(“BookingObjectAppointment”)

Dim currentDateAndTime As DateTime = _SelectedDate.Date.AddHours(i)

Dim startDate As DateTime = Convert.ToDateTime(myAppointmentRow(“StartDate”)) Dim endDate As DateTime = Convert.ToDateTime(myAppointmentRow(“EndDate”))

If currentDateAndTime >= startDate And currentDateAndTime < endDate Then myTableCell.CssClass = “TimesheetCellBusy” myTableCell.Controls.Clear()

myTableCell.Text = “ ” Exit For

End If Next

333

Chapter 10

This code loops through all the appointments returned by GetChildRows and sees if they overlap with the current date and time that is added to the TimeSheet. If the appointment does overlap, the

HyperLink control is removed from the TableCell and its CssClass is set to TimesheetCellBusy, making the cell unavailable.

The remainder of the DataBind method creates an empty TableRow and a TableRow that holds a legend with the colors and a label for available and unavailable hours. The code is pretty straightforward and has quite a lot of comments, so you should be able to figure out how it works.

To see how all the code for the TimeSheet control ends up in the browser, imagine that the system is used to book conference rooms. There are three conference rooms in the system. All three can be booked between 7 a.m. and 7 p.m. For the first booking room, there is already an appointment on November 14, from 2 p.m. until 4 p.m. If you request the time sheet with this setup, you see what appears in Figure 10-18.

Figure 10-18

In the time sheet you can see that both booking objects can normally be booked from 7 a.m. until (and not including) 7 p.m. Because Conference Room 1–East Wing already has an appointment from 2 p.m. until 4 p.m., those two hours are marked as unavailable on the time sheet.

From this time sheet, users can click the Book link for a specific booking object and hour. This transfers them to CreateAppointment.aspx and passes along the selected date, hour, and ID of the booking object.

The page that allows users to create appointments is discussed next.

The Appointment Wizard

The CreateAppointment.aspx page contains a single <asp:Wizard> control and a <asp:MultiView> control. The Wizard control collects information from users about the appointment they want to book, and the MultiView is used to display information about the success or failure of this appointment request.

The Wizard control contains six wizard steps; one for each of the six Wizard menu items you saw at the beginning of this chapter when the functionality of the Appointment Wizard was discussed. The following table lists these steps and explains the data each step collects:

Step Title	Step Index	Description
Introduction	0	Displays a welcome message.
Select [Booking Object]	1	Displays a drop-down so the user can select a
		booking object. The title of the step is determined
		at run time with code in the Page_Load event.

334

			Appointment Booking System
	Step Title	Step Index	Description
	Select Date	2	Displays a calendar so the user can select a date
			for the appointment.
	Select Time	3	Displays a drop-down with starting hours (with
			an HourPicker control that is explained later) and
			a drop-down for the duration of the appointment.
	Comments	4	Displays a text area so the user can add comments
			to the appointment request.
	Review Your Request	5	Displays a summary of all the data the user
			entered.

The StepType of the first and last step has been set to Start and Finish, respectively. The other four steps have their type set to Step. The StepType defines the buttons placed on the surface for each step. With a Start step, you only see a Next button; for the Finish type, you see a Previous and a Finish button; and for all the steps in between you see a Previous and a Next button, allowing you to move forward and backward through the wizard steps. If you want to block your users from going back to a previous step, you can set the AllowReturn property of the step to False. In the case of the Appointment Wizard, this is not necessary, because the user can follow an arbitrary path through each of the steps of the wizard.

When a user clicks the Next button on one of the steps, the current step is validated with code in the Wizard’s NextButtonClick event:

Select Case e.CurrentStepIndex Case 1

If Not ValidateStep(1) Then e.Cancel = True

End If

‘ Other steps are validated here End Select

This code checks that a booking object has been selected in the second wizard step (with an index of 1). If the validation fails, because a required field wasn’t filled in, the Cancel argument of the WizardNavigation EventArgs argument is set to True. When this property is set to True, the wizard does not proceed to the next step, but stays on the current one instead so the user can fill in the required data.

The ValidateStep method itself uses a Select Case statement to determine which step needs to be validated. Inside the Case block for each step index, the required controls are validated, as you can see in the following code that checks the comments on step five (with an index of 4):

Case 4

If AppConfiguration.RequireCommentsInRequest AndAlso _ txtComments.Text.Length = 0 Then

reqComments.IsValid = False wizAppointment.ActiveStepIndex = 4 Return False

End If

335

Chapter 10

When the configuration file dictates that a comment is required and the txtComments text box is still empty, the RequiredFieldValidator control’s IsValid property is set to False. Then the ActiveStep Index is set to 4, to ensure that the user sees the error message and can fill in the required comment. At the end of the If block, the method returns False to signal calling code that validation failed. When validation succeeds, the method returns True.

This process is repeated for the other two steps (with the booking object and the date) to ensure they contain valid data.

Once all the data is filled in correctly, users are presented with the final step that displays all the data they entered. This is done in the ActiveStepChanged event when the ActiveStepIndex is 5 (the last step). Before the data is shown to the user, ValidateAllSteps is called to ensure that each of the previous steps is valid. Finally, when the user clicks the Finish button to finalize the appointment request, the following code runs:

Protected Sub wizAppointment_FinishButtonClick(ByVal sender As Object, _ ByVal e As System.Web.UI.WebControls.WizardNavigationEventArgs) _ Handles wizAppointment.FinishButtonClick

Page.Validate()

If Page.IsValid Then

If ValidateAllSteps() Then

wizAppointment.Visible = False

Dim myAppointment As New Appointment() myAppointment.StartDate = _

calStartDate.SelectedDate.AddHours(hpTime.SelectedHour) myAppointment.EndDate = _

myAppointment.StartDate.AddHours(Convert.ToInt32( _ lstDuration.SelectedValue))

myAppointment.BookingObjectId = _

Convert.ToInt32(lstBookingObject.SelectedValue) myAppointment.Comments = _

Server.HtmlEncode(txtComments.Text)

Dim myUser As MembershipUser = Membership.GetUser() myAppointment.UserName = myUser.UserName myAppointment.UserEmailAddress = myUser.Email

If AppointmentManager.CheckAppointment(myAppointment) Then

AppointmentManager.CreateAppointment(myAppointment)

MultiView1.ActiveViewIndex = 0

Else

MultiView1.ActiveViewIndex = 1

End If

End If

End If

End Sub

The code first validates the entire page by calling Page.Validate(). If the entire page is valid, it calls the custom ValidateSteps method again to ensure all steps contain valid data. Then a new Appointment object is instantiated and its public properties are filled with the values from the controls on the wizard and from the user’s membership data. The code then calls CheckAppointment to see if the appointment can be made. The code for this method in the AppointmentManagerDB class is pretty straightforward, so it isn’t shown it here. It’s the stored procedure for this method that needs a close examination:

336