使用SqlDataReader读取Decimal值遇OverflowException问题求助

数据库列定义为Numeric(31, 17)，存储值888512650000.0通过SqlBulkCopy存入无异常，但使用SqlCommand结合SqlDataReader读取时抛出System.OverflowException: Conversion overflows异常。尝试GetDecimal()、GetString()均触发相同异常，但该值可通过decimal.Parse正常解析。

异常触发代码 #

using SqlConnection connection = new("connection_string");
await connection.OpenAsync();

using SqlCommand command = connection.CreateCommand();
command.CommandText = "SELECT ColName FROM Table";

using SqlDataReader reader = await command.ExecuteReaderAsync();

while (await reader.ReadAsync())
{
    var whatever = reader["ColName"]; // 异常抛出位置
}

完整异常信息 #

System.OverflowException: Conversion overflows.
at System.Data.SqlClient.SqlBuffer.get_Decimal()
at System.Data.SqlClient.SqlBuffer.get_Value()
at System.Data.SqlClient.SqlBuffer.get_String()
at System.Data.DataReaderExtensions.GetString(DbDataReader reader, String name)
at Program.$(String[] args) in C:\Users...\source\repos\Program.cs:line 30

1. 用GetSqlDecimal()读取后转换 #

C#的SqlDecimal类型支持最高38位精度，完全兼容数据库Numeric(31,17)的定义，不会触发溢出。读取后再转换为标准decimal即可：

using SqlConnection connection = new("connection_string");
await connection.OpenAsync();

using SqlCommand command = connection.CreateCommand();
command.CommandText = "SELECT ColName FROM Table";

using SqlDataReader reader = await command.ExecuteReaderAsync();

while (await reader.ReadAsync())
{
    SqlDecimal sqlDecimal = reader.GetSqlDecimal(reader.GetOrdinal("ColName"));
    decimal result = sqlDecimal.Value;
}

2. 查询时强制转换为字符串 #

修改SQL语句，将列转换为字符串后读取，再用decimal.Parse解析：

SELECT CONVERT(VARCHAR(50), ColName) AS ColName FROM Table

对应的C#代码：

using SqlConnection connection = new("connection_string");
await connection.OpenAsync();

using SqlCommand command = connection.CreateCommand();
command.CommandText = "SELECT CONVERT(VARCHAR(50), ColName) AS ColName FROM Table";

using SqlDataReader reader = await command.ExecuteReaderAsync();

while (await reader.ReadAsync())
{
    string valueStr = reader.GetString(reader.GetOrdinal("ColName"));
    decimal result = decimal.Parse(valueStr);
}

3. 验证数据库存储值 #

确认数据库中实际存储的数值是否存在隐形精度问题（比如超出C# decimal范围的隐藏小数位），可以通过以下SQL查询验证：

SELECT ColName, CAST(ColName AS VARCHAR(100)) FROM Table WHERE ColName = 888512650000.0

内容的提问来源于stack exchange，提问作者Piotr Wojsa