3

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.

  • What about just using $conn = New-Object System.Data.SqlClient.SqlConnection and give it a connection string? – EBGreen Jul 25 '12 at 19:43
  • I'd rather build it up property by property. But really I what I want to know is, why it doesn't work by setting the exposed properties but does work by setting hidden properties. – gerald britton Jul 25 '12 at 19:54
2

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
  • Not sure why this was voted down. It explains exactly the problem, cause and workaround. I have the same problem and the workaround works for me. If you think it's an unhelpful answer, please add something useful or at least an explanation. – charleswj81 Apr 6 '15 at 19:17
0

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.

0

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'}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.