Introduction
This article contains MUST HAVE tables and data for proper multi language development. I highly recommend to use ISO (International Organizations for Standardization) as the primary source of information, because this is the only way to go for high quality products and compatibility. Please remember that ISO usually only manages standards. Behind each ISO standard, there are companies which supply databases and updates to commercial users. Internet is also a good source of information so I've collected globalization data from various resources and placed into 6 tables. If you have some suggestions, then please contact me by email: Evaldas Jocys <evaldas@jocys.com>.
Data for these tables are required to produce full multilingual support:
- Languages - Information about all languages
- Cultures - Information about all cultures (Language + Country)
- Encodings - Information about all encodings
- Ranges - Can help to get customer's culture by IP
- Currencies - Business information about each country
- Currencies by Country- Currencies used in country
Requirements
- Microsoft SQL Server 2005
- Microsoft .NET Framework 3.5
Installation
- Download ZIP file specified above.
- Extract ZIP contents into temp folder.
- Navigate to WebApp\Projects\Globalization\SQL\ folder.
- Open Globalization-SqlDataImport.bat file.
- Go to line 5 and update srv value with your SQL Server name.
- Go to line 6 and update cat value with your Database name.
- Save and close Globalization-SqlDataImport.bat file.
- Execute Globalization-SqlDataImport.bat to recreate [Globalization_*] tables and data.
Note: Import script will ask for database login and password. Hit [Enter] key if you want to use trusted connection.
Using the Code
Archive contains .zip\Engine folder with LINQ to SQL classes and some methods. You can extract content of this folder into your C# project. For example, Engine\Globalization\Ranges.cs file contains two functions...
public long IpToInt(string ipString)
{
System.Net.IPAddress address = System.Net.IPAddress.Parse(ipString);
return (long)BitConverter.ToUInt32(address.GetAddressBytes().Reverse().ToArray(), 0);
}
public string GetCountryByIp(string ipString)
{
Data.GlobalizationDataContext db = new Data.GlobalizationDataContext();
long ip = IpToInt(ipString);
Data.Range range = db.Ranges.SingleOrDefault
(item => item.RangeStart <= ip && ip <= item.RangeEnd);
return (range == null) ? string.Empty : range.CountryA3;
}
So you can get country code of the visitor and redirect him/her to a specific web page:
string ipString = Request.ServerVariables["REMOTE_ADDR"];
string countryA3 =
Engine.AppContext.Current.Globalization.Ranges.GetCountryByIp(ipString);
string language = (countryA3 == "LTU") ? "ltu" : "enu";
System.Web.HttpContext.Current.Response.Redirect("Default_" + language + ".htm", true);
1. Countries
| Id | Name | Data Type | Nulls | Default | Description |
|---|
| 1 | CountryA3 | char(3) | NOT NULL | | ISO 3166-1 Alpha-3 Code. For example: LTU | | 2 | CountryA2 | char(2) | NOT NULL | | ISO 3166-1 Alpha-2 Code. For example: GB | | 3 | ShortName | nvarchar(256) | NOT NULL | | Short Country Name (Pascal Case). For example: United Kingdom | | 4 | FullName | nvarchar(512) | NOT NULL | | Full Name. For example: Republic of South Africa | | 5 | NativeShortName | nvarchar(256) | | | Country Short Native Name. For example: Lietuva | | 6 | NativeFullName | nvarchar(512) | | | Country Full Native Name. For example: Lietuvos Respublika | | 7 | IsoCode | int(4) | NOT NULL | | ISO Code of country | | 8 | IsoName | nvarchar(256) | NOT NULL | '' | ISO 3166-1 Short Name. For example: SOUTH AFRICA | | 9 | DialingCode | int(4) | | | International Dial Code. For example: 44 | | 10 | Remarks | nvarchar(2000) | NOT NULL | '' | ISO 3166-1 Remark. For example: Includes Marion Island, Prince Edward Island | | 11 | IsEnabled | bit(1) | NOT NULL | (1) | Record activity status | | 12 | DateCreated | datetime(8) | NOT NULL | getdate() | Record creation date | | 13 | DateUpdated | datetime(8) | NOT NULL | getdate() | Record update date |
|
|
2. Languages
| Id | Name | Data Type | Nulls | Default | Description |
|---|
| 1 | LanguageA3 | char(3) | NOT NULL | '' | ISO 639-3 Alpha-3 Code. For example: RUS | | 2 | LanguageA2 | char(2) | NOT NULL | '' | SO 639-1 Alpha-2 Code. For example: RU | | 3 | LanguageName | nvarchar(256) | NOT NULL | '' | Language Name (Pascal Case). For example: Russian | | 4 | NativeName | nvarchar(256) | NOT NULL | '' | Native Language Name. For example: Русский | | 5 | Description | nvarchar(2000) | NOT NULL | '' | Description and remarks | | 6 | IsPrimary | bit(1) | NOT NULL | | Primary language | | 7 | IsWellKnown | bit(1) | NOT NULL | | You can use only well known languages: For Example: 1 | | 8 | IsEnabled | bit(1) | NOT NULL | (1) | Record activity status | | 9 | DateCreated | datetime(8) | NOT NULL | getdate() | Record creation date | | 10 | DateUpdated | datetime(8) | NOT NULL | getdate() | Record modification date |
|
|
3. Cultures
| Id | Name | Data Type | Nulls | Default | Description |
|---|
| 1 | CultureCode | varchar(20) | NOT NULL | | Abbreviated culture name. Example: mk-MK | | 2 | ParentCultureCode | varchar(20) | | | Parent abbreviated culture name. Example: mk-MK | | 3 | CultureName | nvarchar(256) | | | Culture Name | | 4 | NativeName | nvarchar(256) | | | Native culture name. | | 5 | AbbreviatedIsoName | char(3) | | | ISO/CD 639-5 Alpha-3 code: Example: mkd | | 6 | AbbreviatedWindowsName | char(3) | | | Abbreviated windows name. Example: MKI | | 7 | CodePageAnsi | int(4) | | | ANSI code page. Example: 1251 | | 8 | CodePageEbcdic | int(4) | | | EBCDI code page. Example: 500 | | 9 | CodePageOem | int(4) | | | OEM code page. Example: 866 | | 10 | CodePageMac | int(4) | | | MAC code page. Example: 10007 | | 11 | IsNeutral | bit(1) | | | Neutral culture indicator | | 12 | DatePattern | nvarchar(40) | | | Date pattern. Example: dd.MM.yyyy | | 13 | TimePattern | nvarchar(40) | | | Time patterns. Example: HH:mm | | 14 | CurrencyPatterns | nvarchar(100) | | | Currency pattern. Example: n $;-n $ | | 15 | CurrencySymbol | nvarchar(20) | | | Currency symbol. Example: ден. | | 16 | FirstDayOfWeek | nvarchar(40) | | | First day of the week. Example: Monday | | 17 | DayNames | nvarchar(512) | | | Day names. Example: недела;понеделник;вторник;... | | 18 | MonthNames | nvarchar(512) | | | Month names. Example: јануари;февруари;март;април;... | | 19 | AbbreviatedDayNames | nvarchar(256) | | | Abbreviated day names. Example: нед;пон;втр;срд;чет;пет;саб | | 20 | AbbreviatedMonthNames | nvarchar(512) | | | Abbreviated month names. Examples: јан;фев;мар;апр;мај;јун;јул;... | | 21 | IsEnabled | bit(1) | NOT NULL | (1) | Record activity status | | 22 | DateCreated | datetime(8) | NOT NULL | getdate() | Record creation date | | 23 | DateUpdated | datetime(8) | NOT NULL | getdate() | Record update date |
|
|
Cultures also called "Language Identifiers" (RFC 3066). Culture represents information about people who speak one language in one region. Culture code represents combination of language and country/region.
4. Encodings
| Id | Name | Data Type | Nulls | Default | Description |
|---|
| 1 | CodePage | int(4) | NOT NULL | | Code page. Example: 21866 | | 2 | EncodingName | nvarchar(256) | NOT NULL | | Encoding name. Example: Cyrillic (KOI8-U) | | 3 | WindowsCodePage | int(4) | | | Windows code page. Example: 1251 | | 4 | IsBrowserDisplay | bit(1) | | | Can be displayed by web browser | | 5 | IsMessageDisplay | bit(1) | | | Can be displayer by mail and news applications | | 6 | HeaderName | nvarchar(100) | | | Header name. Example: koi8-u | | 7 | BodyName | nvarchar(100) | | | Body name to use with messages. Example: koi8-u | | 8 | WebName | nvarchar(100) | | | Web name to use with web browsers: koi8-u | | 9 | IsEnabled | bit(1) | NOT NULL | (1) | Record activity status | | 10 | DateCreated | datetime(8) | NOT NULL | getdate() | Record creation date | | 11 | DateUpdated | datetime(8) | NOT NULL | getdate() | Record update date |
|
|
- Alternative/Free Data Source: Microsoft Windows 2003 Server Internal Resources
5. Ranges
| Id | Name | Data Type | Nulls | Default | Description |
|---|
| 1 | RangeStart | bigint(8) | NOT NULL | (0) | Numeric representation of IP Address: 3560944688 (212.63.180.48) | | 2 | RangeEnd | bigint(8) | NOT NULL | (0) | Numeric representation of IP Address: 3560944691 (212.63.180.51) | | 3 | CountryA3 | char(3) | | '' | ISO 3166-1 Alpha-3 Code. For example: FRA (France) | | 4 | IsEnabled | bit(1) | NOT NULL | (1) | Record activity status | | 5 | DateCreated | datetime(8) | NOT NULL | getdate() | Record creation date | | 6 | DateUpdated | datetime(8) | NOT NULL | getdate() | Record modification date |
|
|
This database will help automatically detect user country and then language by IP or by domain of email. Culture can be detected by using email encoding.
IP To Country Database
6. Currencies
| Id | Name | Data Type | Nulls | Default | Description |
|---|
| 1 | CurrencyA3 | char(3) | NOT NULL | '' | Currency Alpha-3 Code. Example: GBP (Pound) | | 2 | CurrencyName | nvarchar(256) | NOT NULL | '' | Currency Name. Example: Pound | | 3 | NativeName | nvarchar(256) | NOT NULL | '' | Native name of the currency | | 4 | CurrencySymbol | nvarchar(6) | NOT NULL | '' | Currency symbol. Example: £ | | 5 | CurrencyCode | int(4) | NOT NULL | (0) | ISO 4217 Currency Code. | | 6 | CountryA3 | char(3) | | '' | ISO 3166-1 Alpha-3 Country Code. Example: UK (United Kingdom) | | 7 | IsEnabled | bit(1) | NOT NULL | (1) | Record activity status | | 8 | DateCreated | datetime(8) | NOT NULL | getdate() | Record creation date | | 9 | DateUpdated | datetime(8) | NOT NULL | getdate() | Record update date |
|
|
Provides: Latest information about currency info in each country with all latest exchange rates.
7. Currencies by Country
| Id | Name | Data Type | Nulls | Default | Description |
|---|
| 1 | RecordId | uniqueidentifier(16) | NOT NULL | newid() | HMAC-MD5 Checksum of UTF-8: Key="Currency", Value=[CurrencyA3],[CountryA3] | | 2 | CountryA3 | char(3) | NOT NULL | | ISO 3166-1 Alpha-3 Country Code. Example: UK (United Kingdom) | | 3 | CurrencyA3 | char(3) | NOT NULL | | Currency Alpha-3 Code. Example: GBP (Pound) | | 4 | IsPrimary | bit(1) | NOT NULL | | Indicator of primary currency inside country. | | 5 | IntroductionDate | datetime(8) | | | Currency introduction date | | 6 | AbolitionDate | datetime(8) | | | Currency abolition date | | 7 | IsEnabled | bit(1) | NOT NULL | (1) | Record activity status | | 8 | DateCreated | datetime(8) | NOT NULL | getdate() | Record creation date | | 9 | DateUpdated | datetime(8) | NOT NULL | getdate() | Record modification date |
|
|
Provides: Latest information about currency info in each country.
