0

I have a problem while reading my DataBase. Until now I was sure that when I read some doubles from MySQL (they are stored as doubles not as strings / varchar), they are returned with a dot as decimal separator. (10.5). But now I notice that in fact, they are returned with a coma?

I lately created a new table (one month ago), but just now I noticed that its double values are returned with a comma , instead of the dot ..

I checked and the tables are all in uff8_unicode_ci.

I don't understand because I was sure that , could not be used in MySQL, also checked that on that link.

Nota : This is maybe due to the fact that I use several computers for tests, some are in French, some in Russian?

Edit :

To edit the table I do the following :

string request = "UPDATE [vegasteel].consumables SET ID_MACHINE=" + this.idMachine + ",GROUPE="PLASMA",TYPE=\'\',DETAIL=\'14\',SPEED=10.5,TIME=5.5,COST=0.5 WHERE ID=" + this.id;
DataBase.Update(request);

Then to read it :

if (this.OpenConnection() == true)
{
    IDataReader dataReader = ExecuteReader(query);
    while (dataReader.Read())
    {
        Consumable consumable = new Consumable();
        consumable.ID = Convert.ToInt64(dataReader["ID"].ToString());
        consumable.IdMachine = Convert.ToInt64(dataReader["ID_MACHINE"].ToString());
        consumable.Groupe = dataReader["GROUPE"].ToString();
        consumable.Type = dataReader["TYPE"].ToString();
        consumable.Detail = dataReader["DETAIL"].ToString();
        if(consumable.Groupe=="PLASMA")
        {
            string toto = "";
        }
        consumable.Speed = Global.ConvertToDouble(dataReader["SPEED"].ToString());
        consumable.Time = Global.ConvertToDouble(dataReader["TIME"].ToString());
        consumable.Cost = Global.ConvertToDouble(dataReader["COST"].ToString());
        list.Add(consumable);
    }
    this.CloseConnection();
}

the Global.ConvertToDouble is a function I made to add a comma or a dot regarding the current culture. But the problem is when I read it, dataReader["COST"] equals 0,5, and not 0.5

Edit 2 :

As asked, here is Global.ConvertToDouble function, but as I explained the problem is before that function, as comma is sent instead of dot to the function.

public static double ConvertToDouble(this string strToParse, char decimalSymbol = '.')
{
    if (strToParse.Contains('E'))
    {
        strToParse = "0";
    }
    string tmp = Regex.Match(strToParse, @"([-]?[0-9]+)([\s])?([0-9]+)?[." + decimalSymbol + "]?([0-9 ]+)?([0-9]+)?").Value;

    if (tmp.Length > 0 && strToParse.Contains(tmp))
    {
        var currDecSeparator = System.Windows.Forms.Application.CurrentCulture.NumberFormat.NumberDecimalSeparator;

        tmp = tmp.Replace(".", currDecSeparator).Replace(decimalSymbol.ToString(), currDecSeparator);
        return double.Parse(tmp);
    }

    return double.NegativeInfinity;
}
Siegfried.V
  • 1,508
  • 1
  • 16
  • 34
  • Comments are not for extended discussion; this conversation has been [moved to chat](https://chat.stackoverflow.com/rooms/201177/discussion-on-question-by-siegfried-v-how-do-i-read-a-double-column-from-mysql-a). – Samuel Liew Oct 20 '19 at 23:40
  • Possible duplicate of [C# doubles show comma instead of period](https://stackoverflow.com/questions/518625/c-sharp-doubles-show-comma-instead-of-period) – nl-x Oct 21 '19 at 06:13
  • @nl-x The problem was for reading MySQL doubles. In fact the problem was just I did it the wrong way (using ToString). But doing as tymtam wrote, this solved the problem. I also rewrote all of my code modifying it also for bool, int and longs, as I just did it not correctly from the beginning... – Siegfried.V Oct 21 '19 at 06:19

1 Answers1

2

You should not be converting the columns to string and then parsing them. You should read them directly as the type they are.

consumable.Speed = (double) dataReader["SPEED"];

or

consumable.Speed = Convert.ToDouble(dataReader["SPEED"]);

Note on null handling

(Thanks @mjwills for mentioning this)

The results from the db may contain null values and there are many ways of handling nulls in results with IDataReader (you can see SQL Data Reader - handling Null column values or Null safe way to get values from an IDataReader or search the broader web).

For example you will need to decide if you want null or default value (0 for double) when the return value is null.

To get you started here's a helper method (which can easily be changed into a extension method, and maybe even made generic so that you can deal with ints and other types).

public static double GetNotNullExample(IDataReader reader, string colName, double valueIfNull)
{
    var colIndex = reader.GetOrdinal(colName);
    if (!reader.IsDBNull(colIndex))
        return reader.GetDouble(colIndex);
    else
        return valueIfNull;
}

What's happening

After you reinstalled your computer the culture has changed. The following snippet shows the impact of the culture on the ToString method.

double pie = 3.14; // ;)

CultureInfo.CurrentCulture = new CultureInfo("en-US", false);
Console.WriteLine(pie.ToString());
Console.WriteLine(pie.ToString(CultureInfo.InvariantCulture));

CultureInfo.CurrentCulture = new CultureInfo("ru-RU", false);
Console.WriteLine(pie.ToString());
Console.WriteLine(pie.ToString(CultureInfo.InvariantCulture));

Output

3.14
3.14
3,14
3.14

But I still want to ToString() and then parse

  1. Don't.

  2. If you were to continue ToString()->Parse approach, you would have to ensure that ToString is called with InvariantCuture.

consumable.Speed = Global.ConvertToDouble(dataReader["SPEED"]
                   .ToString(CultureInfo.InvariantCulture));
Community
  • 1
  • 1
tymtam
  • 31,798
  • 8
  • 86
  • 126