다음을 통해 공유

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

  • Anonymous
    March 30, 2005
    GREAT news!!

  • Anonymous
    March 30, 2005
    The comment has been removed

  • Anonymous
    April 01, 2005
    Sahil, Its rewarding to see you playing around with ConnectionStringBuilder while building your own shell app. Nice!

  • Anonymous
    April 05, 2005
    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.

  • Anonymous
    April 05, 2005
    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");

  • Anonymous
    April 05, 2005
    The ADO.NET team is working on a building an object called SqlConnectionStringBuilder. The object is...

  • Anonymous
    April 28, 2005
    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

  • Anonymous
    April 29, 2005
    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]

  • Anonymous
    August 03, 2005
    C помощью класса SqlConnectionStringBuilder стало еще удобнее работать со строками соединения.
    1)
    SqlConnectionStringBuilder...

  • Anonymous
    August 03, 2005
    C помощью класса SqlConnectionStringBuilder стало еще удобнее работать со строками соединения.
    1)
    SqlConnectionStringBuilder...

  • Anonymous
    March 14, 2008
    Following the spirit of Primer to ConnectionStringBuilder from ADO.NET 2.0, let’s see how the pattern

  • Anonymous
    March 20, 2008
    Revisando este tema recién me entero de la existencia de ConnectionStringBuilder , pues recontra útil

  • Anonymous
    March 26, 2008
    Revisando este tema recién me entero de la existencia de ConnectionStringBuilder , pues recontra útil

  • Anonymous
    May 31, 2009
    PingBack from http://outdoorceilingfansite.info/story.php?id=5156