How would you specify this:
Decimal(18,2)
In this:
SqlComm.Parameters.Add("@myValue", SqlDbType.Decimal, 0, "myValue");
Currently I have defined precision = 2 from the design side properties. I'm just curious as to how to accomplish this from the code. Thanks
There's not an overload of Add that lets you set the decimal precision inline, so you either need to create a SQlParameter object and add it to the collection:
SqlParameter param = new SqlParameter("@myValue", SqlDbType.Decimal);
param.SourceColumn = "myValue";
param.Precision = 18;
param.Scale = 2;
SqlComm.Parameters.Add(param);
or keep a reference to the parameter after adding it:
SqlParameter param = SqlComm.Parameters.Add("@myValue", SqlDbType.Decimal, 0, "myValue");
param.Precision = 18;
param.Scale = 2;
or using the parameter constructor:
SqlComm.Parameters.Add(new SqlParameter(
parameterName = "@myValue",
dbType = SqlDbType.Decimal,
precision = 18,
scale = 2,
sourceColumn = "myValue"));
SqlParameterCollection.Add returns the newly created parameter. SqlParameter param = SqlComm.Parameters.Add("@myValue", SqlDbType.Decimal, 0, "myValue");
Precision and Scale property prior to assign the value to the SqlParameter ?
My answer is not directly connected with the OP's question, but I've seen a lot of people asking "why set the precision since it is taken from the value".
It has to do with the way SQL Server works when comparing the decimal parameter with the text column. Imagine you have column named strNumberColumn witch is of nvarchar type. If you define a @var Decimal(1,0) = '1', comparison on the condition where strNumberColumn >= @var will work only as long, as the longest entry in that column is between "0" and "9". If any of the entries go beyond, for example "10" or "123" you will get an OverflowException while converting string to decimal. What is important, that conversion is made "behind the scenes".
Please, do not bring arguments like "if that column should contain numbers it should not be made nvarchar" - I totally agree, but that is beyond the scope of the problem (sometimes you work with a legacy system and you have no influence over the column datatype). The above example shows a real life scenario when defining precision is required in order for the query to run successfully despite having a smaller precision amount assigned to the variable (like @var Decimal(12,2) = '1.0').
var cmd = new SqlCommand()
SetDecimalParameter(cmd.Parameters.Add("@paramName", SqlDbType.Decimal), 18, 2).Value = 12.34;
SqlParameter SetDecimalParameter(SqlParameter parameter, byte precision, byte scale) {
parameter.Precision = precision;
parameter.Scale = scale;
return parameter;
}
Math.Roundor take the value as is and round in your SQL statement.