Any plans on changing connections to use Microsoft.Data.SqlClient?

[dsmeltz]

There are some interesting features concerning Active Directory that Microsoft.Data.SqlClient brings with it. My particular scenario is creating a connection string with the newer Authentication keyword. This is used to support Active Directory Integrated, Active Directory Password and Sql Password authentication schemes.

I'm working on a .Net 5 Core project.

[sdrapkin]

I've been watching Microsoft.Data.SqlClient (MDS) project for a while. Some concerns/questions it raises:

• Should libraries switch from System.Data.SqlClient (SDS) to MDS? Supporting SDS only would limit some (niche) features, like the one you mentioned. Supporting MDS only commits one to MDS bugs and forgoes SDS stability built on years of work. Supporting both SDS and MDS adds complexity (maintaining, testing, releasing, supporting). Switching to an interface-based "provider" layer for SDS/MDS would cost some performance, and would also imply an implicit support for not only SDS/MDS, but also for any other implementation of the SqlClient interfaces, which would result in issues filed and expectations of support.
• Is MDS production-ready? This is highly opinionated, but the recent MDS hotfixes continue to indicate serious problems (ex. race conditions, doomed connections). At some point MDS will be mature enough to not exibit serious bugs, but it doesn't seem to be production ready yet.
• Many new niche features of MDS require external dependencies to test. Ex. how would I test AD-auth on my laptop against Azure service account if I don't want to run/pay for AD in Azure? Do I even need to test this, or should I just assume that these MDS features work? When someone complains that they can't get AD-auth working with TinyORM, how can I replicate the problem or run their code-sample without having to setup the same external dependency/service?

One approach is to wait for MDS to become mature enough to rebase to MDM entirely and never look back. However there are still many perfectly valid users and usecases committed to .NET 4.x which will need to be supported forever with SDS. Using MDS is not a guarantee that SDS will not appear as a dependency in some 3rd-party component. The key purpose of SqlClient is that there is only one SqlClient, which can do ex. connection-pooling. .NET cannot drop SDS namespaces, so having both SDS and MDS would fragment connection-pools. The bold move for MS would have been to merge MDS into SDS and break APIs for latest versions of .NET. However that didn't happen. Making an explicit MDS dependency is also weird when the majority of usecases are content with SDS functionality and merely want a quick "Hello World" example to run. SDS rarely changes, rarely has serious bugs, and is unlikely to break TinyORM, which cannot be said about MDS.

[dsmeltz]

Is there any way that the SqlClient could be overridden by the caller? Possibly via IDbConnection? That way the caller could just use the SDS by default or use provide a client of their own choosing.

[sdrapkin]

I have a few not-very-strong arguments against IDb* changes:

• IDb* is not granular enough to indicate Sql* compatibility - ie. any other provider will compile but likely fail at runtime.
• There are very minute performance benefits of direct Sql* non-interface binding. While they are tiny, one of the reasons many users select TinyORM is superior performance.
• It will lead to potential API-breaking changes for existing users, who expect Sql* in existing codebases.
• It will cause all features to adhere to the lowest common denominator (APIs on IDb*), instead of ex. taking advantage of some APIs that are specific/unique to Sql*.

APIs are always the hardest thing to get right (not claiming I did). Especially as time goes by and platforms evolve.

[sdrapkin]

TinyORM v2.0.0 has been released, which has switched to Microsoft.Data.SqlClient.
https://techcommunity.microsoft.com/blog/sqlserver/announcement-system-data-sqlclient-package-is-now-deprecated/4227205