DbConnectionStringBuilder does not parse when used in PowerShell

Question

I am trying to use capabilities of DbConnectionStringBuilder for parsing connection-string-like user input. This works just fine in C#:

using System;
using System.Data.Common;
class Program
{
    static void Main(string[] args)
    {
        var sb = new DbConnectionStringBuilder();
        sb.ConnectionString = "server = 'smtp.gmail.com'; port = 587; user = [email protected]";
        Console.WriteLine(sb["server"]);
        Console.WriteLine(sb["port"]);
        Console.WriteLine(sb["user"]);
    }
}

Output:

smtp.gmail.com
587
[email protected]

But it does not work in PowerShell. Namely, this literally translated code in PowerShell

$sb = New-Object System.Data.Common.DbConnectionStringBuilder
$sb.ConnectionString = "server = 'smtp.gmail.com'; port = 587; user = [email protected]"
$sb["server"]
$sb["port"]
$sb["user"] 

produces no output at all.

Any ideas why? How to make DbConnectionStringBuilder to work as a parser in PowerShell?

Answer 1

System.Data.Common.DbConnectionStringBuilder implements IDictionary. Powershell has a shorthand for dictionaries using . that allows retrieval and assignment of key/value pairs as if the key was a property:

$dict = @{}
$dict["key1"] = 'value1'
$dict.key2 = 'value2'

You can see that it is storing the entire connection string as a key/value pair instead of on the ConnectionString property this way:

$sb = New-Object System.Data.Common.DbConnectionStringBuilder
$sb.ConnectionString = "server = 'smtp.gmail.com'; port = 587; user = [email protected]"
$sb #formatted table of key/value pairs

The easiest way to get around this is to call the set_/get_ methods generated for properties:

$sb = New-Object System.Data.Common.DbConnectionStringBuilder
$sb.set_ConnectionString("server = 'smtp.gmail.com'; port = 587; user = [email protected]")
$sb["server"]
$sb["port"]
$sb["user"]

Answer 2

It is probably a bug (a gotcha anyway) and I will submit it soon. It looks like PowerShell does not call setters on properties of classes that implement IDictionary (as DbConnectionStringBuilder does, and it is the setter of ConnectionString that parses the string).

Here are two demos (the original and workaround):

# test 1 - does not work, presumably PowerShell invokes $sb["ConnectionString"] = .. instead of the setter
$sb = New-Object System.Data.Common.DbConnectionStringBuilder
$sb.ConnectionString = "server = 'smtp.gmail.com'; port = 587; user = [email protected]"

# the original string
$sb.ConnectionString

# nothing at all
$sb["server"]
$sb["port"]
$sb["user"]

# test 2 - works fine, we make PowerShell to invoke the ConnectionString property setter in this way
$sb = New-Object System.Data.Common.DbConnectionStringBuilder
$sb.PSObject.Properties['ConnectionString'].Value = "server = 'smtp.gmail.com'; port = 587; user = [email protected]"

# parsed and re-formatted string
$sb.ConnectionString

# parsed data
$sb["server"]
$sb["port"]
$sb["user"]

Output:

server = 'smtp.gmail.com'; port = 587; user = [email protected]
server=smtp.gmail.com;port=587;[email protected]
smtp.gmail.com
587
[email protected]

As far as the workaround is found we get for free a pretty powerful parser for connection-string-like data. Here is the demo that shows parsing of quite convoluted input:

# get the parser
$sb = New-Object System.Data.Common.DbConnectionStringBuilder

# input the string to parse using this workaround way
$sb.PSObject.Properties['ConnectionString'].Value = @'
Apostrophes = "Some 'value'";
Quotations = 'Some "value"';
Semicolons = '1; 2; 3';
Multiline = Line1
Line2
Line3;
Name with spaces = Some value;
Name with == sign = Some value;
'@

# get the parsed results

# the string: not the original but parsed and re-formatted
$sb.ConnectionString

# the data: parsed key/values
$sb | Format-Table -AutoSize -Wrap

Output:

apostrophes="Some 'value'";quotations='Some "value"';semicolons="1; 2; 3";multiline="Line1
Line2
Line3";name with spaces="Some value";name with == sign="Some value"

Key              Value              
---              -----              
apostrophes      Some 'value'       
quotations       Some "value"       
semicolons       1; 2; 3            
multiline        Line1              
                 Line2              
                 Line3              
name with spaces Some value         
name with = sign Some value