SqlConnection.ConnectionString Property (System.Data.SqlClient)

ConnectionString Property

Switch on low bandwidth view

Collapse All

Language Filter

This page is specific to

Microsoft Visual Studio 2008/.NET Framework 3.5

Other versions are also available for the following:

.NET Framework Class Library

SqlConnection..::.ConnectionString Property

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

Namespace: System.Data.SqlClient
Assembly: System.Data (in System.Data.dll)

Syntax

Visual Basic (Declaration)

Public Overrides Property ConnectionString As String

Visual Basic (Usage)

Dim instance As SqlConnection
Dim value As String

value = instance.ConnectionString

instance.ConnectionString = value

public override string ConnectionString { get; set; }

Visual C++

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

JScript

public override function get ConnectionString () : String
public override function set ConnectionString (value : String)

Property Value

Type: System..::.String
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	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=true;Initial Catalog=Northwind;server=(local)"

Use the new SqlConnectionStringBuilder to construct valid connection strings at run time. For more information, see Connection String Builders (ADO.NET).

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

To connect to a local computer, specify "(local)" for the server. If a server name is not specified, a connection will be attempted to the default instance on the local computer.

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= AdventureWorks", and then reset the connection string to "Data Source=myserver;Integrated Security=true", the Database property is no longer set to "AdventureWorks".

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 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. Using .NET Framework version 1.1 or later, 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.

Keywords are not case sensitive.

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

Keyword	Default	Description
Application Name	N/A	The name of the application, or '.NET SQLClient Data Provider' if no application name is provided.
Async	'false'	When true, enables asynchronous operation support. Recognized values are true, false, yes, and no.
AttachDBFilename -or- extended properties -or- Initial File Name	N/A	The name of the primary database file, including the full path name of an attachable database. AttachDBFilename is only supported for primary data files with an .mdf extension. The attachment will fail if the primary data file is read-only. The path may be absolute or relative by using the DataDirectory substitution string. If DataDirectory is used, the database file must exist within a subdirectory of the directory pointed to by the substitution string. Note: Remote server, HTTP, and UNC path names are not supported. The database name must be specified with the keyword 'database' (or one of its aliases) as in the following: "AttachDbFileName=\|DataDirectory\|\data\YourDB.mdf;integrated security=true;database=YourDatabase" An error will be generated if a log file exists in the same directory as the data file and the 'database' keyword is used when attaching the primary data file. In this case, remove the log file. Once the database is attached, a new log file will be automatically generated based on the physical path.
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.
Context Connection	'false'	true if an in-process connection to SQL Server should be made.
Current Language	N/A	The SQL Server Language record name.
Data Source -or- Server -or- Address -or- Addr -or- Network Address	N/A	The name or network address of the instance of SQL Server to which to connect. The port number can be specified after the server name: server=tcp:servername, portnumber When specifying a local instance, always use (local). To force a protocol, add one of the following prefixes: np:(local), tcp:(local), lpc:(local) Note: ADO.NET 2.0 does not support asynchronous commands over shared memory for SQL Server 2000 or earlier. However, you can force the use of TCP instead of shared memory, either by prefixing tcp: to the server name in the connection string, or by using localhost.
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.
Enlist	'true'	true indicates that the SQL Server connection pooler automatically enlists the connection in the creation thread's current transaction context.
Failover Partner	N/A	The name of the failover partner server where database mirroring is configured. The Failover Partner keyword is not supported by .NET Framework version 1.0 or 1.1.
Initial Catalog -or- Database	N/A	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.
MultipleActiveResultSets	'false'	When true, an application can maintain multiple active result sets (MARS). When false, an application must process or cancel all result sets from one batch before it can execute any other batch on that connection. Recognized values are true and false. The keyword is not supported by .NET Framework version 1.0 or 1.1.
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, Windows RPC) dbmsadsn (Apple Talk) dbmsgnet (VIA) dbmslpcn (Shared Memory) dbmsspxn (IPX/SPX) dbmssocn (TCP/IP) Dbmsvinn (Banyan Vines) 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. In this example, the network library is Win32 Winsock TCP/IP (dbmssocn), and 1433 is the port being used. Network Library=dbmssocn;Data Source=000.000.000.000,1433;
Packet Size	8192	Size in bytes of the network packets used to communicate with an instance of SQL Server.
Password -or- Pwd	N/A	The password for the SQL Server account logging on. Not recommended. To maintain a high level of security, we strongly recommend 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.
Replication	'false'	true if replication is supported using the connection.
Transaction Binding	Implicit Unbind	Controls connection association with an enlisted System.Transactions transaction. Possible values are: Transaction Binding=Implicit Unbind; Transaction Binding=Explicit Unbind; Implicit Unbind causes the connection to detach from the transaction when it ends. After detaching, additional requests on the connection are performed in autocommit mode. The System.Transactions.Transaction.Current property is not checked when executing requests while the transaction is active. After the transaction has ended, additional requests are performed in autocommit mode. Explicit Unbind causes the connection to remain attached to the transaction until the connection is closed or an explicit SqlConnection.TransactionEnlist(null) is called. An InvalidOperationException is thrown if Transaction.Current is not the enlisted transaction or if the enlisted transaction is not active.
TrustServerCertificate	'false'	When set to true, SSL is used to encrypt the channel when bypassing walking the certificate chain to validate trust. If TrustServerCertificate is set to true and Encrypt is set to false, the channel is not encrypted. Recognized values are true, false, yes, and no. For more information, see Connection String Syntax (ADO.NET).
Type System Version	N/A	A string value that indicates the type system the application expects. Possible values are: Type System Version=SQL Server 2000; Type System Version=SQL Server 2005; Type System Version=SQL Server 2008; Type System Version=Latest; When set to SQL Server 2000, the SQL Server 2000 type system is used. The following conversions are performed when connecting to a SQL Server 2005 instance: XML to NTEXT UDT to VARBINARY VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) to TEXT, NEXT and IMAGE respectively. When set to SQL Server 2005, the SQL Server 2005 type system is used. No conversions are made for the current version of ADO.NET. When set to Latest, the latest version than this client-server pair can handle is used. This will automatically move forward as the client and server components are upgraded.
User ID	N/A	The SQL Server login account. Not recommended. To maintain a high level of security, we strongly recommend that you use the Integrated Security or Trusted_Connection keywords instead.
User Instance	'false'	A value that indicates whether to redirect the connection from the default SQL Server Express instance to a runtime-initiated instance running under the account of the caller.
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, see SQL Server Connection Pooling (ADO.NET).

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.
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.
Load Balance Timeout	0	The minimum time, in seconds, for the connection to live in the connection pool before being destroyed.
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 it is required, is created and added to the appropriate pool. Recognized values are true, false, yes, and no.

When you are 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.

Note:
Universal data link (UDL) files are not supported for the .NET Framework Data Provider for SQL Server.

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 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 need to construct connection strings based on user input, use the new SqlConnectionStringBuilder, which validates the connection string and helps to eliminate this problem. See Connection String Builders (ADO.NET) for more information.

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 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 need to construct connection strings based on user input, use the new SqlConnectionStringBuilder, which validates the connection string and helps to eliminate this problem. See Connection String Builders (ADO.NET) for more information.

Examples

The following example creates a SqlConnection and sets the ConnectionString property before opening the connection.

Visual Basic

Private Sub OpenSqlConnection()
    Dim connectionString As String = GetConnectionString()

    Using connection As New SqlConnection()

        connection.ConnectionString = connectionString

        connection.Open()

        Console.WriteLine("State: {0}", connection.State)
        Console.WriteLine("ConnectionString: {0}", _
            connection.ConnectionString)
    End Using
End Sub

Private Function GetConnectionString() As String
    ' To avoid storing the connection string in your code,  
    ' you can retrieve it from a configuration file.
    Return "Data Source=MSSQL1;Database=AdventureWorks;" _
      & "Integrated Security=true;"
End Function

private static void OpenSqlConnection()
{
    string connectionString = GetConnectionString();

    using (SqlConnection connection = new SqlConnection())
    {
        connection.ConnectionString = connectionString;

        connection.Open();

        Console.WriteLine("State: {0}", connection.State);
        Console.WriteLine("ConnectionString: {0}",
            connection.ConnectionString);
    }
}

static private string GetConnectionString()
{
    // To avoid storing the connection string in your code, 
    // you can retrieve it from a configuration file.
    return "Data Source=MSSQL1;Initial Catalog=AdventureWorks;"
        + "Integrated Security=true;";
}

Platforms

Windows 7, Windows Vista, Windows XP SP2, Windows XP Media Center Edition, Windows XP Professional x64 Edition, Windows XP Starter Edition, Windows Server 2008 R2, Windows Server 2008, Windows Server 2003, Windows Server 2000 SP4, Windows Millennium Edition, Windows 98, Windows CE, Windows Mobile for Smartphone, Windows Mobile for Pocket PC

The .NET Framework and .NET Compact Framework do not support all versions of every platform. For a list of the supported versions, see .NET Framework System Requirements.

Version Information

.NET Framework

Supported in: 3.5, 3.0, 2.0, 1.1, 1.0

.NET Compact Framework

Supported in: 3.5, 2.0, 1.0

Reference

SqlConnection Class

SqlConnection Members

System.Data.SqlClient Namespace

Other Resources

Connection Strings (ADO.NET)

SQL Server Connection Pooling (ADO.NET)

Connecting to a Data Source (ADO.NET)

Tags

: Add a tag

Community Content

Add new content

Annotations

Connection Lifetime and Load Balance Timeout connection string pooling parameters

SNMSDN | Edit | Show History

According to http://blogs.msdn.com/angelsb/archive/2004/09/20/231963.aspx, the Connection Lifetime and Load Balance Timeout connection string parameters are exactly the same. The preferred one being Load Balance Timeout. So they do not do different things, even though their descriptions in this topic are different.

Tags

: connection (x) connectionlifetime (x) loadbalancetimeout (x) pooling (x) string (x) Add a tag

Flag as ContentBug

Yes, Connection Lifetime and Load Balance Timeout *are* equivalent

Concrete Gannet | Edit | Show History

Yes, Connection Lifetime and Load Balance Timeout are equivalent. See the SqlConnectionStringBuilder.LoadBalanceTimeout property at http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnectionstringbuilder.loadbalancetimeout.aspx for confirmation.

I agree that this topic should say so, and encourage everyone to use Load Balance Timeout in connection strings.

Tags

: connection (x) connectionlifetime (x) loadbalancetimeout (x) pooling (x) string (x) Add a tag

Flag as ContentBug

|DataDirectory| substitution

Yop83 | Edit | Show History

For those wondering what |DataDirectory| is and how it is handled:

To set the DataDirectory property, call the AppDomain.SetData method. If you do not set the DataDirectory property, the following default rules will be applied to access the database folder:

• For applications that are put in a folder on the user's computer, the database folder uses the application folder.

• For applications that are running under ClickOnce, the database folder uses the specific data folder that is created.

This tidbit was found at: http://forums.microsoft.com/msdn/showpost.aspx?siteid=1&postid=702378

Tags

: datadirectory (x) Add a tag

Flag as ContentBug

TransactionEnlist incorrect method name used

PatrickFOX1 | Edit | Show History

In Transaction Binding attributes, reference is made to calling SqlConnection.TransactionEnlist(null). This method does not exist. I presume the method name should be EnlistTransaction.

See also: http://social.msdn.microsoft.com/forums/en-US/adodotnetdataproviders/thread/206e0b5d-4dcf-415a-83a8-04ccd90197c4/

Tags

: sqlconnection.enlisttransaction (x) sqlconnection.transactionenlist (x) Add a tag

Flag as ContentBug

Default Port

tim878 | Edit | Show History

When using dbmsoccn(tcp) does anyone know what the default port is for the C# application.(I know sql by default uses 1433 but i cant find any mention on the internet what the c# libraries use)

Tags

: Add a tag

Flag as ContentBug