In one of my previous posts, I detailed using Table Value parameters. That method works great for my uses. However, I ran into one gotcha worth pointing out.
When creating the SqlMetaData for a given column in your SqlDataRecord, one must be mindful of Decimal types.
I was using this bit of code to create the SqlMetaData without record to type beyond simply specifying type:
var metaData = new SqlMetaData[] { new SqlMetaData(name, sqlType) };
The problem with Decimal types, though, is that the default precision is set to 18 and scale is set to 0. In practice, when this occurs, effectively, the portion of the number to the right of the decimal place is ignored/truncated. Ugh! This creates an obvious problem. The funny thing is that I swear I read somewhere in Microsoft’s documentation that precision and scale don’t have to be specified and are inferred by the actual numeric value.
Mitigating the default behavior, then, becomes a matter of being explicit about the precision and scale.
SqlMetaData meta = sqlType == SqlDbType.Decimal ?
new SqlMetaData(name, sqlType, 19, 4) :
new SqlMetaData(name, sqlType);