We had got good amount of feedback from developers in 1.0 and 1.1 versions for need to have a Connection-String Builder. Consider that you want to develop an application that needs to dynamically build a connection string depending on the Data Source Name, User ID and Password values given by the user; then open the connection based on this values and do data access. 

In 1.0 and 1.1, when the user wanted to do this, he/she had to manually append the keyword value pair:

//serverName,uid,pwd are provided from the user

public void AccessData(string serverName, string uid, string pwd)

{

      String connectionString = String.Empty;

      connectionString += (“Data Source = ” + serverName +”;”);

connectionString += (“User ID = ” + uid +”;”);

      connectionString += (“Password = ” + pwd +”;”);

SqlConnection c = new SqlConnection (connectionString);

c.Open();

//Do something with the connection

}

Some of the common problems when using the above construct are as follows:

1. No compile-time check: Using wrong connection string keywords (like using ‘DataSource’ instead of ‘Data Source’) in code can cause considerable pain during deployment. Basically the code written will pass all Compile-time check but fails at Run-time/deployment with the following exception:

Type:    ArgumentException ("Keyword not supported: 'datasource'.")

Source: System.Data

2. Connection string injection: In the above code, the values received from the user are not checked and not quoted appropriately. This might lead to Connection string injection. In the above example, if the user supplies the pwd to be “HACK;Integrated Security =true”. Then the connection string will be constructed as “Data Source = DBServer;User ID=guest;Password = HACK;Integrated Security = true”. Since the last value will win for SQL Client Managed Provider, the connection opened will open with the credentials the application is running.
3. Remembering all the keywords (28 the last time I checked for SqlClient Managed Provider), could be difficult.
4. On top of that, there is no easy way to get the default values of the keywords.

In v2.0, we have introduced the concept of ConnectionStringBuilder, which allows you to build connection string dynamically. Here is an example

//serverName,uid,pwd are provided from the user

public void AccessData(string serverName, string uid, string pwd)

{

      String connectionString = String.Empty;

      //Get serverName from the user

SqlConnectionStringBuilder conStrbuilder = new SqlConnectionStringBuilder();

conStrbuilder.DataSource = serverName;

conStrbuilder.UserID = uid;

conStrbuilder.Password = pwd;

SqlConnection c = new SqlConnection (conStrbuilder.ConnectionString);

c.Open();

//Do something with the connection

}

With this code:

1. The code uses properties to set the keyword, we get compile time checks.
2. Also, the keyword values are appropriately quoted and reduce the risk for connection string injection attacks. For the example above: the connection string produced will be Data Source=DBServer;User ID=guest;Password="HACK;Integrated Security=true"
3. Since the keywords are just properties on the ConnectionStringBuilder class; it’s easily available for lookup. (HINT: Intellisense could be helpful here J)
4. The ConnectionStringBuilder is aware of all the default keyword values. For example, calling conStrbuilder.MinPoolSize will return 0 (its default value)

Reading Configuration file entries: If already have a part of the Connection string stored in the configuration files then you can call SqlConnectionStringBuilder (string) constructor to get you started then you can assign more values dynamically from your code.

conStrbuilder = new SqlConnectionStringBuilder (configConnectionString);

conStrbuilder.Pooling = false;

//More connection string changes

SqlConnection c = new SqlConnection (conStrbuilder.ConnectionString);

Support in other providers: Yes! This feature is supported in all the four providers namely: SQLClient, OleDb, Oracle, and Odbc managed providers. Also, ConnectionStringBuilder automatically picks the right quotation rule that applies to the underneath provider.

One last thing:There are other user cases where SqlConnectionStringBuilder class could be useful. Above example, is to just get you started. Comments/Suggestions/Feedback welcome!

Sushil Chordia
SDET - ADO.NET Team

Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights