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.
Best Answer
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:
So, when you execute this:
It gets translated to:
To see the available properties to use in this manner, run this command: