Skip to content

porting-cheat-sheet.md

Latest commit

 

History

History
58 lines (44 loc) · 4.24 KB

porting-cheat-sheet.md

File metadata and controls

58 lines (44 loc) · 4.24 KB

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

1 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.

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.