Connecting to SQL Server via a .NET application provides multiple options for specific connection parameters and other options. Tony Patton provides more details about SQL Server connection string syntax.
Database connectivity has evolved into a standard aspect of application development. The database connection string is now a standard requirement of every project. I often find myself copying a connection string from another application or conducting a search to locate the necessary syntax. This is especially true when interacting with SQL Server, since there are so many connection string options. Let's examine the many facets of connection strings.
The connection string
The database connection string is passed to the necessary object during object instantiation or set via a property or method. The format of a connection string is a semicolon-delimited list of key/value parameter pairs. Listing A contains an example in C# that shows how to connect to SQL Server with the creation of a SqlConnection object. (The actual connection string is assigned via the object's ConnectionString property.) Listing B contains the VB.NET version.
The connection string specifies the database server and database, along with the necessary username and password to access the database. While this format isn't appropriate for all database interactions, it does have a number of available options; many of the options have synonyms.
Along with the Data Source, Initial Catalog, User ID, and Password elements, the following options are available:
- Application Name: The name of the application. If not specified, the value is .NET SqlClient Data Provider.
- AttachDBFilename/extended properties/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/Connection Timeout: The length of time (in seconds) to wait for a connection to the server before terminating. The default value is 15.
- Connection Lifetime: When a connection is returned to the pool, its creation time is compared with the current time. The connection is destroyed if that time span (in seconds) exceeds the value specified by connection lifetime. The default value is zero.
- Connection Reset: Signals whether a connection is reset when removed from a pool. A false valid negates an additional server round-trip when obtaining a connection. The default value is true.
- Current Language: The SQL Server Language record name.
- Data Source/Server/Address/Addr/Network Address: The name or network address of the instance of SQL Server.
- Encrypt: 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: Signals whether the pooler automatically enlists the connection in the creation thread's current transaction context. The default value is true.
- Database/Initial Catalog: The database name.
- Integrated Security/Trusted Connection: Signals whether Windows authentication is used to connect to the database. It may be set to true, false, or sspi, which is equivalent to true. The default value is false.
- Max Pool Size: The maximum number of connections allowed in the pool. The default value is 100.
- Min Pool Size: The minimum number of connections allowed in the pool. The default value is zero.
- Network Library/Net: The network library used to establish a connection to an instance of SQL Server. Supported values include dbnmpntw (Named Pipes), dbmsrpcn (Multiprotocol/RPC), dbmsvinn (Banyan Vines), dbmsspxn (IPX/SPX), and dbmssocn (TCP/IP). The protocol's DLL must be installed to properly connect. The default value is TCP/IP.
- Packet Size: The size of the network packets (in bytes) used to communicate with the database. The default value is 8192.
- Password/Pwd: The account name's corresponding password.
- Persist Security Info: Determines whether security information is available once a connection has been established. A true value says security-sensitive data like the username and password are available, whereas false says it is not. Resetting the connection string resets all connection string values including the password. The default value is false.
- Pooling: Determines if connection pooling is utilized. A true value says the connection is drawn from the appropriate pool, or if necessary, is created and added to the appropriate pool. The default value is true.
- User ID: The account name used to log on the database.
- Workstation ID: The name of the workstation connecting to SQL Server. The default value is the local computer's name.
The following connection string connects to the Northwind database on the Test\Dev1 server using a trusted connection and the specified logon (the less than secure blank administrator password) credentials:
The next connection string used TCIP/IP and a specific IP address:
Data Source=188.8.131.52,1433;Network Library=DBMSSOCN;
Initial Catalog=Northwind;User ID=sa;Password=;
The options used are easy to include in the connection string, but they will depend upon your application and its requirements. It is good to know what is available, so you can use it appropriately.
Working with ADO.NET 2.0
ADO.NET 2.0 introduces new connection string builders for each .NET Framework data provider. Keywords are exposed as properties, enabling connection string syntax to be validated before submission to the data source. There are also new classes that simplify storing and retrieving connection strings in configuration files and encrypting them using protected configuration.
As little or as much as you need
Connecting to SQL Server via a .NET application provides multiple options for specific connection parameters and other options. It may be as simple as specifying the database, server, and log on credentials, or as complex as setting connection pooling and security options. ADO.NET 2.0 does offer more flexibility by making the connection string options via database class. Simply use the options necessary for your application and ignore the rest. And, if you ever find yourself in a jam and you can't remember connection string syntax, refer to the site ConnectionStrings.com.
Miss a column?
Check out the .NET Archive, and catch up on the most recent editions of Tony Patton's column.
Tony Patton began his professional career as an application developer earning Java, VB, Lotus, and XML certifications to bolster his knowledge.