Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .