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/library/en-us/cpguide/html/cpconADONETConnections.asp 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), 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 supports ODBC connections. 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.

Important 

The Toolbox window does not automatically display tools for these objects. To add them, right-click the Toolbox tab where you want them and select Choose Items. Select the check boxes next to the tools you want to add (for example, OracleCommand or OdbcConnection) and click OK.

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

Open table as spreadsheet

Property

Purpose

ConnectionString

The string that defines the connection to the database.

ConnectionTimeout

The time the object waits while trying to connect to the database. If this timeout expires, the object gives up and raises an error.

Database

Returns the name of the current database.

DataSource

Returns the name of the current database file or database server.

Provider

(OleDbConnection only) Returns the name of the OLE DB database provider (for example, Microsoft.Jet.OLEDB.4.0).

ServerVersion

Returns the database server’s version number. This value is available only when the connection is open and may look like 04.00.0000.

State

Returns the connection’s current state. This value can be Closed, Connecting, Open, Executing (executing a command), Fetching (fetching data), and Broken (the connection was open but then broke; you can close and reopen the connection).

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:\Personnel\Data\Personnel.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" 

Tip 

Note that the data source value will be different on your system. In this example, the database is at C:\Personnel\Data\Personnel.mdb. You would need to change it to match the location of the data on your system.

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.

Open the Data Sources window shown in Figure 11-20. If you can’t find the Data Sources window, select the Data menu’s Show Data Sources command.

image from book
Figure 11-20: You can use the Data Sources window to generate a connection string.

In the Data Sources window, click the Add New Data Source button in the upper left to open the Data Source Configuration Wizard. Select the Database data source type and click Next to see the wizard’s step shown in Figure 11-21.

image from book
Figure 11-21: The Data Source Configuration Wizard can show you a database’s connection string.

Select a connection from the drop-down list, or click the New Connection button to connect to a new database. If you want to include the database password in the connection string, select the Yes, include sensitive data in the connection string option. Click the plus sign to the left of the Connection string area to show the connection string, as shown in Figure 11-21.

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:

  ' To use this Imports statement, add a reference to System.Data. Imports System.Data.OleDb Public Class Form1     Private Sub Form1_Load(ByVal sender As System.Object, _      ByVal e As System.EventArgs) Handles MyBase.Load         ' Create the connection.         Dim conn As New OleDbConnection( _   "Provider=Microsoft.Jet.OLEDB.4.0;" & _   "Data Source=""C:\VB Prog Ref\CeSrc\Ch11\MakeConnection\ClassRecords.mdb"";" & _   "Persist Security Info=True;" & _   "Jet OLEDB:Database Password=MyPassword")         ' Open the connection.         conn.Open()         ' Do stuff with the connection.         '...         ' Close the connection.         conn.Close()         conn.Dispose()     End Sub End Class  

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

Open table as spreadsheet

Method

Purpose

BeginTransaction

Begins a database transaction and returns a transaction object representing it. A transaction lets the program ensure that a series of commands are either all performed or all canceled as a group. See the section “Transaction Objects” later in this chapter for more information.

ChangeDatabase

Changes the currently open database.

Close

Closes the database connection.

CreateCommand

Creates a command object that can perform some action on the database. The action might select records, create a table, update a record, and so forth.

Open

Opens the connection using the values specified in the ConnectionString property.

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 the method for using a connection object shown in Figure 10-18 relies on the data adapter’s Fill and Update methods, not on the connection object’s Open and Close 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. For example, when the program calls Fill, the data adapter quickly opens the connection, copies data from the database into the DataSet, and then closes the database. When you use this model for database interaction, the data connections are open only very briefly.




Visual Basic 2005 with  .NET 3.0 Programmer's Reference
Visual Basic 2005 with .NET 3.0 Programmer's Reference
ISBN: 470137053
EAN: N/A
Year: 2007
Pages: 417

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net