I'm developing a project for commercial automation, using PAF-ECF and everything else.
What would be the best data type to work with SQL Server and C # for money issues? Especially in relation to rounding and truncations.
I imagine the best options are Float , Numeric or Money . But I can not find good reliable sources that fit my case so I can implement it safely.
Friend, I leave here a little of my experience, I work with commercial automation systems using Microsoft SQL Server, and strongly suggest that you use currency fields with type DECIMAL .
In the company where we work we have already used floating-point numbers for money, but we have had serious problems after the values have been processed many times, mainly by modules such as Electronic Invoice, and worse, SPED accounting and fiscal.
With the type DECIMAL we have a lot more control and precision about the values stored.
I suggest these two readings for more details.
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 (reais) you would store 1234 (cents). This is only feasible if you only work with a coin 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 is a good list on this MSDN page .
Use Decimal , as this type will represent descriptive values (that is, exact values ). Monetary values and absolute quantities are examples of this case;
Floating-point types (eg float ) serve to represent continuous values (that is, values that represent an approximation ), or that "tend to "). Examples of "real life" would be: weight, distance, height, etc.
Note that although your scale tells you to weigh 80 kg (say), your actual weight will probably be approximation of that (79.899875499 ....). In these cases floating-point types fall well. Already with money, will not be legal ...
Concept taken from this video by Jon Skeet .
Never use floating point types such as float or double. The representation of these values follows the IEEE_754 standard and were designed exclusively for scientific applications. Financial applications require absolute precision because in an accounting system, for example, no difference can be tolerated in a balance, even if it is a few cents in Billions of Reals, because it causes distrust 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 an implicit error when we use binary arithmetic and convert it to decimal arithmetic (the one we learned in school) that occurs because of the difference in the basis of the two numbering systems.
I 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 in PAF-ECF (both in C #) I used Decimal. Everything is working perfectly. In the case of management systems and PAF-ECF, emphatically do not recommend the separation of cents, which makes the treatment much more difficult, both in the DBMS and in the applications. In summary, in my opinion, use Money in SGBD and Decimal in C #.
As recommended by Martin Fowler in his book Patterns of Enterprise Application Architecture you should use:
You should avoid using any type of floating point as this may cause rounding problems which is what you want to avoid. In calculations you should always take into account the currency type.
I suggest this code for this:
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;
}
//---------------------------------------
//---------------------------------------
}