OleDbConnection.ConnectionString Property

Definition

Namespace:

System.Data.OleDb

Assembly:

System.Data.OleDb.dll

Package:

System.Data.OleDb v9.0.0-rc.2.24473.5

Gets or sets the string used to open a database.

public:
 virtual property System::String ^ ConnectionString { System::String ^ get(); void set(System::String ^ value); };

[System.ComponentModel.SettingsBindable(true)]
public override string ConnectionString { get; set; }

[<System.ComponentModel.SettingsBindable(true)>]
member this.ConnectionString : string with get, set

Public Overrides Property ConnectionString As String

Property Value

String

The OLE DB provider connection string that includes the data source name, and other parameters needed to establish the initial connection. The default value is an empty string.

Implements

ConnectionString

Attributes

SettingsBindableAttribute

Exceptions

ArgumentException

An invalid connection string argument has been supplied or a required connection string argument has not been supplied.

Examples

The following example creates an OleDbConnection and sets some of its properties in the connection string.

static void OpenConnection(string connectionString)
{
    using (OleDbConnection connection = new OleDbConnection(connectionString))
    {
        try
        {
            connection.Open();
            Console.WriteLine("ServerVersion: {0} \nDataSource: {1}",
                connection.ServerVersion, connection.DataSource);
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
        // The connection is automatically closed when the
        // code exits the using block.
    }
}

Public Sub OpenConnection(ByVal connectionString As String)

    Using connection As New OleDbConnection(connectionString)
        Try
            connection.Open()
            Console.WriteLine("Server Version: {0} DataSource: {1}", _
                connection.ServerVersion, connection.DataSource)
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try
        ' The connection is automatically closed when the
        ' code exits the Using block.
    End Using
End Sub

Remarks

The 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 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 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 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 Using Connection String Keywords with SQL Server Native Client.

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, 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 that are contained in the ConnectionString.

Resetting the 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 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 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.

Caution

It is possible to supply connection information for an OleDbConnection in a Universal Data Link (UDL) file; however you should avoid doing so. UDL files are not encrypted and expose connection string information in clear text. Because a UDL file is an external file-based resource to your application, it cannot be secured using the .NET Framework.

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.

Caution

You 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 make sure that a user cannot embed additional connection-string parameters in these values, for example, entering a password as "validpassword;database= somedb" in an attempt to attach to a different database. If you use the Extended Properties connection string parameter for OLE DB connections, avoid passing user IDs and passwords because you should avoid storing user IDs and passwords in clear text if you can, and because the default setting of Persist Security Info= false does not affect the Extended Properties parameter.

See also

• Connecting to Data Sources