Building better ConnectionStrings with ConnectionStringBuilder

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.

comments powered by Disqus