dotnet / SqlClient

Microsoft.Data.SqlClient provides database connectivity to SQL Server for .NET applications.
MIT License
860 stars 288 forks source link

Feature Request: Have ConnectionString property use backward compatible keywords without spaces #2974

Open deadlydog opened 2 weeks ago

deadlydog commented 2 weeks ago

Is your feature request related to a problem? Please describe.

Connection strings returned from the SqlConnectionStringBuilder are not always compatible with System.Data.SqlClient connection strings, even when they are using features supported by both. This is because the Microsoft.Data.SqlClient connection string introduced new aliases that are not supported by the System.Data.SqlClient connection string, such as Application Intent= instead of ApplicationIntent= (notice the space). There are many other keywords updated to include spaces that System.Data.SqlClient does not support.

This problem likely won't affect most apps, however, there are some scenarios where it will. For us, it affected our service-discovery service that other applications talk to in order to get the connection string they should use. Some of those other applications are still using System.Data.SqlClient, and thus the connection string returned by our service-discovery service was now invalid for those apps; they were receiving connection strings with Application Intent=ReadOnly in them instead of ApplicationIntent=ReadOnly, which is unsupported in System.Data.SqlClient.

When trying to use a Microsoft.Data.SqlClient connection string in an app connecting using System.Data.SqlClient, we received the following error:

Keyword not supported: 'application intent'

Describe the solution you'd like

It would be good if the SqlConnectionStringBuilder's .ConnectionString property used the backward compatible keywords, where applicable. For example, instead of returning a connection string with Application Intent= or Multi Subnet Failover= in it, it used ApplicationIntent= and MultiSubnetFailover= instead.

Describe alternatives you've considered

For now we've updated our service-discovery service to manually sanitize the connection string to use the backward compatible keywords, rather than just returning back the SqlConnectionStringBuilder.ConnectionString directly.

For this repo, if you choose to leave things as-is, we should at least add some documentation for this backward incompatibility.

Additional context

The new connection string format is a breaking change from System.Data.SqlClient. This seems like an easy way to restore backward compatibility.

deadlydog commented 2 weeks ago

I've created PR #2975 to mention the backward incompatibility in the docs for now, but it would still be nice to have SqlConnectionStringBuilder use the backward-compatible keywords by default.

samsharma2700 commented 2 weeks ago

@David-Engel Looking for your input on this.