Checking if SQL Connection String specifies a parameter

user_689923 0
Viewed 39

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;
system.data.sqlclient connection-string c#
1 Answers

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

user_148689970
Related