Follow @PDSAInc Using The ConnectionStringBuilder class - Paul Sheriff's Blog for the Real World

Paul Sheriff's Blog for the Real World

This blog is to share my tips and tricks garnered over 25+ years in the IT industry

Paul's Favorites

Using The ConnectionStringBuilder class

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".

Posted: Sep 08 2009, 11:48 AM by psheriff | with 3 comment(s)
Filed under: ,

Comments

Mike said:

Wow...never knew this was even available.  Thanks!

# September 8, 2009 2:27 PM

Fraz said:

Could you please comment on how to use the Integrated Security on a local instance of MS SQL. Ive tried to get it to connect by stating that the integrated security was true or sspi but neither worked.

Thanks in advance

# June 4, 2011 2:49 PM

psheriff said:

Franz,

This is a tough one to troubleshoot since I would need to know how you are trying to connect (from ASP.NET, WPF, Win Forms), what version of IIS (if applicable), what version of SQL Server, what mode you have SQL Server in, if your login or the group you are in has rights to connect to SQL Server, whether or not you are using SQL Express, and a ton of other things! :)

I would recommend a Bing search on "Connect Local SQL Server Trusted" to help with this.

Paul

# June 6, 2011 10:35 AM

psheriff said:

Franz,

One more thing, check out www.connectionstrings.com to ensure that your connection string is formatted correctly.

Paul

# June 6, 2011 10:36 AM