SqlConnectionStringBuilder deviation in behavior for MultipleActiveResultSets

[aarondandy]

Describe the bug

I am using SqlConnectionStringBuilder to generate my connection strings for different database connections at run-time. For some usages I have older System.Data code (DbUp in this case) that consumes these generated connection strings. When connection strings are generated by Microsoft.Data.SqlClient.SqlConnectionStringBuilder it adds extra spaces to MultipleActiveResultSets making it Multiple Active Result Sets which is not compatible with the older System.Data versions of the library.

Note: these stack traces are for System.Data.SqlClient.SqlConnectionStringBuilder and System.Data.SqlClient.SqlConnection to show the incompatibility

System.ArgumentException: Keyword not supported: 'multiple active result sets'.
   at System.Data.SqlClient.SqlConnectionStringBuilder.GetIndex(String keyword)
   at System.Data.SqlClient.SqlConnectionStringBuilder.set_Item(String keyword, Object value)
   at System.Data.Common.DbConnectionStringBuilder.set_ConnectionString(String value)
   at System.Data.SqlClient.SqlConnectionStringBuilder..ctor(String connectionString)

System.ArgumentException: Keyword not supported: 'multiple active result sets'.
   at System.Data.Common.DbConnectionOptions.ParseInternal(Dictionary`2 parsetable, String connectionString, Boolean buildChain, Dictionary`2 synonyms, Boolean firstKey)
   at System.Data.Common.DbConnectionOptions..ctor(String connectionString, Dictionary`2 synonyms)
   at System.Data.SqlClient.SqlConnectionString..ctor(String connectionString)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(DbConnectionPoolKey key, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions)
   at System.Data.SqlClient.SqlConnection.ConnectionString_Set(DbConnectionPoolKey key)
   at System.Data.SqlClient.SqlConnection.set_ConnectionString(String value)
   at System.Data.SqlClient.SqlConnection..ctor(String connectionString)

To reproduce

References:
System.Data.SqlClient 4.8.2
Microsoft.Data.SqlClient 2.1.0

using System;

namespace MarsConnectionString
{
    class Program
    {
        static void Main(string[] args)
        {
            const string connectionString = @"Data Source=localhost;PersistSecurityInfo=False;MultipleActiveResultSets=False;";

            var builderSystem = new System.Data.SqlClient.SqlConnectionStringBuilder(connectionString);
            Console.WriteLine(builderSystem.ConnectionString);
            // Expected: Data Source=localhost;Persist Security Info=False;MultipleActiveResultSets=False

            var builderMsft = new Microsoft.Data.SqlClient.SqlConnectionStringBuilder(connectionString);
            Console.WriteLine(builderMsft.ConnectionString);
            // Actual:   Data Source=localhost;Persist Security Info=False;Multiple Active Result Sets=False

            // OK
            new System.Data.SqlClient.SqlConnectionStringBuilder(builderSystem.ConnectionString);

            // OK
            new Microsoft.Data.SqlClient.SqlConnectionStringBuilder(builderMsft.ConnectionString);


            // exception: System.ArgumentException: Keyword not supported: 'multiple active result sets'.
            try
            {
                // System.Data can't handle a connection string made by Microsoft.Data
                new System.Data.SqlClient.SqlConnectionStringBuilder(builderMsft.ConnectionString);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }

            // exception: System.ArgumentException: 'Keyword not supported: 'multiple active result sets'.'
            try
            {
                // System.Data can't handle a connection string made by Microsoft.Data
                using var _ = new System.Data.SqlClient.SqlConnection(builderMsft.ConnectionString);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }
        }
    }
}

Expected behavior

I expect Microsoft.Data.SqlClient.SqlConnectionStringBuilder to produce a connection string where "MultipleActiveResultSets" does not have extra spaces added to it

Further technical details

Microsoft.Data.SqlClient version: 2.1.0
.NET target: netcoreapp3.1
SQL Server version: N/A
Operating system: Reproduced on Windows 19041.630 and Azure PaaS Linux Hosts

Additional context

Possibly related to #654

[cheenamalhotra]

Hi @aarondandy

Mixing drivers like that is not correct approach as they're different products.
Just like you cannot create a connection from 1 driver and pass on to another, or cast object from System.Data.SqlClient namespace to Microsoft.Data.SqlClient. They're different drivers.

If you want to provide connection string to System.Data.SqlClient, you need to fetch it from the same driver.

[aarondandy]

Workarounds

It'll do...

// from https://github.com/dotnet/SqlClient/blob/master/release-notes/2.0/2.0.0.md#new-connection-string-property-synonyms
private static readonly (string @new, string old)[] SqlPropertyRenames = new (string, string)[]
{
    ("Application Intent", "ApplicationIntent"),
    ("Connect Retry Count", "ConnectRetryCount"),
    ("Connect Retry Interval", "ConnectRetryInterval"),
    ("Pool Blocking Period", "PoolBlockingPeriod"),
    ("Multiple Active Result Sets", "MultipleActiveResultSets"),
    ("Multi Subnet Failover", "MultiSubnetFailover"),
    ("Transparent Network IP Resolution", "TransparentNetworkIPResolution"),
    ("Trust Server Certificate", "TrustServerCertificate")
};

public static string NormalizeToLegacyConnectionString(string connectionString)
{
    if (!string.IsNullOrWhiteSpace(connectionString))
    {
        foreach (var replacement in SqlPropertyRenames)
        {
            connectionString = connectionString.Replace(replacement.@new, replacement.old, StringComparison.OrdinalIgnoreCase);
        }
    }

    return connectionString;
}

[aarondandy]

Mixing drivers like that is not correct approach as they're different products.

I understand but this is still a reality for those of us using tools in this ecosystem and as the ecosystem makes this transition. Is there at least a way to get a list of connection string "keywords" that have changed between these libraries? If the burden of compatibility falls to users that would be a big help to us.

[cheenamalhotra]

Yes, they're documented here:
https://github.com/dotnet/SqlClient/blob/master/release-notes/2.0/2.0.0.md#new-connection-string-property-synonyms

You may use them for transition period!
The old names will continue to be supported by Microsoft.Data.SqlClient.

[aarondandy]

Awesome, thanks. I'll update my horrible workaround!

[cheenamalhotra]

Closing in light of above discussions.

[tjrobinson]

@cheenamalhotra Sorry, I don't follow how to use the workaround? Could you give an example?

[aarondandy]

Where you may have code similar to new System.Data.SqlClient.SqlConnection(connectionString) you may instead need to pass the connection string through the workaround above to format it as the legacy client expects. It may then look like new System.Data.SqlClient.SqlConnection(NormalizeToLegacyConnectionString(connectionString)). If your connection strings are in configuration files or variables it may be easier to just modify those values manually based on the list of "keywords" found here: https://github.com/dotnet/SqlClient/blob/master/release-notes/2.0/2.0.0.md#new-connection-string-property-synonyms

[tjrobinson]

@aarondandy Thanks, that makes sense now.