Internationalisation query in SQL SERVER and C#

Question

I have to make an application that will be used in USA which has decimal separator as . and in scandinavia which has decimal separator as , Also scandinavian countries have extra characters like ø æ å etc

I am fairly new to such type of internationlisation. The application and the database will be same for both USA and scandinavia.

I need your help with these questions:-

1. What type of collation should I use in the database ?

2. In the front end ( C#) Is it possible that in USA I can show the numeric values displayed as 100.00 and in scandinavia to show them as 100,00 and somehow make sure that the calculations and saving to the database works without any problem?

Thank you

Answer 1

Make all your string data types are NVARCHAR (or NCHAR if fixed length) so that you support unicode characters.

Since you say that your 'database' will be the same, do you mean that the server is the same physical instance, or just the same schema?

If you will have a separate server for USA to Scandinavia, this means you just set your SQL server collation and your Windows Server localization settings to USA or Scandinavia, you will need to test your solution under both environments continuously through development to ensure than any error doesn't propagate for too long unnoticed.

It's also possible to have a single database instance and then just change the localization at the WS / application side, for example this means you pass all times and numbers as USA format. But you display the values as either USA or Scandinavia format, if you're a installed (and not a web-app) you could run this control off the user's local computer localization setting which should do most of the heavy lifting for you without a problem.

eg: DateTime.Now.ToString() will report "16:34 14/11/2011" for one localization setting but "16:34 11/14/2011" for USA localization, internally the data is the same.

Lastly, if you are using a single central database, make sure you save your dates as UTC time rather than local time, otherwise you will be ignoring timezone differences between the data.

Answer 2

1. You can choose a collation here Selecting a SQL Server Collation there are scandinavian collations.

2. The decimal delimeter depends on Windows settings and will not affect your calculations.

You will have some problems with converting character expressions to a numeric data type.

Data Type Conversion:

Character expressions that are being converted to an exact numeric data type must consist of digits, a decimal point, and an optional plus (+) or minus (-). Leading blanks are ignored. Comma separators, such as the thousands separator in 123,456.00, are not allowed in the string.

Answer 3

In SQL Server, collation is used for operations such as sorting on text data, so I don't think that decision would affect currency etc.

From .NET front-end (and sql back-end), you have to choose correct data type (such as Decimal, DateTime) for manipulating the data - for display & input purpose, you can do locale specific formatting (e.g. see currency format specifier in numeric formatting string). You can specify the format in functions such as Decimal.ToString or String.Format. These will use your current locale but you can have overrides to specify locale specific format providers. Similarly you have Parse method to convert from string to actual data type.