将ConnectionString从SqlConnectionSringBuilder传递给ConnectionMana

本文关键字：ConnectionMana SqlConnectionSringBuilder ConnectionString | 更新日期: 2023-09-27 18:15:31

我想从SqlConnectionStringBuilder的实例中获得ConnectionString作为String。

这看起来很简单，应该像这样简单:

String conString = builder.ConnectionString;

但是SqlConnectionStringBuilder放弃的String不包含Password字段/值。我猜这是某种安全功能，有没有办法强制Password包含在String中?

进一步看，我认为这可能与ConnectionManager有关。我要做的是修改ConnectionString的Package，改变Initial Catalog。

下面是我的代码，builder的连接字符串被传递回connectionManager的点密码丢失了…

    public void DataTransfer(String sourceConnection, String destConnection, String pkgLocation)
    {     
        Package pkg;
        Application app;
        DTSExecResult pkgResults;
        try
        {
            app = new Application();
            pkg = app.LoadPackage(pkgLocation, null);
            foreach (ConnectionManager connectionManager in pkg.Connections)
            {
                SqlConnectionStringBuilder builder;
                switch (connectionManager.Name)
                {
                    case "SourceConnection":
                        builder = new SqlConnectionStringBuilder(sourceConnection) { PersistSecurityInfo = true };
                        builder.Remove("Initial Catalog");
                        builder.Add("Initial Catalog", "StagingArea");
                        connectionManager.ConnectionString = builder.ConnectionString.ToString();
                        connectionManager.ConnectionString += ";Provider=SQLNCLI;Auto Translate=false;";
                        Debug.WriteLine(connectionManager.ConnectionString.ToString());
                        break;
                    case "DestinationConnection":
                        builder = new SqlConnectionStringBuilder(sourceConnection) { PersistSecurityInfo = true };
                        builder.Remove("Initial Catalog");
                        builder.Add("Initial Catalog", "StagingArea");
                        connectionManager.ConnectionString = builder.ConnectionString.ToString();
                        connectionManager.ConnectionString += ";Provider=SQLNCLI;Auto Translate=false;";
                        Debug.WriteLine(connectionManager.ConnectionString.ToString());
                        break;
                }
            }
            pkgResults = pkg.Execute();
        }
        catch (Exception e)
        {
            throw;
        }
        Console.WriteLine(pkgResults.ToString());
    }

将ConnectionString从SqlConnectionSringBuilder传递给ConnectionMana

在设置SqlConnectionStringBuilder中的其他属性之前，先将PersistSecurityInfo设置为True: D

我可能需要重命名这个，因为现在的问题是不同的，但这是我的解决方案:

@madd0正确回答了这个问题的第一部分。ConnectionString不包含Password字段。

第二部分是通过下面的格式化代码解决的:

public void DataTransfer(String sourceConnection, String destConnection, String pkgLocation)
{     
    Package pkg;
    Application app;
    DTSExecResult pkgResults;
    try
    {
        app = new Application();
        pkg = app.LoadPackage(pkgLocation, null);
        foreach (ConnectionManager connectionManager in pkg.Connections)
        {
            SqlConnectionStringBuilder builder;
            switch (connectionManager.Name)
            {
                case "SourceConnection":
                    builder = new SqlConnectionStringBuilder(sourceConnection) { PersistSecurityInfo = true };
                    builder.Remove("Initial Catalog");
                    builder.Add("Initial Catalog", "StagingArea");
                    var sourceCon = builder.ConnectionString + ";Provider=SQLNCLI;Auto Translate=false;";
                    //Added spaces to retain password!!!
                    sourceCon = sourceCon.Replace(";", "; ");
                    connectionManager.ConnectionString = sourceCon;
                    Debug.WriteLine(connectionManager.ConnectionString.ToString());
                    break;
                case "DestinationConnection":
                    builder = new SqlConnectionStringBuilder(sourceConnection) { PersistSecurityInfo = true };
                    builder.Remove("Initial Catalog");
                    builder.Add("Initial Catalog", "StagingArea");
                    var destCon = builder.ConnectionString + ";Provider=SQLNCLI;Auto Translate=false;";
                    //Added spaces to retain password!!!
                    destCon = destCon.Replace(";", "; ");
                    connectionManager.ConnectionString = destCon;
                    Debug.WriteLine(connectionManager.ConnectionString.ToString());
                    break;
            }
        }
        pkgResults = pkg.Execute();
    }
    catch (Exception e)
    {
        throw;
    }
    Console.WriteLine(pkgResults.ToString());
}

我玩了ConnectionString s，过了一会儿，我注意到原来的String在每个属性之间都有空格。

运行2个测试，我发现没有空格Password丢失了…

connectionManager.ConnectionString = destCon;

Test 1: No空格:

当destCon = Data Source=xxx.xxx.xxx.xxx;Initial Catalog=StagingArea;User ID=*****;Password=*****;Provider=SQLNCLI;Auto Translate=false;

则connectionManager.ConnectionString = Data Source=xxx.xxx.xxx.xxx;User ID=*****;Initial Catalog=StagingArea;Provider=SQLNCLI;Auto Translate=false;

Test 2:空格:

当destCon = Data Source=xxx.xxx.xxx.xxx; Initial Catalog=StagingArea; User ID=*****; Password=*****; Provider=SQLNCLI; Auto Translate=false;

则connectionManager.ConnectionString = Data Source=xxx.xxx.xxx.xxx; Initial Catalog=StagingArea; User ID=*****; Password=*****; Provider=SQLNCLI; Auto Translate=false;

不知道为什么会这样，但是没有空格，排序被调整，Password字段丢失。

相关文章：