BT

Facilitating the Spread of Knowledge and Innovation in Professional Software Development

Write for InfoQ

Topics

Choose your language

InfoQ Homepage News Introducing Microsoft.Data.SqlClient

Introducing Microsoft.Data.SqlClient

Bookmarks

Continuing the effort to decouple Microsoft products from .NET Core itself, Microsoft is spinning off their SQL Server drivers into a separate deployment stream. This new package will be called Microsoft.Data.SqlClient and is intended to be a drop-in replacement for System.Data.SqlClient.

Background

When .NET was created, an important component was the System.Data framework. This generalized the way database drivers were written for .NET much in the same way that ActiveX Data Objects did for Visual Basic. Though the API itself was very different, they reused the name which is how we got the ADO.NET moniker.

A key difference between ADO and ADO.NET (i.e. System.Data) is the object model. In ADO, you usually only have to deal with the Connection, Command, and Recordset objects. Everything else was hidden by the OleDB/ODBC drivers. This allowed for great code reuse, but prevented developers from exposing database-specific features.

In ADO.NET you can use OleDB/ODBC, but most of the time you’ll instead use a collection of database-specific classes. These derive from DBConnection, DBCommand, and DBDataReader so code reuse is still possible. But since they are exposed as strongly named types, they need to be explicitly shipped as .NET libraries.

Probably in order to simplify development, the SQL Server, OleDB, and ODBC drivers were shipped in the same library as the System.Data framework itself. This decision was acceptable at the time, but is now causing problems with the SQL Server development cycle.

Essentially what’s happening is SQL Server has gone from a 3 to 5-year release cycle to nearly yearly releases. And these releases often require updating the .NET drivers. This can’t happen in a timely fashion if it’s tied to the .NET Standard release cycle.

The first step was to break up the System.Data library. This was done in .NET Core, giving us separate libraries for each database driver. The next step is to completely divorce the SQL Server driver from .NET Core/Standard. To do this, they’ve forked the library, creating Microsoft.Data.SqlClient.

Upgrading to Microsoft.Data.SqlClient

For most developers, using Microsoft.Data.SqlClient will be as simple as just changing the using statements at the top of each class. It otherwise uses the same class names and APIs and roughly offers the same features.

For lightweight ORMs such as Dapper or RepoDB, no further work should be necessary.

If a developer is using an ORM that manages its own connections (e,g, EF, NHibernate), they will have to wait until their ORM is upgraded. At which point, everything should continue to operate as expected.

The ones that are going to get into trouble are the ones that mix ORMs. If one ORM is using Microsoft.Data.SqlClient and the other System.Data.SqlClient, they’ll no longer work together. This is especially important when sharing a SqlTransaction object.

Availability

Version 1.0 of Microsoft.Data.SqlClient is available for these platforms:

  • .NET Framework 4.6+
  • .NET Core 2.1+
  • .NET Standard 2.0+.

Known Issues

Upgrading right away isn’t necessarily for everyone. The documentation notes these known issues:

  • User Data Types (UDTs) may not work with Microsoft.Data.SqlClient.
  • There is no key store provider for Azure Key Vault and Microsoft.Data.SqlClient at this time.
  • Always Encrypted with secure enclaves is not supported with Microsoft.Data.SqlClient.
  • Always Encrypted is only supported against .NET Framework and .NET Core targets. It is not supported against .NET Standard since .NET Standard is missing certain encryption dependencies.

For more information, see the Microsoft.Data.SqlClient FAQ.

Rate this Article

Adoption
Style

Hello stranger!

You need to Register an InfoQ account or or login to post comments. But there's so much more behind being registered.

Get the most out of the InfoQ experience.

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Community comments

  • Microsoft.Data.SqlClient - Schema API - GetSchema Method

    by Jerry Merchand,

    Your message is awaiting moderation. Thank you for participating in the discussion.

    I used a version of the System.Data.SqlClient which contained the Schema API - GetSchema() method. I used this to get the schema information for a schema named DataFix and I was able to get all of the stored procedures in this schema and then get all of the parameter data for each stored procedure in the list in this schema. I then was able to dynamically build a web UI to allow users to access the stored procedure they need to run from a web application. This application is old about 8-10 years old and was originally written using Silverlight 4/5. I need to rewrite this application and I'm planning to use .Net Standard 2.0 and Blazer or Razor Components using MVC framework. Will I have this schema API in the new Microsoft.Data.SqlClient?

    Btw, this was exactly what I was looking for in this article! I will be reading a lot more content from infoQ!

    Thanks in Advance! Jerry

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

BT