Limit the Data Displayed in a Bound List Box

Giới hạn hiển thị dữ liệu trong một Box Bound Danh sách Ngay cả populating một hộp danh sách với một vài cột từ một bảng đầy đủ các dữ liệu có thể là một hiệu suất lớn hit. Điều này thế nào-Để cho bạn thấy làm thế nào để tạo ra một tuyên bố tham số SQL để giới hạn các mục được hiển thị trong danh sách hộp
Nội dung trích xuất từ tài liệu:
Limit the Data Displayed in a Bound List Box 1.2 Limit the Data Displayed in a Bound List BoxEven populating a list box with a couple of columns from a table full of data can be a bigperformance hit. This How-To shows you how to create a parameterized SQL statementto limit the items that are displayed in the list box, thus giving you better performance onyour forms.You have hundreds of thousands of customers in your database, and you dont want thelist box loaded up with the whole customer table. How can you limit the data that isdisplayed in your list box?TechniqueYou are going to make a copy of the form that you created in How-To 1.1. You will thenadd a Label and TextBox control that the Select statement contained within theOleDbDataAdapter control will query against to limit the data displayed in the list box. Acommand button will be added to allow you to call the Fill method of theOleDbDataAdapter control whenever you update the text box, and then you can click thecommand button (see Figure 1.6). Figure 1.6. You can now limit the amount of data loaded into the list box.StepsTo get started with this How-To, right-click the form you created in How-To 1.1, whichshould be listed in the Solutions Explorer. Choose Copy from the pop-up menu. Next,right-click the project in the Solution Explorer, and choose Paste from the pop-up menu.You will now have a new Class object in the Solutions Explorer called Copy Of whateverthe previous name of the form was. Rename the new form that you have created to thename you desire. Then, with that form highlighted, click on the Code button above theSolutions Explorer. Change the first line of code to say this:Public Class You see, VS does not change the line of code automatically for you. It thinks you have aduplicate Class definition.Now you can see that the icon of the form is correct. You can continue with the steps ofthe How-To. 1. Select the Data Adapter that you created. In the Properties pane, you will see the CommandText property when you click on the SelectCommand property plus sign. Replace the CommandText property with the following command: 2. SELECT CustomerID, CompanyName FROM Customers WHERE (CompanyName LIKE ? + %) You will learn more about the Select statement in Chapter 3. However, the WHERE clause used here compares CompanyName to a parameter that will be supplied, as indicated by the ?. This will be performed using code in the final step of this How-To. The % is a wildcard that tells the server to make it a fuzzy search. 3. Resize the ListBox control, and leave room at the top of the form for the Label, TextBox, and Command button. Create these three controls, setting the properties described in Table 1.3. Table 1.3. Label, TextBox, and Command Button Control Property Settings Object Property Setting Label Text Customer TextBox Name txtCustLimit Text A Command Button Name btnLoadList Text Load List 4. Double-click the new command button you just created called btnLoadList. Enter the code in Listing 1.2 in the Click event of the btnLoadList button. This code loads the data entered from txtCustLimit into the parameter of the OleDBDataAdapter1, which was created by using the ? in the Select statement of the data adapter. Then Dataset1 is cleared of its data with the Clear method. Finally, DataSet1 is refilled with data based off the value in txtCustLimit, using the data adapter. Listing 1.2 frmHowTo1_2.vb: Submitting a Parameter to a DataAdapter and Filling the Dataset Private Sub btnLoadList_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnLoadList.Click Me.OleDbDataAdapter1.SelectCommand.Parameters(0).Value = _ Me.txtCustLimit.Text Me.DataSet1.Clear() Me.OleDbDataAdapter1.Fill(Me.DataSet1) End Sub Note There is one big difference here between an OleDbDataAdapter and a SqlDataAdapter. Whereas the OleDbDataAdapter takes a ? to specify a parameter within the Select statement, the SqlDataAdapter requires a named parameter such as @parCustLimit. Therefore, instead of the select statement in step 1 being this: SELECT CustomerID, CompanyName FROM Customers WHERE (CompanyName LIKE ? + %) It would be this: SELECT CustomerID, CompanyName FROM Customers ...