Using the Connection and Command Design-Time Components

Using the Connection and Command Design-Time Components

In the Visual Studio Toolbox, the Data tab contains components that mirror some of the data access components. They allow you to set property values via with the properties window at design time, rather than doing everything in code at run-time. They also provide visual tools for setting some of the more complex properties.

You can implement Listing 4.8 by using some of these components, as follows.

1. Add another form, Form2, to the DataProviderObjects project.

2. In the Properties window for Form2, set its Text property to the Connection and Command Components.

3. Enlarge the size of Form2.

4. Add a textbox, Textbox1, to Form2.

5. In the Properties window, set the textbox Multiline property to True, and the ScrollBars property to Both.

6. Enlarge Textbox1 to cover most of Form2.

7. From the Data tab of the toolbox, drag an OledbConnection component onto the design surface of Form2. As this component isn't visible at run-time, it will appear in the component tray beneath the form's design surface.

8. In the Properties window for this new OledbConnection component, named Oledb-Connection1, set the ConnectionString property to

    provider=SQLOLEDB;server=localhost;uid=sa;database=pubs  

9. From the Data tab of the toolbox, drag an OledbCommand component onto the design surface of Form2. As this component isn't visible at run-time, it too will appear in the component tray beneath the form's design surface.

10. In the Properties window for this new OledbCommand component, OledbCommand1, set the Connection property to the SqlConnection1 component and set the CommandText property to

     select * from EmployeeJobs_view  

11. Add the code for the Form2_Load event handler, as shown in Listing 4.9.

    Tip

    In Chapter 6 we present tools that enable you to prepare graphically the connection string and command text, instead of having to enter the actual text strings as here.

    Listing 4.9 Code to create a DataReader and retrieve field values with SqlConnection and SqlCommand components

     Private Sub Form2_Load(ByVal sender As System.Object, _      ByVal e As System.EventArgs) Handles MyBase.Load      'Must open the connection before calling ExecuteReader      OledbConnection1.Open()      Dim reader = OledbCommand1.ExecuteReader()      TextBox1.Clear()      While reader.Read()        TextBox1.Text = TextBox1.Text & reader("fname") & _          ControlChars.Tab &_reader("lname") & _          ControlChars.Tab & ControlChars.Tab & _          reader("job_desc") & ControlChars.CrLf      End While      'Deselect all lines in textbox      TextBox1.SelectionLength = 0      reader.Close()        OledbConnection1.Close() End Sub 

12. Right-click on the DataProviderObjects project in the Solution Explorer and select Properties from the pop-up menu displayed.

13. Select the General item in the Common Properties folder and then set the Startup object property to Form2.

If you now run the DataProviderObjects project, the textbox on Form2 will display all the data from the EmployeeJobs_view, as shown in Figure 4.5.

Figure 4.5. Results of displaying Form2, which utilizes the OledbConnection and OledbCommand components