SQL in a Nutshell, 2nd Edition

Opening an ADO.NET Database Connection

Opening a connection with ADO.NET requires instantiating a connection object with a properly formatted connection string and then invoking the Open method on the connection object. The connection object can be an OdbcConnection, SqlConnection, or an OleDbConnection. The OdbcConnection is designed for any ODBC datasource, and the OleDbConnection type will work with any OLE DB Provider. For the highest performance data access, use connection objects specifically tuned to the specific database platform, such as SqlConnection for Microsoft SQL Server. Following is the syntax for creating a Connection object in ADO.NET:

{Odbc|OleDb|Sql}Connection connection = 
   new {Odbc|OleDb|Sql}Connection(connection_string);
connection.Open( );

The format of the connection string is the same for all of the connection types. The format is a string of key/value pairs delimited by semicolons. For example:

key1=value1; key2=value2; key3=value3; ...

While the format is the same for every connection type, the keys and values are quite different. Tables Table 5-1 through Table 5-3 list the attributes for the three connection types listed above. Many database platforms support additional attributes that can also be set through the connection string. For a list of these attributes, please consult the appropriate database vendor documentation.

Following are examples of two connection strings for an OdbcConnection:

DSN=MyOracleDSN; UID=scott; PWD=tiger; DRIVER={SQL Server};SERVER=(local);UID=sa;PWD=;DATABASE=pubs; ...