When does SQL Server decimal NOT convert to .Net decimal?

Having the SSMS Tools Pack out in the wild enables me to get much “joy” from different bug reports people send me. And let me tell you, people send me back some seriously weird errors.

But the most unexpected error message I’ve seen so far was the OverflowException when calling System.Data.SqlClient.SqlDataReader.GetDecimal(Int32 i).

It turns out that SQL Server decimal data type is not mapped to the .Net decimal in it’s whole range. .Net decimal type only maps to SQL Server one in the decimal(29, X) range.

That means that if you have a decimal column that has the precision higher than 29 and no matter the scale, you won’t be able to use the native .Net data type.

So what to do? Let’s take a look with an example. The comments provide additional info.

SQL Code:

-- create a test table in tempdb with one valid and one invalid decimal mapping column.
CREATE TABLE TestTable
(
[ID] [INT] IDENTITY(1,1) NOT NULL,
[ValidDecVal] [DECIMAL](29, 2) NOT NULL,
[InvalidDecVal] [DECIMAL](30, 30) NOT NULL
)
GO

-- insert some data
INSERT INTO TestTable(ValidDecVal, InvalidDecVal)
-- both values are in correct range
SELECT 123456789012345678901234567.56, 0.123456789012345678901234567890

C# Code:

private void GetData()
{
using (SqlConnection conn = new SqlConnection(@"server=TestServer; database=tempdb; Integrated Security=SSPI;"))
{
using (SqlCommand cmd = new SqlCommand("SELECT ID, ValidDecVal, InvalidDecVal FROM TestTable", conn))
{
conn.Open();
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

rdr.Read();
// get the id
int id = rdr.GetInt32(0);
// get the 29 precision value just fine
decimal validDecimal = rdr.GetDecimal(1);
// this errors out ...
decimal invalidDecimal = rdr.GetDecimal(2);
// so does this ...
decimal invalidDecimal = rdr.GetSqlDecimal(2).Value;

// the only thing to do is to either pass around SqlDecimal class
SqlDecimal invalidDecimalAsSqlDecimal = rdr.GetSqlDecimal(2);
// or to cast it to string
string invalidDecimalAsString = invalidDecimalAsSqlDecimal.ToString();

// ... do something with upper values
Console.WriteLine(invalidDecimalAsSqlDecimal);
Console.WriteLine(invalidDecimalAsString);
}
}
}

If you’re working with large precision types I’d like to hear how you overcome this problem in .Net.

The only way I’ve found to deal with this is to either pass around the raw SqlDecimal data type or its string value.

A quick search revealed this Connect item that showed the problem with Linq2SQL. I don’t know why this isn’t fixed yet but I hope it will be soon. If you encounter this problem, vote it up.


Avtor: Anonymous, objavljeno na portalu SloDug.si (Arhiv)

Leave a comment

Please note that we won't show your email to others, or use it for sending unwanted emails. We will only use it to render your Gravatar image and to validate you as a real person.

HedaWhece
HedaWhece - sobota, 09. november 2024

Multivariable analysis of gynecological diseases <a href=https://fastpriligy.top/>priligy</a> PLoS ONE 16 6 e0246496