Database connectivity has evolved into a standard aspect of application development. Database connection strings are now a standard must-have for every project. I often find myself copying a connection string from another application or performing a search to find the syntax I need. This is especially true when interoperating with SQL Server, which has so many connection string options. Now let's examine the many aspects of connection strings.
connection string
During object instantiation or creation, the database connection string is passed to the necessary object through properties or methods. The format of the connection string is a semicolon-delimited list of key/value parameter pairs. Listing A includes an example in C# that illustrates how to connect to SQL Server by creating a SqlConnection object (the actual connection string is assigned through the object's ConnectionString property). Included in Listing B is the version of VB.NET.
List A
string cString = "Data Source=server;Initial Catalog=db;User ID=test;Password=test;";
SqlConnectionconn = new SqlConnection();
conn.ConnectionString = cString;
conn.Open();
List B
Dim cString As String
cString = "Data Source=server;Initial Catalog=db;User ID=test;Password=test;"
Dim conn As SqlConnection = New SqlConnection()
conn.ConnectionString = cString
conn.Open()
The connection string specifies the database server and database, as well as the username and password necessary to access the database. While this format is not suitable for all database interactions, it does have many options available, many of which 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, its value is the .NET SqlClient Data Provider.
AttachDBFilename/extended properties/Initial File Name: The name of the main file that can be connected to the database, including the full path name. The database name must be specified with the keyword database.
Connect Timeout/Connection Timeout: The length of time (in seconds) a connection to the server waits before terminating. The default value is 15.
Connection Lifetime: When a connection is returned to the connection pool, its creation time is compared with the current time. If this time span exceeds the validity period of the connection, the connection is canceled. Its default value is 0.
Connection Reset: Indicates whether a connection is reset when it is removed from the connection pool. A false valid eliminates the need for an additional server round-trip when obtaining a connection, and its default value is true.
Current Language: The name of the SQL Server language record.
Data Source/Server/Address/Addr/Network Address: The name or network address of the SQL Server instance.
Encrypt (encryption): When the value is true, SQL Server will use SSL encryption for all data transferred between the client and the server if the server has an authorized certificate installed. The accepted values are true, false, yes, and no.
Enlist (registration): Indicates whether the connection pool program will automatically register the connection in the current transaction context of the creation thread. Its default value is true.
Database/Initial Catalog: The name of the database.
Integrated Security/Trusted Connection: Indicates whether Windows authentication is used to connect to the database. It can be set to true, false, or the sspi equivalent of true. Its default value is false.
Max Pool Size (maximum capacity of the connection pool): The maximum number of connections allowed by the connection pool. Its default value is 100.
Min Pool Size (minimum capacity of the connection pool): The minimum number of connections allowed by the connection pool, and its default value is 0.
Network Library/Net: Network library used to establish a connection to a SQL Server instance. Supported values include: dbnmpntw (Named Pipes), dbmsrpcn (Multiprotocol/RPC), dbmsvinn (Banyan Vines), dbmsspxn (IPX/SPX), and dbmssocn (TCP/IP). The protocol's dynamic link library must be installed for the appropriate connection; its default is TCP/IP.
Packet Size: The size of the network packets used to communicate with the database. Its default value is 8192.
Password/Pwd: The password corresponding to the account name.
Persist Security Info: Used to determine whether security information is available once the connection is established. If the value is true, security-sensitive data such as usernames and passwords are available, while if the value is false, they are not available. Resetting the connection string will reconfigure all connection string values, including the password. Its default value is false.
Pooling: Determines whether to use connection pooling. If true, the connection will be obtained from the appropriate connection pool, or, if necessary, the connection will be created and added to the appropriate connection pool. Its default value is true.
User ID: The account name used to log in to the database.
Workstation ID: The name of the workstation connected to SQL Server. Its default value is the name of the local computer.
The following connection string establishes a connection to the Northwind database on the TestDev1 server using a trusted connection and specified login credentials (less secure than leaving the administrator password blank): Server=TestDev1;Database=Northwind;User ID=sa;
Password=;Trusted_Connection=True;
The next connection string uses 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 can easily be included in the connection string, but they again depend on your application and its requirements. It's good to know what's available so you can use it appropriately.
Using ADO.NET 2.0
ADO.NET 2.0 introduces new connection string generators for each .NET Framework data provider. Keywords are listed as attributes, enabling connection string syntax to take effect before being submitted to the data source. There are also new classes that make it easy to store and retrieve connection strings in configuration files, and to encrypt them in a protected manner.