porting-cheat-sheet.md

Cheat sheet for porting from System.Data.SqlClient to Microsoft.Data.SqlClient

This guide is meant to cover all namespace changes needed in client applications when porting SqlClient references to Microsoft.Data.SqlClient:

Namespace Changes needed

Microsoft.Data.SqlClient v5.0 and newer

Namespace Change	Applicability
using System.Data.SqlClient; using Microsoft.Data.SqlClient;	Applicable to all classes, enums and delegates.
using Microsoft.SqlServer.Server; using Microsoft.Data.SqlClient.Server;	Applicable Classes: SqlDataRecord SqlMetaData 1 All remaining types continue to be referenced from Microsoft.SqlServer.Server namespace.
using System.Data.SqlTypes; using Microsoft.Data.SqlTypes;	Applicable Classes: SqlFileStream
using System.Data.Sql; using Microsoft.Data.Sql;	Applicable Classes: SqlNotificationRequest
using System.Data; using Microsoft.Data;	Applicable Classes: OperationAbortedException

¹ Breaking change for User-Defined types and Microsoft.SqlServer.Types support over Microsoft.Data.SqlClient v3.0.0.

Microsoft.Data.SqlClient v4.0 and older

Namespace Change	Applicability
using System.Data.SqlClient; using Microsoft.Data.SqlClient;	Applicable to all classes, enums and delegates.
using Microsoft.SqlServer.Server; using Microsoft.Data.SqlClient.Server;	Applicable Classes: InvalidUdtException SqlDataRecord SqlFunctionAttribute SqlMetaData SqlMethodAttribute SqlUserDefinedAggregateAttribute SqlUserDefinedTypeAttribute Applicable Interfaces: IBinarySerialize Applicable Enums: DataAccessKind Format SystemDataAccessKind
using System.Data.SqlTypes; using Microsoft.Data.SqlTypes;	Applicable Classes: SqlFileStream
using System.Data.Sql; using Microsoft.Data.Sql;	Applicable Classes: SqlNotificationRequest
using System.Data; using Microsoft.Data;	Applicable Classes: OperationAbortedException

Configuration

For .NET Framework projects it may be necessary to include the following in your App.config or Web.config file:

<configuration>
    ...
    <system.data>
        <DbProviderFactories>
            <add name="SqlClient Data Provider"
                invariant="Microsoft.Data.SqlClient"
                description=".Net Framework Data Provider for SqlServer" 
                type="Microsoft.Data.SqlClient.SqlClientFactory, Microsoft.Data.SqlClient" />
        </DbProviderFactories>
    </system.data>
    ...
</configuration>

Functionality Changes

System.Data.SqlClient	Microsoft.Data.SqlClient
Can use DateTime object as value for SqlParameter with type DbType.Time.	Must use TimeSpan object as value for SqlParameter with type DbType.Time.
Using DateTime object as value for SqlParameter with type DbType.Date would send date and time to SQL Server.	DateTime object's time components will be truncated when sent to SQL Server using DbType.Date.
Encrypt defaults to false.	Starting in v4.0, default encryption settings were made more secure, requiring opt-in to non-encrypted connections. Encrypt defaults to true and the driver will always validate the server certificate based on TrustServerCertificate. (Previously, server certificates would only be validated if Encrypt was also true.) If you need to turn off encryption, you must specify Encrypt=false. If you use encryption with a self-signed certificate on the server, you must specify TrustServerCertificate=true. In v5.0, SqlConnectionStringBuilder.Encrypt is no longer a bool. It's a SqlConnectionEncryptOption with multiple values to support Strict encryption mode (TDS 8.0). It uses implicit conversion operators to remain code-backwards compatible, but it was a binary breaking change, requiring a recompile of applications.

.NET Framework to .NET Considerations

In .NET Framework and .NET versions prior to .NET 5, globalization APIs use National Language Support (NLS) on Windows. Starting in .NET 5, .NET globalization APIs changed to use International Components for Unicode (ICU) on Windows in order to be consistent across all platforms (Windows, Linux, macOS, etc.). This affects the behavior of comparisons of some SqlStrings in System.Data.SqlTypes. Comparisons using ICU don't always match NLS for some strings. Since SQL Server still uses NLS on the server side for string comparisons, this difference can result in SqlString comparisons behaving differently than server side string comparisons. If your application relies on SqlString behavior matching server side behavior, you need to resolve this issue. For detailed information in .NET, see Globalization and ICU.

If your application is affected, the workaround is to Use NLS instead of ICU in your application.

Contribute to this Cheat Sheet

We would love the SqlClient community to help enhance this cheat sheet by contributing experiences and challenges faced when porting their applications.