如何使用PowerShell从Connectionstring获取数据源? [英] How to get datasource from Connectionstring using PowerShell?

问题描述

在.Net中，我们可以使用以下机制从连接字符串获取数据源:

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;

我试图在PowerShell中执行此操作，但出现以下异常:

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

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

新对象:使用"1"参数调用".ctor"的异常:关键字不支持:元数据".在线:1字符:17+ $ ConstringObj =新对象System.Data.SqlClient.SqlConnectionStringBuilder($ con ...+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~+ CategoryInfo:InvalidOperation:(:) [New-Object]，MethodInvocationException+ FullyQualifiedErrorId:ConstructorInvokedThrowException，Microsoft.PowerShell.Commands.NewObjectCommand

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

如何在PowerShell中做到这一点?

How to do that in PowerShell?

推荐答案

问题

使用在PowerShell中 SqlConnectionStringBuilder -让我解释一下

Problem

There's some weird behavior when using SqlConnectionStringBuilder in PowerShell - let me explain

由于它是一个dotnet类，因此您希望C#中提供所有相同的属性和方法

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

例如，这在C#中可以正常工作:

For example, this works fine in C#:

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

所以PS中的等效代码应该起作用:

So the equivalent code in PS, should work:

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

但是， SqlConnectionStringBuilder 是在 IDictionary 因此，从根本上讲，我们正在使用具有一些语法糖包装器的字典对象

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通过像这样(在这里简化)覆盖字典访问器和设置器:

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;
            // ***
        }
    }
}

实际上，它采用了 DataSource 属性并将其映射到"Data Source" 键^{(带有空格的 )}

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

每当PS分配或检索一个值时，它都必须决定是使用基础词典实现还是使用属性.当您在字典中查找 DataSource (不带空格)时，该

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.

您可以将括号或点符号与实际的sql键一起使用，以访问

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"

选择2-使用 PSBase

PSBase 返回 ，它将为我们提供dotnet中的默认行为

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"

选择3-使用 -Property 参数

在构建过程中，您可以设置 新对象上的 -Property 参数，其中设置属性值并调用新对象的方法."

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

其他阅读

在PowerShell中使用SQLConnection对象

这篇关于如何使用PowerShell从Connectionstring获取数据源?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！