dotnet / dotnet/SqlClient

TVP sourced from IDataReader/DbDataReader lose decimal precision.

Open
#924 5 comments 0 reactions 0 assignees View on GitHub
Repro Available :heavy_check_mark:
Dominant language
C#
Stars
989
Forks
340
Avg merge
4d 19h
Merged PRs (30d)
72

Description

### Describe the bug
Table value parameters can be sourced by three different types. `DataTable`, `DataReader`/`DbDataReader` or `IEnumerable`.

If you are using a DataReader approach that is not sourced from a SqlDataReader, then you have a chance to lose decimal precision.

The issue lies in the fact that when the tvp is being set up it calls the method `dataReader.GetSchemaTable()` and for each column uses the method, [MetadataUtilsSmi.SmiMetaDataFromSchemaTableRow](https://github.com/dotnet/SqlClient/blob/master/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/Server/MetadataUtilsSmi.cs#L719), many object based sources do not bother to populate the precision or scale columns and rely on default values.
Here are some examples from a quick perusal of popular libraries that create a dataReader from a non-sql query source:
[DataTableReader](https://source.dot.net/#System.Data.Common/System/Data/DataTable.cs,5299) - Never sets it.
[CsvDataReader](https://github.com/JoshClose/CsvHelper/blob/master/src/CsvHelper/CsvDataReader.cs#L445-L446) - sets it to db null.
[ObjectReader](https://github.com/mgravell/fast-member/blob/master/FastMember/ObjectReader.cs#L111) - actually throws an exception as its datareader doesn't have the column.

If the table source is a `decimal`, and no scale and precision is supplied, then it will use the [default of 18 and 0](https://github.com/dotnet/SqlClient/blob/master/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/Server/SmiMetaData.cs#L89).

What's worse if you're object based reader has an `INullable` source, it will throw an exception when used in a tvp as the tvp only expects sqldatareader to use `INullable`.

### To reproduce
See the following gist https://gist.github.com/mburbea/9092fcd0fb26da0f82d1ab320019d702

DisposableLocalDb is a nuget package you can download. It basically just creates a localdb and on dispose, drops it.
### Expected behavior

|Type | Pass?|
----------|-----------
|string| True|
|float |True|
|double|True|
|decimal|True|

### Actual behavior

|Type | Pass?|
----------|-----------
|string| True|
|float |True|
|double|True|
|**decimal**|**False**|

### Further technical details
Microsoft.Data.SqlClient version: 2.1.1 (found on the nuget or Microsoft.Data.SqlClient.dll)
.NET target: 5.03
SQL Server version: Irrelevant
Operating system: Windows 10 20H2.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.