Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
    
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
add comment

1 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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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