Export (0) Print
Expand All

SqlConnection.ConnectionString Property

Gets or sets the string used to open a SQL Server database.

[Visual Basic]
Public Overridable Property ConnectionString As String  Implements _
   IDbConnection.ConnectionString
[C#]
public virtual string ConnectionString {get; set;}
[C++]
public: __property virtual String* get_ConnectionString();
public: __property virtual void set_ConnectionString(String*);
[JScript]
public function get ConnectionString() : String;
public function set ConnectionString(String);

Property Value

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

Implements

IDbConnection.ConnectionString

Exceptions

Exception Type Condition
ArgumentException An invalid connection string argument has been supplied, or a required connection string argument has not been supplied.

Remarks

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

You can use the ConnectionString property to connect to a database. The following example illustrates a typical connection string.

"Persist Security Info=False;Integrated Security=SSPI;database=northwind;server=mySQLServer"

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. SqlConnection properties return only those settings contained in the ConnectionString.

To connect to a local machine, specify "(local)" for the server. (You must always specify a server.)

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 "Database= northwind", and then reset the connection string to "Data Source=myserver;Integrated Security=SSPI", 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, a runtime exception, such as ArgumentException, is generated. Other errors can be found only when an attempt is made to open the connection.

The basic format of a connection string consists of 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 quotes. If the value contains both a semicolon and a double-quote character, the value can be enclosed in single quotes. The single quote is also useful if the value begins with a double-quote character. Conversely, the double quote can be used if the value begins with a single quote. If the value contains both single-quote and double-quote characters, the quote character used to enclose the value must be doubled each time it occurs within the value.

To include preceding 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 quotes. However, spaces within a string literal keyword or value are preserved. Using .NET Framework version 1.1, single or double quotes may be used within a connection string without using delimiters (for example, Data Source= my'Server or Data Source= my"Server), unless a quote 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.

The following table lists the valid names for keyword values within the ConnectionString.

Name Default Description
Application Name   The name of the application, or '.Net SqlClient Data Provider' if no application name is provided.
AttachDBFilename

-or-

extended properties

-or-

Initial File Name

  The name of the primary file, including the full path name, of an attachable database.

The database name must be specified with the keyword 'database'.

Connect Timeout

-or-

Connection Timeout

15 The length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error.
Current Language   The SQL Server Language record name.
Data Source

-or-

Server

-or-

Address

-or-

Addr

-or-

Network Address

  The name or network address of the instance of SQL Server to which to connect.
Encrypt 'false' When true, SQL Server uses SSL encryption for all data sent between the client and server if the server has a certificate installed. Recognized values are true, false, yes, and no.
Initial Catalog

-or-

Database

  The name of the database.
Integrated Security

-or-

Trusted_Connection

'false' When false, User ID and Password are specified in the connection. When true, the current Windows account credentials are used for authentication.

Recognized values are true, false, yes, no, and sspi (strongly recommended), which is equivalent to true.

Network Library

-or-

Net

'dbmssocn' The network library used to establish a connection to an instance of SQL Server. Supported values include dbnmpntw (Named Pipes), dbmsrpcn (Multiprotocol), dbmsadsn (Apple Talk), dbmsgnet (VIA), dbmslpcn (Shared Memory) and dbmsspxn (IPX/SPX), and dbmssocn (TCP/IP).

The corresponding network DLL must be installed on the system to which you connect. If you do not specify a network and you use a local server (for example, "." or "(local)"), shared memory is used.

Packet Size 8192 Size in bytes of the network packets used to communicate with an instance of SQL Server.
Password

-or-

Pwd

  The password for the SQL Server account logging on (Not recommended. To maintain a high level of security, it is strongly recommended that you use the Integrated Security or Trusted_Connection keyword instead.).
Persist Security Info 'false' When set to false or no (strongly recommended), security-sensitive information, such as the password, is not returned as part of the connection if the connection is open or has ever been in an open state. Resetting the connection string resets all connection string values including the password. Recognized values are true, false, yes, and no.
User ID   The SQL Server login account (Not recommended. To maintain a high level of security, it is strongly recommended that you use the Integrated Security or Trusted_Connection keyword instead.).
Workstation ID the local computer name The name of the workstation connecting to SQL Server.

The following table lists the valid names for connection pooling values within the ConnectionString. For more information about connection pooling, see Connection Pooling for the .NET Framework Data Provider for SQL Server.

Name Default Description
Connection Lifetime 0 When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specified by Connection Lifetime. This is useful in clustered configurations to force load balancing between a running server and a server just brought online.

A value of zero (0) causes pooled connections to have the maximum connection timeout.

Connection Reset 'true' Determines whether the database connection is reset when being drawn from the pool. For Microsoft SQL Server version 7.0, setting to false avoids making an additional server round trip when obtaining a connection, but you must be aware that the connection state, such as database context, is not being reset.
Enlist 'true' When true, the pooler automatically enlists the connection in the creation thread's current transaction context. Recognized values are true, false, yes, and no.
Max Pool Size 100 The maximum number of connections allowed in the pool.
Min Pool Size 0 The minimum number of connections allowed in the pool.
Pooling 'true' When true, the SQLConnection object is drawn from the appropriate pool, or if necessary, is created and added to the appropriate pool. Recognized values are true, false, yes, and no.

When setting keyword or connection pooling values that require a Boolean value, you can use 'yes' instead of 'true', and 'no' instead of 'false'. Integer values are represented as strings.

Note   The .NET Framework Data Provider for SQL Server uses its own protocol to communicate with SQL Server. Therefore, it does not support the use of an ODBC data source name (DSN) when connecting to SQL Server because it does not add an ODBC layer.
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).

Example

[Visual Basic, C#, C++] The following example creates a SqlConnection and sets some of its properties.

[Visual Basic] 
Public Sub CreateSqlConnection()
    Dim myConnection As New SqlConnection()
    myConnection.ConnectionString = "Persist Security Info=False;Integrated Security=SSPI;database=northwind;server=mySQLServer;Connect Timeout=30"
    myConnection.Open()
End Sub 'CreateSqlConnection

[C#] 
public void CreateSqlConnection() 
 {
    SqlConnection myConnection = new SqlConnection();
    myConnection.ConnectionString = "Persist Security Info=False;Integrated Security=SSPI;database=northwind;server=mySQLServer;Connect Timeout=30";
    myConnection.Open();
 }

[C++] 
public:
void CreateSqlConnection() 
 {
    SqlConnection* myConnection = new SqlConnection();
    myConnection->ConnectionString = S"Persist Security Info=False;Integrated Security=SSPI;database=northwind;server=mySQLServer;Connect Timeout=30";
    myConnection->Open();
 }

[JScript] No example is available for JScript. To view a Visual Basic, C#, or C++ example, click the Language Filter button Language Filter in the upper-left corner of the page.

Requirements

Platforms: Windows 98, Windows NT 4.0, Windows Millennium Edition, Windows 2000, Windows XP Home Edition, Windows XP Professional, Windows Server 2003 family, .NET Compact Framework

See Also

SqlConnection Class | SqlConnection Members | System.Data.SqlClient Namespace | ConnectionTimeout | Database | DataSource

Show:
© 2014 Microsoft