如何在运行时修改SQL连接字符串并实现失败重试？

我用以下代码查询数据库：

string connString = "Data Source=ServerName;Initial Catalog=AdventureWorks;User id=UserName;Password=Secret;";    
SqlConnection conn = new SqlConnection();
SqlCommand cmd = new SqlCommand("select * from Orders", connString);
conn.Open();

问题在于服务器可能要求配置TrustServerCertificate和Encrypt参数，运行上述代码会抛出以下错误：

SqlException (0x80131904): A connection was successfully established with the server, but then an error occurred during the login process. 
(provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)

如果将连接字符串修改为以下内容，就能正常连接并执行查询：

"Data Source=ServerName;Initial Catalog=AdventureWorks;User id=UserName;Password=Secret;Encrypt=true;TrustServerCertificate=true"

我需要在运行时动态修改连接字符串，请问有没有巧妙的方法修改代码，实现捕获错误后自动使用修改后的连接字符串重试查询？

推荐实现方式：封装带针对性重试的数据库操作方法 #

可以借助SqlConnectionStringBuilder安全修改连接字符串（避免硬拼字符串引发的格式问题），同时只针对证书信任错误触发重试逻辑：

public static DataTable ExecuteQueryWithRetry(string originalConnString, string sqlCommand)
{
    // 构建重试用的连接字符串
    var retryConnBuilder = new SqlConnectionStringBuilder(originalConnString);
    retryConnBuilder.Encrypt = true;
    retryConnBuilder.TrustServerCertificate = true;
    string retryConnString = retryConnBuilder.ToString();

    // 证书不受信任错误对应的SQL错误码（0x80131904的十进制值）
    const int CertificateUntrustedErrorCode = -2146232060;

    // 第一次尝试原连接字符串
    try
    {
        using (var conn = new SqlConnection(originalConnString))
        using (var cmd = new SqlCommand(sqlCommand, conn))
        {
            conn.Open();
            var resultTable = new DataTable();
            resultTable.Load(cmd.ExecuteReader());
            return resultTable;
        }
    }
    // 仅捕获目标错误码的异常，触发重试
    catch (SqlException ex) when (ex.Number == CertificateUntrustedErrorCode)
    {
        using (var conn = new SqlConnection(retryConnString))
        using (var cmd = new SqlCommand(sqlCommand, conn))
        {
            conn.Open();
            var resultTable = new DataTable();
            resultTable.Load(cmd.ExecuteReader());
            return resultTable;
        }
    }
}

核心优势说明 #

• SqlConnectionStringBuilder：安全修改连接参数，自动处理重复键、格式规范等问题，比硬拼字符串更可靠
• 异常筛选器when：精准匹配目标错误，避免无关异常被错误重试
• using语句：自动释放数据库连接资源，防止连接泄漏
• 单次重试：针对该场景仅需一次重试即可解决问题，无需多余循环

内容的提问来源于stack exchange，提问作者Jennifer