C＃浮点数在插入mysql数据库后丢失了小数

Question

I've got a really weird problem here !

I've written a C# console app to get data from predefined IP addresses, after the calculations, I want the data to be inserted to a MySQL database so I can read it and do more calculations if needed using a PHP web application,

on my lap top everything is working perfectly,

when I run the C# app on the server, the data is being handled perfectly until the last step which is inserting it, but when it inserts the data, it's like an integer, no decimals.

I used VS Studio tools and tracked my variables, they are correct even in the sql statements but on the database they're losing their decimals.

I'm using windows 10 on my laptop and the server has windows Server 2016

here's the C# code :

public static string send_to_db(int uid, float prs, float btlvl, float f1, float f2, double t1, double t2, int dState, int supState, string datetime, string stmt)
    {
        MySqlConnection connection = null;
        string feedback = "";
        double min_prs_val = 0, max_prs_val = 0;

        // voltage to percentage the btlvl

        btlvl = (btlvl * 100) / 12;

        try
        {
            connection = new MySqlConnection(stmt);
            connection.Open();
            MySqlCommand insertData = new MySqlCommand($"INSERT INTO pm_data_records (pm_detail_id, pm_pressure, pm_battery_charge, pm_flow1, pm_flow2, pm_tot1, pm_tot2, pm_door, pm_sup, pm_sent_time) VALUES('{uid}','{prs}','{btlvl}','{f1}','{f2}','{t1}','{t2}','{dState}','{supState}','{datetime}')", connection);
            insertData.ExecuteNonQuery();
            MySqlCommand updateData = new MySqlCommand($"UPDATE pm_data_last SET pm_detail_id = '{uid}', pm_pressure = '{prs}', pm_battery_charge = '{btlvl}', pm_flow1 = '{f1}', pm_flow2 = '{f2}', pm_tot1 = '{t1}', pm_tot2 = '{t2}', pm_door = '{dState}', pm_sup = '{supState}', pm_sent_time = '{datetime}' WHERE pm_detail_id = '{uid}'", connection);
            updateData.ExecuteNonQuery();

            ...

I installed visual studio on the server and copied the code from my laptop and compiled it there too (after installing the MySQL for vs files) and still no decimals,

I'm outta ideas, help me plz ! tnx

Answer 1

1. You should never build query strings with parameters using the string interpolation like this.

   • This is security issue;
   • Your values may not be properly formatted and escaped. So maybe the problem is there actually.

You should do something like this:

using (var command = new MySqlCommand('INSERT INTO pm_data_records (pm_detail_id, pm_pressure, pm_battery_charge, pm_flow1, pm_flow2, pm_tot1, pm_tot2, pm_door, pm_sup, pm_sent_time) VALUES (@uid, @prs, @btlvl, @f1, @f2, @t1, @t2, @dState, @supState, @datetime)', connection))
{
    command.Parameters.AddWithValue("@uid", uid);
    command.Parameters.AddWithValue("@prs", prs);
    command.Parameters.AddWithValue("@btlvl", btlvl);
    ...
    command.ExecuteNonQuery();
}

1. Check data types of columns in your database. Maybe they do not allow floating-point numbers.