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
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
- 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
- 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
- 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
- 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),
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.
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
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
The next connection string used TCIP/IP and a specific IP
Data Source=22.214.171.124,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.