Retrieve Results from SQL Server by Using the DataTable Object

Kết quả lấy từ SQL Server bằng cách sử dụng các đối tượng DataTable Người đọc dữ liệu là rất tốt khi bạn chỉ muốn tải dữ liệu vào một ListBox ComboBox hoặc kiểm soát bằng tay, nhưng bạn có thể tiết kiệm một số mã hóa bằng cách ràng buộc các điều khiển ListBox để một bảng dữ liệu ở thời gian chạy
Nội dung trích xuất từ tài liệu:
Retrieve Results from SQL Server by Using the DataTable Object 3.2 Retrieve Results from SQL Server by Using the DataTable ObjectThe data reader is great when you just want to load data into a ListBox or ComboBoxcontrol manually, but you can save some coding by binding the ListBox control to a datatable at runtime, as well as providing the ability to get not only the displayed value, butthe key column as well. This How-To demonstrates how to bind a limited ListBoxcontrol to a data table.Although getting the quick information is great, you need to be able to refer back to thetable of information, and you dont want to have to open another connection to get there.You know that the DataTable object should allow you to perform this task. How do youget results from SQL Server by using the DataTable object?TechniqueUsing the Windows Forms controls that were introduced in How-To 3.1, you will use afamiliar object from Chapter 1 called the DataAdapter object. This time, instead of usingthe OleDbDataAdapter data control, you will use the OleDbDataAdapter class from theSystem.Data.OleDb Namespace.Using a similar technique that was used when filling a DataSet, you will instantiate thedata adapter by assigning the SQL string and connection object. Then, instead of filling aDataSet object, you will fill a DataTable object. Because you will only be dealing with atables worth of data, you just need to use a data table. That way, you will be able toperform lookups more conveniently, as shown in the next How-To.For now, the next step will be to assign the following properties of the list box: • DataSource. This will be set to the DataTable object-in this case, dtCust. • DisplayMember. This specifies which column from the data table to use for display in the list box. • ValueMember. Here, you will specify which column you want to use for the value that is retrieved when an item is selected from the list box.By programming the ListBox control using this technique, you can access theValueMember column in the SelectItem property of the list box.StepsOpen and run the VB.NET-Chapter 3 solution. From the main form, click on thecommand button with the caption How-To 3.2. 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.1. To save time, you can make a copy of the form that was created in the first How- To in this chapter.2. Replace the btnLoadList Click event with the following code listed here in Listing 3.3. Thats it. After creating the SQL string that will be used and storing it in strSQL, the data adapter called odaCust is created. The odtCust data table is then filled using odaCust. Last, the DataSource, DisplayMember, and ValueMember properties are set for the lstCustomers list box. This was all accomplished with a Try-Catch-End-Try block of code. Listing 3.3 frmHowTo3_2.vb: Loading a List Box By Using the DataTable Object Private Sub btnLoadList_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoadList.Click Dim odaCust As OleDb.OleDbDataAdapter Dim dtCust As DataTable = New DataTable() Dim strSQL As String -- Create the SQL String strSQL = Select CustomerID, CompanyName From Customers & _ Where CustomerID Like & Me.txtCustLimit.Text & % -- Set up the exception catch Try -- Create an instance of the data adapter, and then fill the data table odaCust = New OleDb.OleDbDataAdapter(strSQL, _ BuildCnnStr((local), Northwind)) odaCust.Fill(dtCust) -- Bind the data to the list box lstCustomers.DataSource = dtCust lstCustomers.DisplayMember = CompanyName lstCustomers.ValueMember = CustomerID Catch oexpData As OleDb.OleDbException MsgBox(oexpData.Message) End Try End SubHow It WorksWhen the user clicks on the btnLoadList button, the data adapter called odaCust isinstantiated. The data adapter is passed strSQL and the connection string that is createdby the function called BuildCnnStr, which was introduced in the first How-To in thischapter. The data table is then filled, and then the DataSource, DisplayMember, andValueMember properties of the ListBox control are assigned.CommentsUsing the data table sets up the scene for using the list box in retrieving data in the nextHow-To. Remember: By using the DataTable object, you can assign both the displayvalue and the data item to be tracked.