As we discussed in Hour 13, a database table is composed of a number of columns, each of which has a data type that indicates what types of values can be stored in the column. The Title column in the Books table, for example, has an nvarchar(150) data type, meaning that it can store strings with up to 150 characters; the LastReadOn column has a datetime data type, meaning it can store date and time values. One database column data type that we’ve yet to use in the Books table is the bit data type. A column that has a data type of bit can store one of two values: 0 or 1. Typically, a bit column is used to store a Yes/No or True/False type value.

Let’s take a minute to add a bit column to our Books table. Go to the Database Explorer in Visual Web Developer, drill down to the Books table, right-click its name, and choose the Open Table Definition option. This brings up the list of columns that make up the table. Next, add a new column named Recommended and choose a Data Type value of bit. Because the table contains existing data, this column either must allow Nulls or we must provide a default value. Let’s use the latter approach.

First, uncheck the Allow Nulls check box. We need to specify a default value for this column. In the Column Properties pane, search for the property titled Default Value or Binding in the General section. Then put in the default value there. Let’s use a default of 0. Take a minute to ensure that your screen looks similar to Figure 18.2 and then save the table changes.

Figure 18.2. The Recommended column has been added; it’s a bit column with a default value of 0.

Now that we’ve added this new column, create an ASP.NET page that displays all rows and all columns in an editable GridView. As soon as you bind the GridView to the SqlDataSource control through the GridView’s smart tag, the Recommended field in the GridView is shown as a series of check boxes. Visual Web Developer is smart enough to note that a particular column being bound to the GridView is a bit column. For such columns, it automatically uses a CheckBoxField instead of the standard BoundField (see Figure 18.3).

Figure 18.3. The Recommended field is displayed as a series of check boxes.

To see that the Recommended field is indeed a CheckBoxField, go to the GridView’s smart tag and click the Edit Columns link, bringing up the Fields dialog box. As Figure 18.4 shows, from the bottom-left corner listing of the fields in the GridView, the Recommended field is a CheckBoxField. Selecting the Recommended field loads its properties on the right. As with the BoundField, you can customize the CheckBoxField’s appearance through the properties in the Styles section.

Figure 18.4. The GridView automatically uses a CheckBoxField for Recommended.

Take a moment to test this ASP.NET page through a browser. When you visit the page, each row in the Recommended field is displayed as a disabled check box. When a particular row is edited, the editable row’s check box is enabled, allowing the end user to change the check box’s value. This functionality is made possible thanks to the CheckBoxField.

With the addition of a Recommended field, users visiting your site can see both what books are in your bookshelf and what books you heartily recommend. A visitor who notes that the books you read and like are similar to the books he reads and likes might be interested in buying some of the books you recommend that he has yet to read. To help streamline this process, you could add a link titled “Buy” in each book row that, when clicked, would whisk the user to some online bookstore, displaying the details of that particular book.

Adding such functionality through the GridView (or DetailsView) is possible and quite simple thanks to the HyperLinkField. As its name implies, the HyperLinkField displays a field of HyperLink Web controls. When visited through a browser, the HyperLink control renders as a link that, when clicked, takes the visitor to a specified URL. With the HyperLinkField field, we can set the Text and NavigateUrl properties of the HyperLink control based on database values so that the text and URL of the rendered link for each row in the GridView is based on the values of the columns of the database record that was bound to that row.

If this doesn’t make much sense yet, don’t worry; an example should help. Because we want to add a “Buy” link to each row in a GridView that, when clicked, sends the visitor to an online bookstore, our first order of business is to determine what online bookstore to use and what the URL for viewing the details for a particular book on that site looks like. For this exercise, let’s use www.isbn.nu as the online bookstore. isbn.nu doesn’t sell books directly; instead, it links to a variety of online bookstores, helping the visitor find the lowest price. With isbn.nu, the URL http://www.isbn.nu/ISBN displays the details for the book with the specified ISBN value.

To provide such a link, we need to store the ISBN for each of the books; therefore, we need to add an ISBN column to the Books table. Because a book’s ISBN can be up to 13 characters, create the ISBN column using the nvarchar(13) data type. We don’t want to allow Nulls, but because there’s already data in the Books table, we’ll initially need to allow Nulls for this new column until we have a chance to provide values for the ISBN column for the existing rows. Take a moment to add this new column.

After adding the ISBN column, edit the table’s data (right-click the Books table in the Database Explorer and choose Show Table Data). Enter an ISBN for each of the books, omitting any hyphens (see Figure 18.5). The ISBN for the five books in the Books table are as follows:

Figure 18.5. The ISBN values for the five books have been added.

After you’ve supplied the ISBN values for all the records in the Books table, return to editing the table’s definition and uncheck the Allow Nulls check box for the ISBN column.

Next, create an ASP.NET page with a SqlDataSource that retrieves all the columns and rows from the Books table. Add a GridView control and bind it to the SqlDataSource. At this point the GridView displays the value of the ISBN column using a BoundField. We need to replace this BoundField with a HyperLinkField. To accomplish this, go to the Fields dialog box.

The lower-left corner of the Fields dialog box lists the fields currently being used by the GridView, one of which is an ISBN BoundField. Remove this field from the GridView by selecting it and clicking the Delete icon to the immediate right of this list, as shown in Figure 18.6.

Figure 18.6. Select the ISBN BoundField and delete it.

You can also delete any other BoundFields that you aren’t interested in displaying. For this ASP.NET page, let’s not bother showing the BookID, YearPublished, or LastReadOn BoundFields.

We’re now ready to add the Buy HyperLinkField. From the Available Fields list in the upper-left corner, scroll down and select the HyperLinkField option and then click the Add button. This will add a HyperLinkField to bottom of the list of fields in the lower-left corner. Take a moment to move this HyperLinkField to the top of the list so that it is displayed in the far left of the GridView. We now need to set the HyperLinkField’s properties, indicating what the rendered link’s text and URL values should be.

The HyperLinkField’s text and URL values can be static or dynamic. A dynamic value differs for each row in the GridView based on the data bound to that particular row; a static value is the same among all rows. For our task we want a static text value—Buy—and a dynamic URL value, varying on each book’s ISBN.

If you want to set the text or URL values to static values, use the Text or NavigateUrl properties. The Text property can be found in the Appearance section of the properties list; NavigateUrl is located in the Behavior section. Because we want the link’s text to be Buy for all rows, set the Text property of the HyperLinkColumn to Buy.

To specify a dynamic value for the text or URL values, we need to use two properties. For the text, we can use the DataTextField and DataTextFormatString properties; for the URL, we can use the DataNavigateUrlFields and DataNavigateUrlFormatString properties. These four properties are found in the Data section of the HyperLinkField’s properties. The properties work in tandem in the following manner: the DataTextField or DataNavigateUrlFields properties specify what database column values are used in the text or URL of the rendered link; the DataTextFormatString or DataNavigateUrlFormatString properties can be used to surround the database value with static text. With the DataTextFormatString and DataNavigateUrlFormatString properties, the string {0} is used to inject the dynamic value.

For our example, we want the URL to be dynamic based on the book’s ISBN; therefore, set the DataNavigateUrlFields property to ISBN. Because we want the URL to be http://www.isbn.nu/ISBN, use http://www.isbn.nu/{0} as the value for the DataNavigateUrlFormatString property. This instructs the HyperLinkColumn to inject the current row’s ISBN value at the {0} position, resulting in a properly formatted hyperlink.

After you set these properties and click the OK button in the Fields dialog box, your screen should look similar to Figure 18.7. Note that the GridView now has a Buy HyperLinkField at its far left and that the BookID, YearPublished, and LastReadOn BoundFields have been removed.

Figure 18.7. A HyperLinkField has been added to the GridView.

View the ASP.NET page in a browser. Each row in the rendered GridView contains a Buy link that, when clicked, will whisk you to isbn.nu. For example, clicking the Buy link for Create Your Own Website will take you to http://www.isbn.nu/9780672329265. Notice how the ISBN value for this book—9780672329265—is injected into the URL of the link precisely where the {0} string was placed in the HyperLinkField’s DataNavigateUrlFormatString property.

The ImageField is designed to display an image whose URL is based on a database value. The ImageField injects an Image Web control, which renders as an HTML <img> element. Like the HyperLinkField, the ImageField has a pair of properties that can be used to specify a database column and a format string: DataImageUrlField and DataImageUrlFieldString.

Let’s augment the GridView we created in the “Displaying Hyperlinks with the HyperLinkField” section to include an ImageField that displays each book’s cover. The first step is to find the cover images and save them to your computer. The isbn.nu website lists a small cover image at www.isbn.nu/ISBN. Visit the isbn.nu site for each of the books in the database. Right-click the cover image and save it to your website’s root folder, naming the file BookID.jpg. For example, save the cover image for Visual Studio Hacks using the filename 1.jpg; for Create Your Own Website, use 2.jpg, and so on.

After you have saved the cover images to your computer, add an ImageField to the GridView using the following steps:

After step 3, your Fields dialog box should look similar to the one in Figure 18.8. After verifying this, click the OK button and then view the ASP.NET page through a browser. You should see the thumbnail images for each of the book’s covers, as shown in Figure 18.9.

Figure 18.8. Set the properties of the ImageField through the Fields dialog box.

Figure 18.9. A thumbnail image of the cover is shown for each book.

ASP.NET supports two flavors of data binding: one-way and two-way. One-way data binding takes a specified database column’s value and binds it to a specified Web control property. Two-way data binding can not only assign a database column’s value to a Web control property, but can also do the inverse: It can take the value of a Web control’s property and assign it to the value of a data source control parameter.

One-way data binding is used when working with a nonmodifiable data Web control. When we’re using a data Web control that supports editing, inserting, or deleting, two-way data binding is used instead.

When working with BoundFields, CheckBoxFields, HyperLinkFields, or ImageFields, you do not need to concern yourself with the differences between one-way and two-way data binding; all the intricacies are automatically handled by the various fields. However, when adding data-bound Web controls to a TemplateField, you need to specify whether the data binding should be one-way or two-way. If the Web control is used only to display the information, use one-way data binding; if the Web control is also used to collect user input and save those values back to the database, use two-way data binding.

If you add a TemplateField manually or want to replace a converted TemplateField’s Label or TextBox Web control with a different control, you’ll need to specify the data bindings for the new controls you add to the TemplateField (including whether the data bindings are one-way or two-way). The data binding simply ties a particular property of the Web control to the field’s corresponding database column.

To practice working with data bindings and custom TemplateFields, let’s create an editable GridView with a customized editing interface. Specifically, we’ll create a GridView that lists the BookID, Title, and Genre columns from the database. By default, the BookID field will be read-only, and the Title and Genre fields will have a TextBox Web control for their editing interface. However, when editing a GridView row, we might want to instead have the Genre field displayed as a DropDownList with all the existing genres. To accomplish this, we’ll need to convert the Genre BoundField into a TemplateField and replace the TextBox Web control in the EditItemTemplate with SqlDataSource and DropDownList controls. In doing so, we’ll need to use data binding to tie the DropDownList control’s SelectedValue property to the value of the edited book’s Genre column.

Let’s not get too far ahead of ourselves here. Before we worry about creating a customized, data-bound editing interface for the Genre field, let’s first create an editable GridView that uses the default editing interface. Recall from Hour 16 that this involves first adding a SqlDataSource control configured to support updating. Because we want to allow the user to edit only the Title and Genre fields, in the SqlDataSource’s wizard, be sure to bring back only the BookID, Title, and Genre columns, as shown in Figure 18.13. Also, don’t forget to click the Advanced button and check the Generate INSERT, UPDATE, and DELETE Statements check box.

Figure 18.13. From the Configure the Select Statement screen, check the BookID, Title, and Genre columns.

Next, add a GridView to the page and bind it to the SqlDataSource control. Check the Enable Editing check box in the GridView’s smart tag. Take a moment to view the page through a browser, editing the value for a particular book. Note that when you click the Edit button, the editing interface shows a text box for both the Title and Genre fields (see Figure 18.14).

Figure 18.14. Both the Title and Genre fields use the default editing interface.

Let’s now look at how to customize the Genre field so that it displays a DropDownList of the current genre values in the table, rather than using a TextBox control. To start, go to the Fields dialog box and convert the Genre BoundField into a TemplateField. This automatically creates an ItemTemplate with a data-bound Label Web control and an EditItemTemplate with a data-bound TextBox. In fact, if at this point you retest your page through a browser, there will be no discernable difference from the editable interface in Figure 18.14.

To change the Genre field’s EditItemTemplate, go to the GridView’s smart tag and click the Edit Templates link. This displays the template editing view of the GridView, with the smart tag listing the available templates. Choose the EditItemTemplate. You should see a TextBox Web control in the EditItemTemplate; this is the data-bound TextBox control that was added automatically when we converted the Genre BoundField to a TemplateField. Because we no longer want to use a TextBox in the editing interface, delete this Web control.

We now need to add a DropDownList to the EditItemTemplate that lists the genres in the Books table. To accomplish this, we need to first create a SqlDataSource control that retrieves the unique list of genres. This can be added directly to the Genre field’s EditItemTemplate. After adding the SqlDataSource to the EditItemTemplate, set its ID property to GenreDataSource. Next, run through the wizard, returning just the Genre column and checking the Return Only Unique Rows check box, like we did in the preceding hour when using a DropDownList to filter the GridView results by genre.

After configuring the GenreDataSource SqlDataSource control, add a DropDownList to the EditItemTemplate. Click the Choose Data Source link in the DropDownList’s smart tag and bind it to the GenreDataSource control, with the Genre column serving as both the field to display and the value field. After you’ve added both the SqlDataSource control and the DropDownList to the EditItemTemplate, and configured both, your screen should look similar to Figure 18.15.

Figure 18.15. A SqlDataSource and DropDownList have been added to the Genre TemplateField’s EditItemTemplate.

Test the ASP.NET page through a browser. When you edit a particular row, the Genre field is displayed as a drop-down list with the various genre values in the Books table (Business, Fiction, and Technology), as shown in Figure 18.17. However, notice that the first item of the DropDownList is selected, regardless of the edited book’s actual Genre value. That is, if you edit Visual Studio Hacks, which has the genre Technology, the drop-down list has the Business genre selected. Furthermore, if you save the edits, by clicking the Update button, a value of Null is saved for the book’s genre, regardless of what option you’ve selected from the drop-down list.

What’s going on here? What we’ve yet to do is implement data binding on the DropDownList control in the EditItemTemplate. Right now the DropDownList doesn’t know the value of the row’s Genre column, nor does it provide its selected value to the GridView’s SqlDataSource control when the data is updated. We need to have the DropDownList properly data bound to have this interaction occur correctly.

Return to Visual Web Developer and go back to the Genre field’s EditItemTemplate. Open the DropDownList’s smart tag and click the Edit DataBindings link. This displays the DataBindings dialog box, from which you can tie together the DropDownList’s properties and the column values returned by the GridView’s SqlDataSource control. Because we want the DropDownList’s selection to be based on the Genre column value, select the SelectedValue property from the list on the left and pick the Genre field from the drop-down list on the right, as shown in Figure 18.16. Because the SelectedValue value also dictates the value of the edited book’s Genre value, make sure that the Two-way Databinding check box is checked.

Figure 18.16. Bind the SelectedValue property to the Genre column value using two-way data binding.

After binding the SelectedValue property to the Genre column, revisit the page in the browser. This time the item in the DropDownList is properly set to the book’s Genre value, and when you save the edits, the selected DropDownList value is saved. Figure 18.17 shows the ASP.NET page in action.

Figure 18.17. The Genre field provides a customized, data-bound editing interface.

 1: <asp:TemplateField HeaderText="Genre" SortExpression="Genre">
 2:     <EditItemTemplate>
 3:         <asp:SqlDataSource ID="GenreDataSource" runat="server"
 4:             ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
 5:             SelectCommand="SELECT DISTINCT [Genre] FROM [Books]"></asp:SqlDataSource>
 6:         <asp:DropDownList ID="DropDownList1" runat="server"
 7:             DataSourceID="GenreDataSource" DataTextField="Genre"  DataValueField="Genre"
 8:             SelectedValue='<%# Bind("Genre") %>'>
 9:         </asp:DropDownList>
10:     </EditItemTemplate>
11:     <ItemTemplate>
12:         <asp:Label ID="Label1" runat="server" Text='<%# Bind("Genre")  %>'></asp:Label>
13:     </ItemTemplate>
14: </asp:TemplateField>