CONNECTION OBJECTS

The connection object manages the application’s connection to the database. It allows a data adapter to move data in and out of a DataSet.

The different flavors of connection object (OleDbConnection, SqlConnection, OdbcConnection, OracleConnection, and so on) provide roughly the same features, but there are some differences. Check the online help to see if a particular property, method, or event is supported by one of the flavors. The web page http://msdn.microsoft.com/32c5dh3b.aspx provides links to pages that explain how to connect to SQL Server, OLE DB, ODBC, and Oracle data sources. Other links lead to information on the SqlConnection, OleDbConnection, and OdbcConnection classes.

If you will be working extensively with a particular type of database (for example, SQL Server), you should also review the features provided by its type of connection object to see if it has special features for that type of database.

Some connection objects can work with more than one type of database. For example, the OleDbConnection object works with any database that has an OLE DB (Object Linking and Embedding Database) provider. Similarly the OdbcConnection object works with databases that have ODBC (Open Database Connectivity) providers such as MySQL.

Generally, connections that work with a specific kind of database (such as SqlConnection and OracleConnection) give better performance. If you think you might later need to change databases, you can minimize the amount of work required by sticking to features that are shared by all the types of connection objects.

The following table describes the most useful properties provided by the OleDbConnection and SqlConnection classes.

The ConnectionString property includes many fields separated by semicolons. The following text shows a typical ConnectionString value for an OleDbConnection object that will open an Access database. The text here shows each embedded field on a separate line, but the actual string would be all run together in one long line.

Jet OLEDB:Global Partial Bulk Ops=2;
Jet OLEDB:Registry Path=;
Jet OLEDB:Database Locking Mode=1;
Data Source="C:PersonnelDataPersonnel.mdb";
Mode=Share Deny None;
Jet OLEDB:Engine Type=5;
Provider="Microsoft.Jet.OLEDB.4.0";
Jet OLEDB:System database=;
Jet OLEDB:SFP=False;
persist security info=False;
Extended Properties=;
Jet OLEDB:Compact Without Replica Repair=False;
Jet OLEDB:Encrypt Database=False;
Jet OLEDB:Create System Database=False;
Jet OLEDB:Don't Copy Locale on Compact=False;
User ID=Admin;
Jet OLEDB:Global Bulk Transactions=1"

Many of these properties are optional and you can omit them. Remembering which ones are optional (or even which fields are allowed for a particular type of connection object) is not always easy. Fortunately, it’s also not necessary. Instead of typing all these fields into your code or in the connection control’s ConnectString property in the Properties window, you can let Visual Basic build the string for you.

Simply follow the steps described in the section “Connecting to the Data Source” earlier in this chapter. After you build or select the database connection, look at the connection string at the bottom of the dialog box shown in Figure 19-2. Use the mouse to highlight the connection string and then press Ctrl+C to copy it to the clipboard.

The following code fragment shows how a program can create, open, use, and close an OleDbConnection object. The code assumes the database name is in the text box txtDatabase.

' Make the connect string.
Dim connect_string As String =
    "Provider=Microsoft.Jet.OLEDB.4.0;" &
    "Data Source=""" & txtDatabase.Text & """;" &
    "Persist Security Info=False"
 
' Open a database connection.
Using conn_people As New OleDb.OleDbConnection(connect_string)
    conn_people.Open()
 
    ' Do stuff with the connection.
    '...
 
    ' Close the connection.
    conn_people.Close()
End Using

Example program CommandInsert, which is available for download on the book’s website, uses similar code to open a connection before inserting new data into the database.

The following table describes the most useful methods provided by the OleDbConnection and SqlConnection classes.

The connection object’s most useful events are InfoMessage and StateChange. The InfoMessage event occurs when the database provider issues a warning or informational message. The program can read the message and take action or display it to the user. The StateChange event occurs when the database connection’s state changes.

Note that you don’t need to open and close a connection directly when you use a data adapter’s Fill and Update methods. Fill and Update automatically open the connection, perform their tasks, and then close the connection so that you don’t need to manage the connection object yourself.