Retrieve Data by Using the DataReader Object

3,1 Lấy dữ liệu khi sử dụng các đối tượng DataReader Trong chương 1, bạn đã học cách sử dụng các điều khiển ràng buộc với OleDb điều khiển có thể được bao gồm trong các hình thức. Một số nhà phát triển thích sử dụng điều khiển Chưa cam kết thực hiện nhiệm vụ cùng
Nội dung trích xuất từ tài liệu:
Retrieve Data by Using the DataReader Object 3.1 Retrieve Data by Using the DataReader ObjectIn Chapter 1, you learned how to use bound controls to OleDb controls that could beincluded on the forms. Some developers prefer to use unbound controls to perform thesame task. The DataReader object allows you to add items to a list box in a more efficientmanner because it is only a read-and-forward-only type object. This How-To tells youhow to generate a limited ListBox control by using a DataReader object.You want to create a limited list of customers. You dont want to use bound controlsbecause you are a cool VB developer who knows better than that. You heard that theDataReader object is a fast way to get data. How do you retrieve data using theDataReader object to perform this task?TechniqueFor this How-To, you will be using the ListBox control and loading items into it by usingthe DataReader object. To get to the DataReader object, you need to look at theCommand object.The Command object in .NET works similarly to the ADO Command in that you willassign the stored procedure name or SQL statement to the CommandText property aswell as the connection to use. One difference is that you will use the Open method of theCommand object, and then the ExecuteReader method, to create the DataReader object.You will then use the Read method off of the DataReader object to perform two tasks.The first task is that when used in a loop, you can test for datareader.Read() to checkwhether to terminate the loop and also to iterate through the rows that the DataReaderobject returns.After you have the DataReader populated and you are iterating through the rows, you willload the data into the ListBox control. You will be using the Clear and Add methods,which were used in VB 6. In addition, you will use the BeginEdit and EndEdit methods,which speed up loading ListBox controls when you are loading a large amount of data.To view this example in design view, open the form called frmHowTo3_1.vb in thechapters solution.StepsOpen and run the VB.NET-Chapter 3 solution. From the main form, click on thecommand button with the caption How-To 3.1. When the form loads, click on the LoadList command button. You will see the list below fill with all the company names thatstart with A.You will be creating a form similar to one that was created in Chapter 1. Instead of usingbound controls, however, you will use code along with ADO.NET to populate theListBox control. 1. Create a Windows Form. Then place a Label, TextBox, ListBox, and Command button on the form with the properties that are listed in Table 3.3 set. Table 3.3. Label, TextBox, ListBox, and Command Button Control Property Settings Object Property Setting Label Name Label1 Caption Customer TextBox Name txtCustLimit Text A ListBox Name lstCustomers Command Button Name btnLoadList Caption Load List 2. Notice that the list box does not have the DataBindings properties set. That is because you will be using the ListBox control unbound. Look at Figure 3.3 to see what the form should look like. 3. Figure 3.3. Arrange the controls on the form that you created to look like this form. 4.5. Before creating the code that will be attached to the Click event of the btnLoadList command button, you need to devise a support routine to create the connection string. Called BuildCnnStr, the function can been seen in Listing 3.1. This function takes a server and database names that are passed to it and creates a connection string. Listing 3.1 modGeneralRoutines.vb: Creating a Connection String Function BuildCnnStr(ByVal strServer As String, _ ByVal strDatabase As String) As String Dim strTemp As String strTemp = Provider=SQLOleDB; Data Source= & strServer & ; strTemp &= Initial Catalog= & strDatabase & ; strTemp &= Integrated Security=SSPI Return strTemp End Function Although you could create a routine that would pass back a Connection object, a more versatile method is to pass back a string. The reason for this is that for some objects, you are asked for a Connection object, whereas in other objects, you just need a string. You will see BuildCnnStr called in the next step.6. On the btnLoadList Command button, add the following code from Listing 3.2 to the Click event. In this routine, a SQL string is created and stored in the strSQL string, taking Text property of the txtCustLimit text box and adding it to a literal. Then, within a Try-Catch-End-Try block, a new instance of an OleDbCommand object called ocmdCust is created. The routine then follows the steps that are discussed in the Technique section. Listing 3.2 frmHowTo3_1.vb: Loading a List Box By Using the DataReader Object Private Sub btnLoadList_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnLoadList.Click Dim ocmdCust As OleDb.OleDbCommand Dim odrCust As OleDb.OleDbDataReader Dim strSQL As String -- Create the SQL String strSQL = Select CompanyName From Customers Where CustomerID Like & Me.txtCustLimit.Text & % -- Set up the exception catch Try -- Create an instance of the command ocmdCust = New OleDb.OleDbCommand() With ocmdCust -- ...