Introduction

A connection string is a basic requirement for any application to perform database operations. It contains the details about the server and the database to connect to, including the authentication mode to be used and various other parameters.

The on-going practice for creating connection strings is to store individual elements [like Server and database name, authentication details, timeout parameters, etc] in the configuration file. By string concatenation, the complete connection string is obtained. Also, storing the entire connection string in the configuration file is followed. Both these methods are being used successfully by most of the application.

While using the first method, you are supposed to manually merge the strings to get the connection string. .NET 2.0 provides various in-built classes which give the correct connection string, if valid inputs are provided.

.NET 2.0 Classes

Classes provided by .NET 2.0 [with their namespace]:

• SqlConnectionStringBuilder [System.Data. SqlClient]

• OracleConnectionStringBuilder [System.Data.OracleClient]

• OdbcConnectionStringBuilder [System.Data.Odbc]

• OleDbConnectionStringBuilder [System.Data.OleDb]

All thess four classes inherit from the DbConnectionStringBuilder [in System.Data.Common namespace] class, which can also be used to create your connection strings. This class performs no checks for valid connection string elements, so there are chances to generate invalid connection strings.

The SqlConnectionStringBuilder supports elements supported by SQL Server and OracleConnectionStringBuilder supports elements supported by Oracle only.These classes are used to create and parse correct connection strings programmatically. In this text, SqlConnectionStringBuilder class has been used.

Example

//include namespace
using System.Data.SqlClient;
//declaration
SqlConnectionStringBuilder connStringBuilder = new SqlConnectionStringBuilder();
OR
SqlConnectionStringBuilder connStringBuilder = new SqlConnectionStringBuilder(connectionString);

If connection string is available, it can directly be used to initialize the object. The connection string specified should be valid. There are various properties available to specify/retrieve the details from the connection string.

connStringBuilder.DataSource [Name of the SERVER to connect]

connStringBuilder.InitialCatalog [Name of the DATABASE to connect]

connStringBuilder.IntegratedSecurity [For using windows authentication]

connStringBuilder.PacketSize 

connStringBuilder.ConnectTimeout [TIME in seconds for connecting to Server]

connStringBuilder.UserID 

connStringBuilder.Password [PASSWORD for the specified user]

After specifying the above details, you can find out the connection string using:

connStringBuilder.ConnectionString;

This can be directly used to create the connection object:

SqlConnection  sqlConn = new SqlConnection(connStringBuilder.ConnectionString);

Conclusion

Using these classes, the need to perform string concatenation for preparing connection strings can be avoided. Most of the properties for the ConnectionStringBuilder classes are similar, so a generic design can also be created for various data providers.