ADO.NET and Data Binding
 | |||||||||||
| Chapter 14 - ASP.NET Pages | |
| bySimon Robinsonet al. | |
| Wrox Press 2002 | |
The Web Form application we created in the last section is perfectly functional, but only contains static data. In addition, the event booking process does not include persisting event data. In order to solve both of these problems we can make use of ADO.NET to access data stored in a database, such that we 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 listboxes (and some of the more specialized controls we'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, which includes DataTable objects.
In this section we will start by updating our event booking application to be data-aware, and then move on to take a look at some of the other things we can do 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 application project called PCSWebApp3 and copy the code across from the PCSWebApp2 application created earlier. Before we start on our new code, let's look at the database we will be accessing.
The Database
For the purposes of this example we will use a Microsoft Access database called PCSWebApp3.mdb , which may be found along with the downloadable code for this book. For an enterprise-scale application it would make more sense to use a SQL Server database, but the techniques involved are practically identical and MS Access makes life a bit easier for testing. We will point out the differences in code as they occur.
The database provided contains three tables:
-
Attendees , containing a list of possible event attendees
-
Rooms , containing a list of possible rooms for events
-
Events , containing a list of booked events
Attendees
The Attendees table contains the following columns :
| Column | Type | Notes |
|---|---|---|
| ID | AutoNumber, primary key | Attendee identification number |
| Name | Text, required, 50 chars | Name of attendee |
| | Text, optional, 50 chars | E-mail address of attendee |
The supplied database includes entries for 20 attendees, all of who have (made up) e-mail addresses. It is envisioned that in a more developed application e- mails could automatically be sent to attendees when a booking is made, but this is left to the reader as an optional exercise using techniques found elsewhere in this book.
Rooms
The Rooms table contains the following columns:
| Column | Type | Notes |
|---|---|---|
| ID | AutoNumber, primary key | Room identification number |
| Room | Text, required, 50 chars | Name of room |
20 records are supplied in the database.
Events
The Events table contains the following columns:
| Column | Type | Notes |
|---|---|---|
| ID | AutoNumber, primary key | Event identification number |
| Name | Text, required, 255 chars | Name of event |
| Room | Number, required | ID of room for event |
| AttendeeList | Memo, required | List of attendee names |
| EventDate | Date/Time, required | Date of event |
A few events are supplied in the downloadable database.
Binding to the Database
The two controls we'd like to bind to data are attendeeList and roomList . To do this we have to set the DataSource properties of these controls to tables containing our data. Our code must load data into these tables and perform this binding at run time. Both of these controls also have DataTextField and DataValueField properties that specify what columns to use for displaying list items and setting value properties, respectively. In both cases we can set these properties at design time and they will be used as soon as the DataSource property is set to populate the list items in the control.
Remove the existing entries from the ASP.NET code for these controls such that the declarations read as follows :
... <asp:DropDownList ID="roomList" Runat="server" Width="160px" DataTextField="Room" DataValueField="ID" > </asp:DropDownList> ... <asp:ListBox ID="attendeeList" Runat="server" Width="160px" SelectionMode="Multiple" Rows="6" DataTextField="Name" DataValueField="ID" /> </asp:ListBox> ...
The next task is to create a connection to the database. There are several ways to do this, as we saw in connection is the Microsoft Jet 4.0 OLE DB Provider , so we need to add a System.Data.OleDb.OleDbConnection object to our form called oleDbConnection1 :
public class WebForm1 : System.Web.UI.Page { ... protected System.Data.OleDb.OleDbConnection oleDbConnection1; For a SQL Server connection the object added would be a SqlClient.SqlConnection object.
We then need to add some code to InitializeComponent() to set the ConnectionString property of oleDbConnection1 so it is all ready for us to use:
private void InitializeComponent() { this.oleDbConnection1 = new System.Data.OleDb.OleDbConnection(); ... this.oleDbConnection1.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data " + "Source=C:\Inetpub\wwwroot\PCSWebApp3\PCSWebApp3.mdb"; We want to perform our data binding in the Page_Load() event handler, such that the controls are fully populated when we want to use them in other parts of our code. We will read data from the database regardless of whether a postback operation is in progress (even though the list controls will persist their contents via the viewstate) to ensure that we have access to all the data we might need, although we don't need to perform the data binding itself in a postback. This might seem slightly wasteful , but I will again leave it as an exercise to the reader to add additional logic to the code to optimize this behavior. Here we are concentrating on how to get things working, without going into too much detail.
All of our code will be placed in-between calling the Open () and Close() methods of our connection object:
private void Page_Load(object sender, System.EventArgs e) { oleDbConnection1.Open(); if (!this.IsPostBack) { calendar.SelectedDate = System.DateTime.Now; } oleDbConnection1.Close(); } We'll see why the calendar date setting is left inside this postback-checking code shortly.
For our data exchange we need to use several objects to store our data in. We can declare these at the class level such that we have access to them from other functions. We need a DataSet object to store the database information, three OleDb.OleDbDataAdapter objects to execute queries on the dataset, and a DataTable object to store our events for later access. Declare these as follows:
public class WebForm1 : System.Web.UI.Page { ... protected System.Data.DataSet ds; protected System.Data.DataTable eventTable; protected System.Data.OleDb.OleDbDataAdapter daAttendees; protected System.Data.OleDb.OleDbDataAdapter daRooms; protected System.Data.OleDb.OleDbDataAdapter daEvents; SQL Server versions of all the OLE DB objects exist, and their usage is identical.
Page_Load() now needs to create the DataSet object:
private void Page_Load(object sender, System.EventArgs e) { ... oleDbConnection1.Open(); ds = new DataSet(); Then we must assign the OleDbDataAdapter objects with queries and a link to the connection object:
ds = new DataSet(); daAttendees = new System.Data.OleDb.OleDbDataAdapter( "SELECT * FROM Attendees", oleDbConnection1); daRooms = new System.Data.OleDb.OleDbDataAdapter( "SELECT * FROM Rooms", oleDbConnection1); daEvents = new System.Data.OleDb.OleDbDataAdapter( "SELECT * FROM Events", oleDbConnection1);
Next we execute the queries using calls to Fill() :
daEvents = new System.Data.OleDb.OleDbDataAdapter( "SELECT * FROM Events", oleDbConnection1); daAttendees.Fill(ds, "Attendees"); daRooms.Fill(ds, "Rooms"); daEvents.Fill(ds, "Events");
Now we come to the data binding itself. As mentioned earlier, this simply involves setting the DataSource property on our bound controls to the tables we want to bind to:
daEvents.Fill(ds, "Events"); attendeeList.DataSource = ds.Tables["Attendees"]; roomList.DataSource = ds.Tables["Rooms"];
This sets the properties, but data binding itself won't occur until we call the DataBind() method of the form, which we'll do in a moment. Before we do this we'll populate the DataTable object with the event table data:
roomList.DataSource = ds.Tables["Rooms"]; eventTable = ds.Tables["Events"]; We will only data bind if a postback is not in progress; otherwise , we will simply be refreshing data (which we're assuming is static in the database for the duration of an event booking request). Data binding in a postback would also wipe the selections in the roomList and attendeeList controls. We could make a note of these before binding and then renew them, but it is simpler to call DataBind() in our existing if statement (this is the reason that this statement was kept in the region of code where the data connection was open):
eventTable = ds.Tables["Events"]; if (!this.IsPostBack) { calendar.SelectedDate = System.DateTime.Now; this.DataBind(); } oleDbConnection1.Close(); } Running the application now will result in the full attendee and room data being available from our data bound controls.
| Important | Please note that in order for the code to run you may have to explicitly close any open connections to the database, either in Access or in Server Explorer. To do this simply right-click on the data source in Server Explorer and choose Close . |
Customizing the Calendar Control
Before we discuss adding events to the database let's make a modification to our calendar display. It would be nice to display any day where a booking has previously been made in a different color , and prevent such days from being selectable. This requires modifications to the way we set dates in the calendar, and the way day cells are displayed.
We'll start with date selection. There are three places where we need to check for dates where events are booked and modify the selection accordingly : when we 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 we wish to set a new date to prevent the user booking two events on the same day before selecting a new date. As this is going to be a common feature we may 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.
Add this method, getFreeDate() , to the code-behind file:
private System.DateTime getFreeDate(System.DateTime trialDate) { if (eventTable.Rows.Count > 0) { System.DateTime testDate; bool trialDateOK = false; while (!trialDateOK) { trialDateOK = true; foreach (System.Data.DataRow testRow in eventTable.Rows) { testDate = (System.DateTime)testRow["EventDate"]; if (testDate.Date == trialDate.Date) { trialDateOK = false; trialDate = trialDate.AddDays(1); } } } } return trialDate; }
This simple code uses the eventTable object that we populated in Page_Load() to extract event data. First we check for the trivial case where no events have been booked, in which case we can just confirm the trial date by returning it. Next we iterate through the dates in the Event table comparing them with the trial date. If we find a match we 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 we 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); this.DataBind(); } Next we need to respond to date selection on the calendar. To do this we simply need to add an event handler for the SelectionChanged event of the calendar, and force the date to be checked against existing events. Double-click on the calendar in the Designer and add this code:
private void calendar_SelectionChanged(object sender, System.EventArgs e) { System.DateTime trialDate = calendar.SelectedDate; calendar.SelectedDate = getFreeDate(trialDate); } The code here is identical to that in Page_Load() .
The third place that we must perform this check is in response to the booking button being pressed. We'll come back to this in a little while, as we have many changes to make here.
Next we want to color the day cells of the calendar to signify existing events. To do this we need to 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 us access to the cell object being displayed and the date of this cell through the Cell and Date properties of the DayRenderEventArgs parameter we receive in the handler function. We simply need to compare the date of the cell being rendered to the dates in our eventTable object, and color the cell using the Cell.BackColor property if there is a match:
protected void calendar_DayRender(object sender, System.Web.UI.WebControls.DayRenderEventArgs e) { if (eventTable.Rows.Count > 0) { System.DateTime testDate; foreach (System.Data.DataRow testRow in eventTable.Rows) { testDate = (System.DateTime)testRow["EventDate"]; if (testDate.Date == e.Day.Date) { e.Cell.BackColor = Color.Red; } } } }
Here we are using red, which will give us a display along the lines of:
Here the 15th, 27th, 28th, 29th, and 30th of March all contain events, and the user has selected the 17th. 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 then a later date is selected instead. For example, clicking on the 28th of March on the calendar shown above will result in the 31st being selected.
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 we simply need to reformat the string created into a SQL INSERT query and execute it.
Much of the following code will therefore look familiar:
protected void submitButton_Click(object sender, System.EventArgs e) { if (this.IsValid) { String attendees = ""; foreach (ListItem attendee in attendeeList.Items) { if (attendee.Selected) { attendees += attendee.Text + " (" + attendee.Value + "), "; } } attendees += " and " + nameBox.Text; String dateString = calendar.SelectedDate.Date.Date.ToShortDateString(); String oleDbCommand = "INSERT INTO Events (Name, Room, " + "AttendeeList, EventDate) VALUES ('" + eventBox.Text + "', '" + roomList.SelectedItem.Value + "', '" + attendees + "', '" + dateString + "')"; Once we have created our SQL query string we can use it to build an OleDb.OleDbCommand object:
System.Data.OleDb.OleDbCommand insertCommand = new System.Data.OleDb.OleDbCommand(oleDbCommand, oleDbConnection1);
Next we reopen the connection that was closed in Page_Load() (again, this is perhaps not the most efficient way of doing things, but it works fine for demonstration purposes), and execute the query:
oleDbConnection1.Open(); int queryResult = insertCommand.ExecuteNonQuery();
ExecuteNonQuery() returns an integer representing how many table rows were affected by the query. If this is equal to 1 then we know that our insertion was successful. If so then we put a success message in resultLabel , execute a new query to repopulate eventTable and our dataset with our new list of events (we clear the dataset first, otherwise events will be duplicated ), and change the calendar selection to a new, free, date:
if (queryResult == 1) { resultLabel.Text = "Event Added."; daEvents = new System.Data.OleDb.OleDbDataAdapter( "SELECT * FROM Events", oleDbConnection1); ds.Clear(); daEvents.Fill(ds, "Events"); eventTable = ds.Tables["Events"]; calendar.SelectedDate = getFreeDate(calendar.SelectedDate.AddDays(1)); }
If ExecuteNonQuery() returns a number other than 1 we know that there has been a problem. For this example we won't worry too much about this, and simply display a failure notification in resultLabel :
else { resultLabel.Text = "Event not added due to DB access " + "problem."; }
Finally, we close the connection again:
oleDbConnection1.Close(); } } and our data-aware version of the event booking application is complete.
Note that due to the syntax of the SQL INSERT query we must avoid using certain characters in the event name, such as apostrophes " ' ", as they will cause an error. It would be relatively easy to enforce a custom validation rule that prevented the user from using such characters, or to perform some type of character escaping before inserting data and after reading data, but the code for this will not be covered here.
More on Data Binding
When we looked at the available Server Controls earlier in this chapter we saw three that dealt with data display: DataGrid , Repeater , and DataList . These are all extremely useful when it comes to outputting data to a web page, as they perform many tasks automatically that would otherwise require a fair amount of coding.
To start with, let's look at the simplest of these to use, DataGrid . As a simple example of this control let's add an event-detail display to the bottom of the display of PCSWebApp3 . This enables us to ignore database connections for now, as we have already configured our application for this access.
Add the following to the bottom of WebForm1.aspx in the PCSWebApp3 project :
<br>Results: <asp:Label ID=resultLabel Runat="server" Text="None."></asp:label> <br> <br> <asp:DataGrid Runat="server" ID="eventDetails1" /> </form> </body> </HTML>
Also, add the following to Page_Load() in WebForm1.aspx.cs :
attendeeList.DataSource = ds.Tables["Attendees"]; roomList.DataSource = ds.Tables["Rooms"]; eventTable = ds.Tables["Events"]; eventDetails1.DataSource = eventTable; if (!this.IsPostBack) { System.DateTime trialDate = System.DateTime.Now; calendar.SelectedDate = getFreeDate(trialDate); this.DataBind(); } else { eventDetails1.DataBind(); } oleDbConnection1.Close(); }
Note that the event list may have changed between requests if another user has added an event, so we need to call DataBind() on the DataGrid to reflect these changes. Remember that calling DataBind() on the whole form will result in room and attendee selections being lost, so this is a fair compromise.
If you load the application in your web browser again you should see a list underneath the booking details section containing the full list of events:
We 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."; daEvents = new System.Data.OleDb.OleDbDataAdapter( "SELECT * FROM Events", oleDbConnection1); ds.Clear(); daEvents.Fill(ds, "Events"); eventTable = ds.Tables["Events"]; calendar.SelectedDate = getFreeDate(calendar.SelectedDate.AddDays(1)); eventDetails1.DataBind(); } Note that we call DataBind() on the DataGrid , not on this . This prevents all data bound controls from being refreshed, which would be unnecessary. All data-bindable controls support this method, which is normally called by the form if we call the top-level ( this ) DataBind() method.
As you might expect, the DataGrid control contains many properties that we can use to format the displayed data in a more user-friendly way, but I'll leave these for you to discover.
Data Display with Templates
The other two data displaying controls, Repeater and DataList , require 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 us to customize exactly how data is output to the browser, and can result in professional-looking displays without too much effort.
There are several templates 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. We declare this template (and all the others) inside the control declaration, for example:
<asp:DataList Runat="server" ... > <ItemTemplate> ... </ItemTemplate> </asp:DataList>
Within template declarations we will normally want to output sections of HTML along with parameters from the data that is bound to the control. There is a special syntax that we can use to output such parameters:
<%# expression %> expression might be simply an expression binding the parameter to a page or control property, but is more likely to consist of a DataBinder.Eval() expression. This useful function can be used to output data from a table bound to a control simply by specifying the column, using the following syntax:
<%# DataBinder.Eval(Container.DataItem, "ColumnName") %> There is also an optional third parameter that allows us to format the data returned, which has identical syntax to string formatting expressions used elsewhere.
The full list of available templates and when they are used is shown below:
| Template | Description |
|---|---|
| < ItemTemplate > | Template to use for list items |
| < HeaderTemplate > | Template to use for output before the list |
| < FooterTemplate > | Template to use for output after the list |
| < SeparatorTemplate > | Template for use between items in list |
| < AlternatingItemTemplate > | Template for alternate items; can aid visibility |
| < SelectedItemTemplate > | ( DataList only) Template to use for selected items in the list |
| < EditItemTemplate > | ( DataList only) Template to use for items in a list that are being edited |
Again, the easiest way to look at this is with an example, and we can use our existing data query in PCSWebApp3 to achieve this.
Using Templates Example
We'll extend the table at the top of the page to contain a DataList displaying each of the events stored in the database. We'll make these events selectable such that details of any event can be displayed by clicking on its name.
The changes to the code in WebForm1.aspx in the PCSWebApp3 project are shown below:
<tr> <td align=middle colSpan=3> <asp:ValidationSummary ID=validationSummary Runat="server" HeaderText="Before submitting your request:"/> </td> </tr> <tr> <td align="left" colSpan="3" width="100%"> <table cellspacing="4"> <tr> <td width="40%" bgcolor="#ccffcc" > <asp:DataList Runat="server" ID="eventDetails2" OnSelectedIndexChanged= "eventDetails2_SelectedIndexChanged"> <ItemTemplate> <asp:LinkButton Runat="server" CommandName="Select" ForeColor="#0000ff" ID="Linkbutton1" CausesValidation="false"> <%# DataBinder.Eval(Container.DataItem, "Name")%> </asp:LinkButton> <br> </ItemTemplate> <SelectedItemTemplate> <b><%# DataBinder.Eval(Container.DataItem, "Name") %></b> <br> </SelectedItemTemplate> </asp:DataList> </td> <td valign="top"> <asp:Label Runat="server" ID="edName" Font-Name="Arial" Font-Bold="True" Font-Italic="True" Font-Size="14"> Select an event to view details. </asp:Label> <br> <asp:Label Runat="server" ID="edDate"/> <br> <asp:Label Runat="server" ID="edRoom"/> <br> <asp:Label Runat="server" ID="edAttendees"/> </td> </tr> </table> </td> </tr> </table>
Here we have added a new table row containing a table with a DataList in one column and a detail view in the other. The detail view is simply four labels for event properties, one of which contains the text " Select an event to view details. " when no event is selected (the situation when the form is first loaded).
The DataList uses < ItemTemplate > and < SelectedItemTemplate > to display event details. To facilitate selection we raise a Select command from the event name link rendered in < ItemTemplate >, which automatically changes the selection. We also use the OnSelectedIndexChanged event, triggered when the Select command changes the selection, to populate the event detail labels. The event handler we get if we double-click on eventDetails2 in the Designer is shown below. You'll need to change the protection level of the method from protected to private . (Note that we need to DataBind() first to update the selection.)
private void eventDetails2_SelectedIndexChanged(object sender, System.EventArgs e) { eventDetails2.DataBind(); DataRow selectedEventRow = eventTable.Rows[eventDetails2.SelectedIndex]; edName.Text = (string)selectedEventRow["Name"]; edDate.Text = "<b>Date:</b> " + ((DateTime)selectedEventRow["EventDate"]).ToLongDateString(); edAttendees.Text = "<b>Attendees:</b> " + (string)selectedEventRow["AttendeeList"]; DataRow selectedEventRoomRow = ds.Tables["Rooms"].Rows[(int)selectedEventRow["Room"] - 1]; edRoom.Text = "<b>Room:</b> " + selectedEventRoomRow["Room"]; }
This uses data in ds and eventTable to populate the details.
As with the DataGrid we used earlier, we need to set the data for eventDetails2 and bind in Page_Load() :
eventDetails1.DataSource = eventTable; eventDetails2.DataSource = eventTable; ... eventDetails1.DataBind(); eventDetails2.DataBind();
and re-bind in submitButton_Click() :
eventDetails1.DataBind(); eventDetails2.DataBind(); Now event details are available in the table:
There is much more that we 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.
EAN: 2147483647
Pages: 244