System.Data.OleDb Namespace   OleDbConnection Class

Sets or retrieves the string used to open a database.

The property is read/write with no default value.

The ConnectionString is designed to match OLE DB connection string formats as closely as possble, with the following exception: the "Provider=value" clause is required. However, you cannot use "Provider=MSDASQL" because the OLE DB .NET Data Provider does not support the OLE DB Provider for ODBC ( MSDASQL ).

NOTE: For access to ODBC data sources, an ODBC .NET Data Provider is available as a separate download at MSDN.

Unlike OLE DB or ADO, the connection string that is returned is the same as the user-set ConnectionString minus security information if Persist Security Info value is set to false ( default ). The OLE DB .NET Data Provider does not persist or return the password in a connection string unless you set Persist Security Info to true.

The 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, all of these properties are updated, except when an error is detected. In this case, none of the properties are updated. OleDbConnection properties return only those settings contained in the ConnectionString.

Resetting the ConnectionString on a closed connection resets all connection string values ( and related properties ) including the password. For example, if you set a connection string that includes

"Initial Catalog=northwind"

and then reset the connection string to

"DataSource=myserver; Password=yervzeñun"

the Database property is no longer set to northwind.

The connection string is parsed immediately after being set. If errors in syntax are found when parsing, an OleDbException is generated. Other errors, such as invalid or unsupported properties, can be found only when an attempt is made to Open the connection at run time.

• Values may be delimited by single or double quotes, ( for example, name='value' or name="value" ). Either single or double quotes may be used within a connection string by using the other delimiter, for example, name="value's" or name='value"s',but not name='value's' or name=""value"". The value type is irrelevant.
• All blank characters, except those placed within a value or within quotes, are ignored.
• Keyword value pairs must be separated by a semicolon ( ; ). If a semicolon is part of a value, it also must be delimited by quotes.
• Names are not case sensitive. If a given name occurs more than once in the connection string, the value associated with the last occurence is used.
• No escape sequences are supported.

You can use the ConnectionString property to connect to a variety of data sources. The following example illustrates several possible providers to use when setting the OLE DB connection string.

Provider=MSDAORA; Data Source=ORACLE8i7; User ID=OLEDB; Password=OLEDB
Provider=SQLOLEDB; Data Source=myServer; Integrated Security=SSPI;
Provider=Microsoft.ACE.OLEDB.12.0; Data Source=myData.mdb;

For the Jet Provider, if the data source does not reside in the same directory as the calling page, the complete path from the application root to the data source must be specified using the Server.MapPath property in the connection string. For example:

OleDbConnection myConn = new OleDbConnection (
   "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + 
   Server.MapPath ( "~/app_data/dbtutor.mdb" ) );

NOTE: The connection string MUST NOT contain any spaces, either before or after the equal ( = ) signs within the string, and must be written as a single line without any line breaks. The code examples shown here are done so only for readability.

While it is possible to include a reference to a Microsoft Data Link ( UDL ) in a connection string, it is not recommended as this can affect performance. Connection strings that contain UDLs are parsed each time the OleDbConnection is opened.

CAUTION: In this release, the application should use caution when constructing a connection string based on user input ( for example when retrieving user ID and password information from a dialog box, and appending it to the connection string ). The application should ensure that a user cannot embed extra connection string parameters in these values ( for example, entering a password as "validpassword;database=somedb" in an attempt to attach to a different database ).

The following example instantiates an OleDbConnection, sets some of its parameters in the connection string, and opens the connection.

OleDbConnection myConn = new OleDbConnection ( );
myConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=myData.mdb";
myConn.Open ( );

Dim myConn As New OleDbConnection ( )
myConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=myData.mdb"
myConn.Open ( )

OleDbConnection Members   Provider   Data Source   Connecting to a Database