SqlConnection.ConnectionString Property

Connection
ConnectionString are similar to OLE DB connection strings, but they are not the same. Unlike OLE DB or ADO, if the "Persist Security Info" value is set to False (the default), the returned connection string is the same as the user set ConnectionString but the security information is removed. Unless the Persist security Info is set to True, the SQL Server. NET Framework Data Provider will not be persisted and the password in the connection string will not be returned.

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 connection string values have corresponding read-only properties. When you set the connection string, all of these properties are updated (unless an error is detected). Any attributes are not updated when an error is detected. The SqlConnection property returns only those settings that are contained in ConnectionString.

To connect to the local machine, designate the server as "(locals)." (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 contains "database= Northwind" and then resets the connection string to "Data source=myserver;integrated Security=sspi", the Database property will no longer be set to Northwind.

The connection string is parsed immediately after setting. If an error is found in the syntax during parsing, a Run-time exception, such as ArgumentException, is generated. Other errors are found only when you try to open the connection.

The basic format of the connection string includes a series of keyword/value pairs separated by semicolons. The equal sign (=) joins each keyword and its value. To include a value that contains a semicolon, single quote character, or double quote character, the value must be enclosed in double quotes. If the value contains both semicolons and double quote characters, the value can be enclosed in single quotes. If the value starts with a double quote character, you can also use single quotes. Conversely, if the value starts with a single quotation mark, you can use double quotes. If the value contains both single and double quote characters, the quote character used to enclose the value must appear in pairs each time it appears.

To include leading or trailing spaces in a string value, the value must be enclosed in single or double quotes. Even if you enclose an integer, Boolean, or enumeration value in quotation marks, any leading or trailing spaces around it are ignored. However, the space within the string keyword or value is preserved. When using the. NET Framework version 1.1, you can use either single or double quotes in the connection string without using delimiters (for example, data source= my ' server or data source= my "server), However, the quote character cannot be the first or last character of the value.

To include an equal sign (=) in a keyword or value, it must have another equal sign before it. For example, in a hypothetical connection string,

"Key==word=value"



The keyword is "Key=word" and the value is "value".

If a specific keyword in the keyword= value pair appears multiple times in the connection string, the last one listed is used for the value set.

Keywords are not case-sensitive.

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

The name default value describes the name of the application name application, and if the application name is not provided, the default is: ". Net SqlClient Data Provider" attachdbfilename-or-extended properties-or-initial file name can connect to the name of the primary file of the database, including the full pathname.
You must use the keyword "database" to specify the name of the 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 to connect and generating an error. Current Language SQL Server language
Data Source-or-server-or-address-or-addr-or-network address
The name or network address of the instance of SQL Server to which you want to connect. Encrypt ' false ' when this value is true, if a certificate is installed on the server side, SQL Server uses SSL encryption for all data that is transferred between the client and the server. The recognizable values are true, false, yes, and No.
Initial catalog-or-database
The name of the database.
Integrated security-or-trusted_connection
' False ' when false, the user ID and password are specified in the connection. When True, authentication is performed using the current Windows account credentials.
The recognized value is true, false, yes, no, and SSPI (strongly recommended) equivalent to True.

Network Library-or-net
' DBMSSOCN ' is used to establish a network library that is connected 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 want to connect. If you do not specify a network, use a local server (such as "." or "(local)"), the shared memory is used.
Packet Size8192 the size, in bytes, of the network packets that are used to communicate with instances of SQL Server.
Password-or-pwd
The password for the SQL Server account logon (not recommended for use.) In order to maintain the highest level of security, it is strongly recommended that you use the integrated or trusted_connection keyword instead. Persist Security Info ' false ' when the value is set to False or no (strongly recommended), if the connection is open or is always open, the safe-sensitive information, such as the password, will not be returned as part of the connection. Resetting the connection string resets all connection string values, including passwords. The recognizable values are true, false, yes, and No. User ID SQL Server logon account (not recommended for use.) In order to maintain the highest level of security, it is strongly recommended that you use the integrated or trusted_connection keyword instead. Workstation ID The name of the workstation to which the local computer name is connected to SQL Server.

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

The name default value describes Connection LIFETIME0 when a connection is returned to the pool, the creation time is compared to the current time, and if the length of time (in seconds) exceeds the value specified by the Connection Lifetime, the connection is destroyed. This is useful in a clustered configuration (used to enforce load balancing between a running server and a server that is just put online).
A value of 0 (0) will cause the pool connection to have the maximum connection timeout.
Connection reset ' True ' determines whether the database connection is reset when the database connection is fetched from the pool. For Microsoft SQL Server version 7.0, setting to False avoids an additional server round trip when a connection is made, but note that the connection state, such as the database context, is not reset at this time. Enlist ' true ' when this value is true, the pool program automatically enlists the connection in the current transaction context of the creation thread. The recognizable values are true, false, yes, and No. Max Pool Size100 Maximum number of connections allowed. Min Pool Size0 The minimum number of connections allowed in the pool. Pooling ' true ' when the value is true, the system extracts the SqlConnection object from the pool or, if necessary, creates the object and adds it to the appropriate pool. The recognizable values are true, false, yes, and No.

When you set a keyword or connection pool value that requires a Boolean value, you can use ' yes ' instead of ' true ' and ' no ' instead of ' false '. An integer value is represented as a string.

Note that the SQL Server. NET Framework data Provider communicates with SQL Server using its own protocol. Therefore, when connecting to SQL Server, it does not support the use of ODBC data source names (DSNs) because it does not add an ODBC layer. Warning in this release, you should be cautious about constructing a connection string in your application based on user input (for example, retrieving user ID and password information from a dialog box and appending it to the connection string). The application should ensure that users cannot embed additional connection string parameters in these values (for example, enter "Validpassword;database=somedb" as a password to attempt to connect to another database).
Example
[Visual Basic, 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 Securit Y 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 (); }