THE DATA-BOUND WEB CONTROLS 1053

Binding to DataSets

Real Web applications use databases, and it’s time to look at the process of binding Web controls to DataSets. The control you’ll be using most often in building data-bound Web applications is the DataGrid control. In Chapter 23, I expressed some concerns about the use of the DataGrid control for editing data, but most applications on the Web don’t involve editing data. They simply present data to the users, and the DataGrid control is a fine tool for this task. Actually, the DataGrid control is rendered on the client as an elaborate HTML table.

VB.NET at Work: The WebProducts Project

Let’s start by building a simple application (Figure 24.2), similar to the ones we’ve designed in Chapter 21 and 22. We’ll use a ListBox control to display product names (our navigational tool) and TextBox controls to display the fields of the selected product. The new Web application is called WebProducts, and you will find it in this chapter’s folder on the CD.

Figure 24.2

The WebProducts Web page uses a data-bound ListBox control as a navigational aid.

First, we’ll build this WebProducts application using the visual tools, and then you’ll see how to embed all the necessary code to retrieve and display the data in the page’s Load event handler. Existing ASP programmers will actually find it easier to manually code the application.

Start a new ASP.NET Web Application project and name it WebProducts. When the WebForm of the project appears in the designer’s surface, place a ListBox control and four TextBox controls on it. The names of the TextBox controls are txtPrice, txtPackage, txtStock, and txtSupplier. The name of the ListBox control is lstProducts.

To create the DataSet with the product information, open the Server Explorer and drop the Products table on the form. Then rename the DataAdapter object to DAProducts and configure it. We want to retrieve the ProductID and ProductName columns of the Products table. This is all the information we need in order to use the ListBox control as a navigational tool. We’ll display the product names and keep track of the ID of the selected product. Every time the user selects an item on the list, we’ll make a trip to the database and retrieve the relevant fields of the selected product. The SQL statement you’ll use for the SELECT command of the DAProducts DataAdapter is the following:

SELECT	ProductID, ProductName
FROM	dbo.Products

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

1054 Chapter 24 ACCESSING DATA ON THE WEB

In the Generate SQL Statements window of the configuration wizard, click the Advanced button and clear the option Generate Insert, Update And Delete Statements. We only want to display data to the users, not edit them. Create the DSProducts DataSet by clicking the Generate DataSet link on the Properties window.

You must also create another DataAdapter that retrieves a product by its ID. This time we want to retrieve the supplier’s name along with the product info. Building the appropriate SQL statement shouldn’t be difficult. Drop the Products table on the design surface again, rename the new DataAdapter object to DASelectedProduct, and configure it. If you’re using the Query Builder to build the SQL statement, its window should look like the one shown in Figure 24.3.

Figure 24.3

Building a query to retrieve the selected product along with its supplier’s name

The equivalent SQL statement is:

SELECT	dbo.Products.QuantityPerUnit, dbo.Products.UnitPrice,
	dbo.Products.UnitsInStock, dbo.Suppliers.CompanyName
FROM	dbo.Products INNER JOIN
	dbo.Suppliers ON dbo.Products.SupplierID = dbo.Suppliers.SupplierID
WHERE	(dbo.Products.ProductID = @prodID)

Figure 24.3 shows the query executed for the product with ProductID = 4. The query’s parameter must be set from within the page’s code to the ID of the selected product. Then generate the DSSelectedProduct DataSet by clicking the Generate DataSet link on the Properties window.

OK, we’ve generated all the basic objects we’ll use in our code, let’s write some code. But first, we should bind the ListBox control to the DSProducts1 DataSet by setting the following properties to the values shown below:

Property	Setting
DataSource	DSProducts1
DataMember	Products

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

THE DATA-BOUND WEB CONTROLS 1055

Property

Setting

DataTextField ProductName

DataValueField ProductID

Then enter the following statements in the page’s Load event handler and run the project:

If Not Me.IsPostBack Then

DAProducts.Fill (DSProducts1, “Products”) lstProducts.DataBind()

End If

The first statement populates the DataSet object, as usual, and the second statement binds the ListBox control to the Products table. The details of the binding were set at design time through the Properties window. When the Web page appears in the browser, you will see the names of all products on the control, but nothing on the TextBoxes. We must write some code to update the remaining controls.

The TextBoxes will be bound to the second DataSet, which contains the row of the selected product. Select the txtPrice TextBox on the form, where the UnitPrice field will be displayed. Then locate the DataBindings property in the Properties window (it’s the first property) and click the button with the ellipsis. You will see the DataBindings dialog box for the txtPrice control, which is shown in Figure 24.4. Expand the DSSelectedProduct1 item, then the Products item under it, then the DefaultView (twice), and you will see the fields of the DataSet, as shown in the figure. Click the column UnitPrice (not shown in the figure), then close the dialog box by clicking the OK button. Bind the other TextBox controls on the form to the appropriate columns by repeating the same process for each control.

Figure 24.4

Binding a TextBox control to a column

To update the TextBox controls on the form, we must program the SelectedIndexChanged event. To intercept this event in your code, you must set the ListBox control’s AutoPostBack property to True. Then you must read the Value member of the SelectedItem object and use it as argument to the

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

1056 Chapter 24 ACCESSING DATA ON THE WEB

second DataAdapter object, which retrieves the details of the selected product. Enter the statements from Listing 24.3 in the ListBox control’s SelectedIndexChanged event handler and run the project.

Listing 24.3: Binding the TextBox Control to a Single-Row DataSet

Private Sub lstProducts_SelectedIndexChanged(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles lstProducts.SelectedIndexChanged

DASelectedProduct.SelectCommand.Parameters(“@prodID”).Value = _ lstProducts.SelectedItem.Value

DASelectedProduct.Fill(DSSelectedProducts1, “Products”) txtPrice.DataBind()

txtPackage.DataBind()

txtStock.DataBind()

txtSupplier.DataBind() End Sub

You will see Internet Explorer displaying a form with a ListBox control that contains all the product names. Click a name to see the product’s details, including its supplier’s name.

The values on text boxes are reset each time we revisit a row. You can edit a field, move to another one, and then return to the edited row to find out that your edits were discarded. Do you see why? The DataSet is created every time you connect to the application. The server is not aware that the same page has been visited before. Every time, it will create the page from scratch, including its DataSet. If you find this behavior odd, consider what it would take to maintain a DataSet in memory for every user that connects to the server that hosts the application. Users might connect to your application and then switch to another application and keep the DataSet alive on the server for hours. Multiply this by hundreds or thousands, and you’ll appreciate the disconnected nature of ADO.NET. You can always read the values on the controls and update the table in the database with a SQL statement or a stored procedure.

VB.NET at Work: The CMDProducts Project

In this section, we’ll build the WebProducts project again, only this time without the visual tools. You’ve seen quite a bit of these tools, so it’s a good time to refresh your skills on programming the ADO object. Start a new ASP.NET Web Application project and name it CMDProducts. You won’t have to add any Connection or DataAdapter objects to the project; we’ll create everything from within our code. Listing 24.4 shows the code that’s executed when the page is loaded. The code sets up a new Command object, uses it to populate a new DataSet, and then assigns the DataSet to the ListBox control’s DataSource property. It’s the same thing we did visually in the previous section, only in code.

Listing 24.4: Populating a DataSet with Code

Private Sub Page_Load(ByVal sender As System.Object, _

ByVal e As System.EventArgs) Handles MyBase.Load ‘ Put user code to initialize the page here

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

THE DATA-BOUND WEB CONTROLS 1057

If Not Me.IsPostBack Then

Dim cmd As New SqlClient.SqlCommand()

cmd.CommandText = “SELECT ProductID, ProductName FROM Products” cmd.CommandType = CommandType.Text

cmd.Connection = SqlConnection1 SqlConnection1.Open()

Dim DS As New DataSet()

Dim DA As New SqlClient.SqlDataAdapter() DA.SelectCommand = cmd

DA.Fill(DS, “Products”)

ListBox1.DataSource = DS ListBox1.DataMember = “Products” ListBox1.DataTextField = “ProductName” ListBox1.DataValueField = “ProductID” ListBox1.DataBind() SqlConnection1.Close()

End If End Sub

How do we bind the TextBox controls? The TextBox control doesn’t expose members for binding its text to a column. If you recall from the previous example, we had to bind the TextBox control through its DataBindings dialog box. If you take a good look at Figure 24.4, while you specified the column to which the TextBox control is to be bound, a lengthy expression appeared in the Custom Binding Expression field. This is the expression we must use in our code. The DataBinder object is equivalent to the DataBinding object we encountered in building Windows data driven applications. Its Eval method returns the value of a field in one of the tables.

The listing of the ListBox control’s SelectedIndexChanged event handler is shown in Listing 24.5.

Listing 24.5: Retrieving the Details of the Selected Product

Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, _

ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged Dim prodID As Integer

prodID = ListBox1.SelectedIndex

Dim cmd As New SqlClient.SqlCommand() cmd.CommandText = _

“SELECT QuantityPerUnit, UnitPrice, UnitsInStock, CompanyName “ & _ “FROM Products INNER JOIN Suppliers “ & _

“ON Products.SupplierID = Suppliers.SupplierID “ & _ “WHERE ProductID = “ & prodID

cmd.CommandType = CommandType.Text cmd.Connection = SqlConnection1 SqlConnection1.Open()

Dim DS As New DataSet()

Dim DA As New SqlClient.SqlDataAdapter() DA.SelectCommand = cmd

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

1058 Chapter 24 ACCESSING DATA ON THE WEB

DA.Fill(DS, “Products”) txtPrice.Text = DataBinder.Eval(DS, _

“Tables[Products].DefaultView.[0].UnitPrice”)

txtPrice.DataBind()

txtPackage.Text = DataBinder.Eval(DS, _ “Tables[Products].DefaultView.[0].QuantityPerUnit”)

txtPackage.DataBind()

txtStock.Text = DataBinder.Eval(DS, _ “Tables[Products].DefaultView.[0].UnitsInStock”)

txtStock.DataBind()

txtSupplier.Text = DataBinder.Eval(DS, _ “Tables[Products].DefaultView.[0].CompanyName”)

txtSupplier.DataBind()

SqlConnection1.Close() End Sub

VB.NET at Work: The ProductsPerCategory Project

In this section, we’ll develop a Web application that allows the user to select a category and then displays the products in the selected category. We’ll implement two forms with same functionality, one using the DataList control and another using the DataGrid control. Displaying the data on these two controls is adequate for many types of applications, but for an application of this type to be really useful, you must be able to detect when the user selects an item on the control.

The ProductsPerCategory project has two forms, and you must change the project’s startup page to set the other one. The form with the DataGrid control is shown in Figure 24.5, and the form with the DataList control is shown in Figure 24.6.

Figure 24.5

The DataGridForm of the ProductsPerCategory project

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

THE DATA-BOUND WEB CONTROLS 1059

Figure 24.6

The DataListForm of the ProductsPerCategory project

The DataGrid control on the form displays the products in the selected category. The Select buttons in the first column allow you to select a row, which is displayed with a different background color and in bold. The ID of the selected product is also displayed on a Label control at the top of the form. Once you can extract the key of the selected row, you can access the matching row in the database and manipulate it directly.

To build the application, you must create the appropriate objects to access the database. Drop the Categories and Products tables from the Server Explorer onto the form. Then configure the two DataAdapters and create two DataSets, one with all the rows of the Categories table and another with the products of a specific category. The process of creating DataSets has been described in detail in previous chapters, so I will only show you the SELECT statements for each DataAdapter. None of the DataAdapters support the update of the underlying tables.

The SELECT statement for DACategories is:

SELECT CategoryID, CategoryName, Description, Picture

FROM dbo.Categories

The SELECT statement for DAProductsInCategory is:

SELECT ProductID, ProductName, QuantityPerUnit, UnitPrice

FROM dbo.Products

WHERE (CategoryID = @CatID)

Both forms have a DropDownList control, where the names of all categories are displayed. Here are the properties of the DropDownList control on both forms.

Property	Setting
DataSource	DSCategories1
DataMember	Categories

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

1060 Chapter 24 ACCESSING DATA ON THE WEB

Property	Setting
DataTextField	CategoryName
DataValueField	CategoryID

When the page is loaded for the first time, we must populate the DropDownList control with the category names, using the code in Listing 24.6.

Listing 24.6: Populating the DropDownList Control

Private Sub Page_Load(ByVal sender As System.Object, _

ByVal e As System.EventArgs) Handles MyBase.Load

‘Put user code to initialize the page here If Not Me.IsPostBack Then

DACategories.Fill(DSCategories1)

DropDownList1.DataBind()

End If

End Sub

In our code, we want to detect when an item was selected on the control. This action is signaled to the Web application through the control’s SelectedIndexChanged, and we must insert the appropriate code in this event’s handler to populate the DataGrid (or DataList) control with the products of the selected category. This event won’t be fired unless you set the control’s AutoPostBack property to True. Listing 24.7 presents the SelectedIndexChanged event handler of the DropDownList control.

Listing 24.7: Displaying the Products in the Selected Category

Private Sub DropDownList1_SelectedIndexChanged(ByVal sender As System.Object, _ ByVal e As System.EventArgs) _

Handles DropDownList1.SelectedIndexChanged

DAProductsInCategory.SelectCommand.Parameters(“@catID”).Value = _ DropDownList1.SelectedItem.Value

DAProductsInCategory.Fill(DSProducts1)

DataGrid1.DataBind() End Sub

The code retrieves the ID of the selected category and passes it as argument to the DataAdapter’s SelectCommand. This command is executed when the Fill method is called, and it populates the DSProducts1 DataSet with the matching rows. The last statement binds the DataGrid control to the newly created DataSet. The DataBind method instructs the compiler to use the data in the DataSet to create a grid-like structure with HTML statements to send to the client. After that, the DataSet is discarded.

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

THE DATA-BOUND WEB CONTROLS 1061

The code of the Load event handler in the DataListForm is identical to Listing 24.6.

And so is the code—almost—of the SelectedIndexChanged event handler of the DropDownList control. It differs in that it calls the DataBind method to bind a different control to the DataSet:

Private Sub DropDownList1_SelectedIndexChanged(ByVal sender As System.Object, _ ByVal e As System.EventArgs) _

Handles DropDownList1.SelectedIndexChanged

DAProductsInCategory.SelectCommand.Parameters(“@catID”).Value = _ DropDownList1.SelectedItem.Value

DAProductsInCategory.Fill(DSProducts1)

DataGrid1.DataBind() End Sub

Let’s switch our attention to the design of the DataList control. Unlike the DataGrid control, the DataList control doesn’t autogenerate its items based on the columns of its data source. You’ll have to step in and actually add a few lines of HTML code. What’s really needed is a designer similar to the Web page designer for the cells of the control. Right-click the control and select Edit Templates Item Templates. You’re ready to customize the template for each item.

In the gray area under the ItemTemplate heading, enter any strings you want to appear in the item’s area. Enter the names of the fields as shown in Figure 24.7, and then format them. To specify the data-bound values that will appear next to the heading string, click the HTML tab at the bottom of the design surface, and you will see something like the HTML code shown in Figure 24.8. This is the HTML behind your page. As you can see, everything you do on the design surface with visual tools is translated into HTML, which can be sent to the client.

Figure 24.7

Customizing the appearance of a DataList control

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

1062 Chapter 24 ACCESSING DATA ON THE WEB

Figure 24.8

This is what the HTML code that implements your page looks like in the editor.

Locate the captions you entered on the ItemTemplate box and modify them to look like Listing 24.8 (I’m showing the entire ItemTemplate and AlternatingItemTemplate here). After each title, insert the appropriate DataBinder expression to display each field next to its caption. You will also see many <P> and </P> tags (paragraphs), which insert vertical space. Remove most of them to conserve space on the cell, and use the <BR> tag to move to the next line. (The <BR> tag inserts a newline character but no additional vertical space.)

Listing 24.8: Customizing the ItemTemplate and AlternatingItemTemplate Objects

<ItemTemplate>

<STRONG>Product</STRONG>

<I><%# DataBinder.Eval(Container.DataItem, “ProductName”) %></I> <BR><STRONG>Price</STRONG>

<BR><STRONG>Packaging</STRONG>

<I><%# DataBinder.Eval(Container.DataItem, “QuantityPerUnit”) %></I> <BR><asp:Button id=”bttnSeect” runat=”server” width=”53px” height=”24px”

text=”Select”></asp:Button>

</ItemTemplate>

<HeaderStyle Font-Bold=”True” ForeColor=”#E7E7FF” BackColor=”#4A3C8C”> </HeaderStyle>

<AlternatingItemTemplate>

<STRONG>Product</STRONG>

<I><%# DataBinder.Eval(Container.DataItem, “ProductName”) %></I> <BR><STRONG>Price</STRONG>

<I><%# DataBinder.Eval(Container.DataItem, “UnitPrice”) %></I> <BR><STRONG>Packaging</STRONG>

<I><%# DataBinder.Eval(Container.DataItem, “QuantityPerUnit”) %></I> <BR><asp:Button id=”bttnSelect” runat=”server” width=”53px” height=”24px”

text=”Select”></asp:Button>

</AlternatingItemTemplate>

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

THE DATA-BOUND WEB CONTROLS 1063

This code will retrieve the proper values from the DataSet and place them on the Item box at runtime. Designing the cells of the DataList control will turn out to be a trial-and-error process, because you must understand the code generated by the wizard and then insert your own HTML tags in it. My suggestion is to edit the cell visually, insert placeholders (like ID, Name, Price, and so on), format the strings as if they were the actual field values, and then replace the strings in the HTML code with the proper DataBinder expressions.

So far you’ve seen how the two controls are populated. How do we handle the selection of another row in the DataGrid control? If you run the application now, you will see two forms similar to the ones of Figures 24.5 and 24.6. We have not yet added the buttons on each product’s row (for the DataGrid control) or each product’s cell (for the DataList control). You will see one form at a time—whichever one is the project’s Startup Page. Change the project’s Startup Page to view the other.

Adding the Selection Buttons

Now we’ll add the selection button in the first column of the grid (and in each item of the list). The buttons won’t do anything special; they will simply print the ID of the selected item in a Label control. As you can understand, once you extract the key of the selected item, you can retrieve any related information from the database and display it on the Web form, or process it from within your code. In the section “A Master/Detail Form,” later in this chapter, you’ll see how you can program the Select buttons to update another control.

Right-click the DataGrid control and select Property Builder. In the DataGrid1 Properties dialog box (Figure 24.9), select the Columns tab. Clear the box Create Columns Automatically At Runtime, then add the Button Column item from the Available Columns list to the Selected Columns list. Set the new item’s Text property to “Select” and its CommandName property to “Select” also. When a Select button is clicked, the ItemCommand event is raised in the application. In the ItemCommand event’s handler, insert the statements of Listing 24.9.

Figure 24.9

Setting the properties of the DataGrid

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

1064 Chapter 24 ACCESSING DATA ON THE WEB

Listing 24.9: Handling the Selection of a Row on the DataGrid Control

Private Sub DataGrid1_ItemCommand(ByVal source As Object, _

ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) _ Handles DataGrid1.ItemCommand

Dim itm As Integer

itm = e.Item.ItemIndex()

Dim keys As DataKeyCollection keys = DataGrid1.DataKeys()

Label1.Text = “SELECTED ID = “ & keys(itm).ToString End Sub

The e.Item object represents the selected item on the control. The ItemIndex property returns the item’s index on the control. The DataKeys property of the DataGrid object is a collection

of all the keys on the control. We retrieve the key of the selected item and display it on a Label control. Later in this chapter, you will see how to add the selected item to a basket. As long as you can retrieve the key of the selected item, you can access its row in the corresponding table of the database.

To add a button to the DataList control, switch to the Item Edit mode (Edit Templates Item Template from the context menu) and drop a Button control on the Item area and another one on the AlternatingItem area. Then open the HTML tab of the editor and edit the code to position the button just right on the cell. Again, this is the type of operation we should be able to perform visually, but the designer inserts far too many <P> tags in such a small area. After you’re done editing the DataList control’s template, select End Template Editing from the context menu.

When the button is clicked at runtime, the ItemCommand event is raised. In this event, we want to retrieve the ID of the selected product and use it in our code. The ProductsPerCategory application doesn’t do anything with this ID except for displaying it on a Label control. The code that retrieves the ID of the product displayed on the selected cell of the control is shown in Listing 24.10.

Listing 24.10: Retrieving the Key Field of the Selected Product

Private Sub DataList1_ItemCommand(ByVal source As Object, _

ByVal e As System.Web.UI.WebControls.DataListCommandEventArgs) _ Handles DataList1.ItemCommand

Dim itm As Integer

itm = e.Item.ItemIndex()

Dim keys As DataKeyCollection keys = DataList1.DataKeys()

Label1.Text = “SELECTED ID = “ & keys(itm).ToString End Sub

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com