1/26/2011 Building Connection Strings in .Net

.Net developers frequently need to build connection strings, especially when connecting to Access or Excel files using OleDB.
Code like the following has been written countless times:

//Bad code! Do not use!
string conn = "Data Source=" + openFileDialog1.FileName + "; "
            + "Provider=Microsoft.Jet.OLEDB.4.0;"
            + "Extended Properties=\"Excel 8.0\"";

This code looks innocuous at first glance, but will not work for all filenames.  If the filename contains characters like ', ", ;, or =, this code will create an invalid connection string and throw an exception.

The correct way to build connection strings is to use one of the DbConnectionStringBuilder classes.  This class implements a dictionary of key-value pairs in the connection string.  It has a ConnectionString property which assembles the instance’s contents into a usable connection string.  Unlike the string concatenation shown above, this property will correctly escape all values.

In addition, each of the database clients included with the .Net framework (SQL, OleDB, ODBC, Oracle, and Entity Framework) have their own inherited ConnectionStringBuilder classes in their respective namespaces.  These classes add type-safe properties for the the keys supported by their databases, and handle any special cases when generating the connection string.

Thus, the correct way to write the above code is:

var connBuilder = new OleDbConnectionStringBuilder {
    DataSource = openFileDialog1.FileName,
    Provider = "Microsoft.Jet.OLEDB.4.0"
};
connBuilder["Extended Properties"] = "Excel 12.0 Macro";

As an added bonus, these classes implement ICustomTypeDescriptor, so they can be bound to a PropertyGrid to allow the end-user to edit the connection string.  This can be seen in certain places in Visual Studio.