Primer to ConnectionStringBuilder in ADO.Net v2.0 [Sushil Chordia]


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

  1. 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.
  2. Remembering all the keywords (28 the last time I checked for SqlClient Managed Provider), could be difficult.
  3. 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

Comments (19)

  1. ShadowChaser says:

    GREAT news!!

  2. Sahil Malik says:

    I’ve written a command line utility (for fun), to work with various databases I might want to work with. So I could pass commands like below ..

    sqlsh$ connection local

    localdbtest>DataTable dt = select * from customers.

    localdbtest>dt.WriteXml("C:1.xml")

    ! (this will put be back to shell mode, so I can jump from db to db)

    sqlsh$ notepad c:1.xml

    For the above to work, I have leveraged ConnectionString builder to create any number of connectionstrings and work with them interactively, and then I can jump back n forth between them and work with data at a high speed that SQL Server Management Studio doesn’t let me (too heavy an app, but good nonetheless).

    KOOL HUH? 😀

  3. Sahil, Its rewarding to see you playing around with ConnectionStringBuilder while building your own shell app. Nice!

  4. Greg V says:

    This is a great blog! Keep it going!

    One question: Does the Oledb and ODBC ConnectionStringBuilder keep track of all the connection keywords and expose them as properties? This seems a difficult problem, since the connection properties depend on the underlying Provider for Oledb and Driver for ODBC.

  5. Greg, you are right.Not all the properties that are provider(/Driver) specific in OleDb(/ODBC) are part of the ConnectionStringBuilder. Some that are well known for OleDb are part of the builder, like – provider,DataSource,OleDbServices,FileName,PersistSecurityInfo. Since the builder implements IDictionary, for adding addition keyword-value pairs, you can add them as follows:

    builder.Add("File Name", "C:DataFile");

  6. John Papa says:

    The ADO.NET team is working on a building an object called SqlConnectionStringBuilder. The object is…

  7. Andres Aguiar says:

    I’ve seen that when you bind the SqlConnectionStringBuilder to a Property Grid, you get a decent way to ask for the properties to the user.

    I wonder if you plan to have a standard Windows Forms control that asks for the basic properties (user/pwd/database/host) and has an ‘Advanced’ dialog with the bound SqlConnectionStringBuilder, or if we will need to write it ourselves.

    Thanks

    Andres

  8. MSDN Archive says:

    Aaron, Thanks for your comment that you posted on your blog (http://pluralsight.com/blogs/aaron/archive/2005/04/28/7815.aspx)

    Andres, We dont have a plan to implement a Windows Forms Control for basic properties. Binding ConnectionStringBuilder to the property grid is the one way, through which you can write this yourself. Thanks for the feedback, though.

    Sushil Chordia [MS]

  9. Gollum's den says:

    C помощью класса SqlConnectionStringBuilder стало еще удобнее работать со строками соединения.

    1)

    SqlConnectionStringBuilder…

  10. Gollum's den says:

    C помощью класса SqlConnectionStringBuilder стало еще удобнее работать со строками соединения.

    1)

    SqlConnectionStringBuilder…

  11. Following the spirit of Primer to ConnectionStringBuilder from ADO.NET 2.0, let’s see how the pattern

  12. Revisando este tema recién me entero de la existencia de ConnectionStringBuilder , pues recontra útil

  13. Revisando este tema recién me entero de la existencia de ConnectionStringBuilder , pues recontra útil

Skip to main content