Zach Zach - 1 year ago 42
SQL Question

Double is not stored correctly in MySQL database

I have an MVC application which has a restful service that updates latitude and longitude to the database. I am sending the values to the API as string and its converted to Double before it is stored in database as Double. Here is the code I am using to convert string to double:

Double _latitude, _longitude;
Double.TryParse(lattitude, NumberStyles.Any,CultureInfo.CurrentCulture, out _latitude);
Double.TryParse(Longtitude, NumberStyles.Any,CultureInfo.CurrentCulture, out _longitude);
catch(Exception e)
_latitude = 0;
_longitude = 0;

When its stored in database I am missing decimal points. For example:

53.345634 is stored as 53345634

I have this working on development environment running on Windows 2012. But when its moved to Windows 2008 production server I am seeing this issue.


The behavior that you observed might be caused by the fact that two machines have different CurrentCultures. Default value of CurrentCulture depends on a version of Windows.

To get consistent results across various machines, you should provide a specific culture for Parse or TryParse method, e.g.

// using System.Globalization;
double value = Double.Parse(stringValue, CultureInfo.InvariantCulture); 


double valueUsEnglish = Double.Parse(stringValue, new CultureInfo("en-US"));

Parse method uses a culture-specific decimal point symbol and group-separator symbol. In some languages (e.g. English) decimal point symbol is a dot whereas in others (e.g. Polish) it's a comma. Group-separator symbols are also different in various languages.

If 53.345634 is parsed as 53345634, your computer uses a culture where dot is a group-separator.

You should also think if you want to use Parse of TryParse - if during normal operation your program expects invalid output (e.g. it a user-provided value), you should definitely use TryParse (which does not throw exceptions) and inspect returned value (TryParse returns a boolean indicating whether parsing was successful). If the value that you are parsing should never be wrong (e.g. you are parsing resources that bounded with your program) you can use Parse and catch appropriate exceptions. According to documentation possible exceptions are: ArgumentNullException, FormatException and OverflowException.