I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 218, comments - 2281, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

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.

Print | posted on Tuesday, August 31, 2010 2:11 PM |

Feedback

Gravatar

# re: When does SQL Server decimal NOT convert to .Net decimal?

What happens when one uses System.Numerics.Complex? I don't have VS2010 on my machine yet so I can't test this right now, sorry.
8/31/2010 3:22 PM | AndrewJacksonZA
Gravatar

# re: When does SQL Server decimal NOT convert to .Net decimal?

don't know. I'm not on .net 4.0 yet.
8/31/2010 4:05 PM | mladenp
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET