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?

It has happened again. As soon as I post a question, I find an answer shortly. Questions do stimulate. I will post the answers (on why and a workaround) but let me keep the question opened for a while as a teaser and a good PowerShell exercise (unless anybody needs the answer immediately). — Roman Kuzmin
– Roman Kuzmin, Commented Jun 4, 2011 at 17:07
I submitted this issue: connect.microsoft.com/PowerShell/feedback/details/674159/… — Roman Kuzmin
– Roman Kuzmin, Commented Jun 4, 2011 at 19:39

Joel B Fant · Accepted Answer · 2011-06-04 18:46:28Z

10

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"]

answered Jun 4, 2011 at 18:46

Joel B Fant

24.8k4 gold badges70 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Roman Kuzmin Over a year ago

Excellent. The set_ConnectionString() trick is new to me and it definitely looks simpler than the way proposed in my answer. Thanks!

Joel B Fant Over a year ago

I remembered that get_/set_ methods get generated for properties and those are what are actually called in IL. C# won't let you call them directly, but Powershell is more loosey-goosey so I took a shot and learned something new. But I also learned something from yours, that PSObject is the key to basic reflection in Powershell.

Roman Kuzmin · Accepted Answer · 2011-06-04 18:43:14Z

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

Was this ever formally submitted as a bug [is there a link]? And also has it been addressed in the past 6+ years (if so, as of which version)...

Chad Miller · Accepted Answer · 2011-06-04 16:45:09Z

0

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

$sb["server"]
$sb["port"]
$sb["user"]

answered Jun 4, 2011 at 16:45

Chad Miller

42.4k4 gold badges33 silver badges34 bronze badges

4 Comments

Roman Kuzmin Over a year ago

I do not have the data you pass in Add(). That's the point of the question: the task is to extract that data from an input connection-string-like string.

Chad Miller Over a year ago

ah, not sure on that one--it seems like it should work the same, but as you point out it doesn't. You could hack around it convertfrom-stringdata $($sb.ConnectionString -split ";" -join "`n")

Roman Kuzmin Over a year ago

:) How about complex strings like this: apostrophes = "'foo'"; quotations = '"bar"'; semicolons = '1;2;3'; simple value = some value? DbConnectionStringBuilder is the powerful parser, we should get its power for free.

Chad Miller Over a year ago

Ageed I look forward to seeing your answer.

Collectives™ on Stack Overflow

DbConnectionStringBuilder does not parse when used in PowerShell

3 Answers 3

2 Comments

1 Comment

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related