18.2 Properties Reference

string connectionString = Connection.ConnectionString;

Returns or sets a string that opens a database connection. The connection string is formatted as a collection of name/value pairs separated by semicolons. These settings can be provider-specific but typically follow certain conventions; they include basics such as the source database name, the location of the database server, and login information. The connection string is case-insensitive, and all blank characters are ignored.

Examples

The first example creates a SqlConnection that accesses a database on the local computer. Some properties of the connection string are set to connect to the Northwind database using integrated security.

SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Data Source = localhost;" +
  "Integrated security = SSPI;Initial Catalog = Northwind";

The following example creates an OleDbConnection. Some properties of the connection string are set to connect to the Northwind database, using integrated security. Note that the information is identical, except for the addition of a Provider setting that specifies what underlying OLE DB provider should be used.

OleDbConnection conn = new OleDbConnection();
Conn.ConnectionString = "Provider = SQLOLEDB;Data Source = localhost;" +
  "Integrated Security = SSPI;Initial Catalog = northwind";

Notes

The ConnectionString property can be set only when the connection is closed. The Connection class provides many read-only properties that correspond to individual pieces of information from the connection string.

Values in the connection string can optionally be delimited by single or double quotes, e.g., name='value' or name="value"). The most common reason to use this approach is when you need to include blank or semicolon characters in a connection string value.

Any variation in a connection string can thwart connection pooling, so be sure to draw connection strings from a fixed location such as a configuration file to ensure consistency, rather than place them directly in code or construct them manually.

When retrieving the ConnectionString property, you will find that the user authentication information is automatically removed, unless you specifically submit the Persist Security Info setting in the connection string with a value of true.

For a full list of connection string settings and examples of various provider-specific connection strings, see Chapter 3.

Int32 timeout = Connection.ConnectionTimeout;

Returns an integer containing the amount of time, in seconds, an attempt to establish a connection is allowed to run before timing out and throwing a provider-specific error such as SqlException or OleDbException.

Examples

This example creates a SqlConnection and stores the ConnectionTimeout property value to an Integer variable:

SqlConnection con = new SqlConnection();
int seconds = con.ConnectionTimeout;

The next example creates an OleDbConnection and stores the ConnectionTimeout property value to an integer variable:

OleDbConnection con = new OleDbConnection();
int seconds = con.ConnectionTimeout;

Note

To configure this setting, use the Connection Timeout parameter in the connection string.

String databaseName = Connection.Database;

Returns the name of the database to be used for all commands (e.g., "Northwind"). If the connection isn't open, it's the name of the database that's initially used.

Note

To configure this setting, use the Initial Catalog parameter in the connection string. To change the database after connecting, use the ChangeDatabase( ) method. This property isn't supported by Oracle databases.

String serverName = Connection.DataSource;

Returns a string that specifies the location of the data source. This is almost always the name of the server running the database software (for example, "ProductionSQLServer"). The value of (local) or localhost designates the current computer, regardless of its computer name.

Note

To configure this setting, use the Data Source parameter in the connection string.

String providerName = Connection.Provider;

Returns the name of the underlying OLE DB provider that this OleDbConnection is using to connect to the data source. This matches the name format used in the connection string. Table 18-2 lists some common examples.

Table 18-2. Commonly used OLE DB drivers

Name	Description
SQLOLEDB	OLE DB provider for SQL Server
MSDAORA	OLE DB provider for Oracle 7.3 and 8
Microsoft.Jet.OLEDB.4.0	OLE DB provider for Access (and other Jet data sources)

Note

To configure this setting, use the Provider parameter in the connection string.

Int32 packetSize = Connection.PacketSize;

Returns the size (in bytes) of the network packets that communicate with SQL Server. This value must fall between 512 and 32767 bytes and defaults to 512. Typically, a packet size of 512 bytes is ideal for small messages. However, if you know in advance that your application will primarily be used to transfer large amounts of data (e.g, it performs bulk copy operations or manipulates large text or image fields), larger packet sizes may improve performance because they require fewer network read and write operations.

Note

To configure this setting, use the Packet Size parameter in the connection string.

String version = Connection.ServerVersion;

Returns a string with version information for the data source. This property is accessible only after a connection has been opened. This is typically in the format MM.mm.rrrr. The first two digits are the major version, the next two digits are the minor version, and the last four digits are the release version. Some providers may append a product-specific version, as in "08.01.0001 R3"

For OLE DB providers, this maps to the OLE DB DBPROP_DBMSVER property. If the OLE DB provider doesn't support this property, an empty string is returned.

ConnectionState state = Connection.State;

Returns the current state of the connection, as a bitwise combination of one or more values from the ConnectionState property. Currently most providers simply return ConnectionState.Open or ConnectionState.Closed and don't use other values such as ConnectionState.Fetching.

Retrieving this property incurs some slight overhead, because it requires a call to the OLE DB DBPROP_CONNECTIONSTATUS property. It is therefore not a good technique to poll this value repeatedly in a tight loop while performing database operations.

String id = Connection.WorkstationId;

Returns a string with the name of the connecting client. By default, this setting returns the computer name of the client computer.

Note

You can configure this setting with the Workstation ID parameter in the connection string. If you set this value (with any value other than an empty string), it takes precedence over the computer name.