956 Chapter 21 BUILDING DATABASE APPLICATIONS WITH ADO.NET

Listing 21.10: Retrieving the Selected Customer’s Orders

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

Dim row As Integer = ListBox1.SelectedIndex

Dim CustID As String = DsCustomers1.Customers(row).CustomerID Orders.SelectCommand.Parameters(“CustomerID”).Value = CustID DsOrders1.Clear()

Orders.Fill(DsOrders1) End Sub

We must also detect the selection of a row in the DataGrid control with the Orders, retrieve the detail lines of the selected order and display them on the second DataGrid control. When a row (or cell) is selected on the DataGrid control, the CurrentCellChanged event is fired. Listing 21.11 shows the code that displays the selected order’s details on the second DataGrid control.

Listing 21.11: Retrieving the Selected Order’s Details

Private Sub DataGrid1_CurrentCellChanged(ByVal sender As Object, _

ByVal e As System.EventArgs) Handles DataGrid1.CurrentCellChanged Dim row As Integer = DataGrid1.CurrentRowIndex

Dim OrdID As Integer = DataGrid1.Item(row, 0) Details.SelectCommand.Parameters(“OrderID”).Value = OrdID DsDetails1.Clear()

Details.Fill(DsDetails1) End Sub

This is all the code required by the application, and it’s not complicated either. We determine the parameter required by the query we want to call and then pass it to the query through the Parameters collection of the SelectCommand object. Of course, you must set the data-binding properties of the two DataGrid controls, so that they will update their contents when the underlying DataSets change. To bind the two DataGrid controls, set their DataSource property to the name of the corresponding DataSet.

The Command and DataReader Objects

Sometimes, we want to retrieve data from the database and read them sequentially. If you don’t plan to edit the data and submit any changes back to the client, you can use the DataReader object to read the rows sequentially. The DataReader is an object that lets you iterate through the rows retrieved by a query. It’s faster than storing all the rows to a DataSet, but you can’t move back and forth in the rows. Moreover, the connection to the database is open while you iterate through the rows, so the processing should be as quick as possible. For example, you can’t prompt the user between rows; this

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

THE COMMAND AND DATAREADER OBJECTS 957

would tie the connection for too long. It goes without saying that you can’t use the DataReader object to update the underlying table. The DataReader object returns a forward-only, read-only result.

As with the other major ADO.NET objects, there are two flavors of the DataReader object: the SqlDataReader and the OleDbDataReader objects. Use the SqlDataReader object for SQL Server databases and the OleDbDataReader for OLEDB-compliant databases. To create a DataReader object, you must execute a query against a database through a Command object. You’ve already set up Command objects, even though you didn’t do so explicitly. This time we’ll create a Command object and set its Connection and CommandText properties from within our code. Once the Command object has been set up, you can execute it by calling one of the following methods:

ExecuteReader Executes the command and returns a DataReader object, which you can use to read the results, one row at a time.

ExecuteXMLReader Executes the command and returns a XMLDataReader object, which you can use to read the results, one row at a time.

ExecuteScalar Executes the command, returns the first column of the first row in the result, and ignores all other rows.

ExecuteNonQuery Executes a SQL command against the database and returns the number of rows affected. Use this method to execute a command that updates the database.

The first two methods return a DataReader object, the ExecuteNonQuery method returns an integer (the number of rows affected), and the ExecuteScalar method returns an object (the first column of the first row in the result set). The DataReader is an abstract class and can’t be used in an application. Instead, use the SqlDataReader or the OleDbDataReader object, depending on the database you’re connected to.

VB.NET at Work: The DataReader Project

This section’s project demonstrates the simplest possible use of a SqlDataReader object. We’ll read the category names from the Categories table and place them on a ListBox control. To test this code, place an instance of the SqlConnection and SqlCommand controls on a new form. These two controls must be selected from the Data tab of the Toolbox. They’re not configured, because they’re not associated with any objects in the database. Let’s configure them.

Select the SqlConnection1 object on the designer and open its Properties window. Locate the ConnectionString property and, from the drop-down list, select the Northwind database. Then select the SqlCommand1 object on the designer, open its Properties window, and locate the Connection property. Expand the list of available connections and set it to SqlConnection1 (your project contains a single Connection object). Then locate the CommandText property and click the button with the ellipsis. This action will start the Query Builder, where you can create a query with the rows you want to retrieve. Add the Categories table to the query and select the fields CategoryID and CategoryName. Then click OK to close the Query Builder.

So far, you’ve established a connection to the Northwind database and created a command to retrieve the names of all categories. All you have do now is to execute the command and process its results.

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

958 Chapter 21 BUILDING DATABASE APPLICATIONS WITH ADO.NET

In our code we’ll use the ExecuteReader method to retrieve textual information. Place a Button and a ListBox control on the form, and enter the statements from Listing 21.12 in the button’s Click event handler.

Listing 21.12: Iterating the Rows of a SqlDataReader Object

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

ByVal e As System.EventArgs) Handles Button1.Click

SqlConnection1.Open()

Dim SQLReader As System.Data.SqlClient.SqlDataReader

SQLReader = SqlCommand1.ExecuteReader()

While SQLReader.Read

ListBox1.Items.Add(SQLReader.Item(“CategoryID”) & vbTab & _

SQLReader.Item(“CategoryName”))

End While

SqlConnection1.Close()

End Sub

First, the code opens the connection. Normally, the connection is opened by the DataAdapter object when the application requests data, but this time we don’t have a DataAdapter object. Then it calls the Command object’s ExecuteReader method and assigns the result to a SqlDataReader object. The data isn’t stored in this object. Instead, we’ll use this object to iterate through the rows returned by the query. This must take place from within a loop, which must go quickly through the data and then close the connection.

The Read method of the SqlDataReader object returns true while there is more data to be read. Each time you call the Read method, the DataReader moves to the next row, and you can read the current row’s fields through the Item property. The Item property accepts as argument the name or index of a column and returns its value. At each iteration of the loop, we read the two fields of the current row and add them to the ListBox control.

VB.NET at Work: The StoredProcedure Project

The example of this section shows a slightly different method of retrieving just the information you need to present to the user and no more. This time we’ll use one of the stored procedures we developed in Chapter 20. The OrdersPerCustomer stored procedure retrieves the number of orders placed by a customer and the grand total of these orders. This stored procedure doesn’t place any computational burden on the database server, because it applies to a single customer. What if the user wanted to see the totals for several, or even many, customers? Would you write another procedure that calculates the same totals for all customers? In a real database with thousands of customers and many orders per customer, the computational burden is no longer insignificant.

To avoid having the computer perform unnecessary operations and return results that the user may not even see, we’ll force users to select the customer whose totals they want. Figure 21.18 shows the interface of the application. The user is expected to select a customer from the list on the left, and then the selected customer’s totals will appear on the form. The totals are calculated as requested, which may lead to many requests to the database server. However, each request is serviced in no time

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

THE COMMAND AND DATAREADER OBJECTS 959

at all, and it’s certainly more efficient than calculating the totals for all customers and moving the information to the client; the user may only look at a few totals and just ignore the rest.

Figure 21.18

Retrieving information from the database with stored procedures

Start a new project and place on its form the controls you see in Figure 21.18. The ListBox control must be populated with the names of all the companies in the Customers table. Drop the Customers table from the Server Explorer onto the form and a new DataAdapter object will be added to the project. Rename the DataAdapter to DACustomers and configure it so that it retrieves only the CustomerID and CompanyName fields from the database. Here’s the DataAdapter’s SELECT statement:

SELECT CustomerID, CompanyName FROM dbo.Customers

Then generate the Customers DataSet and use it to populate the ListBox control. Set the control’s DataSource property to Customers1.Customers, its DisplayMember property to CompanyName, and ValueMember to CustomerID. Then call the DataAdapter’s Fill method from within the form’s Load event handler:

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

ByVal e As System.EventArgs) Handles MyBase.Load

DACustomers.Fill(Customers1)

End Sub

If you run the application now, the ListBox will be populated with the customers’ names. In the control’s SelectedIndexChanged event handler, we must execute the OrdersPerCustomer procedure to retrieve the two totals for the selected customer. To execute the stored procedure, add a SqlCommand object to the form.

If you haven’t followed the examples in the previous chapter, you must add OrdersPerCustomer to the Northwind database (the application expects that the stored procedure is part of the database and will call it by name). To add a new stored procedure to a database, start Enterprise Manager, locate the Stored Procedures section under the Northwind database, and from its context menu select New Stored Procedure. Then add the definition shown in Listing 21.13 to the new stored procedure and save it as OrdersPerCustomer.

Listing 21.13: The OrdersPerCustomer Stored Procedure

ALTER PROCEDURE dbo.OrdersPerCustomer @CustomerID nchar(5)=’ALFKI’

AS

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

960 Chapter 21 BUILDING DATABASE APPLICATIONS WITH ADO.NET

SELECT	dbo.Customers.CompanyName,
	COUNT(dbo.Orders.OrderID) AS [Total Orders],
	dbo.Customers.CustomerID,
	CAST(SUM((dbo.[Order Details].UnitPrice *
	dbo.[Order Details].Quantity) *
	(1 - dbo.[Order Details].Discount)) AS money) AS [Total Amount]
FROM	dbo.Customers
	INNER JOIN dbo.Orders
	ON dbo.Customers.CustomerID = dbo.Orders.CustomerID
	INNER JOIN dbo.[Order Details]
	ON dbo.Orders.OrderID = dbo.[Order Details].OrderID
GROUP BY dbo.Customers.CompanyName, dbo.Customers.CustomerID
HAVING	(dbo.Customers.CustomerID = @CustomerID)

Select the SqlCommand object from the Data tab of the Toolbox and drop it on the form. To execute the stored procedure, set the SqlCommand object’s CommandText property to the name of the stored procedure, set its CommandType to CommandType.StoredProcedure, and then create a new parameter and set it to the ID of the selected customer.

To set up a parameter, you must first create a Parameter object and add it to the Parameters collection of the SqlCommand1 object. The Parameter must be added to the collection only once. Then, you must set the properties of the Parameter object. The two most important properties of the Parameter object are the Direction and Value properties. The Direction property determines whether the parameter passes a value to the stored procedure, receives a value from the stored procedure, or both. Its value can be one of the members of the ParameterDirection enumeration: Input, Output, InputOutput, and ReturnValue. The parameter’s Value is the ID of the selected customer, which is given by the SelectedValue property of the ListBox control. The SelectedValue property returns the value of the field specified with the ValueMember property.

The last step is to execute the command and accept the results into a SqlReader object. Listing 21.14 shows the code that’s executed when the user selects a different customer in the ListBox control.

Listing 21.14: Executing a Stored Procedure with the SqlCommand Object

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

SqlConnection1.Open()

SqlCommand1.CommandType = CommandType.StoredProcedure SqlCommand1.CommandText = “OrdersPerCustomer”

If SqlCommand1.Parameters.Count = 0 Then

SqlCommand1.Parameters.Add(New _ System.Data.SqlClient.SqlParameter(“@CustomerID”, SqlDbType.NChar))

SqlCommand1.Parameters(0).Direction = ParameterDirection.Input End If

SqlCommand1.Parameters(0).Value = ListBox1.SelectedValue.ToString Dim SQLReader As System.Data.sqlclient.SqlDataReader

SQLReader = SqlCommand1.ExecuteReader

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com