Decimal values being rounded in SQLIte

Question

Decimal values being rounded in SQLIte

Navigation

#1 by (2 votes)

3

After migrating my Access database to SQLite 3 in a Windows Forms application, I have been having difficulties with values of type Decimal. If you run a simple select:

SELECT * FROM Item WHERE id = 1;

The decimal values in the preco_item column display correctly, with 2 decimal places. Example: 2.53 = 2.53. But when I make a query a little more complex, with related tables as follows:

SELECT Produto.nome AS Produto, 
                Item.quantidade AS Quantidade, 
                Item.preco_item AS Preço,     
                Item.preco_item * Item.quantidade as Subtotal,               
                Item.id  
            FROM Item  
            INNER JOIN Orcamento ON Item.id_orcam = Orcamento.id   
            INNER JOIN Produto ON Item.id_produto = Produto.id   
            WHERE(Orcamento.id = 1 );

The results obtained in the Price line are presented in DataTable as: 2,53 = 2,00 , so it also happens with the preco_item * quantidade multiplication result.

Note: Monetary items are set to Decimal (7.2) in SQLite. I've tried several other formats. In the query by SQLite Studio, the price results appear normally, with the exception of the multiplication that I still did not find a way to calculate correctly. It always returns an integer value.

Q.: Values are inserted into a DataGridView via DataTable.

Update to demonstrate the DB schema:

private static string createQuery()
{
        string createScript = "CREATE TABLE Cliente (" +
            " id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
            " nome       TEXT NOT NULL UNIQUE," +
            " endereco   TEXT," +
            " local      TEXT," +
            " telefone_1 TEXT," +
            " telefone_2 TEXT ); ";
        createScript += "CREATE TABLE Produto (" +
             " id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
             " nome  TEXT NOT NULL UNIQUE," +
             " preco DECIMAL ); ";
        createScript += "CREATE TABLE Tipo (" + 
            " id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"  + 
            " nome      TEXT," + 
            " descricao TEXT ); ";
        createScript += "CREATE TABLE Orcamento (" +
            " id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," +
            " id_cliente INTEGER REFERENCES Cliente (id)," +
            " data DATETIME," +
            " id_tipo INTEGER REFERENCES Tipo(id)," +
            " desconto DECIMAL," +
            " status TEXT DEFAULT Pendente ); ";
        createScript += "CREATE TABLE Item (" +
            " id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
            " id_orcam INTEGER REFERENCES Orcamento (id)," + 
            " id_produto INTEGER REFERENCES Produto(id)," +
            " preco_item DECIMAL," + 
            " quantidade INTEGER NOT NULL ); ";

        return createScript;
}

Here is the code that populates the DataGridView:

string query = Queries.GetItemsByOrcId(orcId);
sql.CustomSelect(query);
dgvpProd.DataSource = sql.Datatable;

c# sqlite3

asked by anonymous 18.12.2016 / 19:39

1 answer

Form Validation with Javascript Call a method using Spinner selection

score 2 · Answer 1

I got a solution based on the suggestions of colleagues. The monetary value fields were created as INTEGER and I handled the entire decimal formatting via code in the application. The class structure I created allowed me to convert the values into the DataTable before arriving at the application's dataGridview. Here is the main class method:

public static DataTable dtColumnIntToDecimal(DataTable dt, int column)
    {
        List<decimal> price = new List<decimal>();
        //Gerar lista de valores em decimal
        foreach (DataRow row in dt.Rows)
        {                
            decimal aux = Convert.ToDecimal((long)row[column]) / 100;
            price.Add(aux);     
        }

        //clonar o datatable, transformando a coluna em decimal
        DataTable dtCloned = dt.Clone();
        dtCloned.Columns[column].DataType = typeof(decimal);
        foreach (DataRow row in dt.Rows)
        {
            dtCloned.ImportRow(row);
        }

        //Inserir valores em decimal
        for (int rowIndex = 0; rowIndex < dt.Rows.Count; rowIndex++)
        {
            dtCloned.Rows[rowIndex][column] = price[rowIndex];
            Console.WriteLine(dtCloned.Rows[rowIndex][column]);
        }

        //atualiza o total de valores (opcional) 
        valorTotal = price.Sum();
        //Retorna o DataTable
        return dtCloned;
    }

Class Use Code:

//Minha consulta
sql.Select("Produto", tbConsulta.Text);                        
//DataTable retornado
DataTable dt = Util.dtColumnIntToDecimal(sql.Datatable, 2);
//Aplicação do DataTable ao DataGridView
dataGridView1.DataSource = dt;

... and for the currency format to appear correctly without deleting the zeros after the comma, simply apply "C2" to the DefaultCellStyle.Format in the column.