Building a connection string from scratch can sometimes be a little daunting when you do not know the exact syntax. Of course, you can always visit www.connectionstrings.com and find some great help there. In lieu of this you can also use the ConnectionStringBuilder class. Each of the ADO.NET providers supplies a version of this class that will build a connection string for you. Below is an example of how to use this class.

VB.NET
Imports System.Data.SqlClient

Private Sub CreateConnectionString()
  Dim builder As New SqlConnectionStringBuilder

  builder.DataSource = "(local)"
  builder.InitialCatalog = "Northwind"
  builder.UserID = "user1"
  builder.Password = "P@ssw0rd"

  MessageBox.Show(builder.ConnectionString)
End Sub

C#
using System.Data.SqlClient;

private void CreateConnectionString()
{
  SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();

  builder.DataSource = "(local)";
  builder.InitialCatalog = "Northwind";
  builder.UserID = "user1";
  builder.Password = "P@ssw0rd";

  MessageBox.Show(builder.ConnectionString);
}

This is a pretty easy class to use. You can just fill in the basic information such as the DataSource, InitialCatalog, the UserId and Password and it will create a connection string for you. The output from the above code will be: "Data Source=(local);Initial Catalog=Northwind;User ID=user1;Password=p@ssword".

To add on additional keywords for your connection string you may use the Add method. This method takes the keyword and the value and will add them in the appropriate format to your connection string.

As mentioned, each ADO.NET data provider supplies one of these classes. For example, if you are using Oracle, you would use the System.Data.OracleClient namespace, then use the OracleConnectionStringBuilder.

The ConnectionStringBuilder class allows you to parse the individual elements of a connection string and put them into the corresponding properties in the ConnectionStringBuilder. In the following example you take an existing connection string like the one shown in the code below and place it into the ConnectionString property of the ConnectionStringBuilder. The ConnectionStringBuilder will then break it into the appropriate properties.

VB.NET
Private Sub ParseConnectionString()
  Dim cnn As String
  Dim builder As New SqlConnectionStringBuilder

  cnn = "Server=Localhost;Initial Catalog=Northwind;User ID=user1;Password=P@ssw0rd"

  builder.ConnectionString = cnn

  MessageBox.Show(builder.DataSource)
  MessageBox.Show(builder.InitialCatalog)
  MessageBox.Show(builder.UserID)
End Sub

C#
private void ParseConnectionString()
{
  string cnn;
  SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();

  cnn = "Server=Localhost;Initial Catalog=Northwind;User ID=user1;Password=P@ssw0rd";

  builder.ConnectionString = cnn;

  Debug.WriteLine(builder.DataSource);
  Debug.WriteLine(builder.InitialCatalog);
  Debug.WriteLine(builder.UserID);
}

So the next time you have a connection string that you wish to extract the elements from, or you need to build a connection string, consider using the ConnectionStringBuilder class. I would not recommend using this in a production application as I can't imagine that the performance would be that great, but for figuring out a connection string, this works great.

Good Luck With Your Coding,
Paul Sheriff

** SPECIAL OFFER FOR MY BLOG READERS **
Visit http://www.pdsa.com/Event/Blog for a free eBook on "Fundamentals of N-Tier".