DAAB 3.0 - Decimal out params.

Topics: Data Access Application Block
May 17, 2007 at 2:13 AM
Hi,

What’s the proper way to add a decimal out parameter and specify the scale and precision using the DAAB 3.0? I tried the following, but it returns “Decimal” in the parameter.value:

System.Data.SqlClient.SqlParameter tax = new System.Data.SqlClient.SqlParameter("@SalesTaxPercentage", DbType.Decimal);

tax.Direction = ParameterDirection.Output;
tax.Precision = 4;
tax.Scale = 2;
dbCommand.Parameters.Add(tax);


first I tried:

db.AddOutParameter(dbCommand, "@SalesTaxPercentage", DbType.Decimal, 0);


But it was rounding my decimal values. I also tried passing in various numbers for size.


Thanks,


-b
May 17, 2007 at 3:15 PM
I haven't tried it, but it looks like the Database Class has an AddParameter Method you can use when you need to be more explicit about your parameters:

AddParameter(DbCommand command, string name, DbType dbType, int size, ParameterDirection direction, bool nullable, byte precision, byte scale, string sourceColumn, DataRowVersion sourceVersion, object value);

Regards,

Dave

______________________________

David Hayden
Microsoft MVP C#
May 17, 2007 at 6:10 PM
that overload makes no sense for an out parameter.
May 18, 2007 at 12:11 PM
But this is the only way to do it!

db.AddParameter(cmd, "SalesTaxPercentage", DbType.Decimal, 0, ParameterDirection.Output, true, 4, 2, String.Empty, DataRowVersion.Default, DBNull.Value);

And it works fine.
May 18, 2007 at 3:11 PM
Thanks for verifying that, Benny.

It looked suitable for out parameters since it asked for the ParameterDirection in the method.

Regards,

Dave

___________________________

David Hayden
Microsoft MVP C#
May 18, 2007 at 6:26 PM
:(

I'm not sure i would use the word "suitable" to describe it. but as you said, it works.

P&P guys - you should really consider adding a better overload for this. Having to pass in sourceColumn, dataRowVersion, and value is just wonky.
May 18, 2007 at 6:52 PM
have you guys actually tested this? it doesn't error, but it's still rounding my decimals.

i tested my stored procedure in sql manager and it's return 8.6, and i'm using the following line to add the parameter:
db.AddParameter(dbCommand, "@SalesTaxPercentage", DbType.Decimal, 0, ParameterDirection.Output, true, 4, 2, "", DataRowVersion.Default, null);
but when i pull it out:
item.SalesTaxPercentage = SqlDataTypeUtil.NullCheckDecimal(dbCommand.Parameters"@SalesTaxPercentage".Value);
i stepped into the debugger to see what the value of "dbCommand.Parameters"@SalesTaxPercentage".Value" was and it was saying 9.

any ideas?
May 19, 2007 at 1:02 PM
Edited May 19, 2007 at 1:03 PM
Sorry, my fault!

DbType.Decimal is not the right datatype to use against SqldbType.Decimal

Use DbType.Double instead, and it works like a charm.

Edit:
db.AddParameter(cmd, "SalesTaxPercentage", DbType.Double, 0, ParameterDirection.Output, true, 4, 2, String.Empty, DataRowVersion.Default, DBNull.Value);

Benny/Norway
May 19, 2007 at 6:27 PM
Edited May 19, 2007 at 6:32 PM
Now the AddOutParameter works, as long as we are using the right kind of datatype!

db.AddOutParameter(dbCommand, "SalesTaxPercentage", DbType.Double, 0);

i recommend using the getter function avoiding the @

db.GetParameterValue(dbCommand, "SalesTaxPercentage");

with regards

Benny/Norway
May 19, 2007 at 9:03 PM
Edited May 19, 2007 at 9:04 PM
hhahaha... yup. double works.

this should probably get documented somewhere. since DbType.Decimal rounds. i can see some creepy bugs happening.
May 19, 2007 at 11:00 PM
Times like these I wish CodePlex had a KnowledgeBase so we could compile this stuff.

I will see about getting a WIKI Page so that answers like this don't get lost in the discussion area.

Regards,

Dave

_______________________________

David Hayden
Microsoft MVP C#