How to get Database Name from Connectionstring in PowerShell

Question

I'm trying to get the Database name from a connection string in PowerShell.

"Server=server\instance;uid=User;pwd=Hello;Database=SomeName;"

I can think of two ways to do that, either to search for the string Database, up until the first ; after that split the string on = and select the Databasename - but I don't really know how to do that.

The second way could be with the DBConnectionStringBuilder like this:

$sb = New-Object System.Data.Common.DbConnectionStringBuilder
$sb.set_ConnectionString($cstring)
[string]$Database = ($sb | ? {$_.Keys -eq 'Database'}).value

but with this way, no matter how hard i try to filter the Databasename, it won't give me the databasename returned.

Question: What's the best way to get my Databasename from the connection string?

Answer 1

Use the second method, but simplify it:

$cstring = "Server=server\instance;uid=User;pwd=Hello;Database=SomeName;"

$sb = New-Object System.Data.Common.DbConnectionStringBuilder
$sb.set_ConnectionString($cstring)
$Database = $sb.database

This works perfectly fine.

If you want to avoid an error in the case where the key doesn't exist, there are a lot of ways to do that, the more idiomatic method of looking for the key first:

if ($sb.HasKey('Database')) {
    $Database = $sb.Database
}

Or the object's own TryGetValue method:

if ($sb.TryGetValue('Database', [ref] $Database)) {
    # It was successful
    # $Database already contains the value, you can use it.
} else {
    # The key didn't exist.
}

---

String Parsing

I don't recommend these in this case because there is some flexibility in the database connection string format, and why make your code aware of all the possibilities and try to correctly handle them all when that code was already written (the object you're using above)?

But for completeness, I'd do it with splitting and regular expression matching and capturing:

$cstring -split '\s*;\s*' |
    ForEach-Object -Process {
        if ($_ -imatch '^Database=(?<dbname>.+)$') {
            $Database = $Matches.dbname
        }
    }

So here I'm first splitting on a semi-colon ; surrounded by any amount of whitespace. Each element (which should be just key-value pairs) is then checked against another regex, looking specifically for Database= and then capturing what comes after that until the end of the string, in a named capture group called dbname. If the match is successful, then the result of the capture group is assigned to the variable.

I still prefer a proper parser when one exists.