Search code examples
c#.netpowershellsqlclient

PowerShell not setting property on SqlConnectionStringBuilder (works in C#)

I'm trying to run the below in PowerShell and getting the error 'Keyword not supported: 'DataSource''.

using namespace System.Data.SqlClient

$builder = New-Object SqlConnectionStringBuilder
$builder.DataSource = "localhost"

Searching around, this error appears when you attempt to open a connection where the key is set to 'DataSource' rather than the correct 'Data Source'. But I'm not writing directly to the connection string, I'm simply trying to set the property on the builder class.

The thing is, when I run the equivalent in C#, it works fine:

SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = "localhost";

I've checked the assembly with builder.GetType().Assembly.Location and they're both loading from the same assembly (C:\Windows\Microsoft.NET\assembly\GAC_64\System.Data\v4.0_4.0.0.0__b77a5c561934e089\System.Data.dll), so why is PowerShell trying to write to the connection string rather than set the property? This issue occurs in both PowerShell 5.1 and 7

Solution

  • What you're encountering here is a conflict between PowerShell's dictionary adapter and the native .NET member adapter.

    In short, the SqlConnectionStringBuilder class implements the IDictionary interface, which in turn causes PowerShell to attempt to treat property access (ie. $builder.PropertyName) as an indexing operation (ie. $builder['PropertyName']).

    You can either use the underlying target key name:

    $builder.'Data Source' = 'localhost'
# or 
$builder['Data Source'] = 'localhost'

    ... or you can bypass the dictionary adapter by interacting with the psobject memberset:

    $builder.psobject.Properties['DataSource'].Value = 'localhost'