如何从 PowerShell 中的连接字符串获取数据库名称 [英] How to get Database Name from Connectionstring in PowerShell

问题描述

我正在尝试从 PowerShell 中的连接字符串中获取数据库名称.

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

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

我可以想到两种方法来做到这一点，要么搜索字符串 Database，直到第一个 ; 之后在 = 上拆分字符串 并选择 Databasename - 但我真的不知道该怎么做.

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.

第二种方法可能是使用 DBConnectionStringBuilder 像这样:

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?

推荐答案

使用第二种方法，但简化它:

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

这工作得很好.

如果你想避免在key不存在的情况下出错，有很多方法可以做到，更惯用的方法是先找key:

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
}

或者对象自己的TryGetValue方法:

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

所以这里我首先用分号 ; 分割，并用任意数量的空格包围.然后根据另一个正则表达式检查每个元素(应该只是键值对)，专门查找 Database= ，然后在命名的捕获组中捕获之后的内容，直到字符串的末尾称为 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.

这篇关于如何从 PowerShell 中的连接字符串获取数据库名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！