ADO.NET and Data Binding
The Web Form application you created in the previous section is perfectly functional, but only contains static data. In addition, the event-booking process does not include persisting event data. To solve both of these problems, you can make use of ADO.NET to access data stored in a database, so that you can store and retrieve event data along with the lists of rooms and attendees.
Data binding makes the process of retrieving data even easier. Controls such as list boxes (and some of the more specialized controls you’ll look at a bit later) come enabled for this technique. They can be bound to any object that exposes an IEnumerable, ICollection, or IListSource interface, as well as any of the data source Web server controls.
In this section you start by updating your event-booking application to be data-aware, and then move on to take a look at some of the other results you can achieve with data binding, using some of the other data-aware Web controls.
Updating the Event-Booking Application
To keep things separate from the last example, create a new Web site called PCSWebApp3 in the directory C:\ProCSharp\Chapter32\ and copy the code from the PCSWebApp2 application created earlier into the new application. Before you start on your new code, take a look at the database you’ll be accessing.
The Database
For the purposes of this example, you will use a Microsoft SQL Server Express database called MeetingRoomBooker.mdf, which is part of the downloadable code for this book. For an enterprisescale application it would make more sense to use a full SQL Server database, but the techniques involved are practically identical, and SQL Server Express makes life a bit easier for testing. The code will also be identical.
| Tip | If you are adding your own version of this database you will need to add a new database to the App_Data folder in the Solution Explorer. You can do this by right-clicking on the App_Data folder, selecting Add New Item, selecting a database, naming it MeetingRoomBooker, and clicking on Add. This will also configure a data connection in the Server Explorer window ready for you to use. Next you can add the tables required as shown in the next sections and supply your own data. Alternatively, to use the downloadable database with your own code, simply copy it to the App_Data directory for your Web site. |
The database provided contains three tables:
-
Attendees, which contains a list of possible event attendees
-
Rooms, which contains a list of possible rooms for events
-
Events, which contains a list of booked events
Attendees
The Attendees table contains the columns shown in the following table.
| Column | Type | Notes |
|---|---|---|
| ID | Identity, primary key | Attendee identification number |
| Name | varchar, required, 50 chars | Name of attendee |
| | varchar, optional, 50 chars | E-mail address of attendee |
Open table as spreadsheet The supplied database includes entries for 20 attendees, all with their own (fictional) e-mail addresses. You can envision that in a more developed application, e-mails could automatically be sent to attendees when a booking is made, but this is left to you as an optional exercise using techniques found elsewhere in this book.
Rooms
The Rooms table contains the columns shown in the following table.
| Column | Type | Notes |
|---|---|---|
| ID | Identity, primary key | Room identification number |
| Room | varchar, required, 50 chars | Name of room |
Twenty records are supplied in the database.
Events
The Events table contains the columns shown in the following table.
| Column | Type | Notes |
|---|---|---|
| ID | Identity, primary key | Event identification number |
| Name | varchar, required, 255 chars | Name of event |
| Room | int, required | ID of room for event |
| AttendeeList | text, required | List of attendee names |
| EventDate | datetime, required | Date of event |
A few events are supplied in the downloadable database.
Binding to the Database
The two controls you are going to bind to data are attendeeList and roomList. Before you do this you have to add SqlDataSource Web server controls that map to the tables you want to access in the MeetingRoomBooker.mdf database. The quickest way to do this is to drag them from the toolbox onto the Default.aspx Web Form and configure them via the Configuration wizard. Figure 32-10 shows how to access this wizard for a SqlDataSource control called MRBAttendeeData.
Figure 32-10
From the first page of the data source Configuration wizard, you need to select the connection to the database created earlier. Next, choose to save the connection string as MRBConnectionString; then choose to select * (all fields) from the Attendees table in the database.
You also need to do this for two more SqlDataSource controls, MRBRoomData and MRBEventData. For these subsequent controls, you can use the saved MRBConnectionString for your connection.
Once you’ve added these data sources, you’ll see in the code for the form that the syntax is very simple:
<asp:SqlDataSource runat="server" ConnectionString="<%$ ConnectionStrings:MRBConnectionString %>" SelectCommand="SELECT * FROM [Attendees]"></asp:SqlDataSource> <asp:SqlDataSource runat="server" ConnectionString="<%$ ConnectionStrings:MRBConnectionString %>" SelectCommand="SELECT * FROM [Rooms]"></asp:SqlDataSource> <asp:SqlDataSource runat="server" ConnectionString="<%$ ConnectionStrings:MRBConnectionString %>" SelectCommand="SELECT * FROM [Events]"></asp:SqlDataSource> The definition of the connection string in use is found in the web.config file, which we’ll look at in more detail later in this chapter.
Next, you need to set the data-binding properties of the roomList and attendeeList controls. For roomList the settings required are as follows:
-
DataSourceID - MRBRoomData
-
DataTextField - Room
-
DataValueField - ID
And, similarly, for attendeeList:
-
DataSourceID - MRBAttendeeData
-
DataTextField - Name
-
DataValueField - ID
Running the application now will result in the full attendee and room data being available from your data-bound controls. You’ll use the MRBEventData control shortly.
Customizing the Calendar Control
Before discussing the addition of events to the database, you need to modify your calendar display. It would be nice to display all days where a booking has previously been made in a different color, and prevent such days from being selectable. This requires modifying the way you set dates in the calendar and the way day cells are displayed.
You’ll start with date selection. You need to check three places for dates where events are booked and modify the selection accordingly: when you set the initial date in Page_Load(), when the user attempts to select a date from the calendar, and when an event is booked and you want to set a new date to prevent the user from booking two events on the same day before selecting a new date. Because this is going to be a common feature, you might as well create a private method to perform this calculation. This method should accept a trial date as a parameter and return the date to use, which will either be the same date as the trial date, or the next available day after the trial date.
Before adding this method, you need to give your code access to data in the Events table. You can use the MRBEventData control to do this, because this control is capable of populating a DataView. To facilitate this, add the following private member and property:
private DataView eventData; private DataView EventData { get { if (eventData == null) { eventData = MRBEventData.Select(new DataSourceSelectArguments()) as DataView; } return eventData; } set { eventData = value; } } The EventData property populated the eventData member with data as it is required, with the results cached for subsequent use. Here you use the SqlDataSource.Select() method to obtain a DataView.
Next, add this method, GetFreeDate(), to the code-behind file:
private System.DateTime GetFreeDate(System.DateTime trialDate) { if (EventData.Count > 0) { System.DateTime testDate; bool trialDateOK = false; while (!trialDateOK) { trialDateOK = true; foreach (DataRowView testRow in EventData) { testDate = (System.DateTime)testRow["EventDate"]; if (testDate.Date == trialDate.Date) { trialDateOK = false; trialDate = trialDate.AddDays(1); } } } } return trialDate; } This simple code uses the EventData DataView to extract event data. First, you check for the trivial case where no events have been booked, in which case you can just confirm the trial date by returning it.
Next you iterate through the dates in the Event table, comparing them with the trial date. If you find a match, you add one day to the trial date and perform another search.
Extracting the date from the DataTable is remarkably simple:
testDate = (System.DateTime)testRow["EventDate"];
Casting the column data into System.DateTime works fine.
The first place you will use getFreeDate(), then, is back in Page_Load(). This simply means making a minor modification to the code that sets the calendar SelectedDate property:
if (!this.IsPostBack) { System.DateTime trialDate = System.DateTime.Now; calendar.SelectedDate = GetFreeDate(trialDate); } Next, you need to respond to date selection on the calendar. To do this, simply add an event handler for the SelectionChanged event of the calendar, and force the date to be checked against existing events. Double-click the calendar in the Designer and add this code:
void calendar_SelectionChanged(object sender, EventArgs e) { System.DateTime trialDate = calendar.SelectedDate; calendar.SelectedDate = GetFreeDate(trialDate); } The code here is practically identical to that in Page_Load().
The third place that you must perform this check is in response to the pressed booking button. You’ll come back to this shortly, because you have several changes to make here.
Next, you need to color the day cells of the calendar to signify existing events. To do this, you add an event handler for the DayRender event of the calendar object. This event is raised each time an individual day is rendered, and gives you access to the cell object being displayed and the date of this cell through the Cell and Date properties of the DayRenderEventArgs parameter you receive in the handler function. You simply compare the date of the cell being rendered to the dates in the eventTable object and color the cell using the Cell.BackColor property if there is a match:
void calendar_DayRender(object sender, DayRenderEventArgs e) { if (EventData.Count > 0) { System.DateTime testDate; foreach (DataRowView testRow in EventData) { testDate = (System.DateTime)testRow["EventDate"]; if (testDate.Date == e.Day.Date) { e.Cell.BackColor = System.Drawing.Color.Red; } } } } Here you are using red, which will give you a display along the lines of Figure 32-11, where June 12, 15, and 22 all contain events, and the user has selected June 24.
Figure 32-11
With the addition of the date-selection logic, it is now impossible to select a day that is shown in red; if an attempt is made, a later date is selected instead (for example, selecting June 15 results in the selection of June 16).
Adding Events to the Database
The submitButton_Click() event handler currently assembles a string from the event characteristics and displays it in the resultLabel control. To add an event to the database, you simply reformat the string created into a SQL INSERT query and execute it.
| Tip | Note that in the development environment you are using you don’t have to worry too much about security. Adding a SQL Server 2005 Express database via a Web site solution and configuring SqlDataSource controls to use it automatically gives you a connection string that you can use to write to the database. In more advanced situations, you might want to access resources using other accounts, for example a domain account used to access a SQL Server instance elsewhere on a network. The capability to do this (via impersonation, COM+ Services, or other means) exists in ASP.NET, but is beyond the scope of this book. In most cases, configuring the connection string appropriately is as complicated as things need to get. |
Much of the following code will therefore look familiar:
void submitButton_Click(object sender, EventArgs e) { if (this.IsValid) { System.Text.StringBuilder sb = new System.Text.StringBuilder(); foreach (ListItem attendee in attendeeList.Items) { if (attendee.Selected) { sb.AppendFormat("{0} ({1}), ", attendee.Text, attendee.Value); } } sb.AppendFormat(" and {0}", nameBox.Text); string attendees = sb.ToString(); try { System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection( ConfigurationManager.ConnectionStrings["MRBConnectionString"] .ConnectionString); System.Data.SqlClient.SqlCommand insertCommand = new System.Data.SqlClient.SqlCommand("INSERT INTO [Events] " + "(Name, Room, AttendeeList, EventDate) VALUES (@Name, " + "@Room, @AttendeeList, @EventDate)", conn); insertCommand.Parameters.Add( "Name", SqlDbType.VarChar, 255).Value = eventBox.Text; insertCommand.Parameters.Add( "Room", SqlDbType.Int, 4).Value = roomList.SelectedValue; insertCommand.Parameters.Add( "AttendeeList", SqlDbType.Text, 16).Value = attendees; insertCommand.Parameters.Add( "EventDate", SqlDbType.DateTime, 8).Value = calendar.SelectedDate; The most interesting thing here is how you access the connection string you created earlier, using the following syntax:
ConfigurationManager.ConnectionStrings["MRBConnectionString"].ConnectionString
The ConfigurationManager class gives you access to all manner of configuration information, all stored in the Web.Config configuration file for your Web application. You look at this in more detail later in this chapter.
After you have created your SQL command, you can use it to insert the new event:
conn.Open(); int queryResult = insertCommand.ExecuteNonQuery(); conn.Close(); ExecuteNonQuery() returns an integer representing how many table rows were affected by the query. If this is equal to 1, you know that your insertion was successful. If so, then you put a success message in resultLabel, clear EventData because it is now out of date, and change the calendar selection to a new, free, date. Because GetFreeDate() involves using EventData, and the EventData property automatically refreshes itself if it has no data, the event data stored will be refreshed:
if (queryResult == 1) { resultLabel.Text = "Event Added."; EventData = null; calendar.SelectedDate = GetFreeDate(calendar.SelectedDate.AddDays(1)); } If ExecuteNonQuery() returns a number other than 1, you know that there has been a problem. For this example you won’t worry too much about this, and simply throw an exception that is caught in the general catch block that is wrapped around the database access code. This catch block simply displays a general failure notification in resultLabel:
else { throw new System.Data.DataException("Unknown data error."); } } catch { resultLabel.Text = "Event not added due to DB access " + "problem."; } } }
This completes your data-aware version of the event-booking application.
More on Data Binding
As mentioned earlier in this chapter, the available Web server controls include several that deal with data display (GridView, DataList, DetailsView, FormView, and Repeater). These are all extremely useful when it comes to outputting data to a Web page, because they perform many tasks automatically that would otherwise require a fair amount of coding.
For starters, you’ll look at how easy using these controls can be, by adding an event list display to the bottom of the display of PCSWebApp3.
Drag a GridView control from the toolbox to the bottom of Default.aspx, and select the MRBEventData data source you added earlier for it, as shown in Figure 32-12.
Figure 32-12
This is in fact all you need to do to display a list of events under the form - try viewing the Web site now and you should see the events, as shown in Figure 32-13.
Figure 32-13
You can also make one further modification in submitButton_Click() to ensure that this data is updated when new records are added:
if (queryResult == 1) { resultLabel.Text = "Event Added."; EventData = null; calendar.SelectedDate = GetFreeDate(calendar.SelectedDate.AddDays(1)); GridView1.DataBind(); } All data-bindable controls support this method, which is normally called by the form if you call the top-level (this) DataBind() method.
One thing you’ll probably have noticed in Figure 32-13 is that the date/time display for the EventDate field is a little messy. Because you are only looking at dates, the time is always 00:00:00 - information that it isn’t really necessary to display. In the next sections, you see how this date information can be displayed in a more user-friendly fashion in the context of a DataList control. As you might expect, the DataGrid control contains many properties that you can use to format the displayed data, but I’ll leave these for you to discover.
Data Display with Templates
Many of the data display controls allow you to use templates to format data for display. Templates, in an ASP.NET sense, are parameterized sections of HTML that are used as elements of output in certain controls. They enable you to customize exactly how data is output to the browser, and can result in professional-looking displays without too much effort.
Several templates are available to customize various aspects of list behavior, but the one template that is essential for both Repeater and DataList is <ItemTemplate>, which is used in the display of each data item. You declare this template (and all the others) inside the control declaration, for example:
<asp:DataList Runat="server" ... > <ItemTemplate> ... </ItemTemplate> </asp:DataList> Within template declarations you will normally want to output sections of HTML along with parameters from the data that is bound to the control. You can use a special syntax to output such parameters:
<%# expression %>
The expression placeholder might be simply an expression binding the parameter to a page or control property, but is more likely to consist of an Eval() or Bind() expression. These functions can be used to output data from a table bound to a control simply by specifying the column. The following syntax is used for Eval():
<%# Eval("ColumnName") %> There is also an optional second parameter that allows you to format the data returned, which has identical syntax to string formatting expressions used elsewhere. This can be used, for example, to format date strings into a more readable format - something that was lacking in the earlier example.
The Bind() expression is identical but allows you to insert data into attributes of server controls, for example:
<asp:Label RunAt="server" Text='<%# Bind("ColumnName") %>' /> Note that because double quotes are used here in the Bind() parameter, single quotes are required to enclose the attribute value.
The following table provides a list of available templates and when they are used.
| Template | Applies To | Description |
|---|---|---|
| <ItemTemplate> | DataList, Repeater | Used for list items |
| <HeaderTemplate> | DataList, DetailsView, FormView, Repeater | Used for output before items(s) |
| <FooterTemplate> | DataList, DetailsView, FormView, Repeater | Used for output after item(s) |
| <SeparatorTemplate> | DataList, Repeater | Used between items in list |
| <AlternatingItemTemplate> | DataList | Used for alternate items; can aid visibility |
| <SelectedItemTemplate> | DataList | Used for selected items in the list |
| <EditItemTemplate> | DataList, FormView | Used for items being edited |
| <InsertItemTemplate> | FormView | Used for items being inserted |
| <EmptyDataTemplate> | GridView, DetailsView, FormView | Used to display empty items, for example, when no records are available in a GridView |
| <PagerTemplate> | GridView, DetailsView, FormView | Used to format pagination |
The easiest way to understand how to use these is by way of an example.
Using Templates
You’ll extend the table at the top of the Default.aspx page of PCSWebApp3 to contain a DataList displaying each of the events stored in the database. You’ll make these events selectable such that details of any event can be displayed by clicking on its name, in a FormView control.
First, you need to create new data sources for the data-bound controls. It is good practice (and strongly recommended) to have a separate data source for each data-bound control.
The SqlDataSource control required for the DataList control, MRBEventData2, is much like MRBEventData, except that it needs to return only Name and ID data. The required code is as follows:
<asp:SqlDataSource Runat="server" SelectCommand="SELECT [ID], [Name] FROM [Events]" ConnectionString="<%$ ConnectionStrings:MRBConnectionString %>"> </asp:SqlDataSource> The data source for the FormView control, MRBEventDetailData, is more complicated, although you can build it easily enough through the data source Configuration wizard. This data source uses the selected item of the DataList control, which you’ll call EventList, to get only the selected item data. This is achieved using a parameter in the SQL query, as follows:
<asp:SqlDataSource Runat="server" SelectCommand="SELECT dbo.Events.Name, dbo.Rooms.Room, dbo.Events.AttendeeList, dbo.Events.EventDate FROM dbo.Events INNER JOIN dbo.Rooms ON dbo.Events.ID = dbo.Rooms.ID WHERE dbo.Events.ID = @ID" ConnectionString="<%$ ConnectionStrings:MRBConnectionString %>"> <SelectParameters> <asp:ControlParameter Name="ID" DefaultValue="-1" Control PropertyName="SelectedValue" /> </SelectParameters> </asp:SqlDataSource> Here the ID parameter results in a value being inserted in place of @ID in the select query. The ControlParameter entry takes this value from the SelectedValue property of EventList, or uses -1 if there is no selected item. At first glance this syntax seems a little odd, but it is very flexible, and once you’ve generated a few of these using the wizard, you won’t have any trouble assembling your own.
Next, you need to add the DataList and FormView controls. The changes to the code in Default.aspx in the PCSWebApp3 project are shown in the shaded area:
<tr> <td align="center" colspan="3"> <asp:ValidationSummary Runat="server" HeaderText="Before submitting your request:" /> </td> </tr> <tr> <td align="left" colspan="3" style="width: 40%;"> <table cellspacing="4" style="width: 100%;"> <tr> <td colspan="2" style="text-align: center;"> <h2>Event details</h2> </td> </tr> <tr> <td style="width: 40%; background-color: #ccffcc;" valign="top"> <asp:DataList Runat="server" DataSource DataKeyField="ID" OnSelectedIndexChanged="EventList_SelectedIndexChanged"> <HeaderTemplate> <ul> </HeaderTemplate> <ItemTemplate> <li> <asp:LinkButton Text='<%# Bind("Name") %>' Runat="server" CommandName="Select" CommandArgument='<%# Bind("ID") %>' CausesValidation="false" /> </li> </ItemTemplate> <SelectedItemTemplate> <li> <b><%# Eval("Name") %></b> </li> </SelectedItemTemplate> <FooterTemplate> </ul> </FooterTemplate> </asp:DataList> </td> <td valign="top"> <asp:FormView Runat="server" DataSource> <ItemTemplate> <h3><%# Eval("Name") %></h3> <b>Date:</b> <%# Eval("EventDate", "{0:D}") %> <br /> <b>Room:</b> <%# Eval("Room") %> <br /> <b>Attendees:</b> <%# Eval("AttendeeList") %> </ItemTemplate> </asp:FormView> </td> </tr> </table> </td> </tr> </table>
Here you have added a new table row containing a table with a DataList control in one column and a FormView control in the other.
The DataList uses <HeaderTemplate> and <FooterTemplate> to output header and footer HTML, and <ItemTemplate> and <SelectedItemTemplate> to display event details. To facilitate selection, you raise a Select command from the event name link rendered in <ItemTemplate>, which automatically changes the selection. You also use the OnSelectedIndexChanged event, triggered when the Select command changes the selection, to ensure that the list display updates itself to display the selected item in a different style. The event handler for this is shown in the following code.
void EventList_SelectedIndexChanged(object sender, EventArgs e) { EventList.DataBind(); } You also need to ensure new events are added to the list:
if (queryResult == 1) { resultLabel.Text = "Event Added."; EventData = null; calendar.SelectedDate = GetFreeDate(calendar.SelectedDate.AddDays(1)); GridView1.DataBind(); EventList.DataBind(); } Now selectable event details are available in the table, as shown in Figure 32-14.
Figure 32-14
There is much more that you can do with templates and data-bound controls in general, enough in fact to fill a whole book. However, this should be enough to get you started with your experimentation.
