The SQL Server .NET data provider that's included in Visual Studio .NET introduces fundamental changes to the data-access mechanisms that you're accustomed to in OLE DB and ADO. The best place to begin learning about these changes is probably in the connection-string keywords that the SQL Server .NET data provider's SqlConnection object supports. The connection-string keywords are necessary for connecting your application to SQL Server. Although many keywords that the SqlConnection object uses are similar to the OLE DB connection-string keywords, they're not identical. Unlike OLE DB and ADO, which let you use properties to set many connection attributes, ADO.NET lets you set these keywords only in the connection string. Here are seven of the most important connection-string keywords.
7. PERSIST SECURITY INFO
This new keyword specifies whether security-sensitive information such as the password is returned to the application after a successful connection. The SQL Server OLE DB data providers always return this information. A value of true instructs the SqlConnection object to return the information; the default value is false.
6. ATTACHDBFILENAME, EXTENDED PROPERTIES, or INITIAL FILE NAME
These keywords all identify the full path and name of a file that will be attached as a SQL Server database when your application establishes the connection. You must use these keywords with the DATABASE keyword.
5. CONNECT TIMEOUT or CONNECTION TIMEOUT
These keywords specify the length of time in seconds to wait before terminating a connection attempt. The default value is 15 seconds.
4. CONNECTION LIFETIME
This connection-string keyword specifies the length of time in seconds to wait before destroying a connection that has returned to the connection pool. The default is 0.
3. INITIAL CATALOG or DATABASE
INITIAL CATALOG and DATABASE are among the most commonly used keywords. These keywords set the default database name for the current connection.
2. INTEGRATED SECURITY or TRUSTED_CONNECTION
For these keywords, a value of true or sspi specifies that the connection uses Windows authentication to connect to SQL Server. A value of false specifies that the connection uses mixed or SQL Server authentication, which requires the connection string to provide the UID and PWD keywords.
1. DATA SOURCE, SERVER, ADDRESS, ADDR, or NETWORK ADDRESS
Undoubtedly, these are the most essential connection-string keywords. You use them to specify the name or network address of a SQL Server instance to connect to.
