The OleDbConnection.ConnectionString is designed to match OLE DB connection string format as closely as possible with the following exceptions:

• The "Provider = value " clause is required. However, you cannot use "Provider = MSDASQL" because the .NET Framework Data Provider for OLE DB does not support the OLE DB Provider for ODBC (MSDASQL). To access ODBC data sources, use the System.Data.Odbc.OdbcConnection object that is in the System.Data.Odbc namespace.

• Unlike ODBC or ADO, the connection string that is returned is the same as the user-set OleDbConnection.ConnectionString, minus security information if Persist Security Info is set to false (default). The .NET Framework Data Provider for OLE DB does not persist or return the password in a connection string unless you set the Persist Security Info keyword to true (not recommended). To maintain a high level of security, it is strongly recommended that you use the Integrated Security keyword with Persist Security Info set to false.

You can use the OleDbConnection.ConnectionString property to connect to a variety of data sources. The following example illustrates several possible connection strings.

"Provider=MSDAORA; Data Source=ORACLE8i7;Persist Security Info=False;Integrated Security=Yes"

"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\bin\LocalAccess40.mdb"

"Provider=SQLOLEDB;Data Source=(local);Integrated Security=SSPI"

If the Data Source keyword is not specified in the connection string, the provider will try to connect to the local server if one is available.

For more information about connection strings, see tp://go.microsoft.com/fwlink/?LinkId=126696.

The OleDbConnection.ConnectionString property can be set only when the connection is closed. Many of the connection string values have corresponding read-only properties. When the connection string is set, these properties are updated, except when an error is detected. In this case, none of the properties are updated. System.Data.OleDb.OleDbConnection properties return only those settings that are contained in the OleDbConnection.ConnectionString.

Resetting the OleDbConnection.ConnectionString on a closed connection resets all connection string values and related properties. This includes the password. For example, if you set a connection string that includes "Initial Catalog= AdventureWorks", and then reset the connection string to "Provider= SQLOLEDB;Data Source= MySQLServer;IntegratedSecurity=SSPI", the OleDbConnection.Database property is no longer set to AdventureWorks. (The Initial Catalog value of the connection string corresponds to the Database property.)

A preliminary validation of the connection string is performed when the property is set. If values for the Provider, Connect Timeout, Persist Security Info, or OLE DB Services are included in the string, these values are checked. When an application calls the OleDbConnection.Open method, the connection string is fully validated. If the connection string contains invalid or unsupported properties, a run-time exception, such as ArgumentException, is generated.

The basic format of a connection string includes a series of keyword/value pairs separated by semicolons. The equal sign (=) connects each keyword and its value. To include values that contain a semicolon, single-quote character, or double-quote character, the value must be enclosed in double quotation marks. If the value contains both a semicolon and a double-quote character, the value can be enclosed in single quotation marks. The single quotation mark is also useful if the value starts with a double-quote character. Conversely, the double quotation mark can be used if the value starts with a single quotation mark. If the value contains both single-quote and double-quote characters, the quotation-mark character used to enclose the value must be doubled every time it occurs within the value.

To include preceding or trailing spaces in the string value, the value must be enclosed in either single quotation marks or double quotation marks. Any leading or trailing spaces around integer, Boolean, or enumerated values are ignored, even if enclosed in quotation marks. However, spaces within a string literal keyword or value are preserved. Single or double quotation marks may be used within a connection string without using delimiters (for example, Data Source= my'Server or Data Source= my"Server) unless a quotation-mark character is the first or last character in the value.

To include an equal sign (=) in a keyword or value, it must be preceded by another equal sign. For example, in the hypothetical connection string

"key==word=value"

the keyword is "key=word" and the value is "value".

If a specific keyword in a keyword=value pair occurs multiple times in a connection string, the last occurrence listed is used in the value set.

Keywords are not case sensitive.