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

Tue Aug 31, 2010 by Mladen Prajdić in sql-server

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.

Legacy Comments


AndrewJacksonZA 2010-08-31	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.

mladenp 2010-08-31	re: When does SQL Server decimal NOT convert to .Net decimal? don't know. I'm not on .net 4.0 yet.