Checking if SQL Connection String specifies a parameter

Question

I am looking for a robust method for checking in C# if a given SQL connection string explicitly specifies a certain parameter, e.g. "Encrypt=...". I tried parsing the string using SqlConnectionStringBuilder, expecting that ContainsKey() would tell me whether a key was specified but it's not working:

System.Data.SqlClient.SqlConnectionStringBuilder x = 
    new("Data Source=.;Initial Catalog=myDb;Integrated Security=True");

bool encryptSpecified = x.ContainsKey("Encrypt"); // returns true :(

---

Clarification

I should have clarified why I need to know whether the Encrypt parameter was specified explicitly. In the current version of Microsoft.Data.SqlClient, the default value of Encrypt is true, but before (in Sql.Data.SqlClient) it was false. Therefore, to ensure backwards compatibility in an application after upgrading to Microsoft.Data.SqlClient, I want to set the Encrypt parameter to false unless the user explicitly specified a value for it.

Solution

[Based on discussion with @Charlieface]

// important: *not* Microsoft.Data.SqlClient.SqlConnectionStringBuilder!
System.Data.SqlClient.SqlConnectionStringBuilder scsb = 
    new(connectionString); 
if (!scsb.Encrypted) scsb.Encrypted = false; // this will explicitly set Encrypt
connectionString = scsb.ConnectionString;

Answer 1

I ran into the same problem for the same reason. We have lots of SQL Server installs on small internal networks, so it's much preferred for us to keep the old default behavior of Encrypt=false. It's not worth the certificate hassles on all these machines just because Microsoft changed a default. In the rare cases where we have cloud-hosted databases, we'll explicitly configure the certificate on the server and set Encrypt=true.

I didn't want to keep using the old System.Data.SqlClient.SqlConnectionStringBuilder type (as the question's Solution suggests) since my Directory.Packages.props file no longer allows a reference to the System.Data.SqlClient package.

My workaround uses a simple regex:

using Microsoft.Data.SqlClient;
...

SqlConnectionStringBuilder builder = new(connectionString);
Regex containsEncrypt = CreateContainsEncryptRegex();
if (!containsEncrypt.IsMatch(connectionString))
{
    builder.Encrypt = false;
}
connectionString = builder.ConnectionString;

...
[GeneratedRegex(@"(?i)(^|;)\s*Encrypt\s*=", RegexOptions.Compiled)]
private static partial Regex CreateContainsEncryptRegex();