Database Access with Visual Basic .NET (3rd Edition)

The Connection Object

The ADO.NET Connection object is very similar to the Connection object that you know and love from classic ADO. Its purpose is straightforward to establish a connection to a specific data source, with a particular user account and password, as specified by a connection string. You can customize the connection by specifying other parameters and values in the connection string. A Command object (or a DataAdapter) can then use this connection to perform desired operations against the data source.

Note

Unlike the ADO 2.X Connection object, the ADO.NET Connection doesn't have Execute or OpenSchema methods. The ability to execute SQL commands is available only through the Command or DataAdapter objects. The functionality of the OpenSchema method is available by means of the GetOleDbSchemaTable method of the OleDbConnection object.

Although the derived objects OleDbConnection, SqlConnection, and OdbcConnection all implement the same interfaces, there are still differences among them. For example, the connection string formats are not the same. The format for the OleDbConnection is designed to match the standard OLEDB connection string format with only minor exceptions. The format for the OdbcConnection is designed to closely match that of a standard ODBC connection string, but it contains some deviations. The connection string format for the SqlConnection is different from both of the others, as it contains only parameters relevant to SQL Server 7.0 and higher.

Furthermore, some objects will add additional properties. For example, the OleDbConnection has a Provider property to specify the OLEDB provider to be used and the OdbcConnection has a Driver property to specify the ODBC driver to be used. The SqlConnection has neither of these properties because the data source type is predetermined (SQL Server). However, the SqlConnection has the PacketSize and WorkstationID properties, which are specific to SQL Server and not supported by the other two types of connections.

Okay, let's finally start writing some code! We lead you through each of the core data provider objects in simple, concrete steps. We start with the following simple example and develop it as we go through the chapter.

1. Launch Visual Studio.NET.

2. Create a new Visual Basic Windows Application project.

3. Name the project DataProviderObjects.

4. Specify a path for where you want the project files to be saved.

5. Enlarge the size of Form1.

6. In the Properties window for Form1, set its Text property to Data Provider Objects.

7. In the upper-left-hand corner of the form, add a button from the Windows Forms tab of the Toolbox.

8. In the Properties window, set the Name property of the button to cmdConnection and set the Text property to Connection.

9. From the Windows Forms tab of the Toolbox, add a textbox to Form1 and place it on the right side of the form.

10. In the Properties window, set the Name property of the textbox to txtResults, the Multiline property to True, and the ScrollBars property to Both.

11. Enlarge the textbox so that it covers about 80 percent of the area of the form.

When you've finished, your form should look something like that shown in Figure 4.2.

Switch to the code view of the form and add the following lines of code at the top of the file. Doing so imports the namespaces you'll use as you develop the sample application throughout this chapter:

Imports System.Data Imports System.Data.SqlClient Imports System.Data.OleDb Imports Microsoft.Data.Odbc

Note the namespace for the generic ADO.NET classes and definitions and the separate namespace for each data provider.

Note

The Visual Studio editor may not recognize the Microsoft.Data.Odbc namespace, as it is actually an add-on to the base product release. If that's the case, do the following.

1. Download the Odbc data provider installation file from the Microsoft Web site and follow the instructions to install it on your computer.

2. In the Solution Explorer, right-click on the References node for the DataProviderObjects project.

3. Select Add Reference from the pop-up menu that is displayed.

4. On the .NET tab of the Add Reference dialog box, scroll through the list of components until you see Microsoft.Data.Odbc.dll.

5. Double-click on the Microsoft.Data.Odbc.dll list item to add it to the Selected Components list at the bottom of the dialog.

6. Click on the OK button to close the dialog box.

If, for some reason, one of the other imported namespaces isn't recognized, you'll need to add a reference to System.Data.dll. Follow steps 2 6, substituting System.Data.dll for Microsoft.Data.Odbc.dll in step 4.

Now add the code shown in Listing 4.1 to the btnConnection to open a connection to the pubs database on SQL Server. This code opens a connection and displays the state of the connection before and after attempting to open the connection.

Listing 4.1 Code to open a database connection and display its state

Private Sub btnConnection_Click (ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles _ btnConnection.Click ' Create an instance of an Connection object Dim cnn As SqlConnection = New SqlConnection() ' Set the connection string cnn.ConnectionString = _ "server=localhost;uid=sa;database=pubs" txtResults.Clear() ' display connection state If (cnn.State = System.Data.ConnectionState.Open) Then txtResults.Text = txtResults.Text & "Connection is Open" Else txtResults.Text = txtResults.Text & "Connection is Closed" End If txtResults.Text = txtResults.Text & ControlChars.CrLf ' Open the Connection txtResults.Text = txtResults.Text & "Opening DB connection . . ." _ & ControlChars.CrLf cnn.Open() ' display connection state If (cnn.State = System.Data.ConnectionState.Open) Then txtResults.Text = txtResults.Text & "Connection is Open" Else txtResults.Text = txtResults.Text & "Connection is Closed" End If txtResults.Text = txtResults.Text & ControlChars.CrLf End Sub

Tip

A useful new feature of VB.NET is the ability to get a text string representation of an enumeration (enum) value automatically, rather than having to write a routine that performs a select-case statement over all the possible values for the enumeration. All enumeration types, which are objects, inherit the ToString method that returns the string corresponding to its current value.

In Listing 4.1, you can replace the If-Else statements that display the connection state with a single line. Thus you can replace the lines

' display connection state If (cnn.State = System.Data.ConnectionState.Open) Then txtResults.Text = txtResults.Text & "Connection is Open" Else txtResults.Text = txtResults.Text & "Connection is Closed" End If with ' display connection state txtResults.Text = txtResults.Text & "Connection is" & _ cnn.State.ToString & ControlChars.CrLf

When you run the DataProviderObjects project and click on the Connection button, the textbox should indicate that the connection is closed, being opened, and then open, as shown in Figure 4.3.

Note

When writing production code, you need to decide on and implement an error handling strategy for most routines and operations. This strategy should normally be based on the Try-Catch block error handling structure. We don't normally include this code in our examples because our purpose is to focus on database programming concepts, rather than general practices for programming in VB.NET.