Connection Object Overview

The Connection object, like all provider-specific ADO.NET objects, comes in more than one version. You use the version that’s tailored for your specific data source. Here are two examples:

• System.Data.SqlClient.SqlConnection allows you to connect to a SQL Server database (Version 7.0 or later).

• System.Data.OleDb.OleDbConnection allows you to connect to almost any data source with an associated OLE DB provider.

Every Connection object that accesses relational databases implements the common System.Data.IDbConnection interface. By looking at the IDbConnection interface, you’ll quickly see the small set of properties and methods that every Connection object is guaranteed to support (see Tables 3-1 and 3-2). The most important of these are the Close( ) and Open( ) methods, and the ConnectionString property, which specifies a variety of options about the data source and how to connect to it. All IDbConnection properties are read-only, except ConnectionString.

Table 3-1. IDbConnection properties

Member	Description
ConnectionString	A string with name-value pairs of connection settings. These settings often include information such as the user to log in and the location of the database server. This is the only writeable property.
ConnectionTimeout	The time to wait for a connection to open before failing with a provider-specific exception (such as SqlException or OleDbException). The default is 15 seconds; 0 waits indefinitely. This value must be set through the connection string; the property is read-only.
Database	The name of the database to use once the connection is open. This can be set in the connection string and changed with the ChangeDatabase( ) method. Oracle databases don’t support this property.
State	A bitwise connection of values from the ConnectionState enumeration. Currently, only Open and Closed are supported, and information isn’t provided about whether the connection is currently retrieving data or executing a query.

Table 3-2. IDbConnection methods

Member	Description
BeginTransaction( )	Programmatically starts a database transaction. Database transactions are detailed in Chapter 16.
ChangeDatabase( )	Sets a new database to be used for subsequent operations. Alternatively, you can execute the SQL USE command with SQL Server. Oracle databases don’t support this method.
CreateCommand( )	Returns a provider-specific IDbCommand object that is set to use this connection. This method is primarily useful when writing provider-agnostic code.
Open() and Close( )	Attempts to connect to or disconnect from the data source.

Other providers add additional members. For example, most Connection objects add the ServerVersion property (which contains a string with version information for the database product) and two events: StateChange (which fires when the connection is opened or closed) and InfoMessage (which fires when warning or error messages are received). SqlConnection also adds a WorkstationId and PacketSize property with additional information. For complete information about provider-specific members, refer to Part III.