Why does GetSqlDecimal throw when GetDecimal doesn't?

Question

I have a database table that has a column of type money, allowing nulls. Using a SqlDataReader named reader, I can do

decimal d = reader.GetDecimal(1);

which works, unless of course we're reading a null. If I try using SqlDecimal instead--and I thought the whole point of the SqlTypes was to deal with nulls--then I get an invalid cast, whether or not the value is null.

SqlDecimal s = reader.GetSqlDecimal(1);  // throws an invalid cast exception

What am I doing wrong? Do I really have to write a conditional statement to shepherd the value from the database to a SqlDecimal variable?

Answer 1

I bet your value fits in a Decimal but not in a SqlDecimal.

SqlDecimal has different underlying data structures from its corresponding .NET Framework Decimal data type.

http://msdn.microsoft.com/en-us/library/system.data.sqltypes.sqldecimal.aspx

Answer 2

From the documents:

No conversions are performed; therefore, the data retrieved must already be a decimal value, or an exception is generated.

My suspicion is that the value in the column is already a decimal, not sqldecimal, because you are using money (which is 18,4, i think), and not the full 38 precision decimal. Does this happen if you make a straight SqlDecimal with full precision?

Since you are using money, a regular decimal should be sufficient. And in that case, since decimal is lower "precision" in a way, you aren't losing anything. Life will be easier for all your downstream code too.

Or you can do the conversion like this:

SqlDecimal s = new SqlDecimal(reader.GetDecimal(1));