Using SQLConnection object in powershell

Question

I want to use the SQLConnection object as I would from vb, for example. I'd like to do this:

e.g.

$conn = New-Object system.data.sqlclient.sqlconnectionstringbuilder
$conn.DataSource = 'myserver'

but this fails:

Keyword not supported: 'DataSource'.
At line:1 char:4
+ $conn. <<<< DataSource = 'myserver'
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : PropertyAssignmentException

even though:

PS C:\WINDOWS\system32\WindowsPowerShell> $conn|Get-Member *sour*


   TypeName: System.Data.SqlClient.SqlConnectionStringBuilder

Name       MemberType Definition
----       ---------- ----------
DataSource Property   System.String DataSource {get;set;}

My Powershell version:

PS C:\WINDOWS\system32\WindowsPowerShell> $PSVersionTable

Name                           Value
----                           -----
CLRVersion                     2.0.50727.3634
BuildVersion                   6.0.6002.18111
PSVersion                      2.0
WSManStackVersion              2.0
PSCompatibleVersions           {1.0, 2.0}
SerializationVersion           1.1.0.1
PSRemotingProtocolVersion      2.1

What is causing the error message?

Specifically, this works:

$conn.database = 'mydb' 

(and sets the property 'InitialCatalog')

and this fails

$conn.initialcatalog = 'mydb' 

even though:

PS C:\WINDOWS\system32\WindowsPowerShell> $conn|Get-Member initialcatalog 


TypeName: System.Data.SqlClient.SqlConnectionStringBuilder 

Name MemberType Definition 
---- ---------- ---------- 
InitialCatalog Property System.String InitialCatalog {get;set;} 

What I'm confused about is why I can set the "database" property even though it's not in the member list, but I cannot set the properties in the member list, even though PS says that they are set-able.

Answer 1

The "Keyword" is "Data Source" so you have to set $ConnString."Data Source" = 'myserver'

Edit: Adding explanation

Step 1: SqlConnectionStringBuilder derives from DbConnectionStringBuilder which implements the IDictionary, ICollection, IEnumerable, and ICustomTypeDescriptor interfaces.

Step 2: SqlConnectionStringBuilder::InitialCatalog internally just sets SqlConnectionStringBuilder::Item['Initial Catalog'].

Step 3: When we combine the previous statements, we get the following result:

PS> $sc = New-Object System.Data.SqlClient.SqlConnectionStringBuilder
PS> $sc.InitialCatalog.Equals($sc.Item('Initial Catalog'))
True

PS> $sc.InitialCatalog.Equals($sc.Item('Database'))
True

# And just for completeness
PS> $sc.InitialCatalog.Equals($sc.Item('Server'))
False

So, when you execute this:

$Conn.InitialCatalog = "mydb'

It gets translated to:

$Conn.Item['Initial Catalog'] = "mydb"

To see the available properties to use in this manner, run this command:

$Conn.Keys

Answer 2

Note that both .psbase and -Property give you access to the connection string properties by name, as in

$conn = New-Object system.data.sqlclient.sqlconnectionstringbuilder
$conn.psbase.DataSource= 'yourDataSource'

or

$conn = New-Object system.data.sqlclient.sqlconnectionstringbuilder -Property @{DataSource='ursrc'}

Answer 3

Still don't have a reason why this fails, but a different way to get this to work is to call the setters directly rather than trying to set the value of the properties:

$conn.set_InitialCatalog("mydb")

should behave as you expect.