ADO.NET and Data Binding C# Help

The Web Form application you created in the previous section is perfectly functional, but it contains only 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 1Enumerable, 1Collection, or 1ListSource 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 application created earlier into the new application. Before you start on your new code: take a look at the database you will 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 enterprise-scale application, it makes 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.

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 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:

  1. Attendees, which contains a list of possible event attendees
  2. Rooms, which contains a list of possible rooms for events
  3. Events, which contains a list of booked events

Attendees

The Attendees table contains the columns shown in the following table.

Capture

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.

Capture

Twenty records are supplied in the database.

Events

The Events table contains the columns shown in the following table.

Capture

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 need to add SqlDataSource Webserver 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 37-10 shows how to access this wizard for a SqlDataSource control.

Figure 37-10

Figure 37-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.

After completing the wizard, change the 1D of the SqlDataSource control to MRBAttendeeData. You also need to add and configure rwo more SqlDataSource controls to obtain data from the Rooms and events tables, with 1D values of MRBRoomData and MRBEventData respectively. For these subsequent controls, you can use the saved MRBConnectionString for your connection.

Once you’ve added these data sources, you will see in the code for the form that the syntax is very simple:

Capture

The definition of the connection string in use is found in the web. config file, which we will 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:

  1. DataSource1D – MRBRoomData
  2. DataTextField – Room
  3. DataValueField – 1D

And, similarly, for attendeeList:

  1. DataSource1D – MRBAttendeeData
  2. DataTextField – Name
  3. DataValueField – 1D

You can also remove the existing hard-coded list items from the code for these controls.

Running the application now will result in the full attendee and room data being available from your data-bound controls. You will use the MRBEventDa ta control shortly

Customizing the Calendar Control

Before adding 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 select table. This requires that you modify the way you set dates in the calendar and the way day cells are displayed.

You will 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:

Capture

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:

Capture

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 Even t table, comparing them with the trial date. If you find a match, add one day to the trial date and perform another search.

Extracting the date from the DataTable is remarkably simple:

testDate = (System.DateTime)testRow[“Event Date”];

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 Selected Date property:

Capture

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:

Capture

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. We will come back to this shortly, as 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. BlrckColor property if there is a match:

Capture

Here you are using red, which will give you a display along the lines of Figure 37-11, in which June 12, 15, and 22 (2008) all contain events, and the user has selected June 24.

With the addition of the date-selection logic,it is now impossible to select a day that is shown in red. If you attempt it, a later date is selected instead (for example, selecting June 15 results in the selection of June 16). .

Figure 37-11

Figure 37-11

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 stringcreated into a SQL INSERT query and execute it.

Note that in the development environment that 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 will automatically give 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 chapter. 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:

sa

The most interesting thing here is how you access the connection string you created earlier, using the following syntax:

ConfigurationManager.ConnectionStrings[“MRBConnectionStringel .ConnectionString

The ConfigurationManager class gives you access to all assorted configuration information, all stored in the Web. conf ig configuration file for your Web application. You will 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, your insertion was successful. If so, 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 stored event data will be refreshed:

Capture

If ExecuteNonQuery() returns a number other than 1, you know that there has been a problem. The code in this example throws an exception if a number other than 1 is returned. This exception is caught by the general catch block for the database access code. .

This catch block simply displays a general failure notification in resultLabel:

Capture

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, ForrnView,and Repeater). These are all extremely useful when it comes to outputting data to a Webpage because they perform many tasks automatically that would otherwise require a fair amount of coding.

First, you will 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 37-12.

Figure 37·12

Figure 37·12

Next, click Refresh Schema, and that’s 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 37-13.

Figure 37-13

Figure 37-13

You can also make one further modification in submitButton_Click() to ensure that this data is updated when new records are added:

Capture

All data-bindable controls support this method, which is normally called by the form if you call the top-level (this) DataBind() method.

You probably noticed in figure 37-13 that the date/time display for the EventDate field is a little messy. Because you are looking at data only, the time is always 12: 00: 00 AM- information that it isn’t really necessary to display. In the next sections, you will see how this date information can be displayed in a more user-friendly fashion in the context of a ListView 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 template’s available to customize various aspects of list behavior. One of the most important templates is <ftemTemplate>, which is used in the display of each data item in a list for Repeater,
DataList, and ListView controls. 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’) %>

An optional second parameter allows you to’format the data returned, which has syntax identical 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:

Capture

Note that because double quotes are used 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.

Capture

The easiest way to understand how to use these is through an example.

Using Templates

You will extend the table at the top of the Default.aspx page of PCSWebApp3to contain a ListView displaying each of the events stored in the database. You will make these events select table 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 ListView control, MRBEventData2, is much like MRBEventData, except that it needs to return only Name and ID data. The required code is as follows:

Capture

The data source for the Form view 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 ListView control, which you will call EventList, to get only the selected item data. This is achieved using a parameter in the SQL query, as follows:

Capture

Here, the ID parameter results in a value being inserted in place of @ID in the select query. The Control Parameter 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 ListView and FormView controls. The changes to the code in Default.aspx in the PCSWebApp3project are shown in the following code:

sa

Here you have added a new table row containing a table with a ListView control in one column and a FormView control in the other.

The ListView uses <LayoutTemplate> to output a bulleted list and <ItemTemplate> and <SelectedItemTemplate> to display event details as list items. In <LayoutTemplate>, a container element for items is specified with a PlaceHolder control that has the ID=”itemPlaceholder” attribute. 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:

Capture

You also need to ensure new events are added to the list:

Capture

Now select table event details are available in the table, as shown in Figure 37-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.

Figure 37-14

Figure 37-14

Posted on November 3, 2015 in ASP.NET Pages

Share the Story

Back to Top