How to get datasource from Connectionstring using PowerShell?

In .Net we can get the datasource from a connectionstring using below mechanism:

System.Data.SqlClient.SqlConnectionStringBuilder builder = new System.Data.SqlClient.SqlConnectionStringBuilder(connectionString);

string server = builder.DataSource;

I was trying to do that in PowerShell but getting the following exception:

$ConstringObj = New-Object System.Data.SqlClient.SqlConnectionStringBuilder($conString)

New-Object : Exception calling “.ctor” with “1” argument(s): “Keyword not supported: ‘metadata’.” At line:1 char:17 + $ConstringObj = New-Object System.Data.SqlClient.SqlConnectionStringBuilder($con … + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [New-Object], MethodInvocationException + FullyQualifiedErrorId : ConstructorInvokedThrowException,Microsoft.PowerShell.Commands.NewObjectCommand

How to do that in PowerShell?

Answer

Problem

There’s some weird behavior when using SqlConnectionStringBuilder in PowerShell – let me explain

Since it’s a dotnet class, you’d expect all of the same properties and methods available in C#

For example, this works fine in C#:

var cnnBuilder = new SqlConnectionStringBuilder();
cnnBuilder.DataSource = "server_name";
cnnBuilder.InitialCatalog = "db_name";

So the equivalent code in PS, should work:

$cnnBuilder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder
$cnnBuilder.DataSource = "server_name"
$cnnBuilder.InitialCatalog = "db_name"

However, SqlConnectionStringBuilder is built ontop of DbConnectionStringBuilder which implements IDictionary so fundamentally we’re working with a dictionary object that has some syntactic sugar wrappers

.NET resolves this with an override on the dictionary accessors and setters like this (simplified here):

public override object this[string keyword] {
    get {
        Keywords index = GetIndex(keyword);
        return GetAt(index);
    }
    set {
        Keywords index = GetIndex(keyword);
        switch(index) {
            case Keywords.DataSource: DataSource = ConvertToString(value); break;
            case Keywords.InitialCatalog: InitialCatalog = ConvertToString(value); break;
            // ***
        }
    }
}

So really, it’s taking the DataSource property and mapping it to the "Data Source" key ^{(with space)}

Whenever PS assigns or retrieves a value, it has to decide whether to use the underlying dictionary implementation or the property. And when you look for DataSource in the dictionary (without the space), that sql connection keyword doesn’t exist.

Solutions

Opt 1 – Use Dictionary Names

You can use the bracket or dot notation with the actual sql key to access the entry in the hashtable

$cnnBuilder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder
$cnnBuilder["Data Source"] = "server_name"
$cnnBuilder."Initial Catalog" = "db_name"

Opt 2 – Use PSBase

PSBase returns the “raw view of the object” and will give us the default behavior in dotnet

$cnnBuilder  = New-Object System.Data.SqlClient.SqlConnectionStringBuilder
$cnnBuilder.PSBase.DataSource = "server_name"
$cnnBuilder.PSBase.InitialCatalog = "db_name"

Opt 3 – Use -Property Parameter

During the construction, you can set the -Property parameter on New-Object which “sets property values and invokes methods of the new object.”

$cnnBuilder  = New-Object System.Data.SqlClient.SqlConnectionStringBuilder `
    -Property @{
        DataSource = "server_name"
        InitialCatalog = "db_name"
    }

Additional Reading

Using SQLConnection object in PowerShell