Building better ConnectionStrings with ConnectionStringBuilder

February 14, 2011

Okay, I never admitted to being a .NET guru or anything, and that’s why I get so excited whenever I run across a gem in the framework that allows me to do something easier and with fewer issues.

ConnectionStrings has always been one of those things I did the hard way.  For example, I would have a line of code that was like so:

string connectionString =  "Data Source={0};Initial Catalog={1};User Id={2};Password={3};";
string.Format(connectionString, serverName, databaseName, userName, password);

This seemed like a logical way to build my connection strings.  However, it wasn’t very flexible.  That was until I discovered the suite of ConnectionStringBuilder classes.

Let’s take the above OleDb connection string and use the OleDbConnectionStringBuilder to build it.

System.Data.OleDb.OleDbConnectionStringBuilder oleDbConnectionStringBuilder  =
            new OleDbConnectionStringBuilder();
oleDbConnectionStringBuilder.DataSource = "myServer";
oleDbConnectionStringBuilder.FileName = "myAccessFile.mdb";

Look at how much cleaner that is!  Maybe you’re working with a SQL Server database:

System.Data.SqlClient.SqlConnectionStringBuilder connectionStringBuilder =
                new SqlConnectionStringBuilder();
connectionStringBuilder.DataSource = "myServer";
connectionStringBuilder.InitialCatalog = "databaseName";
connectionStringBuilder.UserID = "userName";
connectionStringBuilder.Password = "password";

Isn’t that awesome?!  Now, finally, let’s imagine you’re doing all this with Entity Framework:

System.Data.EntityClient.EntityConnectionStringBuilder entityConnectionStringBuilder =
                new EntityConnectionStringBuilder();
entityConnectionStringBuilder.ProviderConnectionString = connectionStringBuilder.ToString();
entityConnectionStringBuilder.Metadata = "(entity framework metadata here)";

There you go!  Instead of hand writing your connection strings, take a look to see if there is a StringBuilder class that’ll do the work for you.