82
I am developing a project for commercial automation, using PAF-ECF and everything else.
Would Aual be the best type of data to work with SQL Server and C# for money issues? Mainly in relation to rounding and truncation.
I imagine the best options are Float, Numeric or Money. But I can’t find good reliable sources that fit my case so I can safely implement.
70
Friend, I leave here some of my experience, work with commercial automation systems using Microsoft SQL Server, and I strongly suggest that you use monetary fields with type DECIMAL.
In the company where I work we have already used floating point numbers for money, but however we had serious problems after the values are processed many times, mainly by modules such as Electronic Invoice, and even worse, of accounting and tax SPED.
With the DECIMAL type we have much more control and accuracy on the stored values.
I suggest these two readings for more details.
Just complementing the answer. In some cases, floating point numbers may be "evil". The explanation of the reason: Why floating point numbers (Float and Double) are inaccurate?
what decimal setting you recommend? I say size
36
Decimal. Never use floating point types (float, single, double, etc.)
Another known alternative is to store the values in cents, such as int's or long's. That is, instead of storing 12.34 (real) you would store 1234 (cents). This is only feasible if you work with only one currency because the number of decimal places varies from currency to currency.
Edited: In SQL Server I recommend Money and in C# decimal.
By the way, there’s a good list on this MSDN page.
It is a fact that binary floating comma (like float) is a mistake to work with money. However, the question also asks numeric vs money and this answer does not explain why avoid the "money".
View this OS issue: http://stackoverflow.com/questions/582797/should-you-choose-the-money-or-decimalx-y-datatypes-in-sql-server
26
Utilize Decimal, because this type will represent descriptive values (i.e., exact values). Monetary values and absolute quantities are examples of this case;
Floating point types (e.g.: float) are used to represent continuous values (i.e., values representing a rapprochement, or that "tend to"). Examples of "real life" would be: weight, distance, height, etc.
Note that although your scale tells you that you weigh 80 kg (say), probably your actual weight will be one rapprochement (79.899875499 ....). In these cases the types of floating point fall well. Already with money, it will not be legal...
Withdrawn concept this video by Jon Skeet.
20
Never use floating point types such as float or double. The representation of these values follows the pattern IEEE_754 and were designed exclusively for scientific applications. Financial applications require absolute precision because in an accounting system, for example, no difference in a balance can be tolerated, even if it is a few cents in Billions of Reais, because it causes mistrust that would cause in the system (giving the impression of existence of error).
So you should always use decimal arithmetic provided by DECIMAL, Bigdecimal, etc. There is a implicit error when we use binary arithmetic and convert to decimal arithmetic (the one we learn at school) which occurs due to the difference of bases of the two numbering systems.
14
Recently completed a PAF-ECF application, from start to finish, and I have no problem with it. I used Money in the database (SQL Server), and in the management system and PAF-ECF (both in C#) I used Decimal. Everything is working perfectly. For the case of management systems and PAF-ECF, emphatically do not recommend the separation of pennies, which makes the treatment much more difficult, both in SGBD and in applications. In short, in my opinion, use Money in DBMS and Decimal in C#.
12
As recommended by Martin Fowler in his book Patterns of Enterprise Application Architecture you must use:
You should avoid using any type of floating point as this may cause rounding problems which is what you want to avoid. In the calculations you should always take into account the currency type.
-5
I suggest that code for that:
public class MOEDA
{
public int umavez = 0;
public string Calcular(string valor1, string valor2, int operacao)
{
string saida = "";
int total = 0;
int val1, val2;
valor1 = valor1.Replace("$", ""); valor2 = valor2.Replace("$", "");
valor1 = valor1.Replace(".", ""); valor1 = valor1.Replace(",", "");
valor2 = valor2.Replace(".", ""); valor2 = valor2.Replace(",", "");
valor1 = valor1.Trim(); valor2 = valor2.Trim();
int.TryParse(valor1, out val1); int.TryParse(valor2, out val2);
int calcular = 0;
if (operacao == 1) { calcular = 1; }
if (operacao == 2) { calcular = 2; }
if (operacao <= 0) { calcular = 3; }
if (operacao == 4) { calcular = 4; }
if (operacao == 5) { calcular = 5; }
switch (calcular)
{
case 1://soma
total = val1 + val2;
break;
case 2://multiplicar
val2 *= operacao;
total = val1 + val2;
break;
case 3://subtrair
if (val1 == val2)
{
total = val1 - val2;
}
else
{
if (val1 > val2)
{
total = val1 - val2;
}
else
{
if (val1 < val2)
{
total = val2 - val1;
}
}
}
break;
case 4://porcentagem
float porcentagem = ((float)val2 / 100) * val1;
total = val1 + (int)porcentagem;
break;
case 5://divisao
float v = (float)val1 / (float)val2;
total = (int)v;
break;
}
saida = total.ToString();
saida = FormataMoeda(saida);
return saida;
}
string FormataMoeda(string corpo)
{
string set = corpo.Replace("$ ", "");
if (set.Length < 22)
{
set = set.Replace("$ ", "");
set = set.Replace(".", "");
set = set.Replace(",", "");
switch (set.Length)
{
case 3:
set = "$ " + set.Substring(0, 1) + "," + set.Substring(1, 2);
umavez = 1;
break;
case 4:
set = "$ " + set.Substring(0, 2) + "," + set.Substring(2, 2);
umavez = 1;
break;
case 5:
set = "$ " + set.Substring(0, 3) + "," + set.Substring(3, 2);
umavez = 1;
break;
case 6:
set = "$ " + set.Substring(0, 1) + "." + set.Substring(1, 3) + "," + set.Substring(4, 2);
umavez = 1;
break;
case 7:
set = "$ " + set.Substring(0, 2) + "." + set.Substring(2, 3) + "," + set.Substring(5, 2);
umavez = 1;
break;
case 8:
set = "$ " + set.Substring(0, 3) + "." + set.Substring(3, 3) + "," + set.Substring(6, 2);
umavez = 1;
break;
case 9:
set = "$ " + set.Substring(0, 1) + "." + set.Substring(1, 3) + "." + set.Substring(4, 3) + "," + set.Substring(7, 2);
umavez = 1;
break;
case 10:
set = "$ " + set.Substring(0, 2) + "." + set.Substring(2, 3) + "." + set.Substring(5, 3) + "," + set.Substring(8, 2);
umavez = 1;
break;
case 11:
set = "$ " + set.Substring(0, 3) + "." + set.Substring(3, 3) + "." + set.Substring(6, 3) + "," + set.Substring(9, 2);
umavez = 1;
break;
case 12:
set = "$ " + set.Substring(0, 1) + "." + set.Substring(1, 3) + "." + set.Substring(4, 3) + "," + set.Substring(7, 3) + "," + set.Substring(10, 2);
umavez = 1;
break;
case 13:
set = "$ " + set.Substring(0, 2) + "." + set.Substring(2, 3) + "." + set.Substring(5, 3) + "." + set.Substring(8, 3) + "," + set.Substring(11, 2);
umavez = 1;
break;
case 14:
set = "$ " + set.Substring(0, 3) + "." + set.Substring(3, 3) + "." + set.Substring(6, 3) + "." + set.Substring(9, 3) + "," + set.Substring(12, 2);
umavez = 1;
break;
///-------------------------------------- Não reconhece 15 caracteres
case 15:
set = "$ " + set.Substring(0, 1) + "." + set.Substring(1, 3) + "." + set.Substring(4, 3) + "." + set.Substring(7, 3) + "." + set.Substring(10, 3) + "," + set.Substring(13, 2);
umavez = 1;
break;
}
}
//-------------------------------------- Não reconhece 15 caracteres
//-------------------------------------- Não reconhece 15 caracteres
return set;
}
//---------------------------------------
//---------------------------------------
}
USE: Currency calculate = new Currency();
Total string = calculate.Calculate("$ 100,00","$ 20,00",1);
I don’t think a code that has a soup of switches, which has a lot of magic numbers, which uses a magic flag and which was developed with enough copy-and-paste is the best way to model itself a currency class.
Browser other questions tagged c# sql-server typing
You are not signed in. Login or sign up in order to post.
Decimal is the easy-to-work field. O Microsoftly correct is: money and smallmoney http://msdn.microsoft.com/pt-br/library/ms179882.aspx
– Dorathoto
Decimal for sure.
– Matheus Miranda