SqlClient: System.Data.SqlClient (not Microsoft.Data) does not support DateTime2 in Batch Update/Insert anymore

Describe the bug

When utilizing the System.Data.SqlClient.SqlDataAdapter with UpdateBatchSize>1 in .Net 5 (or previous .Net Core Versions), DateTime2 Columns loose their precision (as if they were declared as DateTime Columns) This does not happen when executing the same code with .Net 4.8

To reproduce

See attached minimal repro sample (a simple .net 5 console app) A Sql Server is required on your system, but the DDL Code is included bulkinsertbug.zip

Build&Run the csproj with net48 and net5.0 frameworks Build&Run the code with UpdateBatchSize 1 and 2 Comment/Uncomment the CreateDbAndTablesIfNotExists method as needed

The result (viewable in Sql Server) will look like this:

bulkinsertbug In the screenshot you can see the Framework (Enviroment.Version) as well as the UpdateBatchSize for convenience You can clearly see the date values with lost precision in the described scenario (last 2 entries)

Expected behavior

The datetime precision is kept, even when UpdateBatchSize is >1

Further technical details

Microsoft.Data.SqlClient version: (found on the nuget or Microsoft.Data.SqlClient.dll) .NET target: Core 5.0.8, and Framework 48 (4.0.30319.42000) SQL Server version: SQL Server 2019, 13.0.15888.11 Operating system: Windows 20H2

Additional context We already analyzed the problem in detail and found the culprit to lie in the discrepancy how SqlParameter.CopyTo is implemented

.Net Framework Code: https://referencesource.microsoft.com/#System.Data/fx/src/data/System/Data/SqlClient/SqlParameter.cs => See SqlParameter.CloneHelper

.Net Core Code https://github.com/dotnet/corefx/blob/release/3.1/src/System.Data.SqlClient/src/System/Data/SqlClient/SqlParameterHelper.cs#L185 This code is responsible for the bug - why is the type name / db type not copied?

For good measure the new source (Microsoft.Data.*) https://github.com/dotnet/SqlClient/blob/ba2dbfed0e3a080caf9db9ca154ed4479a41cc53/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SqlParameter.cs#L999 Here it seems to be fixed again (e.g. type name is correctly copied)

Now we can’t upgrade to Microsoft.Data.* and instead need to use System.Data.* because we are still using EF6 with .net core

About this issue

Original URL
State: open
Created 3 years ago
Reactions: 2
Comments: 19 (12 by maintainers)

Most upvoted comments

If you want to proceed @cheenamalhotra @David-Engel please throw up a PR against https://github.com/dotnet/corefx/tree/release/3.1 with the usual template and the label servicing-consider.

danmoseley on Aug 6, 2021

@danmoseley @cheenamalhotra

To add a little history here, this was originally fixed through issue #67 (which was originally filed against SDS) in the MDS 1.0 preview timeframe (mid-2019). The code change is very small and has by now been pretty widely used in the MDS releases since then (plus NetFx). This would be a low-risk change in SDS, in my opinion:

Plus the corresponding test: https://github.com/dotnet/SqlClient/blob/v1.0.19239.1/src/Microsoft.Data.SqlClient/tests/ManualTests/SQL/ParameterTest/ParametersTest.cs#L103

As for cost, it would be minimal for the SqlClient team to submit the PR. I don’t know what the cost is on the runtime side for turning the release wheels for SDS, which hasn’t happened in about a year.

I would support back porting since the transparent loss of precision is not something someone would notice immediately and could very well be happening to more customers than we are aware of.

David-Engel on Aug 6, 2021