PROGRAMMING THE DATAADAPTER OBJECT 951

Listing 21.8: Navigating via the ListBox

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

Me.BindingContext(DsProducts1, “Products”).Position = ListBox1.SelectedValue End Sub

Run the application now and check out how the current product’s category and supplier are displayed on the two ComboBox controls. You can also change a product’s category or supplier by selecting another item on the appropriate control. This application will crash if you enter an invalid field value (a negative price, for example). However, it’s easy to validate the data on the controls and make sure that they don’t violate any constraints, before you submit them to the database. Because the current product’s category and supplier must be selected from a list, users can’t violate the integrity of the database by mistake.

Programming the DataAdapter Object

Before we exhaust the topic of the data binding, I’d like to bring to your attention the fact that all the applications we’ve developed so far move all the data they may need from the database server

to the client. This is the essence of disconnected DataSets: you bring the data to the client and work with them locally. But shouldn’t there be a limit on the amount of data we move around? Indeed, you can’t download a table with 30,000 customers just because a user wants to view (or edit) a phone number. Likewise, you can’t move information about half a million titles to the client just because the user wants to view a couple of them.

Disconnected DataSets are not your license to make copies of the database (or even a substantial section of it) to every client. You must design your application so that it stores to the client only the rows absolutely necessary for the task at hand, and no more. This is easier said than done, and we usually pass this responsibility to the user. We design an interface that allows users to specify the rows they need and then retrieve only the ones that meet the criteria (like products in a price range or customers from a country). You will also bring the related rows in other tables.

The Command Objects

Each DataAdapter has four command objects, which provide the information needed to interact with the database: DeleteCommand, InsertCommand, UpdateCommand, and SelectCommand. If an application isn’t going to alter the database, then you need only specify SelectCommand, which retrieves data with a SELECT statement.

Each of these objects has a CommandText property, which is the name of the stored procedure or SQL statement that acts against the database; a Connection object, which determines the database the command object acts upon; and a collection of Parameter objects (the Parameters collection), which are the parameters expected by the SQL statement or stored procedure. These four Command objects are adequate to interact with the tables in the database.

In the examples so far, we let the wizard generate the appropriate SQL statements for us. In this section we’ll create a Connection object and a DataAdapter from scratch. This will help you understand a

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

952 Chapter 21 BUILDING DATABASE APPLICATIONS WITH ADO.NET

little better the basic objects of ADO.NET. In Chapter 22, we’ll go past data binding and you’ll learn how to program the same objects.

Start a new project and name it CustomerOrders. As you would guess, this application will display customers, orders, and order details. However, this time we’ll use a more elaborate interface, and we’ll write code that doesn’t move too much information over the network. In other words, we will not create a DataSet with all the customers, all their orders, and all the detail lines in each order. We’ll ask the user to specify the name of the customer by entering the first few characters of the company name. Then, with the appropriate SELECT statement, we move only the matching names to the client and display them on a ListBox control, as shown in Figure 21.15. When the user selects a name in this ListBox control, the selected customer’s orders are displayed on a DataGrid control that shows the order’s ID, the date it was placed, and its total. When an order is selected with the mouse, the order’s details are moved from the database to the client and displayed on the second DataGrid control.

Figure 21.15

The CustomerOrders application’s main form

To build this project, we’ll use the tools of the Data tab of the Toolbox. Instead of dropping tables from the Server Explorer onto the form and letting VB configure them, we’ll place the appropriate objects and configure them manually.

Start by placing an OleDbConnection object on the form and set its ConnectionString property to the following string:

Provider=SQLOLEDB.1;Integrated Security=SSPI;

Initial Catalog=Northwind;Data Source=PowerToolkit

You must change the name of the Data Source to match your installation. The OleDbConnection is functionally equivalent to the SqlConnection object, but it uses the OLEDB drivers to access the database. The SqlConnection object is optimized for accessing SQL Server databases, but I’ve used the OleDbConnection object to demonstrate that the two are totally equivalent.

Then drop an OleDbDataAdapter object on the form. The Data Adapter Configuration Wizard will start, and you must specify the following SQL statement to select a few customers:

SELECT CompanyName, ContactName, Country, CustomerID

FROM Customers

WHERE (CompanyName LIKE ?)

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

PROGRAMMING THE DATAADAPTER OBJECT 953

This is a parameterized query, and you must specify the value of the parameter before calling it (the parameter is indicated by the question mark).

On the screen of the wizard where you specify the SQL SELECT statement, click Advanced Options, and on the Advanced SQL Generation Options form, clear the option Generate Insert, Update, and Delete Statements. Our application will only display data, and we won’t use any other of these commands.

Then create a second DataAdapter object for the Orders table. This DataAdapter’s SQL statement should be:

SELECT	Orders.OrderID, Orders.OrderDate,
	SUM(([Order Details].UnitPrice * [Order Details].Quantity) *
	(1 - [Order Details].Discount)) AS OrderTotal
FROM	Orders INNER JOIN [Order Details]
	ON Orders.OrderID = [Order Details].OrderID
WHERE	(Orders.CustomerID = ?)

GROUP BY Orders.OrderID, Orders.OrderDate

This is a fairly straightforward statement that selects a few fields of the orders of a customer along with each order’s total. The query’s parameter is the customer’s ID, a value we’ll extract from the ListBox control when the user selects a name in the list.

Place yet another DataAdapter on the form, the OrderDetails Adapter, and set its SELECT statement to the following:

SELECT Products.ProductName, [Order Details].Quantity,

[Order Details].UnitPrice,

[Order Details].Discount

FROM [Order Details] INNER JOIN Products

ON [Order Details].ProductID = Products.ProductID

WHERE ([Order Details].OrderID = ?)

This statement selects the details of an order specified by its ID. Notice that instead of the ProductID field (which identifies the product in the Order Details table), we retrieve the name of the product from the Products table.

Once the three DataAdapters are in place, create three DataSet objects, one for each DataAdapter object on the form. Select the Customers DataAdapter and then use Data Generate Dataset. Set the new DataSet’s name to DSCustomers and add only the Customers table to the DataSet. Do the same for the other two DataAdapters and name their DataSets DSOrders and DSDetails. Figure 21.16 shows the form’s design surface. Notice the three DataAdapter and the three matching DataSet objects.

Select the Customers DataAdapter object on the form and, in the Properties window, locate and expand its SelectCommand object. You will see the SELECT statement you created next to the CommandText property. The setting of the CommandType property is Text. In the following section, you will see how to set the CommandText property to the name of a stored procedure. When the command is a stored procedure, the CommandType property should be set to Stored Procedure.

Another of the properties under the SelectCommand section of the Properties window is the Parameters property, which is a collection. This property contains information about the parameters of the SQL statement or stored procedure. Click the button with the ellipsis and you will see the OleDbParameter Collection Editor (Figure 21.17 shows the Properties window and the Collection

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

954 Chapter 21 BUILDING DATABASE APPLICATIONS WITH ADO.NET

Editor.) In this window you can specify the parameter’s name and data type. You will use the parameter’s name to assign a value to it later, and its type must match the type of the parameter in the query or stored procedure. The parameter of the statement that selects one or more customers is a string, while the parameter of the statement that retrieves the details of an order is an integer. You can also set the parameter’s value in this window, but parameter values are almost always set from within the code. The reason for using parameterized queries and stored procedures is that we want to be able to set the parameters at runtime.

Figure 21.16

The CustomerOrder project’s main form in design mode

Figure 21.17

Setting the properties of Command’s parameters

It’s time now to add some code to the application. The statement that retrieves customers must be entered in the TextBox control’s KeyUp event handler. Every time the user presses the Enter key, the statements of Listing 21.9 must be executed.

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

PROGRAMMING THE DATAADAPTER OBJECT 955

Listing 21.9: Selecting Customer’s by Name

Private Sub TextBox1_KeyUp(ByVal sender As Object, _

ByVal e As System.Windows.Forms.KeyEventArgs) Handles TextBox1.KeyUp If e.KeyCode = Keys.Enter Then

ListBox1.Visible = True Customers.SelectCommand.Parameters(“CompanyName”).Value = _

TextBox1.Text & “%” DsCustomers1.Clear() Customers.Fill(DsCustomers1) ListBox1.Focus()

End If End Sub

SelectCommand is a property of the DataAdapter object, so we can access it with the expression Customers.SelectCommand. It’s also an object that exposes its own members, one of them being the Parameters collection. To set the value of a parameter, you call the command’s Parameters collection, passing the name of the parameter as argument. Then set this member’s Value property to the desired value. Each member of the Parameters collection exposes more properties, such as DbType (use it to set the parameter’s type), IsNullable, and the Precision and Scale properties of numeric parameters.

Notice the percent sign following the value entered by the user on the TextBox control. If the user enters Antonio on the TextBox control, the parameter value passed to the query is Antonio%. This parameter will return all the customers whose name begins with “Antonio” followed by any other string.

Then the code clears the DSCustomers1 DataSet and populates it again with the rows returned by the query of the SelectCommand (this happens when the Fill method is called). The ListBox control is bound to the DSCustomers DataSet’s Customers table, so it’s automatically populated. The databinding properties of the ListBox control are as follows:

Property	Value
DataSource	DsCustomers1.Customers
DisplayMember	CompanyName

There are no other data-binding properties to be set. The order of the rows in the ListBox control is the same as their order in the DataSet’s table, so the SelectedIndex property determines the order of the selected rows in the Customers table of the DSCustomers DataSet.

When the user selects one of the customers in the ListBox control, the statements of Listing 21.10 are executed. They retrieve the CustomerID of the selected customer and pass it as parameter to

the SelectCommand of the Orders DataSet. Finally, the code calls the Fill method to populate

DSOrders1 DataSet.

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com