c# – Decimal values ​​being rounded in SQLIte

Question:

After migrating my database from Access to SQLite 3 in a Windows Forms application, I've been having difficulties with Decimal values. If I run a simple select:

SELECT * FROM Item WHERE id = 1;

The decimal values ​​of the price_item column display correctly, with 2 decimal places. Example: 2.53 = 2.53. But when I do a slightly more complex query, with related tables like the following:

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 displayed in the DataTable like this: 2,53 = 2,00 , the same happens with the result of the preco_item * quantidade multiplication.

Note: Currency items are set to Decimal(7,2) in SQLite. I've tried several other formats. In the query by SQLite Studio, the price results are presented normally, with the exception of the multiplication which I still haven't found a way to calculate correctly. Always returns an integer value.

PS: Values ​​are inserted into a DataGridView through DataTable.

Update to demonstrate the BD 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's the code that fills the DataGridView:

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

Answer:

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

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;
    }

Code for using the class:

//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 eliminating the zeros after the comma, just apply the "C2" in the DefaultCellStyle.Format in the column.

Scroll to Top