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.

Weekly .NET tips in your inbox

TechRepublic’s free .NET newsletter, delivered each Wednesday, contains useful tips and coding examples on topics such as Web services, ASP.NET, ADO.NET, and Visual Studio .NET.

Automatically sign up today!

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:

Server=Test\Dev1;Database=Northwind;User ID=sa;
Password=;Trusted_Connection=True;

The next connection string used TCIP/IP and a specific IP
address:

Data Source=192.162.1.100,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.