Using the DataReader Object ADO provided a Recordset object, and this object allowed for both connected and disconnected as well as cursored and cursorless data access. There were so many options that many developers never really figured out all that an ADO recordset could do. To avoid this overloading nightmare, ADO.NET provides separate objects, based on functionality. The DataAdapter object (SqlDataAdapter or OleDbDataAdapter) contains SQL command information and connection information, and it can go out and retrieve or manipulate data for you. The DataSet object provides a client-side cache for working with data. Under the covers, there must be some read-only, forward-only, highly optimized means of retrieving data as quickly as possible from a data source the DataSet has to have some way to retrieve data to fill its cache, right? And so it does. The DataReader object (actually, the SqlDataReader or OleDbDataReader object) is a distinct class that includes methods, properties, and events all focused on retrieving data much like a forward-only, read-only recordset in ADO. Of course, while a DataReader is retrieving its data, it must be connected to its data source. There's no caching involved in a DataReader's activity it simply reads a row and provides the row to your code; then it moves on to the next row. Using a DataReader is simple: You use the ExecuteReader method of a Command object to retrieve the DataReader; then you call the Read method of the DataReader to retrieve each row in turn. When the Read method returns False, you know you're done. In the simplest form, your code that works with a DataReader might look like this (assuming that cmd is an OleDbCommand object, already opened and poised for action): Dim dr As OleDbDataReader dr = cmd.ExecuteReader() Do While dr.Read ' Do something with columns within the current row Loop NOTE There are other issues, of course. For example, when you call the ExecuteReader method, you can pass in values indicating how you want the DataReader to behave. If you only want to retrieve a single row, using the CommandBehavior.SingleRow value will optimize for that situation. If you know you'll only want to retrieve data in column order, you can optimize for that situation by passing the CommandBehavior.SequentialAccess value. See the online help for the OleDbDataReader object for more information.
Why use the DataReader? If your goal is to fill a ListBox or DropDownList control programmatically, you can't beat it it's the fastest way to get your job done. Given that the DataSet object uses a DataReader under the covers to fill its own cache, you have to believe that you'll get better performance by using it yourself, in situations where you can live with forward-only, read-only access. Using a DataReader to Fill a DropDownList Control To demonstrate using a DataReader in an application, this section will walk through the steps involved in filling a DropDownList control with data from a DataReader. You've already seen how to accomplish this same goal by setting properties of the control to read its data from a DataSet. If your data is static, however, and you only need to fill the list once, you may decide to use a DataReader to accomplish the same goal. In this section, you'll add a DropDownList control containing a list of suppliers. When you select a supplier, you'll filter the grid to display only products from the selected supplier. (Selecting a category will also filter the grid, to only show products from the selected category.) When you're done, the page will look like Figure 14.1. Figure 14.1. Add the Suppliers drop-down list to filter products by the SupplierID field.
Adding the GetDataReader Method Although you could add code to your page to retrieve a DataReader object, it's a task you're likely to need to accomplish in more than one place in your application. Therefore, it makes sense to add a generic GetDataReader method to your DataHandler class. Given a connection string and a SQL string, this method will create an OleDbDataReader object and will return the DataReader back to your calling code. Follow these steps to add the GetDataReader method: -
Make sure your Northwind.sln solution is open in Visual Studio .NET. -
In the Solution Explorer window, select DataHandler.vb. -
Double-click to open the code editor window. -
Scroll to the top of the file and add the following Imports statement: Imports System.Data.OleDb -
Add the procedure shown in Listing 14.1 to the class. Listing 14.1 Given a SQL Statement and a Connection String, Create a DataReader Object Public Shared Function GetDataReader( _ ByVal SQLStatement As String, _ ByVal ConnectionString As String) As OleDbDataReader Dim dr As OleDbDataReader Dim cmd As New OleDbCommand() With cmd .Connection = _ New OleDbConnection(ConnectionString) .Connection.Open() .CommandText = SQLStatement dr = .ExecuteReader(CommandBehavior.CloseConnection) End With Return dr End Function TIP Because the GetDataReader procedure uses the Shared modifier, you needn't instantiate a DataHandler object before using the GetDataReader method.
To do its job, GetDataReader takes these actions: Declares an OleDbDataReader variable and creates an OleDbCommand object: Dim dr As OleDbDataReader Dim cmd As New OleDbCommand() Sets the Connection property of the Command object and opens the Command's connection: With cmd .Connection = _ New OleDbConnection(ConnectionString) .Connection.Open() ... End With Sets the CommandText property of the Command object and then calls the ExecuteReader method of the Command object to retrieve the DataReader object: With cmd ... .CommandText = SQLStatement dr = .ExecuteReader(CommandBehavior.CloseConnection) End With Returns the DataReader object as the return value of the function: Return dr WARNING It's important for you to close any connections that you've opened when you're done working with them. Normally, you can simply call the Close method of the Connection object when you're done with an object. In this case, however, you're passing the DataReader object back as the return value of the function, and the connection is still in use. Therefore, you can't close the connection! To solve this problem, ADO.NET allows you to pass the CommandBehavior.CloseConnection value when you call the ExecuteReader method. This value indicates to ADO.NET that it should close the connection for you, when you close the associated DataReader object. That's the approach we've taken here it's up to your code, however, to close the DataReader when you're done with it.
Loading Suppliers Using a DataReader Now that you have a method that can build a DataReader for you, follow these steps to fill a drop-down list with supplier information (you'll use the CompanyName field as the text displayed in the list and the SupplierID field as the value for each item in the list): -
Add Label and DropDownList controls for suppliers, as shown in Figure 14.1. -
Set the properties for the new controls as shown in Table 14.1. Table 14.1. Set These Properties for the New Controls | Control | Property | Value | | Label | Text | Suppliers | | DropDownList | ID | ddlSuppliers | | | AutoPostBack | True | -
Double-click the page to display the Page_Load procedure and then modify the procedure so that it looks like this: Private Sub Page_Load( _ ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles MyBase.Load If Not Page.IsPostBack Then CategoryLoad() ProductsLoad() CategoryAvgPrice() SupplierLoad() End If End Sub -
Add the SupplierLoad procedure: Private Sub SupplierLoad() Dim oItem As ListItem Dim dr As OleDbDataReader Dim strSQL As String Dim strConn As String strSQL = _ "SELECT SupplierID, CompanyName " & _ "FROM Suppliers ORDER BY CompanyName" strConn = Session("ConnectString").ToString Try dr = DataHandler.GetDataReader(strSQL, strConn) Do While dr.Read oItem = New ListItem( _ dr("CompanyName").ToString, _ dr("SupplierID").ToString) ddlSuppliers.Items.Add(oItem) Loop dr.Close() Catch End Try End Sub The SupplierLoad procedure fills in the DropDownList control with all the suppliers, using these actions: -
It builds the appropriate SQL and connection strings: strSQL = _ "SELECT SupplierID, CompanyName " & _ "FROM Suppliers ORDER BY CompanyName" strConn = Session("ConnectString").ToString -
It retrieves the DataReader object, supplying the SQL and connection strings: dr = DataHandler.GetDataReader(strSQL, strConn) -
It loops through all the rows provided by the DataReader and then closes the DataReader (which also closes the connection): Do While dr.Read ... Loop dr.Close() -
For each row provided by the DataReader, the SupplierLoad procedure creates a new ListItem object, supplies the ListItem object with the text and value to be added to the DropDownList control, and then adds the new ListItem object to the DropDownList control: oItem = New ListItem( _ dr("CompanyName").ToString, _ dr("SupplierID").ToString) ddlSuppliers.Items.Add(oItem)
One more interesting side note: How can you retrieve field values from the DataReader? You have a number of choices here: You can retrieve the columns by their ordinal position (for example, dr.Item(3) or dr(3)). You can retrieve the columns by name (for example, dr.Item("CompanyName") or dr("CompanyName")). This is the technique we used in the example. You can retrieve the columns by calling a function that returns a specific data type. For example, dr.GetString(0) returns a string containing the value in column 0. You'll find Get… methods for all the basic data types. This is the most efficient technique, because it requires no data conversion. The other techniques require you to convert from an object to the appropriate data type we used the ToString method in the example to accomplish this conversion. To keep things simple, this example uses the field name to retrieve the field value. Although this is the simplest technique, in terms of development and maintenance, it's also the least efficient. If speed is your goal, look into the Get… methods.  | To try out all the changes you've added to the page, follow these steps: |
-
In the Solution Explorer window, right-click Products.aspx and select Build and Browse from the context menu. -
Verify that the list of suppliers has been filled in and that you can select a supplier from the list. You don't need to loop through the rows in a DataReader to fill a DropDownList control with data. You can get the benefits of using a DataReader using the same type of data binding you've already seen ASP.NET allows you to bind a DropDownList control to a DataReader, just as you can bind it to a DataSet. We included the previous example to show how you can work with a DataReader, because it's important that you see how to loop through the rows of the DataReader explicitly. Actually, you could rewrite the code in the SupplierLoad procedure so that it simply binds the control to the DataReader, as shown in Listing 14.2. Listing 14.2 Rewrite of the Previous Version, Binding the DropDownList Control Private Sub SupplierLoad() Dim oItem As ListItem Dim dr As OleDbDataReader Dim strSQL As String Dim strConn As String strSQL = _ "SELECT SupplierID, CompanyName " & _ "FROM Suppliers ORDER BY CompanyName" strConn = Session("ConnectString").ToString Try dr = DataHandler.GetDataReader(strSQL, strConn) With ddlSuppliers .DataSource = dr .DataTextField = "CompanyName" .DataValueField = "SupplierID" .DataBind() End With dr.Close() Catch End Try End Sub If you like, try it out you get the exact same results, and the code may run the slightest bit faster, as well. In production code, most likely we'd use this technique. When would you loop through the rows of a DataReader? If you needed to run some code for each row, as you loaded the DropDownList control, for example, it might be necessary to visit each row in turn before adding content to the list. It's up to you it requires just about the same amount of code either way. Filtering the Grid Based on Supplier ID Now that your page includes a list of suppliers, it only makes sense that you would want to be able to filter the grid to show all products from a given supplier or all products from a given category, depending on which drop-down list you select. The ProductsLoad procedure currently takes only the CategoryID value into account when building the SQL string it uses to create the DataSet that fills the grid. You can modify the procedure so that if you pass an optional supplier ID, it will use that value instead. To add this functionality, follow these steps: -
With Products.aspx open in the designer, double-click ddlSuppliers to load the ddlSuppliers_SelectedIndexChanged procedure. Modify the procedure so that it looks like this: Private Sub ddlSuppliers_SelectedIndexChanged( _ ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles ddlSuppliers.SelectedIndexChanged ProductsLoad(ddlSuppliers.SelectedItem.Value) End Sub -
Modify the ProductsLoad procedure, adding code to support the SupplierID parameter: Private Sub ProductsLoad( _ Optional ByVal SupplierID As String = "") Dim ds As DataSet Dim strSQL As String strSQL = daProducts.SelectCommand.CommandText If SupplierID = String.Empty Then ' No supplier ID? Use the value in the ' Category dropdown list. strSQL &= " WHERE CategoryID = " & _ ddlCategories.SelectedItem.Value Else ' If you got a SupplierID value, use ' that to filter the data. strSQL &= " WHERE SupplierID = " & _ SupplierID End If ds = New DataSet() With daProducts .SelectCommand.CommandText = strSQL .Fill(ds) End With With grdProducts .DataSource = ds .DataBind() End With End Sub TIP In Visual Basic .NET, you can add optional parameters to procedures, as we've done here. If you don't pass a value for the SupplierID parameter, the code will assign it a default value (""). If you do pass a value, the parameter will take on that value. -
Browse the page, verifying that selecting either a category or a supplier correctly filters the grid. |
