OleDbConnectionStringBuilder returning value incompatible with OleDbConnection

Question

We have run into an unexpected exception thrown when opening a OleDbConnection created from the return value of the OleDbConnectionStringBuilder.ConnectionString property. Were using the OleDbConnectionStringBuilder to update any relative DataSource paths to absolute paths.

We get an System.Data.OleDb.OleDbException exception when we call OleDbConnection.Open and the message states:
"Format of the initialization string does not conform to the OLE DB specification.".

The code is:

    var oleDBbuilder = new OleDbConnectionStringBuilder(connectString);

    oleDBbuilder.DataSource = ResolveDataSourcePath(oleDBbuilder.DataSource);

    m_pOleDb = new OleDbConnection(oleDBbuilder.ConnectionString);
    m_pOleDb.Open(); // <-- Throws exception here

We have had no issue with this until we attempted to process a connect string which contained the value Mode=ReadWrite|Share Deny None;. The value returned by OleDbConnectionStringBuilder.ConnectionString property returns the property with the its value quoted. e.g:

This connection string (variable 'connectString'):

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\SomePath\@Some Database.mdb;Mode=ReadWrite|Share Deny None;Persist Security Info=False

becomes (OleDbConnectionStringBuilder.ConnectionString):

Provider=Microsoft.Jet.OLEDB.4.0;Data Source="c:\SomePath\Some Database.mdb";Persist Security Info=False;Mode="ReadWrite|Share Deny None"

The Mode value without quotes works without error!

I have a couple of questions:

• Is this by design? Why does it return an incompatible value?
• How can I work around this without duplicating the job of OleDbConnectionStringBuilder?

Answer 1

I don't have a real answer, just a work around.

Before using oleDBbuilder.ConnectionString you can remove the quotes with a regex like this:

m_pOleDb = new OleDbConnection(Regex.Replace(oleDBbuilder.ConnectionString, @"(?<=mode=)((?<quote>""|')?(?<inner>[^;]+?)\k<quote>)", "${inner}", RegexOptions.IgnoreCase));

instead of

m_pOleDb = new OleDbConnection(oleDBbuilder.ConnectionString);

Another option, if you are responsible for the original connection string, would be to replace the spaces in "Share Deny None" with a placeholder like "Share###Deny###None" before committing it to oleDBbuilder. At the end before using oleDBbuilder.ConnectionString this replacement must be undone again.

To answer your first question ("Is this by design?") I can only speculate. To me it looks more like careless programming. Net programmers ignored the fact that ADODB.ConnectionModeEnum has a kind of flags attribute, so that multiple values can be added with Or.

Edit

First: For me it looks like a misconception that keys with spaces inside are quoted. This is only necessary for keys with leading or trailing spaces.

Second: I did some tests. This works with Provider=VFPOLEDB.1 and Provider=Microsoft.ACE.OLEDB.12.0:

Mode=ReadWrite|Share Deny None
Mode=ReadWrite|"Share Deny None"

This doesn't work:

Mode="ReadWrite|Share Deny None"
Mode="ReadWrite"|"Share Deny None"