ConnectionString - connectionStringBuilder - Keys/Synonyms Centralized stuff

[Maleaume]

I saw some comments about centralized stuff

SqlClient/src/Microsoft.Data.SqlClient/src/Microsoft/Data/SqlClient/SqlConnectionStringBuilder.cs

Line 193 in be62e90

// @TODO: Ok, I think we should consider centralizing all these keywords into a single

// @todo: Ok, I think we should consider centralizing all these keywords into a single
// place. We have DbConnectionString*, Keywords, etc.
// Can we consider something DbConnectionOptions as backing store for the values
// and both SqlConnectionStringBuilder and SqlConnectionString use it to store?

one idea that can also be generic for any provider is the following:

keep DbConnectionStringSynonyms and DbConnectionStringKeywords for constants definition.

Create Attributes:

    /// <summary>
    /// Defines the mapping between logical enum key and actual connection string key.
    /// </summary>
    [AttributeUsage(AttributeTargets.Field)]
    public sealed class ConnectionKeyAttribute(string name, params string[] synonyms) : Attribute
    {
        public string Name { get; } = name;
        public HashSet<string> Synonyms = [.. synonyms];
    }

The enum and associated keys/Synonyms

 /// <summary>
 /// Enumerates supported SQL Server connection string keys.
 /// </summary>
 public enum SqlConnectionKeys
 {
     [ConnectionKey(DbConnectionStringKeywords.ApplicationIntent, DbConnectionStringSynonyms.ApplicationIntent)]
     ApplicationIntent,
     [ConnectionKey(DbConnectionStringKeywords.ApplicationName, DbConnectionStringSynonyms.App)]
     ApplicationName,
     [ConnectionKey(DbConnectionStringKeywords.AttachDbFilename, DbConnectionStringSynonyms.ExtendedProperties, DbConnectionStringSynonyms.InitialFileName)]
     AttachDbFilename,
     [ConnectionKey(DbConnectionStringKeywords.AttestationProtocol)]
     AttestationProtocol,
     [ConnectionKey(DbConnectionStringKeywords.Authentication)]
     Authentication,
     [ConnectionKey(DbConnectionStringKeywords.ColumnEncryptionSetting)]
     ColumnEncryptionSetting,
     [ConnectionKey(DbConnectionStringKeywords.CommandTimeout)]
     CommandTimeout,
     [ConnectionKey(DbConnectionStringKeywords.ConnectRetryCount, DbConnectionStringSynonyms.ConnectRetryCount)]
     ConnectRetryCount,
     [ConnectionKey(DbConnectionStringKeywords.ConnectRetryInterval, DbConnectionStringSynonyms.ConnectRetryInterval)]
     ConnectRetryInterval,
     [ConnectionKey(DbConnectionStringKeywords.ConnectTimeout, DbConnectionStringSynonyms.ConnectionTimeout, DbConnectionStringSynonyms.Timeout)]
     ConnectTimeout,
     [ConnectionKey(DbConnectionStringKeywords.ContextConnection)]
     ContextConnection,
     [ConnectionKey(DbConnectionStringKeywords.CurrentLanguage, DbConnectionStringSynonyms.Language)]
     CurrentLanguage,
     [ConnectionKey(DbConnectionStringKeywords.DataSource, DbConnectionStringSynonyms.Addr, DbConnectionStringSynonyms.Address, DbConnectionStringSynonyms.NetworkAddress, DbConnectionStringSynonyms.Server)]
     DataSource,
     [ConnectionKey(DbConnectionStringKeywords.EnclaveAttestationUrl)]
     EnclaveAttestationUrl,
     [ConnectionKey(DbConnectionStringKeywords.Encrypt)]
     Encrypt,
     [ConnectionKey(DbConnectionStringKeywords.Enlist)]
     Enlist,
     [ConnectionKey(DbConnectionStringKeywords.FailoverPartner)]
     FailoverPartner,
     [ConnectionKey(DbConnectionStringKeywords.FailoverPartnerSpn, DbConnectionStringSynonyms.FailoverPartnerSpn)]
     FailoverPartnerSpn,
     [ConnectionKey(DbConnectionStringKeywords.HostNameInCertificate, DbConnectionStringSynonyms.HostNameInCertificate)]
     HostNameInCertificate,
     [ConnectionKey(DbConnectionStringKeywords.InitialCatalog, DbConnectionStringSynonyms.Database)]
     InitialCatalog,
     [ConnectionKey(DbConnectionStringKeywords.IntegratedSecurity, DbConnectionStringSynonyms.TrustedConnection)]
     IntegratedSecurity,
     [ConnectionKey(DbConnectionStringKeywords.IpAddressPreference, DbConnectionStringSynonyms.IpAddressPreference)]
     IpAddressPreference,
     [ConnectionKey(DbConnectionStringKeywords.LoadBalanceTimeout, DbConnectionStringSynonyms.ConnectionLifetime)]
     LoadBalanceTimeout,
     [ConnectionKey(DbConnectionStringKeywords.MultipleActiveResultSets, DbConnectionStringSynonyms.MultipleActiveResultSets)]
     MultipleActiveResultSets,
     [ConnectionKey(DbConnectionStringKeywords.MaxPoolSize)]
     MaxPoolSize,
     [ConnectionKey(DbConnectionStringKeywords.MinPoolSize)]
     MinPoolSize,
     [ConnectionKey(DbConnectionStringKeywords.MultiSubnetFailover, DbConnectionStringSynonyms.MultiSubnetFailover)]
     MultiSubnetFailover,
     [ConnectionKey(DbConnectionStringKeywords.PacketSize)]
     PacketSize,
     [ConnectionKey(DbConnectionStringKeywords.Password, DbConnectionStringSynonyms.Pwd)]
     Password,
     [ConnectionKey(DbConnectionStringKeywords.PersistSecurityInfo, DbConnectionStringSynonyms.PersistSecurityInfo)]
     PersistSecurityInfo,
     [ConnectionKey(DbConnectionStringKeywords.Pooling)]
     Pooling,
     [ConnectionKey(DbConnectionStringKeywords.PoolBlockingPeriod, DbConnectionStringSynonyms.PoolBlockingPeriod)]
     PoolBlockingPeriod,
     [ConnectionKey(DbConnectionStringKeywords.Replication)]
     Replication,
     [ConnectionKey(DbConnectionStringKeywords.ServerCertificate, DbConnectionStringSynonyms.ServerCertificate)]
     ServerCertificate,
     [ConnectionKey(DbConnectionStringKeywords.ServerSpn, DbConnectionStringSynonyms.ServerSpn)]
     ServerSpn,
     [ConnectionKey(DbConnectionStringKeywords.TrustServerCertificate, DbConnectionStringSynonyms.TrustServerCertificate)]
     TrustServerCertificate,
     [ConnectionKey(DbConnectionStringKeywords.TransactionBinding)]
     TransactionBinding,
     [ConnectionKey(DbConnectionStringKeywords.TypeSystemVersion)]
     TypeSystemVersion,
     [ConnectionKey(DbConnectionStringKeywords.UserId, DbConnectionStringSynonyms.Uid, DbConnectionStringSynonyms.User)]
     UserId,
     [ConnectionKey(DbConnectionStringKeywords.UserInstance)]
     UserInstance,
     [ConnectionKey(DbConnectionStringKeywords.WorkstationId, DbConnectionStringSynonyms.WsId)]
     WorkstationId
 }

Class to manage Keys and normalize

this class host

• mapping between all keys/Synonyms => enum Keys;
• mapping between enumKey and normalize Key (string)
• by this way an easy way to normalize a connection string by replacing synonyms by official key (string).

 /// <summary>
 /// Abstract base class for type-safe connection string management.
 /// </summary>
 public abstract class ConnectionStringBase<TKey>
     where TKey : struct, Enum
 {
     protected static readonly IReadOnlySet<string> NormalizeAliases = InitializeNormalizedAlias();
     protected static readonly IReadOnlyDictionary<string, TKey> AliasToKey = InitializeAliasToKeyDictionary();
     protected static readonly IReadOnlyDictionary<TKey, string> KeyToAlias = InitializeKeyToAliasDictionary();

     #region Initialize dictionary
     private static HashSet<string> InitializeNormalizedAlias()
          => [.. Enum<TKey>.GetAttributes<ConnectionKeyAttribute>().Select(a => a.Name)];

     private static Dictionary<string, TKey> InitializeAliasToKeyDictionary()
         => new(Enum<TKey>
                     .GetEnumAttributePairs<ConnectionKeyAttribute>()
                     .SelectMany(pairs =>
                     {
                         var allAlias = pairs.Value.Synonyms;
                         allAlias.Add(pairs.Value.Name);

                         return allAlias.Select(a => KeyValuePair.Create(a, pairs.Key));
                     }));


     private static Dictionary<TKey, string> InitializeKeyToAliasDictionary()
         => Enum<TKey>
                 .GetEnumAttributePairs<ConnectionKeyAttribute>()
                 .ToDictionary(x => x.Key, x => x.Value.Name);
     #endregion
     protected string NormalizeAlias(string incomingAlias)
     {
         try
         {
             return NormalizeAliases.Contains(incomingAlias) ? incomingAlias : KeyToAlias[AliasToKey[incomingAlias]];
         }
         catch (KeyNotFoundException)
         {
             IoC.Instance.Resolve<IConsoleLogger>().Error($"this attributes '{incomingAlias}' is not an official key to construct connection string");
             return string.Empty;
         }
     }
   
 }

[benrr101]

Hi @Maleaume - I'm really sorry I haven't gotten back to you on this. I want to work on this, but I keep getting stuck on other goals that keep me from getting to it. Please know I haven't forgotten about it, and I do hope to address it at some point.

I put those comments in while I was flattening some classes earlier. The idea I had in mind was to have a list of objects that represent the keys in the connection string. It has a canonical key, and a list of synonyms. Maybe helpers for validating and setting it in the builder. It's all very vague ideas.