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);
//Do something with the connection
Some of the common problems when using the above construct are as follows:
Type: ArgumentException ("Keyword not supported: 'datasource'.")
In v2.0, we have introduced the concept of ConnectionStringBuilder, which allows you to build connection string dynamically. Here is an example
//Get serverName from the user
SqlConnectionStringBuilder conStrbuilder = new SqlConnectionStringBuilder();
conStrbuilder.DataSource = serverName;
conStrbuilder.UserID = uid;
conStrbuilder.Password = pwd;
SqlConnection c = new SqlConnection (conStrbuilder.ConnectionString);
With this code:
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
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 ChordiaSDET - ADO.NET Team
Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights