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();

Answer 2

ContainsKey is essentially useless, as it only tells you whether a key is supported, not whether the string actually contains it. If the string does not have that key then the default is used.

You can just use the Encrypt property to tell you what the value is. This will return false in cases when the default is false (this was changed to true in the new version of Microsoft.Data.SqlClient).

Moreover, this is more useful than just checking to see whether the string contains the key, as it tells you what value would actually be used at runtime.

var x = new SqlConnectionStringBuilder("Data Source=.;Initial Catalog=myDb;Integrated Security=True");
Console.WriteLine(x.ContainsKey("Encrypt"));

dotnetfiddle