Using Connection Strings

Using Connection Strings

You can define the behavior of a connection using a connection string or the properties of the PsqlConnection object.

However, values set in the connection string cannot be changed by the connection properties.

The basic format of a connection string includes a series of keyword/value pairs separated by semicolons. The following example shows the keywords and values for a simple connection string for the PSQL data provider:

"Server DSN=SERVERDEMO;Host=localhost"

Use the following guidelines when specifying a connection string:

•

The spaces in the connection string option names are required.

•

All connection string option names are case-insensitive. For example, Password is the same as password. However, the values of options such as User ID and Password may be case-sensitive.

•

To include values that contain a semicolon, single quote, or double quotes, enclose the value in double quotes. If the value contains both a semicolon and double quotes, use single quotes to enclose the value.

•

You can also use single quotes when the value starts with a double quote. Conversely, double quotes can be used if the value starts with a single quote. If the value contains both single quotes and double quotes, the character used to enclose the value must be doubled every time it occurs within the value.

•

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

•

Special characters can be used in the value of the connection string option. To escape special characters, surround the value in single or double quotes.

•

The Equals character (=) can also be repeated within the connection string. For example:

Initialization String=update mytable set col1 == 'foo'"

•

If the connection string contains invalid connection string options, the connection attempt returns an error. For example, an error is returned if you specify a value for Load Balancing when Alternate Servers has not been defined.

•

If the connection string contains duplicated connection string options, the data provider uses the connection string option that appears last in the connection string. For example, Connection Timeout appears twice in the following connection string, with different values. The data provider uses the second value and waits 35 seconds before terminating an attempted connection:

"Server DSN=SERVERDEMO;Host=localhost;Connection Timeout=15;Min Pool Size=50;Connection Timeout=35"

See Table 27 for a list of the supported connection string options.

Using the PSQL Performance Tuning Wizard

You can use the Performance Wizard to select the optimal connection string options for both the ADO.NET data provider or the ADO.NET Entity Framework data provider.

See Using the PSQL Performance Tuning Wizard for more information.