How to use same database and same program for two different locales in .NET

Question

I have a C# program which uses a SQL Server database.I am already using it in a country that uses . as decimal separator. Now I want to use it in another country that uses , as decimal separator.

in C# is there some application level setting that I can change or write some code so that I can use the same database and the same program ? or do I have to change my entire code to handle this new decimal separator?

I dont know how this works.Basically I think there would be problems in My Sql Queries. example say one of my existing statements is

insert into tblproducts(productId,Price) values('A12',24.10)

now in new country it will become

insert into tblproducts(productId,Price) values('A12',24,10)

this will raise an error

so do I have to change whole code to handle this situation ?

Thank you

Answer 1

you can do a couple of things to fix this.

first, if you are taking values in from the interface, then you are casting these values to a decimal. Decimal.parse is a culture dependant function and will use the current culture to parse values. Therefore if the CurrentCulture uses commas as decimal separators then your cast will work properly. Then when you output the value from your variable, you can specify the decimal.ToString format to always output using a period as the separator.

Oh, forgot to add. You could also change your parsing to indicate Currency, which allows commas as well as $ signs. for example: decimal.parse(amount, NumberStyles.Currency)

Answer 2

In your global.asax.vb file, you can set the culture of the current page load:

Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US")
Thread.CurrentThread.CurrentUICulture = Thread.CurrentThread.CurrentCulture

This will make all the culture-aware functionality work nicely. e.g., (5000.25).ToString() will use commas vs. periods depending on whatever culture you set. Also, reading a inputs from the user into a numeric type will be parsed according to their culture rules. Dates will be displayed properly (12/9/08 vs. 9/12/08). You get all that basically for free.

This obviously causes problems when talking to other systems that are expecting everything in the same culture. To solve this, you write your queries with the invariant culture:

(5000.25).ToString(CultureInfo.InvariantCulture)

This will explicitly set that output to something that Mysql can get along with.

Note: if you have a proper data layer and you pass numeric types into it, you can probably avoid a lot of this mess.

Answer 3

If you built the query using string concatenation, use parameters instead. So instead of writing:

 var query = "insert into tblproducts(productId,Price) values('" + article + "','"
    + price + ')';

use OleDbParameters:

 var query = "insert into tblproducts(productId,Price) values(?,?)"
 var cmd = new OleDbCommand(query, connection);
 cmd.Parameters.Add("@article", OleDbType.VarChar).Value = article;
 cmd.Parameters.Add("@price", OleDbType.Single).Value = price;

This will save you a lot of troubles, including localization issues.

Answer 4

Thank you Xsl,Micheal and Victor for your answers.