多环境生态系统的连接字符串管理器

• 下载 MySql8ConnectionString.zip - 1.9 KB
• 下载 ConnStringPOC_20210307.zip - 313.6 KB

引言

我在一个涉及四个“环境”的生态系统中编写代码，每个环境存在于两个“区域”之一中。对于不擅长数学的人来说，这意味着有八个环境，每个环境都连接到自己的数据库服务器。不那么重要的是，我们支持十几个 Web 应用程序、至少七个 Windows 服务以及运行在这些环境中的 Web 和数据库服务器上的多个桌面应用程序。目前，我们必须维护数十个包含数据库连接字符串的配置文件。

部署非常混乱，因为我们必须为每个应用程序/环境/区域组合创建一个部署包，而这样做会引入一个应用程序在一个环境/区域中工作，但在另一个环境中不工作的可能性。我不认为我需要告诉在座的各位，如果某事有可能出问题，那么某事最终确实会出问题，而且是在最糟糕的时候。这将导致部署在开发、测试、验收、生产生命周期中进行广泛的回归测试，从而极大地影响生活。

我们已经开始向云迁移，项目经理希望我们在所有环境中使用单个部署包，所以我们不得不向他解释，连接字符串包含在配置文件中这一事实使我们无法这样做。在那次讨论之后，我决定从我们旗舰项目的计划重写中提取一些代码，并进行修改/调整以支持我们的多环境/区域/应用程序生态系统。本文就是这项工作的结果。

该库旨在用于多环境系统（例如我所描述的系统），但它当然也可以在限制不那么严格的环境中使用，甚至可以用于需要连接到单个数据库服务器的单个桌面计算机。然而，本文 squarely 针对在这些多环境系统中编写代码的开发人员。

最后，这段代码（如所示）仅支持 MS SQL Server 连接字符串。如果您使用任何其他数据库系统，您可能只需要更改 PWConnectionString 类即可进行适应。我上次使用 MS SQL Server 以外的任何东西是在 2000-2001 年，所以我将无法在帮助您将其转换为其他数据库方面提供太多帮助。但是，我会尽我所能倾听那些需要执行此类转换的人的意见。如果您提供您的转换代码，我甚至可能会在其中（在 said 代码中适当地引用作者）进行补充。

注意： - 没有屏幕截图，因为此代码不需要界面即可实现。样本项目中有 WPF 应用程序，可以轻松生成加密的用户 ID 和密码，如果您需要/想要在 PWConnectionStringMgr 系统中使用该功能，但它不是实际实现代码所必需的。

特点

PWConnectionString 类实现了以下功能

• 更新 2021.03.06 - 支持 MS Sql Server、Oracle 和 PostgreSQL 连接字符串。只需为您的情况选择合适的连接字符串类即可。
   
• 支持无限数量的开发人员定义的可能环境。
   
• 支持所有应用程序类型（Web、桌面、Windows 服务）。
   
• 能够选择不进行数据混淆，此时连接字符串数据在静态和传输过程中均为纯文本。
   
• 在传输过程中使用 Base64 编码连接字符串的能力
   
• 在传输过程中使用 256 位 Rijndael 加密连接字符串的能力（这是默认行为）
   
• 允许每个环境中的应用程序通过回调方法定义其连接字符串。
   
• 允许在静态时加密用户 ID 和密码。这意味着您实际上可以从加密的用户 ID 和密码开始，这样它们就不会以纯文本形式出现在您的源代码中。
   
• 完全消除存储连接字符串的配置文件。
   

此代码的一个限制是它仅支持每个环境单个数据库服务器。如果您需要支持更多，您将必须在定义环境以及确定应用程序主机时发挥创意。一旦您深入研究本文，您应该很清楚创意必须发生在何处以及如何发生。

用法

在移除专有数据后，我得到了一段代码，它需要我 considered 到相当大的工作量才能在您自己的代码中实现该库。不幸的是，这是不可避免的，我们将在本节中逐步了解原因。其中一些步骤在描述方面可能非常复杂，因此在基本实现步骤列表之后会有子部分。下面显示的示例代码是从本文附带的代码中提取的。

0) 将 PWConnectionString 项目添加到您自己的解决方案中。当然，您可以将代码复制到您自己的公共程序集解决方案中，但仅使用提供的程序集可能更简单、更整洁。

1) 打开 PWConnectionStringMgr.cs 文件，并在 PopulateServerMap() 方法（下文详述）中添加必要的代码。

2) 在您的应用程序中，创建所需的回调方法，该方法指定该环境的应用程序连接字符串。

3) 在您的应用程序中，添加代码以确定主机名，并实例化 PWPWConnectionStringMgr 对象。

填充服务器映射（第 1 步）

该库的原理是，每个应用程序（无论是 Web 还是桌面）都可以独立识别其主机。Web 应用程序可以使用其 URL 的主机部分，桌面应用程序可以使用其 IP 地址、计算机名称和/或其 ActiveDirectory 域名称。该原理还扩展到假设给定的 URL 主机名或桌面计算机运行在一个定义明确且隔离的环境中。

为了真正使此代码有用，开发人员的职责是将其生态系统中可识别的环境硬编码。这是通过向 PopulateServerMap 方法添加代码来完成的。

private static void PopulateServerMap()
{
    // Environments are constrained by the host

    // Since we're using a HashSet, we can't possibly insert items with duplicate 
    // host names into the collection. 

    // if you encrypt your userid and pass, do not specify a passphrase. This 
    // will allow the encryption routine to use the DefaultSessionID. The reason 
    // is that we don't save the passphrase in the ServerMapItem object. 
    // Example below:
    string testuid = "John";
    string testpwd = "12345";
    //testuid = testuid.Encrypt();
    //testpwd = testpwd.Encrypt();
    PWConnectionStringMgr.ServerMap
                         .Add(new ServerMapItem("MARSVM", 
                                                "MARSVMDB", 
                                                Environment.Dev, 
                                                ClassLevel.Unclass, 
                                                testuid, 
                                                testpwd));
}

正如您从注释中可以看到的，服务器映射表示为 HashSet，这可以防止添加重复条目。这意味着您可以一次又一次地调用此方法，并使用上面的示例，您永远不会在具有主机名“MARSVM”的集合中拥有超过一个条目。请参阅下面的代码部分，了解 ServerMapItem 类的说明。

您可以根据需要添加任意数量的 ServerMapItem 项来定义您的可用环境

回调方法（第 2 步）

当管理器实例化时，它会调用您传递给管理器构造函数的类方法。此机制用于避免开发人员将应用程序特定的代码放入管理器类。这也有助于不必重新编译管理器类或冒着弄乱核心代码的风险。

回调方法实现到您的应用程序代码的某个位置。它可以是任何地方，但我喜欢将其放在实例化管理器的同一代码文件中（个人偏好）。您可以为该方法指定任何您喜欢的名称。它必须符合 PWConnectionStringMgr.cs 文件中定义的委托原型。

// Every app that uses the PWConnectionStringMgr class needs to provide a callback method that 
// will allow that app to define its own connection strings. This keeps this code from needing 
// significant changes when a new environment is added to the server map collection.
public delegate void CreateConnectionStringCallBack(string sessionID, 
                                                    ServerMapItem server, 
                                                    List<PWAbstractConnectionString> list);

示例应用程序实现其此方法的版本如下

public static void BuildMyConnectionStrings(string sessionID, ServerMapItem server, List<PWConnectionString> list)
{
	// To create a connection string object, do something like this:
	// if you want to encrypt uid/password at rest, it is advised that you use 
    // the default passphrase defined in the ExtendConnectionString class (this 
    // is done by calling the Encrypt method without specifying a passphrase. 
    // Of course, feel free to substitute this default value if desired.

    // the following simplify typing
	string theS = server.DbServer;
	string theU = server.TheU;
	string theP = server.TheP;
    // DO NOT encrypt the userID/password here. That's done in the 
    // PWConnectionStringMgr.PopulateServerMap method.

    // for the purposes of example, we're creating four connection strings that 
    // will allow connection to the indicated catalogs.
	list.Add(new MsSqlConnectionString("ConsoleApp1", theS, "MyDatabase", theU, theP, false, true, sessionID));
	list.Add(new MsSqlConnectionString("Users"      , theS, "Users"     , theU, theP, false, true, sessionID));
	list.Add(new MsSqlConnectionString("Common"     , theS, "Common"    , theU, theP, false, true, sessionID));
	list.Add(new MsSqlConnectionString("Invoices"   , theS, "Invoices"  , theU, theP, false, true, sessionID));
}

实例化连接字符串管理器（第 3 步）

在实例化管理器之前，您必须识别您的应用程序及其主机环境。

通常，对于从开发部署到测试、QA（验收）并最终部署到生产的 Web 应用程序，它们运行的服务器位于不同的域。因此，在每种情况下，您都有以下可能的（高度简化的）示例 URL

• https://mycompany.dev/myapp.aspx
• https://mycompany.test/myapp.aspx
• https://mycompany.staging/myapp.aspx
• https://mycompany.com/myapp.aspx

假定您使用类似以下内容填充了您的服务器映射。请注意，每个环境都有一个映射的服务器。

ClassLevel lvl = ClassLevel.Unclass;
ServerMap.Add(new ServerMapItem("nycompany.dev"    , "pcdbvmdev"    , Environment.Dev    , lvl, "dev_userid"  , "dev_password"  );
ServerMap.Add(new ServerMapItem("nycompany.test"   , "pcdbvmtest"   , Environment.Text   , lvl, "test_userid" , "test_password" );
ServerMap.Add(new ServerMapItem("nycompany.staging", "pcdbvmstaging", Environment.Staging, lvl, "stage_userid", "stage_password");
ServerMap.Add(new ServerMapItem("nycompany.com"    , "pcdbvmprod"   , Environment.Prod   , lvl, "prod_userid" , "prod_password" );

对于桌面应用程序，这可能有点棘手，但绝对可行。有许多方法可以确定主机标识，包括计算机名称、IP 地址，甚至 Active Directory 网络名称。您甚至可以为 Web 应用程序使用非应用程序特定的配置文件，其中包含某种面向环境的定义。识别信息的可能排列组合多种多样，我所能做的就是就如何处理这项任务提出建议。在示例中，本示例应用程序使用了计算机名称。

string host = System.Environment.MachineName;

实例化管理器的另一个先决条件是想出一个我委婉地称为“会话 ID”的东西。这个值实际上代表了用于加密/解密连接字符串的密码短语。我个人喜欢使用 GUID，因为每次生成一个，它都是唯一的，这使得加密更加随机且难以破解。

// the sessionID is used as the passphrase to encrypt/decrypt connection strings
string sessionID = Guid.NewGuid().ToString().Replace("-","").MangleSessionID();

您可能已经注意到了对 MangleSessionID() 的调用。此方法用于在大小写字母之间交替，然后可能反转字符串。这是一种过度的额外步骤，旨在使密码短语在任何给定时间点都更难辨认。这完全没有必要，但编码起来很有趣。您当然不必使用它。

最后，您终于可以真正实例化管理器了。

try
{
    // Note that creating an instance of the manager will throw an exception if the 
    // specified host isn't found in the server map (thus the encapsulating 
    // try/catch block).
    PWConnectionStringMgr connStrings = new PWConnectionStringMgr(sessionID, BuildMyConnectionStrings, host);

    // once the manager's connection string list has been created, you need to add it 
    // to your session variables (if you're writing a web app). It won't be 
    // too big of an object since most apps generally create fewer than 10 connection 
    // strings. It's recommended that you don't put this list into a static class if 
    // you're writing a web app because it will be visible to all requests by all 
    // users. 

    //-------------------------------------------------------
    // the following code is simply for testing for testing
    StringBuilder encodedText = new StringBuilder();
    StringBuilder decodedText = new StringBuilder();
    foreach(PWAbstractConnectionString connStr in connStrings)
    {
        encodedText.AppendLine(connStr.ConnectionString);
        decodedText.AppendLine(connStr.ConnectionStringDecoded);
    }
    Console.WriteLine("Encoded text:");
    Console.WriteLine(encodedText);
    Console.WriteLine();
    Console.WriteLine("Decoded text");
    Console.WriteLine(decodedText);
    Console.WriteLine();
    Console.WriteLine("Press a key...");
    Console.ReadKey();
}
catch (Exception ex)
{
    // avoid compiler warning, but allow inspection of ex if necessary
    if (ex != null) { }
    // why an exception would be thrown:
    // - the host you specified was not found in the server map. This 
    //   means you need to add the host to the server map, recompile, 
    //   and try again.
    // - You specified that the user id and password were encrypted 
    //   inside the connection string, but failed to encrypt those 
    //   items in connection string manager when you added a given 
    //   server map item. (this will generate a "salt not at least 
    //   8 characters" exception)
}

代码

这里的代码按依赖性递减顺序显示。这使您在看到使用这些对象的代码之前，可以熟悉这些对象。在大多数情况下，代码是高度注释的，因此我可以直接将其复制到本文中，让注释代替外部叙述文本，但如果您将其合并到自己的项目中，请随意删除您认为不需要的任何注释。

加密

本节中的代码无处不在，其必要性几乎等于零，除了告诉您它在项目中的位置以及实现类型之外，不需要叙述。

所有加密和编码代码都包含在 PWEncryption.cs 文件中。此代码使用的加密实现 Rijndael（256 位对称密钥）加密方案。加密/解密代码实现为一对字符串扩展方法。我的个人观点是，既然我们正在加密字符串，那么这样做是很有意义的。与加密代码一样，所有 Base64 编码代码也实现为一系列字符串扩展方法。

关于加密代码，您应该了解的一点是，扩展方法类包含一个名为 _DEFAULT_SESSION_ID_ 的常量。当您尝试加密字符串而不指定自己的密码短语时，将使用此值。这正是当您指定用户 ID 和密码应在静态时加密时所做的。

Web 应用程序注意事项：静态代码由所有请求共享，无论会话/用户如何。因此，_DEFAULT_SESSION_ID_ 被定义为常量，以避免代码更改其值。当然，桌面应用程序不受此行为的影响，但由于这是必要的“跨平台”代码，因此在所有平台上都保持约束。这并不能阻止您将值更改为您偏好的内容，但代码不应更改为允许在程序执行期间更改它。

.Net Core 注意事项：(2021.03.05) .Net Core 不支持指定块大小的功能，因此我们必须使用 128 位块。唯一的解决方法是使用 BouncyCastle 之类的东西，而我不想让这段代码依赖于外部 NuGet 包，所以对于 .Net Core 项目，它使用 128 位块。

using System;
using System.IO;
using System.Linq;
using System.Security.Cryptography;
using System.Text;

namespace PWConnectionStringManager
{

    public static class ExtendConnectionString
    {
        // Keep in mind that static data in a web app is shared by ALL requests, 
        // even if the request is from a different user. For this reason, you 
        // should not change this value from within the code (after it's 
        // compiled). 
        const string _DEFAULT_SESSION_ID_ = "D26B118F240D4552A9F9DD00A98EA333";

        /// <summary>
        /// Get/set the number of key bits for encryption (default is 256)
        /// </summary>
        public static int      Keysize              { get; set; }
        /// <summary>
        /// Get/set number of iterations for password byte generation method (default=1000)
        /// </summary>
        public static int      DerivationIterations { get; set; }

        // setting the default values
        static ExtendConnectionString()
        {
            ExtendConnectionString.Keysize              = 256;
            ExtendConnectionString.DerivationIterations = 1000;
        }

        // This code is probably over-the-top in terms of security. 
        /// <summary>
        /// Mangles the specified text by alternating upper/lower case and <br />
        /// then maybe reversing it
        /// </summary>
        /// <param name="text">The tetxt to "mangle"</param>
        /// <returns>The "mangled" string</returns>
        public static string MangleSessionID(this string text)
        {
            // generate a random number between 1 and 100 and then mod by 2. Result will 
            // be either 1 or 0. This will be where we start in the string with our 
            // alternating character case.
            int startPos = MakeRandomNumber(1,100) % 2;
            // we need to convert the text string to a char array
            char[] charText = text.ToArray();

            for (int i = 0; i < charText.Length; i++)
            {
                charText[i] = (i % 2 == startPos) ? char.ToUpper(charText[i]) : char.ToLower(charText[i]);
            }

            // put the text back into a string, and see if we should reverse it
            text = new string(charText);
            text = (startPos == 1) ? text.Reverse().ToString() : text;

            return text;
        }

        public static int MakeRandomNumber(int min, int max)
        {
            Random random = new Random();
            return random.Next(min, max);
        }

        /// <summary>
        /// Encode the specified text to base64. 
        /// </summary>
        /// <param name="text">The text to encode</param>
        /// <param name="encoding">What encoding to use for the text</param>
        /// <returns>If the text is null/empty, an empty string, otherwise the base4 encoded string.</returns>
        public static string Base64Encode(this string text, Encoding encoding = null)
        {
            string value = string.Empty;
            if (!string.IsNullOrEmpty(text))
            {
                encoding = (encoding == null) ? Encoding.UTF8 : encoding;
                byte[] bytes = encoding.GetBytes(text);
                value = Convert.ToBase64String(bytes);
            }
            return value;
        }

        /// <summary>
        /// Decode the specified text from a base64 value
        /// </summary>
        /// <param name="text">The text to encode<</param>
        /// <param name="encoding">What encoding to use for the text<</param>
        /// <returns>If the text is null/empty, an empty string is returned, If the text 

        /// is not a valid base64 string, the original text is returned. Otherwise the base4 

        /// decoded string.</returns>
        public static string Base64Decode(this string text, Encoding encoding = null)
        {
            string value = string.Empty;
            byte[] bytes;
            if (!string.IsNullOrEmpty(text))
            {
                encoding = (encoding == null) ? Encoding.UTF8 : encoding;
                try
                {
                    bytes = Convert.FromBase64String(text);
                    value = encoding.GetString(bytes);
                }
                catch (Exception)
                {
                    value = text;
                }
            }
            return value;
        }

        public static string Encrypt(this string plainText, string passPhrase = null)
        {
            // jms - added to avoid having to pass a passPhrase to encrypt/decrypt
            passPhrase = (string.IsNullOrEmpty(passPhrase)) ? _DEFAULT_SESSION_ID_ : passPhrase;

            // Salt and IV is randomly generated each time, but is preprended to encrypted cipher text
            // so that the same Salt and IV values can be used when decrypting.  
            var saltStringBytes = Generate256BitsOfRandomEntropy();
            var ivStringBytes   = Generate256BitsOfRandomEntropy();
            var plainTextBytes  = Encoding.UTF8.GetBytes(plainText);
            using (var password = new Rfc2898DeriveBytes(passPhrase, saltStringBytes, DerivationIterations))
            {
                var keyBytes = password.GetBytes(Keysize / 8);
                using (var symmetricKey = new RijndaelManaged())
                {
                    symmetricKey.BlockSize = 256;
                    symmetricKey.Mode      = CipherMode.CBC;
                    symmetricKey.Padding   = PaddingMode.PKCS7;
                    using (var encryptor = symmetricKey.CreateEncryptor(keyBytes, ivStringBytes))
                    {
                        using (var memoryStream = new MemoryStream())
                        {
                            using (var cryptoStream = new CryptoStream(memoryStream, encryptor, CryptoStreamMode.Write))
                            {
                                cryptoStream.Write(plainTextBytes, 0, plainTextBytes.Length);
                                cryptoStream.FlushFinalBlock();
                                // Create the final bytes as a concatenation of the random salt bytes, the 
                                // random iv bytes and the cipher bytes.
                                var cipherTextBytes = saltStringBytes;
                                cipherTextBytes     = cipherTextBytes.Concat(ivStringBytes).ToArray();
                                cipherTextBytes     = cipherTextBytes.Concat(memoryStream.ToArray()).ToArray();
                                memoryStream.Close();
                                cryptoStream.Close();
                                return Convert.ToBase64String(cipherTextBytes);
                            }
                        }
                    }
                }
            }
        }

        public static string Decrypt(this string cipherText, string passPhrase = null)
        {
            // jms - added to avoid having to pass a passPhrase to encrypt/decrypt
            passPhrase = (string.IsNullOrEmpty(passPhrase)) ? _DEFAULT_SESSION_ID_ : passPhrase;

            // Get the complete stream of bytes that represent:
            // [32 bytes of Salt] + [32 bytes of IV] + [n bytes of CipherText]
            var cipherTextBytesWithSaltAndIv = Convert.FromBase64String(cipherText);
            // Get the saltbytes by extracting the first 32 bytes from the supplied cipherText bytes.
            var saltStringBytes = cipherTextBytesWithSaltAndIv.Take(Keysize / 8).ToArray();
            // Get the IV bytes by extracting the next 32 bytes from the supplied cipherText bytes.
            var ivStringBytes = cipherTextBytesWithSaltAndIv.Skip(Keysize / 8).Take(Keysize / 8).ToArray();
            // Get the actual cipher text bytes by removing the first 64 bytes from the cipherText string.
            var cipherTextBytes = cipherTextBytesWithSaltAndIv.Skip((Keysize / 8) * 2)
                                                              .Take(cipherTextBytesWithSaltAndIv.Length - ((Keysize / 8) * 2))
                                                              .ToArray();

            using (var password = new Rfc2898DeriveBytes(passPhrase, saltStringBytes, DerivationIterations))
            {
                var keyBytes = password.GetBytes(Keysize / 8);
                using (var symmetricKey = new RijndaelManaged())
                {
                    symmetricKey.BlockSize = 256;
                    symmetricKey.Mode = CipherMode.CBC;
                    symmetricKey.Padding = PaddingMode.PKCS7;
                    using (var decryptor = symmetricKey.CreateDecryptor(keyBytes, ivStringBytes))
                    {
                        using (var memoryStream = new MemoryStream(cipherTextBytes))
                        {
                            using (var cryptoStream = new CryptoStream(memoryStream, decryptor, CryptoStreamMode.Read))
                            {
                                var plainTextBytes = new byte[cipherTextBytes.Length];
                                var decryptedByteCount = cryptoStream.Read(plainTextBytes, 0, plainTextBytes.Length);
                                memoryStream.Close();
                                cryptoStream.Close();
                                return Encoding.UTF8.GetString(plainTextBytes, 0, decryptedByteCount);
                            }
                        }
                    }
                }
            }
        }

        private static byte[] Generate256BitsOfRandomEntropy()
        {
            var randomBytes = new byte[32]; // 32 Bytes will give us 256 bits.
            using (var rngCsp = new RNGCryptoServiceProvider())
            {
                // Fill the array with cryptographically secure random bytes.
                rngCsp.GetBytes(randomBytes);
            }
            return randomBytes;
        }
    }

}

ServerMapItem

ServerMapItem 类允许我们定义服务器，而服务器又标识它们所处的环境。这里的理论是，一个给定的 Web 应用程序的 URL 在该 Web 应用程序运行在不同环境时是不同的，因此当它们在测试服务器或生产服务器上运行时，应该可以唯一地识别它们。

桌面应用程序没有 URL，因此您必须从不同的方向入手。如果您想依赖 .Net 框架提供的内容，有许多方法可以用来确定计算机名称或 IP 地址。事实上，您也可以将此方法用于 Web 应用程序。

唯一标识给定计算机的第三种方法是使用机器或根证书的序列号。这意味着给定网络上的服务器必须有一个机器证书，但请记住，证书有有限的生命周期，因此您必须获取新证书并在几年后更改您的代码以获取新证书的序列号。

我能想到的最后一种方法是机器配置文件，它根据机器所处的环境来标识机器。实际上，IMHO，这是最可靠的方法，因为文件总是存在的（或者至少应该如此），并且它总是包含预期的数据（或者至少应该如此）。然后，您可以编写一个通用方法来检索此文件中的数据，并在所有应用程序中使用该方法。唯一的诀窍是，您必须能够说服您的系统管理员在每台服务器上放置相应的文件。

更新 2021.03.06 - 当我添加对 Oracle 连接字符串的支持时，我必须向构造函数添加两个新参数来支持代理凭据。虽然它们是默认凭据，但您需要知道，如果您加密了数据库凭据，您也必须加密您的代理凭据。就静态凭据而言，它们要么全部加密，要么全部不加密。

// Represents a server item. This is used to determine the environment we're in when the 
// connection string list is instantiated. The ConnectionStringList populates a 
// self-contained collection of possible server items, and the calling web application 
// instantiates the PWCollectionStringMgr, passing in the host part of its URL. The 
// ServerMap collection is searched to discover the classification, environment, and 
// db server associated with the specified host. We derive from IEquatable to allow the 
// HashSet to identify duplicates the way we want them identified.
public class ServerMapItem : IEquatable<ServerMapItem>
{
    #region IEquality implementation

    // the code in this region allows us to make sure the item is unique 
    // before adding it tot the HashSet.

    public override int GetHashCode()
    {
        return this.Host.GetHashCode();
    }

    public bool Equals(ServerMapItem  otherItem)
    {
        return this.Host.ToLower() == otherItem.Host.ToLower();
    }

    #endregion IEquality implementation

    // The host name identifies the machine or web site that this item represents. 
    // This value must be unique because this item is added to a HashSet collection, 
    // which doesn't allow more than one item with a given host value. This value is 
    // case-sensitive, so "MyHost" and "myHost" are not considered duplicates.
    /// <summary>
    /// Get/set the "unique" host name for this server. 
    /// </summary>
    public string         Host           { get; set; }

    /// <summary>
    /// Get/set the instance name or IP address of the database instance
    /// </summary>
    public string         DbServer       { get; set; }

    /// <summary>
    /// Get/set the environment (dev/test/staging/prod) in which this host resides
    /// </summary>
    public Environment    Environment    { get; set; }

    // The Classification property is probably only applicable to government ecosystems, 
    // so commercial debvs will probably always use ClassLevel.Unclass. 
    /// <summary>
    /// Get/set the classification area (classified, unclassified) in which this host resides
    /// </summary>
    public ClassLevel     Classification { get; set; }

    // if the folllwoing properties are null/empy, the connection string will assume 
    // that the windows logon is to be used. Honestly, you should always use database 
    // credentials on publicly accessible servers.
    /// <summary>
    /// Get/set the user id to be used for the database.
    /// </summary>
    public string         TheU           { get; set; }
    /// <summary>
    /// Get/set the password to be used for the database.
    /// </summary>
    public string         TheP           { get; set; }

    public ServerMapItem(string host, string dbServer, Environment env, 
                            ClassLevel classLevel=ClassLevel.Unclass, 
                            string theU="", string theP=""
                            // only used for oracle connection strings
                            string theProxyU="", string theProxyP="")
    {
        this.Host           = host;
        this.DbServer       = dbServer;
        this.Environment    = env;
        this.Classification = classLevel;
        this.TheU           = theU;
        this.TheP           = theP;
    }

    // Shows the pertinent info when you're running under the debugger and want to 
    // inspect the HashSet that contains this item
    public override string ToString()
    {
        return string.Format("{0}/{1}   host={2}   db={3}", this.Environment.ToString(), this.Classification.ToString(), this.Host, this.DbServer);
    }
}

更新 2021.03.06 - PWAbstractConnectionString

如果您使用了此代码的先前版本，请仔细重新阅读本节。

在上一节标题中引用的更新中，我重构了连接字符串对象，使其成为抽象的。这使我能够为以下指定的数据库创建派生类 - MS Sql Server、Oracle 和 PostgreSQL。您应该知道，我对后两者几乎没有经验（我上次处理 Oracle 是在 2010 年，PostGre 是在 2001 年左右），我只是在网上搜索了它们的连接字符串属性。我也不愿意在我的机器上安装任何一个数据库系统来测试它们，所以在使用它们之前请检查它们以确保它们是正确的。

正如您可能猜到的，PWAbstractConnectionString 类是代码预期产品的基石。其想法是将连接字符串作为其组成部分（实例名称、目录、凭据等）存在，并在请求时才提供完整的连接字符串。可选地（默认情况下），组合的连接字符串以加密形式提供。我认识到此代码最有可能被重新集成到已运行的项目中，并且这些项目使用了实际的连接*字符串*，但如果您正在启动一个新解决方案，那么将 PWAbstractConnectionString 派生对象传递给您的 DAL 代码，并让它在调用代码完全不可见的情况下处理解密，这是没有理由的。

// If you're using entity framework, uncomment this line, and make sure you add
// the necessary assembly reference(s) to the project, and using statement(s) 
// to this file.
//#define _USING_ENTITY_FRAMEWORK_

using System;
using System.Text;

namespace PWConnectionStringManager
{
    ////////////////////////////////////////////////////////////////////////////////////////////////
    /// <summary>
    /// Implements the PWConnectionString object. This object contains the parameters necessary to 
    /// construct a connection string. 
    /// </summary>
    public class PWAbstractConnectionString
    {
        // You are advised to avoid Base64 encoding because a text string who's length 
        // is evenly divisible by 4 could be mishandled when encoding/decoding the 
        // string. IMHO, it's simply better to either encrypt the string, or don't 
        // encode it at all. This property is static so you only have to set it once. 
        // Default value is to encrypt.

        /// <summary>
        /// Get/set the string used as a key to encrypt the requested connection string 
        /// </summary>
        public string SessionID { get; set; }

        /// <summary>
        /// Get/set a flag indicating whether our connections encrypt traffic between <br />
        /// the db and the app
        /// </summary>
        public bool EncryptTraffic { get; set; }

        /// <summary>
        /// Get/set the name of this object (use this name to retrieve it later)
        /// </summary>
        public string Name { get; set; }

        /// <summary>
        /// Get/set the server instance name/ip
        /// </summary>
        public string Server { get; private set; }

        /// <summary>
        /// Get/set the name of the default catalog
        /// </summary>
        public string Database { get; private set; }

        /// <summary>
        /// Get/set the user id (if needed by the server)
        /// </summary>
        protected string UserID { get; set; }

        /// <summary>
        /// Get/set the password (if needed by the server)
        /// </summary>
        protected string Password { get; set; }

        /// <summary>
        /// Get/set a flag indicating whether the uid and pwd are encrypted
        /// </summary>
        protected bool UPAreEncoded { get; set; }

        #region virtual properties

        public virtual string ConnectionStringDecoded 
        { 
            get { return this.ConnDecode(this.ConnectionString, this.SessionID); } 
        }

        // override this if you want/need to do different validation
        protected virtual bool IsValid
        {
            get
            {
                //string value = string.Concat( this.Server,   ","
                //							 ,this.Database, ","
                //							 ,this.UserID,   ","
                //							 ,this.Password);
                //string[] parts = value.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
                //return (parts.Length >= 2);
                return (
                            !string.IsNullOrEmpty(this.Server) &&
                            !string.IsNullOrEmpty(this.Database) &&
                            (
                                (!string.IsNullOrEmpty(this.UserID) && !string.IsNullOrEmpty(this.Password)) ||
                                (string.IsNullOrEmpty(this.UserID)  && string.IsNullOrEmpty(this.Password))
                            )
                        );
            }
        }

        /// <summary>
        /// Get the appropriate credentials needed for the server (if any). This property is used 
        /// only when the connection string is being constructed for use. Override if you need 
        /// different handling
        /// </summary>
        protected virtual string Credentials
        {
            get
            {
                string value = "Integrated Security=";
                // If the userid OR password are not specified, we assume that we use the windows 
                // login
                if (string.IsNullOrEmpty(this.Password) && string.IsNullOrEmpty(this.UserID))
                {
                    value = string.Format("{0}true;", value);
                }
                else
                {
                    value = string.Format("{0}false; user id={1}; password={2};", 
                                          value, 
                                          (this.UPAreEncoded) ? this.UserID.Decrypt()   : this.UserID, 
                                          (this.UPAreEncoded) ? this.Password.Decrypt() : this.Password);
                }
                return value;
            }
        }

        /// <summary>
        /// Get the part of the connection string that indicates that we encrypt the traffic between 
        /// the database and the app. By default, this indicator should be true, and thus will NOT 
        /// return an empty/null string. Override if  you need different handling.
        /// </summary>
        protected virtual string WithEncryptedTraffic
        {
            get
            {
                string value = string.Empty;
                if (this.EncryptTraffic)
                {
                    value="Encrypt=true; TrustServerCertificate=true;";
                }
                return value;
            }
        }

        /// <summary>
        /// Get the server instance (and port, if specified)
        /// </summary>
        protected virtual string InstanceAndPort
        {
            get
            {
                return string.Format("Server={0}{1};", 
                                     this.Server, 
                                     (this.Port == null) ? string.Empty 
                                                         : string.Format(",{0}", this.Port));
            }
        }

        #endregion virtual properties

        #region abstract properties

        /// <summary>
        /// Get the connection string, (constructed and returned as a base64 or encrypted string).
        /// </summary>
        public abstract string ConnectionString { get; }

        #endregion abstract properties

        /// <summary>
        /// Abstract base class for all conntection string objects 
        /// </summary>
        /// <param name="name">The english name of the object</param>
        /// <param name="server">The server instance</param>
        /// <param name="database">The database to use</param>
        /// <param name="uid">The database uid</param>
        /// <param name="pwd">The database pwd</param>
        /// <param name="upAreEncoded">Flag indicating encoded status of credentials</param>
        /// <param name="encryptTraffic">Excrypt database traffic</param>
        /// <param name="sessionID">Connection string pass phrase</param>
        /// <param name="encodingType">How to encode the connection string</param>
        public PWAbstractConnectionString(string name, string server, string database, 
                                          string uid, string pwd, bool upAreEncoded, 
                                          bool encryptTraffic, string sessionID, 
                                          ConnEncodeType encodingType)
        {
            // sanity checks
            if (string.IsNullOrEmpty(name    )) { throw new ArgumentNullException("name"    ); }
            if (string.IsNullOrEmpty(server  )) { throw new ArgumentNullException("server"  ); }
            if (string.IsNullOrEmpty(database)) { throw new ArgumentNullException("database"); }

            this.Name           = name;
            this.Server         = server;
            this.Database       = database;
            this.UserID         = uid;
            this.Password       = pwd;
            this.UPAreEncoded   = upAreEncoded;
            this.EncryptTraffic = encryptTraffic;
            this.SessionID      = sessionID;
            // encoding type affects both the database credentials AND 
            // the proxy credentials - it's all or nothing
            this.EncodingType   = encodingType;
        }

        /// <summary>
        /// Override that returns the encoded (either base64 or encrypted) connection string
        /// </summary>
        /// <returns></returns>
        public override string ToString()
        {
            return this.ConnectionString;
        }

        // Format the specified connection string element
        protected string FormatValue(object value, string propertyName)
        {
            return (value == null) ? string.Empty : string.Format("{0}={1};", propertyName, value.ToString());
        }

        #region encoding/encrypting

        /// <summary>
        /// Encodes the string as specified, without considering default encoding type. <br />
        /// Use this overload for uid and pwd if you intend to encode those components <br />
        /// at rest.
        /// </summary>
        /// <param name="text">This string to encode</param>
        /// <param name="encodeType">How to encode the string</param>
        /// <param name="passPhrase">If encrypting, the passphrase that allows decryption</param>
        /// <returns>The encoded string</returns>
        public string ConnEncode(string text, ConnEncodeType encodeType, string passPhrase=null)
        {
            string value = text;
            switch (encodeType)
            {
                case ConnEncodeType.Base64  : value = value.Base64Encode(Encoding.UTF8); break;
                case ConnEncodeType.Encrypt : value = text.Encrypt(passPhrase); break; 
                default : break;
            }
            return value;
        }

        /// <summary>
        /// Encodes the string as Base64
        /// </summary>
        /// <param name="text">The text to encode</param>
        /// <param name="encoding">The text encoding to use. Default is UTF8</param>
        /// <returns>The base64 encoded string</returns>
        public string ConnEncode(string text, Encoding encoding)
        {
            return text.Base64Encode(encoding);
        }

        /// <summary>
        /// Encrypts string with the specified pass phrase (or the default pass phrase)
        /// </summary>
        /// <param name="text">The plain text string to encrypt</param>
        /// <param name="passPhrase">The pass phrase to use for the encryption. If empty, <br/>
        /// the default encryption passphrase is used</param>
        /// <returns>The encrypted string</returns>
        public string ConnEncode(string text, string passPhrase)
        {
            return text.Encrypt(passPhrase);
        }

        /// <summary>
        /// Decodes the string as specified, without considering default encoding type. <br />
        /// Use this overload for uid and pwd if you intend to encode those components <br />
        /// at rest.
        /// </summary>
        /// <param name="text">This string to decode</param>
        /// <param name="encodeType">How to encode the string</param>
        /// <param name="passPhrase">If encrypting, the passphrase that allows decryption</param>
        /// <returns>The decoded string</returns>
        public string ConnDecode(string text, ConnEncodeType encodeType, string passPhrase=null)
        {
            string value = text;
            switch (encodeType)
            {
                case ConnEncodeType.Base64  : value = value.Base64Decode(Encoding.UTF8); break;
                case ConnEncodeType.Encrypt : value = text.Decrypt(passPhrase); break; 
                default : break;
            }
            return value;
        }

        /// <summary>
        /// Decodes a Base64 encoded string
        /// </summary>
        /// <param name="text">The text to decode</param>
        /// <param name="encoding">The text encoding to use. Default is UTF8</param>
        /// <returns>The base64 decoded string</returns>
        public string ConnDecode(string text, Encoding encoding)
        {
            return (EncodingType == ConnEncodeType.Base64) ? text.Base64Decode(encoding) : text;
        }

        /// <summary>
        /// Decrypts string with the specified pass phrase (or the default pass phrase)
        /// </summary>
        /// <param name="text">The encrypted text string to encrypt</param>
        /// <param name="passPhrase">The pass phrase to use for the decryption. If empty, <br/>
        /// the default passphrase is used.</param>
        /// <returns>The decrypted string</returns>
        public string ConnDecode(string text, string passPhrase)
        {
            return (EncodingType == ConnEncodeType.Encrypt) ? text.Decrypt(passPhrase) : text;
        }

        #endregion encoding/encrypting

#if _USING_ENTITY_FRAMEWORK_
        /// <summary>
        /// Get the connection string with Entity Framework compatible meta data
        /// </summary>
        public string ConnectionStringWithMetadata
        {
            get { return this.BuildEntityConnectionString(this.Name, this.ConnectionString); }
        }

        /// <summary>
        /// Decorate the specified connection string to make it compatible with the ADO Data Entity 
        /// Model generated db contexts.
        /// </summary>
        /// <param name="name"></param>
        /// <param name="shortConnString"></param>
        /// <returns></returns>
        public string BuildEntityConnectionString(string name, string shortConnString)
        {
            // Specify the provider name, server and database. 
            string providerName = "System.Data.SqlClient";

            // Initialize the connection string builder for the 
            // underlying provider taking the short connection string.
            SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder(shortConnString);

            // Set the properties for the data source.
            //sqlBuilder.IntegratedSecurity = false;

            // Build the SqlConnection connection string. 
            string providerString = sqlBuilder.ToString();

            // Initialize the EntityConnectionStringBuilder.
            EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder();

            //Set the provider name.
            entityBuilder.Provider = providerName;

            // Set the provider-specific connection string.
            entityBuilder.ProviderConnectionString = providerString;

            // Set the Metadata location.
            entityBuilder.Metadata = String.Format("res://*/{0}{1}.Model{0}{1}.csdl|res://*/{0}{1}.Model{0}{1}.ssdl|res://*/{0}{1}.Model{0}{1}.msl", name, this.dbNamePostfix);
            return entityBuilder.ToString();
        }
#endif
    }
}

特定于数据库的连接字符串类

目前提供了三个特定于数据库的连接字符串类。我无意在我的开发盒上安装除 SQL Server 以外的任何东西，所以请注意，我对下面提到的任何外国数据库的可用性或适用性都未进行测试。

• MSSqlConnectionString - 此类用于相对较新（2012 及更高版本）的 Microsoft SQL Server 数据库。请注意，此类中没有 ODBC 提供程序或 LocalDB 支持，因为我个人（还）不需要那种支持。如果您确实需要，我建议您创建一个继承此类的新类，并向其中添加所需的 suupport。
   
• Oracle - 我不知道此代码支持哪个版本的 Oracle，因为我搜索了连接字符串的元素并将它们放入此类中。再说一遍，我除了验证参数确实在指定时被添加到连接字符串之外，没有进行任何测试。
   
• PostgreSQL - 将“PostgreSQL”替换为上一项中所有“Oracle”的实例，就完成了。 

这些连接字符串类的构造函数几乎相同，除了 Oracle 允许您指定代理凭据（用户 ID 和密码）。除此之外，它们都是相同的。

附加的“非标准”属性（支持给定数据库类型的专门功能，并且不包含在构造函数参数列表中）可以根据需要填充为自动属性，如下所示

list.Add(new MSSqlConnectionString("Users", theS, "Users", theU, theP, false, true, sessionID){Port=1444,Async=True...});

PWConnectionStringMgr

PWConnectionStringMgr 将上述所有内容整合到一个连贯的对象中，允许您实例化它，并获取适合应用程序运行环境的连接字符串。我假设您已经阅读了用法部分，所以我们在这里将看到管理器类中实际发生的情况。

using System;
using System.Collections.Generic;
using System.Linq;

namespace PWConnectionStringManager
{
    // Every app that uses the PWConnectionStringMgr class needs to provide a callback method that 
    // will allow that app to define its own connection strings. This keeps this code from needing 
    // significant changes whena new environment is added to the server map collection.
    public delegate void CreateConnectionStringCallBack(string sessionID, ServerMapItem server, List<PWAbstractConnectionString> list);

    ////////////////////////////////////////////////////////////////////////////////////////////////
    /// <summary>
    /// Implements the AefConnectionStringList object, which is a list of connection strings that 
    /// are required by the specified app. Only the connection strings needed by the app are 
    /// generated.
    /// 
    /// Keep in mind that if you're using Entity Framework, these connection strings are NOT 
    /// compatible because the metadata required by EF is not part of the connection strings 
    /// represented herein. Therefore, you will have to add code to your app in the form of a 
    /// partial class that extends this code, and add one or more properties that calls the 
    /// GetConnectionString() method (to get the actual connection string), and then adds the 
    /// required metadata.
    /// 
    /// There is a property in the PWConnectionString class that's commented out that makes 
    /// an attempt to add the metadata that you can copy/paste into your partial extension 
    /// class, and this will give you a decent starting point.
    /// </summary>
    public partial class PWConnectionStringMgr : List<PWAbstractConnectionString>
    {
        // HashSets don't allow duplicates entries (see the ServerMapItem). Since we're doing this, 
        // we can digest multiple calls to add items to the hashset without worrying about the 
        // collection growing larger every time we try to add to it.
        public static HashSet<ServerMapItem>  ServerMap = new HashSet<ServerMapItem>();

        // this callback method exists somewhere in your application code - NOT in this class
        /// <summary>
        /// Get/set the callback method for actually creating app-specific connection strings
        /// </summary>
        public CreateConnectionStringCallBack CreateMethod     { get; set; }

        // To avaoid confusion, ALL connection strings use the same pass phrase for encryption.
        /// <summary>
        /// Get/set the pass phrase used to encrypt the connection strings. 
        /// </summary>
        public string                         SessionID        { get; set; } 

        /// <summary>
        /// Get/set the discovered mapped server
        /// </summary>
        public ServerMapItem                  MappedServerItem { get; set; }

        /// <summary>
        /// Instantiates the list, and populates it based on the specified this.App.
        /// </summary>
        /// <param name="sessionID">The pass phrase for encrypting the connection strings</param>
        /// <param name="aefApp">The callback delegate for the method that creates the desired <br/>
        /// connection strings.</param>
        /// <param name="aefApp">The application this group of connection strings is for</param>
        /// <param name="encryptTraffic">Determines if traffic between db and app should be <br />
        /// encrypted (default value is true)</param>
        public PWConnectionStringMgr(string sessionID, CreateConnectionStringCallBack createMethod, string host)
        {

            // sanity checks
            if (string.IsNullOrEmpty(sessionID))
            {
                throw new ArgumentNullException("sessionID");
            }
            if (createMethod == null)
            {
                throw new ArgumentNullException("createMethod");
            }
            if (string.IsNullOrEmpty(host))
            {
                throw new ArgumentNullException("domain");
            }

            this.SessionID    = sessionID;
            this.CreateMethod = createMethod;

            PWConnectionStringMgr.PopulateServerMap();
            this.DetermineEnvironment(host);
            this.CreateMethod(this.SessionID, this.MappedServerItem, this);
        }

        /// <summary>
        /// Populate the server map with hard-coded data.
        /// </summary>
        private static void PopulateServerMap()
        {
            // Environments are constrained by the host

            // Since we're using a HashSet, we can't possibly insert items with duplicate 
            // host names into the collection. 

            // if you encrypt your userid and pass, do not specify a passphrase. This 
            // will allow the encryption routine to use the DefaultSessionID. The reason 
            // is that we don't save the passphrase in the ServerMapItem object. 
            // Example below:
            string testuid = "John";
            string testpwd = "12345";
            // if you want to test uid and pwd encryption
            //testuid = testuid.Encrypt();
            //testpwd = testpwd.Encrypt();
            PWConnectionStringMgr.ServerMap.Add(new ServerMapItem("MARSVM", "MARSVM", 
                                                                  Environment.Dev, ClassLevel.Unclass, 
                                                                  testuid, testpwd));
        }

        /// <summary>
        /// Attempt to find the specified host in the server map
        /// </summary>
        /// <param name="host">The host name to find</param>
        private void DetermineEnvironment(string host)
        {
            try
            {
                ServerMapItem server = ServerMap.First(x=>x.Host.ToLower() == host.ToLower()); 
                this.MappedServerItem = server;
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format("Server instance not found for domain \"{0}\"", host), ex);
            }
        }

        /// <summary>
        /// Get connection string object by connectionstring name
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public virtual PWConnectionString Get(string name)
        {
            // Sanity check - there must be a (connectionstring) name specified. 
            if (string.IsNullOrEmpty(name))
            {
                throw new ArgumentNullException("The name parameter must be specified.");
            }

            // get the first connection string object that matches our criteria
            PWAbstractConnectionString obj = this.FirstOrDefault(x => x.Name.ToUpper() == name.ToUpper());

            if (obj == null)
            {
                throw new InvalidOperationException("Specified connection string is not available.");
            }

            return obj;
        }

        /// <summary>
        /// Gets the string representation of the connection string. You shoud call this method 
        /// instead of accessing the property directly.
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public virtual string GetAsString(string name)
        {
            string value = string.Empty;
            // the Get method will throw any necessary exceptions.
            PWAbstractConnectionString obj = this.Get(name);
            if (obj != null)
            {
                value = obj.ConnectionString;
            }
            return value;
        }
    }
}

关注点

ServerMap.Add(new ServerMapItem("MARSVMDB", "MARSVMDB", Environment.Dev, ...));